Using Stored Procedures

[sparkey]

I looked in the What's new in V9 and have not found any information on how to execute a stored procedure within a firebird sql database or any other.

Is there any available info on this ?

[Steve Workings]

Re: Using Stored Procedures

I've been running stored procedures against Firebird with version 8. Here's one that I generalized a bit for this example. The fldlist and valuelist variables are, of course, created before this:

dim conn as ole::adodb.connection
dim rs as ole::adodb.recordset
rs.CursorType = 3
conn.open("MyConnectionStringHere")
sql = "INSERT INTO USERS (" + fldlist + ") VALUES (" + valuelist + ")"
Success_Flag = rs.open(sql, conn)
conn.close()

[Raymond Lyons]

Re: Using Stored Procedures

Also, v8 had (and I am pretty sure v9 does too) a genie that would call a stored procedure. But note that for mysql one had to manually put "call" in front of the name of the stored procedure. The "call" is probably only necessary for mysql.

Ray

[Raymond Lyons]

Re: Using Stored Procedures

On second thought, I suppose I should have been a bit more specific. I meant there is in V8 an xabasic script genie (same thing for action scripting) under SQL (using AlphaDAO).

Edit: I was going to put a screen capture in here but I guess I am too ignorant (or lazy) to figure out how to do that. Sorry.

[sparkey]

Re: Using Stored Procedures

Ok,

Thanks to you guys, I was able to find the unique code to run a stored procedure in firebird: EXECUTE PROCEDURE and have a local ask variable passed into the procedure to query on the correct information.

However, in using an ask variable, I am having the dialog box pop up twice....The first time it pops up and I enter in data, it seems to rerun its self with the default entry reset to its initialized value.

The section in blue is the troubled section. It can be tested independently of the remained of code. Anyone know why it would do this ?

Source code below:
'--------------------------------------------------------------------------

'Date Created: 26-Mar-2008 04:56:56 PM
'Last Updated: 26-Mar-2008 05:19:42 PM
'Created By : Mark Liermann
'Updated By : Mark Liermann

'Dialog prep: Set prompts and dimension variables
dim prmpt_title as c
dim prmpt_prompt as c
dim prmpt_default as c
prmpt_title = "VendorID"
prmpt_prompt = "Enter the Vendor ID to find:"
prmpt_default = "0"


'DIM a connection variable to a Firebird database
DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::FIREBIRD")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .f.

'Dim a SQL arguments object, create arguments and set their values
DIM args as sql::arguments
args.add("UID",convert_type(a5_eval_expression("=val(ui_get_number(prmpt_title,prmpt_prompt,prmpt_default))",local_variables()),"N"))

'Execute a stored procedure to select a vendor
dim sqlStatement as c
sqlStatement = <<%sql%
EXECUTE PROCEDURE AP_VENDORS_SELECT :UID
%sql%

'Execute the Query
flagResult = cn.Execute(sqlStatement,args)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet

'The sql_resultset_preview() function opens a window showing a table with the results
'of your query. Show a maximum of 1,000 rows of data
'Note that the connection object is passed into the sql_resultset_preview() function so
'that the function can immediately release the resultset, thereby releasing any locks
'on the database that some databases might have applied.
sql_resultset_preview(rs,1000,"Preview Query Results",cn)
'Now, close the connection
cn.close()

[Raymond Lyons]

Re: Using Stored Procedures

Try setting a variable to = ui_get_number(prmpt_title,prmpt_prompt,prmpt_default)
farther up in your script and then plug the variable into your arg.add() line.

[sparkey]

Re: Using Stored Procedures

I should have clarified, but I did that already and then it works fine. I guess I am curious why it repeats when I merge the ask within the sql arg definition..........