Parameters for Stored Procedures
lazarus / 16-Databases

----------------------------------------------------------------------
magu · 2015-11-05 00:08

Hi,
Having used Lazarus and Free Pascal for a few months now, I believe
that the database features (sqldb) lack the facility of using
parameters with stored procedures. That is, I haven't been able to use
the parameters facility when using the 'CALL' statement in sql.
Instead I have had to create my own functions to use parameters in
these instances:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Function
SetSqlSpParam (strSp: string; spParameter: string; strVariable :
string; blUseNull : boolean) : string; var strTemp : String;begin 
strTemp := Trim(strVariable);   if blUseNull AND (strTemp = '') Then 
 strTemp := 'NULL'  Else  begin    strTemp := StrSqlSafe(strTemp); 
end;   result := StringReplace(strSp, spParameter,
strTemp,[rfIgnoreCase]);end;   Function StrSqlSafe (strString :
String) : String;{uses AnsiQuotedStr to safely quote the singlequote
(') in a string.}var  P : PChar;Begin P:=Pchar(StrString); Result:=Ans
iQuotedStr(P,chr(39));End; 
I then use the above functions in the following manner:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
sqlUpdateCode := SetSqlSpParam ('CALL
sqlStoredProcedure(:[stMyParam])', ':[stMyParam]', deFolder.text,
True);Query.SQL.Clear;Query.SQL.Add(sqlUpdateCode);Query.Open 
I was wondering if I am missing something in sqldb or if any of you
know of a better system to do this.

Thanks

----------------------------------------------------------------------
balazsszekely · 2015-11-05 04:29

Hi magu,

The sql text may depend on the database server. You should modify it
accordingly.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
Query.SQL.Clear;  Query.SQL.Text := 'call sqlStoredProcedure(:param1,
:param2)';  Query.Params.BeginUpdate; 
Query.Params.ParamByName('param1').AsInteger := 25; 
Query.Params.ParamValues['param2'] := null;  Query.Params.EndUpdate; 
Query.ExecSQL;
1. If the store procedure returns something then the SQL text should
look like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.SQL.Text :=  'select * from sqlStoredProcedure(:param1,
:param2)';Query.Open;
2. If you only want to execute the procedure use:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.SQL.Text :=  'call sqlStoredProcedure(:param1,
:param2)';Query.ExecSQL;
regards,
GetMem

----------------------------------------------------------------------
magu · 2015-11-05 22:19

Thanks GetMem, it worked like a charm!

Beginupdate and EndUpdate seem to have made all the difference,
because I had tried to do something similar before.

PS1 : I couldn't find the definition for params.beginupdate and
endupdate on the CCR documentation (http://lazarus-
ccr.sourceforge.net/) could it be that this needs updating?

regards,
Magu

----------------------------------------------------------------------
balazsszekely · 2015-11-06 04:27

BeginUpdate prevents the sql to be parsed each time you change a
parameter. So in in essence BeginUpdate/EndUpdate will speed up
things.