Page 1 of 2
changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 23.04.2024, 07:06
by 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.
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 23.04.2024, 09:13
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 23.04.2024, 09:53
by 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.
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 02.05.2024, 12:15
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 07.05.2024, 05:21
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 07.05.2024, 17:16
by 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.
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 08.05.2024, 08:10
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 08.05.2024, 09:53
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 14.05.2024, 06:34
by 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;
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 14.05.2024, 06:41
by 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);
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 14.05.2024, 06:42
by 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);
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 14.05.2024, 12:10
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 15.05.2024, 01:47
by 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;
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 16.05.2024, 16:46
by 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
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Posted: 17.05.2024, 01:35
by 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