TSQLQuery for mysql5 stored procedure
lazarus / 16-Databases

----------------------------------------------------------------------
senglit · 2022-03-16 15:26

hi all,

I have a store procedure in mysql5.7 which works fine with MySQL
workbench.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
DEFINER=`root`@`%` PROCEDURE `userlogin`(IN in_uname VARCHAR(255), IN
in_passwd VARCHAR(255),        OUT o_uid INT signed, OUT o_did INT
signed,        OUT o_rs BOOLEAN,OUT o_rd BOOLEAN,OUT o_rr BOOLEAN,OUT
o_ru BOOLEAN,o_re BOOLEAN)BEGIN    SELECT uid,did,rolesysoverview,role
dataoverview,rolereports,roleusers,roleequipments        INTO
o_uid,o_did,o_rs,o_rd,o_rr,o_ru,o_re        FROM users WHERE
users.uname=in_uname AND users.upasswd=in_passwd;    IF o_uid IS NULL
THEN        SET o_uid=-1;        SET o_did=-1;        SET o_rs=FALSE; 
      SET o_rd=FALSE;        SET o_rr=FALSE;        SET o_ru=FALSE;  
    SET o_re=FALSE;             END IF;END I can run this sql to get
the correct result:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CALL
`userlogin`('root','123456',@uid,@did,@rs,@rd,@rr,@ru,@re);SELECT
@uid,@did,@rs,@rd,@rr,@ru,@re; 
In Lazarus, I used TSQLQuery to do it:
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
TWSConnection = record  utype: string;  uname: string;  uid: integer; 
token: string;  did: integer;  dname: string;  rSys: boolean;  rdata:
boolean;  rReports: boolean;  rUsers: boolean;  rEquipments: boolean; 
query: TSQLQuery;  trans: TSQLTransaction;end;PWSConnection =
^TWSConnection; function newConn(utype: string; uname:
string):PWSConnection;var  c:PWSConnection;begin  c :=
new(PWSConnection);  c^.utype := utype;  c^.uname := uname;  c^.token
:= '';  c^.uid:=-1;  c^.did := -1;  c^.rSys := False;  c^.rdata :=
False;  c^.rReports := False;  c^.rUsers := False;  c^.rEquipments :=
False;  c^.query := TSQLQuery.Create(nil);  c^.trans :=
TSQLTransaction.Create(nil);  c^.trans.DataBase := mysql5;  //this is
a TMySQL56Connection, already connected to mysql5.7.34 successfully.
with TMySQL57Connection it always failed to connect 
c^.query.Transaction := c^.trans;  Result := c;end;     function
Login(c: PWSConnection;uname: string; pwd: string): boolean;begin 
Result := False;  c^.query.SQL.Text := 'call
`userlogin`(:uname,:pwd,@uid,@did,@rs,@rd,@rr,@ru,@re);'; 
c^.query.ParamByName('uname').AsString := trim(uname); 
c^.query.ParamByName('pwd').AsString := trim(pwd);  c^.query.ExecSQL;
     // this can be done without error  c^.query.Close;         //with
or without this and the following 2 lines, I got the same exception 
c^.query.SQL.Text := 'select @uid,@did,@rs,@rd,@rr,@ru,@re;'; 
c^.query.Open;  c^.uid := c^.query.ParamByName('uid').AsInteger;     
 //always get an exception: EDatabase Error with message Parameter
