mysql/mariadb and stored procedures
lazarus / 16-Databases

----------------------------------------------------------------------
radug · 2019-09-06 10:26

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Procedure
TForm1.ButtonConnectClick(Sender: TObject); var num_users:
integer;begin    //...  MySQL55Connection1.Connected := true; 
MySQL55Connection1.Open;          //  SQLQuery1.Close; 
SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Text := 'call
`count_users_per_client`(:CLIENTID, :NUMUSERS)';  
SQLQuery1.Params.CreateParam(ftInteger, 'CLIENTID', ptInput); 
SQLQuery1.Params.CreateParam(ftInteger, 'NUMUSERS', ptOutput); 
SQLQuery1.ParamByName('CLIENTID').AsInteger := 11; 
SQLQuery1.ExecSQL;  num_users :=
SQLQuery1.ParamByName('NUMUSERS').AsInteger;  ShowMessage('USERS# = '
+ IntToStr(num_users));            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 ;-(

----------------------------------------------------------------------
radug · 2019-09-06 13:35

Hi,

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

----------------------------------------------------------------------
Thaddy · 2019-09-06 18:33

Quote from: radug on September 06, 2019, 03:35:50 pmHi,

It seems it's not even possible, according to this
link:http://www.vajhoej.dk/arne/articles/pasdb.html#callsp
QuoteLazarus 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 · 2019-09-06 19:16

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).

QuoteAnd 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:
QuoteTSQLQuery
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 · 2019-09-06 20:46

Quote from: radug on September 06, 2019, 12:26:09 pmHi 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Procedure
TForm1.ButtonConnectClick(Sender: TObject); var num_users:
integer;begin    //...  MySQL55Connection1.Connected := true; 
MySQL55Connection1.Open;          //  SQLQuery1.Close; 
SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Text := 'call
`count_users_per_client`(:CLIENTID, :NUMUSERS)';  
SQLQuery1.Params.CreateParam(ftInteger, 'CLIENTID', ptInput); 
SQLQuery1.Params.CreateParam(ftInteger, 'NUMUSERS', ptOutput); 
SQLQuery1.ParamByName('CLIENTID').AsInteger := 11; 
SQLQuery1.ExecSQL;  num_users :=
SQLQuery1.ParamByName('NUMUSERS').AsInteger;  ShowMessage('USERS# = '
+ IntToStr(num_users));            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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.SQL.Text := 'call `count_users_per_client`(:CLIENTID,
@NUMUSERS); SELECT @NUMUSERS'; 
SQLQuery1.ParamByName('CLIENTID').AsInteger := 11;  SQLQuery1.Open;Or
maybe:
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  
SQLQuery1.SQL.Text := 'call `count_users_per_client`(:CLIENTID,
@NUMUSERS)';  SQLQuery1.ParamByName('CLIENTID').AsInteger := 11; 
SQLQuery1.ExecSQL;  SQLQuery1.Close;  SQLQuery1.SQL.Text := 'SELECT
@NUMUSERS';  SQLQuery1.Open;

----------------------------------------------------------------------
radug · 2019-09-06 21:22

"Or maybe" worked!
THANK YOU!