How to use a procedure in Alpha V12

[Atta]

I am very confused in how to use a procedure in alpha5 V12 (MS SQL)

I would love to see an example in how to do this.

Let's say that you have a button that you can push and it runs a procedure in Sql 2008 /2012 where a customer wants to know how many of product X is still available.

Can someone point me to the write spot how to do that?
I know how to write a trigger, but do not know how to write a procedure that I can call within a grid, and get a variable back from SQL 2008/2012

[kiwibruce]

Re: How to use a procedure in Alpha V12

Do you mean a Stored Procedure?
Hmm I have been struggling with that since V11 You have the ability to call a SP instead of using a Select statement but I have had very very limited success with it. but to be honest that is probably more due to my lack of knowledge and my lack of effort. In 2 cases I ended putting it in the too hard basket and instead building views and using them.

In Help Videos look for
Version 10 video 78 "Customizing SQL Insert, Update and Delete Statements - Using stored Procedures to Update your Database"
or video SSE12

There have been many other forum threads on this topic but not sure anyone has found using SPs easy to use, I haven't! and I used them heavily when programming in Access but so have have shied away from them in A5.

Bruce

[kiwibruce]

Re: How to use a procedure in Alpha V12

Here is one thread I remember and Selwyn jumped in and did a video.
http://msgboard.alphasoftware.com/al...nSQLCountQuery

[christappan]

Re: How to use a procedure in Alpha V12

in XBasic (if I understand what you're trying to do, it'd be in an Ajax Callback) using MS SQL Server, you'd just use:

dim cn as sql::connection
cn.PortableSQLEnabled = .f.
cn.Open("::Name::MyNamedConnection")
dim args as sql::arguments

args.set("arg1",arg1value)
args.set("arg2",arg2value)

cn.Execute("EXEC MyStoredProcedure(:arg1,:arg2)",args)

result = cn.ResultSet.data("ColumnName")

For creating the stored procedure itself, you'd need to Google MS SQL Server stored procedure.

I'm thinking though that depending on how complex the query, you may just be able to use a query in the Ajax Callback without having to use MS SQL Server stored procedures.

[Atta]

Re: How to use a procedure in Alpha V12

Sorry for my stupidity but here are some questions:
Where are you getting the arg1value and arg2value? Is that information from a grid in alpha5?
In the last line you have "ColumnName" I do not understand what that does there? Is that the information that you return.

Thanks

[christappan]

Re: How to use a procedure in Alpha V12

No worries.

The arg1value and arg2value are just placeholder variables in my example, and the "ColumnName" is a placeholder value too. you would set them to a value in XBasic, either from e.dataSubmitted or a session variable or whatever's applicable to the particular XBasic function. Those are fairly well explained in the default comments in each event or prototype functions.

So you'll have to Google MS SQL Server stored procedures to build one with the right syntax, but it's something similar to this MySQL procedure:

DELIMITER $$
CREATE
    PROCEDURE `cns_dbo`.`Sample`(vEmpID INT)
    BEGIN
	SELECT Emp_Last_N FROM employee WHERE Emp_ID = vEmpID;
    END$$
DELIMITER ;

Then in MySQL, for employee ID 16 you would call that procedure this way:

CALL Sample(16);

(I do know in MSSQL it's EXEC rather than CALL)
and it would return a result set with the column Emp Last_N.

So in Alpha Five it works just like a regular SQL query from Xbasic, producing a result set just like it would if you just ran the query directly.
This

dim cn as sql::connection
cn.PortableSQLEnabled = .f.
cn.Open("::Name::MyNamedConnection")
dim args as sql::arguments

args.set("arg1",session.ulink)

cn.Execute("EXEC Sample(:arg1)",args)

lastname = cn.ResultSet.data("Emp_Last_N")

would be the same thing as this

dim cn as sql::connection
cn.PortableSQLEnabled = .f.
cn.Open("::Name::MyNamedConnection")
dim args as sql::arguments

args.set("arg1",session.ulink)

cn.Execute("SELECT Emp_Last_N FROM employee WHERE Emp_ID = :arg1",args)

lastname = cn.ResultSet.data("Emp_Last_N")

Edit: please note there is lots of exception/error handling that isn't included in the basics here. You can look at documentation on handling resultsets properly.