changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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.
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.
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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
in which way does sending empty strings fail? Which driver/database are you using? Which compiler version do you use?
Best regards,
Jan
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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.
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.
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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
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
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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
So could you kindly tell me where I can download this file and how to use it?
Thank you
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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.
You can download a current snapshot there.
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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
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
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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
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
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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;
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;
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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);
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);
You do not have the required permissions to view the files attached to this post.
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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);
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);
You do not have the required permissions to view the files attached to this post.
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Hello,
from the release notes:
Your code should look like this:
from the release notes:
So - check your applicationsFor 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.
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
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
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
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;
You do not have the required permissions to view the files attached to this post.
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Hello,
again your code needs to be different:
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
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;
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
-
- Fresh Boarder
- Posts: 10
- Joined: 23.04.2024, 07:00
Re: changed behavior for empty Strings and TZStoredProc on Zeos Ver8.0
Hello.
Best regards
Thank you for the tip! This makes sense!Maybe it makes sense to call parameters by name (ParamByName) instead of calling them by Index?
I'll wait until it will be fixed!There is a bug in Zeos preventing you from using the procedure. I will have to see what I can do.
Best regards