"uid" not found!!!  c^.did := c^.query.ParamByName('did').AsInteger; 
c^.rSys := c^.query.ParamByName('rs').AsBoolean;  c^.rdata :=
c^.query.ParamByName('rd').AsBoolean;  c^.rReports :=
c^.query.ParamByName('rr').AsBoolean;  c^.rUsers :=
c^.query.ParamByName('ru').AsBoolean;  c^.rEquipments :=
c^.query.ParamByName('re').AsBoolean;  c^.query.Close;  if c^.uid > 0
then begin    c^.token := HMACSHA1(SHA1Key, trim(uname) + 'love and
peace' + trim(pwd));    Result := True;  end;end; 
How can I avoid from "EDatabase Error with message Parameter "uid" not
found" and get the data from Mysql server?

----------------------------------------------------------------------
Zvoni · 2022-03-16 15:56

Err... Line 51 et al: Use FieldByName instead of ParamByName?

----------------------------------------------------------------------
senglit · 2022-03-16 16:20

First I think you are right, it should be FieldByName.
Second, I changed it and got EDatabase Error with message Field not
found: "uid"  :'(

Quote from: Zvoni on March 16, 2022, 04:56:14 pmErr... Line 51 et al:
Use FieldByName instead of ParamByName?

----------------------------------------------------------------------
Sieben · 2022-03-16 16:43

How does SQL Workbench name the fields of your SELECT result set? Look
at the column titles there and try to use these names.

----------------------------------------------------------------------
senglit · 2022-03-16 17:31

The names are the same.

Quote from: Sieben on March 16, 2022, 05:43:29 pmHow does SQL
Workbench name the fields of your SELECT result set? Look at the
column titles there and try to use these names.

----------------------------------------------------------------------
Sieben · 2022-03-16 17:46

Not really, there's an '@' in front of them...

----------------------------------------------------------------------
Zvoni · 2022-03-16 18:14

What‘s the RecordCount after line 50?
Instead of FieldByName you could iterate through the Fields by ordinal
index, and check the field‘s name

----------------------------------------------------------------------
senglit · 2022-03-17 02:36

Thanks for everybody's help, problem solved:

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   for i:=0
to c^.query.Fields.Count-1 do begin    writeln(c^.query.Fields[i].Disp
layName,':',c^.query.Fields[i].AsString);  end;     and then got
Code: Bash  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
@uid:-1@did:-1@rs:0@rd:0@rr:0@ru:0@re:0 
There IS a @ before the field name.

But I'm a little confused, I think the '@' symble is to declare the
parameter followed is an output parameter, is should not be a part of
the fieldname. Just like in this code, I do not add ':' before the
parameter
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  
c^.query.SQL.Text := 'call
`userlogin`(:uname,:pwd,@uid,@did,@rs,@rd,@rr,@ru,@re);'; 
c^.query.ParamByName('uname').AsString := trim(uname); 
c^.query.ParamByName('pwd').AsString := trim(pwd);

----------------------------------------------------------------------
af0815 · 2022-03-17 06:04

Make in the SQL Workbench 2 Queries, not all in one. Use 2 seperate
Queries.

It looks the @xxx are internal variables. So the statement will not
work if is it split.

See the declaration
QuoteCREATE DEFINER=`root`@`%` PROCEDURE `userlogin`(IN in_uname
VARCHAR(255), IN in_passwd VARCHAR(255),
        OUT o_uid INT signed, OUT o_did INT signed,
        OUT o_rs BOOLEAN,OUT o_rd BOOLEAN,OUT o_rr BOOLEAN,OUT o_ru
BOOLEAN,o_re BOOLEAN)
And IMHO i see no way, to read the outputvalues in Lazarus. Normal it
is only possible if the stored procedure delivers a resultset direct.

Edit: Or is is possible in the meanwhile - but i think not, because it
is a missing feature for me.

----------------------------------------------------------------------
paweld · 2022-03-17 06:26

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
Login(c: PWSConnection;uname: string; pwd: string): boolean;begin 
Result := False;  c^.query.SQL.Clear;  c^.query.SQL.Add('call
`userlogin`(:uname, :pwd, @uid, @did, @rs, @rd, @rr, @ru, @re);'); 
c^.query.SQL.Add('select @uid uid, @did did, @rs rs, @rd rd, @rr rr,
@ru ru, @re re;');  c^.query.ParamByName('uname').AsString :=
trim(uname);  c^.query.ParamByName('pwd').AsString := trim(pwd); 
c^.query.Open;  c^.uid := c^.query.FieldByName('uid').AsInteger;     
  c^.did := c^.query.FieldByName('did').AsInteger;  c^.rSys :=
c^.query.FieldByName('rs').AsBoolean;  c^.rdata :=
c^.query.FieldByName('rd').AsBoolean;  c^.rReports :=
c^.query.FieldByName('rr').AsBoolean;  c^.rUsers :=
c^.query.FieldByName('ru').AsBoolean;  c^.rEquipments :=
c^.query.FieldByName('re').AsBoolean;  c^.query.Close;  if c^.uid > 0
then begin    c^.token := HMACSHA1(SHA1Key, trim(uname) + 'love and
peace' + trim(pwd));    Result := True;  end;end;

----------------------------------------------------------------------
senglit · 2022-03-17 07:40

Quote from: paweld on March 17, 2022, 07:26:38 amCode: 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
Login(c: PWSConnection;uname: string; pwd: string): boolean; 
c^.query.SQL.Add('select @uid uid, @did did, @rs rs, @rd rd, @rr rr,
@ru ru, @re re;');end;
It caused an error.

I also tried these, but all of them caused the same error.
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
c^.query.SQL.Add('select @uid, @did, @rs, @rd, @rr, @ru, @re into
uid,did,rs,rd,rr,ru,re;');c^.query.SQL.Add('select
@uid,@did,@rs,@rd,@rr,@ru,@re;');c^.query.SQL.Add('select @uid into
uid, @did into did, @rs into rs, @rd into rd, @rr into rr, @ru into
ru, @re into re;'); 
I guess the call `userlogin` need an query.ExecSQL but the select need
an query.Open so that they can't be done in 1 SQL. But when the
query.Open executes, the error message is not about line 1 but line 2.
So strange. Does it mean It can be done in 1 SQL but the line 2 need
to be written in a diffrent way?

----------------------------------------------------------------------
Zvoni · 2022-03-17 09:20

Pay attention to paweld's code in Line 5 and 6
He's keeping both calls within the same Transaction

Your original code closes the Query after executing the SP, and then
reopens it with the SELECT
At a guess, the serverside variables are in Nirvana

EDIT:
ExecSQL cannot/shouldnot be used for Queries expecting results back
Open works with any Query (with/without results)

----------------------------------------------------------------------
senglit · 2022-03-18 05:08

Quote from: Zvoni on March 17, 2022, 10:20:15 amEDIT:
ExecSQL cannot/shouldnot be used for Queries expecting results back
Open works with any Query (with/without results)

It seems open only works with select sql
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  
c^.query.SQL.Add('call
`userlogin`(:uname,:pwd,@uid,@did,@rs,@rd,@rr,@ru,@re);'); 
c^.query.ParamByName('uname').AsString := trim(uname); 
c^.query.ParamByName('pwd').AsString := trim(pwd);  c^.query.Open;
 //this caused an error