Recent

Author Topic: mysql/mariadb and stored procedures  (Read 405 times)

radug

  • Newbie
  • Posts: 4
mysql/mariadb and stored procedures
« on: September 06, 2019, 12:26:09 pm »
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

  • Newbie
  • Posts: 4
Re: mysql/mariadb and stored procedures
« Reply #1 on: September 06, 2019, 03:35:50 pm »
Hi,

It seems it's not even possible, according to this link:http://www.vajhoej.dk/arne/articles/pasdb.html#callsp
Quote
Lazarus SQLDB does not support stored procedures even though Delphi DBExpress does.

Regards,
Radu G.

Thaddy

  • Hero Member
  • *****
  • Posts: 9193
Re: mysql/mariadb and stored procedures
« Reply #2 on: September 06, 2019, 08:33:19 pm »
Hi,

It seems it's not even possible, according to this link:http://www.vajhoej.dk/arne/articles/pasdb.html#callsp
Quote
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.
« Last Edit: September 06, 2019, 08:55:10 pm by Thaddy »
also related to equus asinus.

radug

  • Newbie
  • Posts: 4
Re: mysql/mariadb and stored procedures
« Reply #3 on: September 06, 2019, 09:16:36 pm »
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).

Quote
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:
Quote
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?
« Last Edit: September 06, 2019, 09:56:30 pm by radug »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 831
Re: mysql/mariadb and stored procedures
« Reply #4 on: September 06, 2019, 10:46:58 pm »
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

  • Newbie
  • Posts: 4
Re: mysql/mariadb and stored procedures
« Reply #5 on: September 06, 2019, 11:22:47 pm »
"Or maybe" worked!
THANK YOU!
« Last Edit: September 07, 2019, 12:03:54 am by radug »