mysql/mariadb and stored procedures

[radug]

Hi everyone,

I'm creating a small application to manage an MySQL/MariaDB database and I'm struggling to call a stored procedure using TMySQL55Connection and TSQLQuery.
The procedure takes two parameters one: is INPUT (integer) the other is OUTPUT (integer)
The code is simple / should be like this:

Code: Pascal  [Select][+][-]

1. Procedure TForm1.ButtonConnectClick(Sender: TObject);
2. var num_users: integer;
3. begin  
4.   //...
5.   MySQL55Connection1.Connected := true;
6.   MySQL55Connection1.Open;        
7.   //
8.   SQLQuery1.Close;
9.   SQLQuery1.SQL.Clear;
10.   SQLQuery1.SQL.Text := 'call `count_users_per_client`(:CLIENTID, :NUMUSERS)';
11.  
12.   SQLQuery1.Params.CreateParam(ftInteger, 'CLIENTID', ptInput);
13.   SQLQuery1.Params.CreateParam(ftInteger, 'NUMUSERS', ptOutput);
14.   SQLQuery1.ParamByName('CLIENTID').AsInteger := 11;
15.   SQLQuery1.ExecSQL;
16.   num_users := SQLQuery1.ParamByName('NUMUSERS').AsInteger;
17.   ShowMessage('USERS# = ' + IntToStr(num_users));            
18. end;
19.  

Should this be working?
Is there any demo / documentation for this?

I also notice that , if I add the parameters for the SQLQuery1 in the interface via Properties, they are not shown in the Components tree view (as child node). 

Thank you!
Regards,
Radu G.

PS I know I can do it with ZeosLib but with ZeosLib I have other problems ;-(

[radug]

Hi,

It seems it's not even possible, according to this link:http://www.vajhoej.dk/arne/articles/pasdb.html#callsp

Lazarus SQLDB does not support stored procedures even though Delphi DBExpress does.

Regards,
Radu G.

[Thaddy]

Hi,

It seems it's not even possible, according to this link:http://www.vajhoej.dk/arne/articles/pasdb.html#callsp

Lazarus SQLDB does not support stored procedures even though Delphi DBExpress does.

Regards,
Radu G.

In the same link he suggests zeos which is supported.
And the TMySQLConnection family supports stored procedures since FPC 3.0.0.

[radug]

Yes, I know.
But with ZeosLib I can use TZStoredProc and TZTable but I cannot use TZQuery and TZReadOnlyQuery.
I tried both stable and svn versions of ZeosLib. The application just freezes when I try to set TZ[ReadOnly]Query.Active to true.

I'm using Lazarus 2.0.4, ZeosLib 7.2.4 (on Windows 10 - 64bit) with MySQL server 5.5 (local) and MariaDB 5.5 (remote on Linux).

And the TMySQLConnection family supports stored procedures since FPC 3.0.0.

Is there a link to some documentation/sample code?

Thank you!
Regards,
Radu G.

 PS Now I am really confused ... Here https://wiki.freepascal.org/SQLdb_Package one can read:

TSQLQuery
Documentation: see TSQLQuery
This is a descendant of TDataset, and provides the data as a table from the SQL query that you submit. However, it can also be used to execute SQL queries (e.g. stored procedures, INSERT INTO..) that don't return any data.

Which one is true?

[valdir.marcos]

Hi everyone,

I'm creating a small application to manage an MySQL/MariaDB database and I'm struggling to call a stored procedure using TMySQL55Connection and TSQLQuery.
The procedure takes two parameters one: is INPUT (integer) the other is OUTPUT (integer)
The code is simple / should be like this:

Code: Pascal  [Select][+][-]

1. Procedure TForm1.ButtonConnectClick(Sender: TObject);
2. var num_users: integer;
3. begin  
4.   //...
5.   MySQL55Connection1.Connected := true;
6.   MySQL55Connection1.Open;        
7.   //
8.   SQLQuery1.Close;
9.   SQLQuery1.SQL.Clear;
10.   SQLQuery1.SQL.Text := 'call `count_users_per_client`(:CLIENTID, :NUMUSERS)';
11.  
12.   SQLQuery1.Params.CreateParam(ftInteger, 'CLIENTID', ptInput);
13.   SQLQuery1.Params.CreateParam(ftInteger, 'NUMUSERS', ptOutput);
14.   SQLQuery1.ParamByName('CLIENTID').AsInteger := 11;
15.   SQLQuery1.ExecSQL;
16.   num_users := SQLQuery1.ParamByName('NUMUSERS').AsInteger;
17.   ShowMessage('USERS# = ' + IntToStr(num_users));            
18. end;

Should this be working?
Is there any demo / documentation for this?

I also notice that , if I add the parameters for the SQLQuery1 in the interface via Properties, they are not shown in the Components tree view (as child node). 

Thank you!
Regards,
Radu G.

PS I know I can do it with ZeosLib but with ZeosLib I have other problems ;-(

There are some alternatives.
First, use a Function, or a Select From Select, or a View, or a temporary table.

Second, try:

Code: Pascal  [Select][+][-]

1.   SQLQuery1.SQL.Text := 'call `count_users_per_client`(:CLIENTID, @NUMUSERS); SELECT @NUMUSERS';
2.   SQLQuery1.ParamByName('CLIENTID').AsInteger := 11;
3.   SQLQuery1.Open;

Or maybe:

Code: Pascal  [Select][+][-]

1.   SQLQuery1.SQL.Text := 'call `count_users_per_client`(:CLIENTID, @NUMUSERS)';
2.   SQLQuery1.ParamByName('CLIENTID').AsInteger := 11;
3.   SQLQuery1.ExecSQL;
4.   SQLQuery1.Close;
5.   SQLQuery1.SQL.Text := 'SELECT @NUMUSERS';
6.   SQLQuery1.Open;

[radug]

"Or maybe" worked!
THANK YOU!