changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0

[meeeeeeeeee]

I'd like to ask about newest version Zeos8.0.
Zeos 8.0 's TZStoredProc does'nt allow sending empty strings to stored procedures.
I can send empty strings by using previous version Zeos version 7.2.14.0.
I do like this
ZStoredProc1.StoredProcName :=xxxxx
ZStoredProc1.Params[0].AsInteger :=1;
ZStoredProc1.Params[1].AsInteger :=0;
ZStoredProc1Params[2].AsString :=''; ....this part is the problem with version 8.0
ZStoredProc.Open;

could anyone give me any tip to solve this issue?
Thank you.

[marsupilami]

Hello meeeeeeeeee,

in which way does sending empty strings fail? Which driver/database are you using? Which compiler version do you use?

Best regards,

Jan

[meeeeeeeeee]

Hello.Jan,
I'm using Lazarus3.2 and MariaDB(version 10.6.14) with Windows 11.
And MariaDB database Engine is InnoDB.
I can send Empty string in command-line into MariaDB stored procedures.
My English isn't good, so if you don't understand well what I'm saying, my apology.

[marsupilami]

Hello meeeeeeeeee,

we added a change to the Zeos 8.0-patches branch. Could you please test and check if it solves your problem?

Best regards,

Jan

[meeeeeeeeee]

Hello, marsupilami! Thank you very much for the Zeos 8.0-patches, but I've never used the Zeos 8.0-patches before.
So could you kindly tell me where I can download this file and how to use it?
Thank you

[marsupilami]

The 8.0-patches branch is a branch in our svn: https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/8.0-patches/
You can download a current snapshot there.

[meeeeeeeeee]

Hello. marsupilami. I downloaded "zeoslib-code-0-r8159-branches-8.0-patches.zip" from the link. I copied "examples","packages" and "src" folders over the folder "zeosdbo", then installed into Lazarus3.2. "TZconection" component-version shows "8.0.1-beta". So I think I could applied your patch into my Lazarus3.2. About the problem of "empty Strings and TZStoredProc on Zeos Ver8.0" is fixed with this patch! Thank you so much.
I found out another problem, shall I post to a new message? or can I ask here?
There is another error.

Unsupported parameter type, SQL Type:stUnicodeString/.
in file 'ZDatasetParam.pas' at line 2705:
jmpErr:raise CreateConversionError(FSQLDataType, stUnicodeString);

I use storedfunction with TZStoredProc, and
ZStoredProc1.StoredProcName :=xxxxx
ZStoredProc1.Params[0].AsInteger :=1;....this part is the problem with version 8.0
ZStoredProc1.Params[1].AsString :=xxxx;
ZStoredProc1Params[2].AsString :=xxxx';
xReturn :=ZStoredProc1Params[2].AsString;
ZStoredProc.Open;
this code is ok with Zeos7.2.14-release and Lazarus2.2.6

[marsupilami]

Hello,

could you try to create a small sample project (lazarus project + mysql source for function) that demonstrates the problem? This really helps in debugging the problem.

Did you set the ClientCodepage to utf8 or utf8mb4?

Best regards,

Jan

[meeeeeeeeee]

Hello. I created a small sample project. I set ClientCodepage to utf8mb4. I would be grateful, if you'd check it.
I couldn't post my small sample project at once, so I separated to 3 parts.
**********************
* Lazarus3.2+Zeos8.0.1-beta
**********************
TForm1 = class(TForm)
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Memo1: TMemo;
ZConnection1: TZConnection;
ZReadOnlyQuery1: TZReadOnlyQuery;
ZStoredProc1: TZStoredProc;
procedure FormShow(Sender: TObject);
procedure TForm1.FormShow(Sender: TObject);
var
xSqltext :string;
begin
ZConnection1.HostName:='localhost';
ZConnection1.Protocol:='mariadb';
ZConnection1.ClientCodepage :='utf8mb4';
ZConnection1.Connect;

//############################
//test storedfunction<1>
//############################
ZStoredProc1.Close;
ZStoredProc1.StoredProcName :='test_zeos_func';
ZStoredProc1.Params[0].value :='test_zeos';
ZStoredProc1.Prepare;
ZStoredProc1.Open;
xSqltext:=ZStoredProc1.Params[1].value;
ZStoredProc1.Close;

//############################
//test storedfunction<2>
//############################
ZStoredProc1.Close;
ZStoredProc1.StoredProcName :='test_zeos_2func';
ZStoredProc1.Params[0].asinteger :=0;
ZStoredProc1.Params[1].asstring :='db_master_';
ZStoredProc1.Prepare;
ZStoredProc1.Open;
xSqltext:=ZStoredProc1.Params[2].value;
ZStoredProc1.Close;

memo1.Lines.Add(xSqltext);
end;

