Error when passing parameters to TSQLQuery object
lazarus / 16-Databases

----------------------------------------------------------------------
georges · 2016-09-28 20:20

Hello

I am getting an error when passing parameters to a TSQLQuery object.

The following code works without parameters:

//--------- begin code
begin
   Form2.SQLQuery2.SQL.Clear;
   Form2.SQLQuery2.SQL.Add('update USR_CUST_FIN_CHRG');
   Form2.SQLQuery2.SQL.Add('set AMT_PAST_DUE = 110');
   Form2.SQLQuery2.SQL.Add('where CUST_NO = ' + '''1435''');

   Form2.SQLQuery2.ExecSQL;
   Form2.SQLTransaction2.Commit;
end
//--------- end code



I change the code to work with parameters such as below:

//--------- begin code
var
   v_cust_no: string[15];
   v_amt_past_due: real;

begin
   v_cust_no:= '1435';
   v_amt_past_due:= 110;

   Form2.SQLQuery2.SQL.Clear;
   Form2.SQLQuery2.SQL.Add('update USR_CUST_FIN_CHRG');
   Form2.SQLQuery2.SQL.Add('set AMT_PAST_DUE = :amt_past_due');
   Form2.SQLQuery2.SQL.Add('where CUST_NO = :cust_no');

   Form2.SQLQuery2.ParseSQL := true;
   Form2.SQLQuery2.Params.ParamByName('amt_past_due').AsFloat :=
v_amt_past_due;
   Form2.SQLQuery2.Params.ParamByName('cust_no').AsString :=
v_cust_no;

   Form2.SQLQuery2.ExecSQL;
   Form2.SQLTransaction2.Commit;
end
//--------- end code


When I run the code, I get the following error:

Project project1 raised an exception class 'EDatabaseError' with
message:
Parameter "amt_past_due" not found

At address 54ADAC

What am I doing wrong?

Regards,

----------------------------------------------------------------------
LacaK · 2016-09-29 05:40

What value has Form2.SQLQuery2.ParamCheck ?
(is it True ?)

----------------------------------------------------------------------
georges · 2016-09-29 11:38

Hello LacaK

Thanks for your input.   The value of Form2.SQLQuery2.ParamCheck is
true.

Regards,

----------------------------------------------------------------------
rvk · 2016-09-29 12:08

Can you change your code like this and tell us the result?
(adding line 2-4)
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  
Form2.SQLQuery2.ParseSQL := true; 
Showmessage(inttostr(Form2.SQLQuery2.Params.Count));  if
Form2.SQLQuery2.Params.Count > 0 then   
Showmessage(Form2.SQLQuery2.Params[0].Name);

----------------------------------------------------------------------
georges · 2016-09-29 12:25

Hi Rvk

The result for line 2
"Showmessage(inttostr(Form2.SQLQuery2.Params.Count));" is 0. 

I also added the code "ShowMessage(Form2.SQLQuery2.SQL.Text);" and the
result was

update USR_CUST_FIN_CHRG
set AMT_PAST_DUE = :amt_past_due
where CUST_NO = :cust_no


I can't see why the parameter count is zero.

Regards,

----------------------------------------------------------------------
rvk · 2016-09-29 12:51

Yeah, the only reason I can think of is what LacaK already suggested.

Could you try adding ParamCheck := true before setting the SQL just to
be sure.

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  
Form2.SQLQuery2.ParamCheck := true;  Form2.SQLQuery2.SQL.Clear; 
Form2.SQLQuery2.SQL.Add('update USR_CUST_FIN_CHRG'); 
Form2.SQLQuery2.SQL.Add('set AMT_PAST_DUE = :amt_past_due'); 
Form2.SQLQuery2.SQL.Add('where CUST_NO = :cust_no'); 
Form2.SQLQuery2.ParseSQL := true; 
Showmessage(inttostr(Form2.SQLQuery2.Params.Count));   if
Form2.SQLQuery2.Params.Count > 0 then   
 Showmessage(Form2.SQLQuery2.Params[0].Name);
Otherwise you would need to make a reproducible test-project.

----------------------------------------------------------------------
georges · 2016-09-29 14:16

Hello Rvk

I made a new project from scratch and got it to work.   

Thanks for your assistance.   Also, thanks to LacaK.

Regards,