[meeeeeeeeee]

No error from command line.
select test_zeos_func('test_zeos');
+-----------------------------+
| test_zeos_func('test_zeos') |
+-----------------------------+
| select * FROM test_zeos |
+-----------------------------+


*This is Error Message from ZStoredProc1
Unsupported parameter type, SQL Type:stString/stTimestamp
in file 'ZDatasetParam.pas' as line 2639
jmpFail:raise Self.CreateConversionError(FSQLDataType,stString);

[meeeeeeeeee]

No error from command line.....
select test_zeos_2func(0, 'db_master_');
+----------------------------------+
| test_zeos_2func(0, 'db_master_') |
+----------------------------------+
| select * FROM db_master_zeos |
+----------------------------------+

*Error Message from ZStoredProc1
Unsupported parameter type, SQL type :stUnicodeString/
in file 'ZDatasetParam.pas' as line 2705:
jmpFail:raise Self.CreateConversionError(FSQLDataType,stUnicodeString);

[marsupilami]

Hello,

from the release notes:

For database systems, where a return value is defined, like MS SQL Server, the return value has moved to ordinal position 0. This change affects dblib based drivers (MS SQL Server / Sybase Adaptive Server Enterprise) as well Oracle, MySQL / MariaDB, OLEDB, ODBC and ADO. Please check your
applications.

So - check your applications

Your code should look like this:

Code: Select all

var
  xSqltext :string;
begin
  ZConnection1.HostName:='localhost';
  ZConnection1.Protocol:='mariadb';
  ZConnection1.ClientCodepage :='utf8mb4';
  ZConnection1.Connect;

  //############################
  //test storedfunction<1>
  //############################
  ZStoredProc1.Close;
  ZStoredProc1.StoredProcName :='test_zeos_func';
  ZStoredProc1.Params[1].value :='test_zeos'; // <-- was 0 before
  ZStoredProc1.Prepare;
  ZStoredProc1.Open;
  xSqltext:=ZStoredProc1.Params[0].value;  // <-- was 1 before
  ZStoredProc1.Close;

  //############################
  //test storedfunction<2>
  //############################
  ZStoredProc1.Close;
  ZStoredProc1.StoredProcName :='test_zeos_2func';
  ZStoredProc1.Params[1].asinteger :=0;  // <-- was 0 before
  ZStoredProc1.Params[2].asstring :='db_master_';  // <-- was 1 before
  ZStoredProc1.Prepare;
  ZStoredProc1.Open;
  xSqltext:=ZStoredProc1.Params[0].value;  // <-- was 2 before
  ZStoredProc1.Close;

  memo1.Lines.Add(xSqltext);
end;
[/quote]

Also you might need the patch that I added for Mariadb today. So please fetch a fresh copy of the testing branch.

Best regards,

Jan

[meeeeeeeeee]

Hello.
With your most Recent patch that you added for Mariadb, I could solve my error! Thank you very much!
And I encountered another error...
How Can I get "OUT" result of StoredProcedure? Has it changed something with it with Zeos8.0 too?

Error message :SQL Error:Out or INOUT argument 9 for routine test_zeos_3proc is not a variable or NEW pseudo-variable in BEFORE trigger
Code:1414 SQL:CALL test_zeos_3proc(?,?)
in file 'ZDbcMySql.pas' at line 1272

Code: Select all

  ZStoredProc1.Close;
  ZStoredProc1.StoredProcName :='test_zeos_3proc';
  ZStoredProc1.Params[1].value :='test_zeos';
  ZStoredProc1.Prepare;
  ZStoredProc1.Open;
  memo1.Lines.Add(ZStoredProc1.Params[0].value);
  ZStoredProc1.Close;

[marsupilami]

Hello,

again your code needs to be different:

Code: Select all

  ZStoredProc1.Close;
  ZStoredProc1.StoredProcName :='test_zeos_3proc';
  ZStoredProc1.Params[0].value :='test_zeos';
  ZStoredProc1.Prepare;
  ZStoredProc1.Open;
  memo1.Lines.Add(ZStoredProc1.Params[1].value);
  ZStoredProc1.Close;

This time you are calling a stored procedure that has no result. So parameters appear in the order in which they were declared when the stored procedure was created. Maybe it makes sense to call parameters by name (ParamByName) instead of calling them by Index?

But that doesn't solve the problem you reported. There is a bug in Zeos preventing you from using the procedure. I will have to see what I can do.

Best regards,

Jan

[meeeeeeeeee]

Hello.

Maybe it makes sense to call parameters by name (ParamByName) instead of calling them by Index?

Thank you for the tip! This makes sense!

There is a bug in Zeos preventing you from using the procedure. I will have to see what I can do.

I'll wait until it will be fixed!
Best regards