Full Unicode/Ansi-Support in /testing branch
Moderators: gto, EgonHugeist, olehs
@EgonHugeist, @olehs
I read it to the dataset:
I do not know why they are not recognized procedure parameter names:
p1, p2 p3 only $0, $1, $2 ?
For FireBird they are properly recognized.
Michal
I read it to the dataset:
Code: Select all
ZStoredProc1.Close;
ZStoredProc1.Unprepare;
ZStoredProc1.StoredProcName:='abtest';
ZStoredProc1.ParamByName('$0').AsInteger:=50;
ZStoredProc1.ParamByName('$1').AsInteger:=100;
ZStoredProc1.ParamByName('$2').AsWideString:='a';
ZStoredProc1.Open;
Edit1.Text:=IntToStr(ZStoredProc1.FieldByName('p4').AsInteger);
Edit2.Text:=ZStoredProc1.FieldByName('p5').AsString;
I do not know why they are not recognized procedure parameter names:
p1, p2 p3 only $0, $1, $2 ?
For FireBird they are properly recognized.
Michal
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
this is corect. I was thinking an out-Param should return the result.
miab3,
Nice to know, Michal. I must admit on some components i have not so much expieriences. If i do understand you right can we fetch the values as fields. And if i do understand Oleg right do we not support returned resultsets with that Component. Michal i'll add your proposal to the tests too.
According the $1 paramnames i must admit i don't know if Zeos renames them or PostgreSQL. And if Zeos does it then i don't know why it was made. (Maybe to have allways an ParamName, for unnamed only typed once too?).
this is corect. I was thinking an out-Param should return the result.
miab3,
Nice to know, Michal. I must admit on some components i have not so much expieriences. If i do understand you right can we fetch the values as fields. And if i do understand Oleg right do we not support returned resultsets with that Component. Michal i'll add your proposal to the tests too.
According the $1 paramnames i must admit i don't know if Zeos renames them or PostgreSQL. And if Zeos does it then i don't know why it was made. (Maybe to have allways an ParamName, for unnamed only typed once too?).
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
miab3,
found the answer for your question in function TZPostgreSQLDatabaseMetadata.UncachedGetProcedureColumns(const Catalog: string;
const SchemaPattern: string; const ProcedureNamePattern: string;
const ColumnNamePattern: string): IZResultSet;
Here you can clearly see that Zeos (re??)names the parameters. Now i don't know about speed decrease if some joins where added for the select statment of the MetaInformations...
It might be possible that this was done with a good reason, Michal. Eventually can Oleg check the statment and patch the ?missing? fields. Also would it be interesting to know if we've to expect a speed decrease IF we change this..
Michael
found the answer for your question in function TZPostgreSQLDatabaseMetadata.UncachedGetProcedureColumns(const Catalog: string;
const SchemaPattern: string; const ProcedureNamePattern: string;
const ColumnNamePattern: string): IZResultSet;
Code: Select all
if ReturnTypeType <> 'c' then
begin
Result.MoveToInsertRow;
Result.UpdateNull(1);
Result.UpdateString(2, GetStringByName('nspname'));
Result.UpdateString(3, GetStringByName('proname'));
Result.UpdateString(4, 'returnValue');
Result.UpdateInt(5, Ord(pctReturn));
Result.UpdateInt(6, Ord(GetSQLTypeByOid(ReturnType)));
Result.UpdateString(7, GetPostgreSQLType(ReturnType));
Result.UpdateNull(8);
Result.UpdateNull(9);
Result.UpdateNull(10);
Result.UpdateNull(11);
Result.UpdateInt(12, Ord(ntNullableUnknown));
Result.UpdateNull(13);
Result.InsertRow;
end;
for I := 0 to ArgTypes.Count-1 do
begin
ArgOid := StrToInt(ArgTypes.Strings[i]);
Result.MoveToInsertRow;
Result.UpdateNull(1);
Result.UpdateString(2, GetStringByName('nspname'));
Result.UpdateString(3, GetStringByName('proname'));
Result.UpdateString(4, '$' + IntToStr(I));
Result.UpdateInt(5, Ord(pctIn));
Result.UpdateInt(6, Ord(GetSQLTypeByOid(ArgOid)));
Result.UpdateString(7, GetPostgreSQLType(ArgOid));
Result.UpdateNull(8);
Result.UpdateNull(9);
Result.UpdateNull(10);
Result.UpdateNull(11);
Result.UpdateInt(12, Ord(ntNullableUnknown));
Result.UpdateNull(13);
Result.InsertRow;
end;
It might be possible that this was done with a good reason, Michal. Eventually can Oleg check the statment and patch the ?missing? fields. Also would it be interesting to know if we've to expect a speed decrease IF we change this..
Michael
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
But i must admit i can understand Michal here. FB/Oracle are able to return the right ParamNames. Why not PostgreSQL?
The TZPostgreSQLDatabaseMetadata do have access to the Connection.Properties. So we can add simply an Option we check before. But i propose to keep the old behavior as default if it is logical or not..
What do you think?
Michael
That was my impression here too. But a if ColumnResultSet.GetString() = '' then this '$'+IntToStr() can be used instead.First of all PostgreSQL supports unnamed params. So we will have to give them names anyway.
These are my worries too. I'm sure this code exists since veeeeeeery long times. So doing this without:Second, we will break working code for those who rely on current paramnames
will indeed only result new trouble.(or we'll have to add one more Option)
But i must admit i can understand Michal here. FB/Oracle are able to return the right ParamNames. Why not PostgreSQL?
The TZPostgreSQLDatabaseMetadata do have access to the Connection.Properties. So we can add simply an Option we check before. But i propose to keep the old behavior as default if it is logical or not..
What do you think?
Michael
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
EgonHugeist,
and what if there already is a param named $0, $1... As long as PG doesn't require param names and doesn't use them, params can be named anyway you like ("$0", " h8^uajj fjh"). So we'll have to add extra logic to avoid duplicates.That was my impression here too. But a if ColumnResultSet.GetString() = '' then this '$'+IntToStr() can be used instead.
It should rather be ZStoredProc Option, not connection.The TZPostgreSQLDatabaseMetadata do have access to the Connection.Properties. So we can add simply an Option we check before.
Oleg
Hmm, made a funny sample
Code: Select all
CREATE OR REPLACE FUNCTION public.mult2 (
"$0" integer
)
RETURNS integer AS
$body$
BEGIN
return "$0" + $1;
END
$body$
LANGUAGE 'plpgsql'
Oleg
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
As i wrote the little test today i was running into the same issue that i expacted the 'P1'-'P5' ParameterNames and did wonder about exceptions i had... That is what i mean i really didn't know about the '$1'renaming before.
Next point i have here: This '$'+Integer name makes it (in my mind after introducing that realprepared statment) !eventually!(with some extra-changes no issues to see) impossible to prepare the callable statement(not supported yet).
Imagine a syntax like 'PREPARE "fooplan" AS INSERT INTO fooplan($0, $1, $2(generated from MeataInformations)) values ($1, $2(generated from my PreparedStatment syntax))...
Oleg here we can start from the premise: feature! The existing code works and handles unnamed parameters too. All i want to say is: If you felt motivated then it would be fince to have this posibility. I've started the PQprepare implementation to get the direct C-API statments running so i wont have the time yet to check the statement syntax of that procedure.
But one thing would be great: How do you propose to manage the access of TZDataSet.Properties to the TZDataBaseMetainformations? Ludob and me had big problems here as i proposed to make IsBlobOid optional for the DataSet.Properties. We had no luck here...
That was an example i was waiting for. (; Yap this might be right BUT you are the autor of that procedure AND you do expect the '$0' as ParameterName, or not?Hmm, made a funny sample
As i wrote the little test today i was running into the same issue that i expacted the 'P1'-'P5' ParameterNames and did wonder about exceptions i had... That is what i mean i really didn't know about the '$1'renaming before.
Next point i have here: This '$'+Integer name makes it (in my mind after introducing that realprepared statment) !eventually!(with some extra-changes no issues to see) impossible to prepare the callable statement(not supported yet).
Imagine a syntax like 'PREPARE "fooplan" AS INSERT INTO fooplan($0, $1, $2(generated from MeataInformations)) values ($1, $2(generated from my PreparedStatment syntax))...
Oleg here we can start from the premise: feature! The existing code works and handles unnamed parameters too. All i want to say is: If you felt motivated then it would be fince to have this posibility. I've started the PQprepare implementation to get the direct C-API statments running so i wont have the time yet to check the statement syntax of that procedure.
But one thing would be great: How do you propose to manage the access of TZDataSet.Properties to the TZDataBaseMetainformations? Ludob and me had big problems here as i proposed to make IsBlobOid optional for the DataSet.Properties. We had no luck here...
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
EgonHugeist,
There are troubles with overloaded functions (try to set 'max' as StoredProcName - will get an interesting paramlist), out params(just not supported), return value, variadic params, default-valued params etc.
Finally we stopped using TZStoredProc in our projects at all.
But I have an idea. We could just leave unnamed params without a name. They still will be available by index. It also should not be an option, but default.
What do you think, can we change the default behavior and not be cursed by Zeos-users?
I felt motivated when started that other topic. But after going deeper I understood that PostgreSQL's implementation of TZStoredProc is just a "stub" and has to be completely rewritten.If you felt motivated then it would be fince to have this posibility.
There are troubles with overloaded functions (try to set 'max' as StoredProcName - will get an interesting paramlist), out params(just not supported), return value, variadic params, default-valued params etc.
Finally we stopped using TZStoredProc in our projects at all.
But I have an idea. We could just leave unnamed params without a name. They still will be available by index. It also should not be an option, but default.
What do you think, can we change the default behavior and not be cursed by Zeos-users?
Oleg
@EgonHugeist
However, FireBird is also something wrong with the procedures.
And now:
I get an error (attached) on the second call ExecProc:
Is it something similar to?:
http://zeosbugs.firmos.at/view.php?id=20
Michal
However, FireBird is also something wrong with the procedures.
Code: Select all
create or alter procedure ABTEST (
P1 integer,
P2 integer,
P3 varchar(10))
returns (
P4 integer,
P5 varchar(20))
as
begin
P4 = P1 * 10 + P2;
P5 = P3 || P3;
suspend;
end^
Code: Select all
ZStoredProc1.Unprepare;
ZStoredProc1.StoredProcName:= 'ABTEST';
// ZStoredProc1.ParamByName('P1').AsInteger:= 50;
// ZStoredProc1.ParamByName('P2').AsInteger:= 100;
// ZStoredProc1.ParamByName('P3').AsString:= 'a';
ZStoredProc1.Params[2].AsInteger:= 50;
ZStoredProc1.Params[3].AsInteger:= 100;
ZStoredProc1.Params[4].AsString:= 'a';
ZStoredProc1.ExecProc;
// ZStoredProc1.ParamByName('P3').AsString:= 'b';
ZStoredProc1.Params[4].AsString:= 'b';
ZStoredProc1.ExecProc;
Is it something similar to?:
http://zeosbugs.firmos.at/view.php?id=20
Michal
You do not have the required permissions to view the files attached to this post.
miab3,
can't test it myself on FireBird, but shouldn't P1 be Param #1?
can't test it myself on FireBird, but shouldn't P1 be Param #1?
Code: Select all
ZStoredProc1.Params[1].AsInteger:= 50;
ZStoredProc1.Params[2].AsInteger:= 100;
ZStoredProc1.Params[3].AsString:= 'a'
Oleg
@olehs
I think mine is correct:
First ExecProc work.
So it works:
If Unprepare before each use ExecProc.
Michal
I think mine is correct:
Code: Select all
object ZStoredProc1: TZStoredProc
Connection = ZConnection1
Params = <
item
DataType = ftInteger
Name = 'P4'
ParamType = ptResult
end
item
DataType = ftWideString
Name = 'P5'
ParamType = ptResult
end
item
DataType = ftInteger
Name = 'P1'
ParamType = ptInput
end
item
DataType = ftInteger
Name = 'P2'
ParamType = ptInput
end
item
DataType = ftWideString
Name = 'P3'
ParamType = ptInput
end>
StoredProcName = 'ABTEST'
Left = 620
Top = 376
ParamData = <
item
DataType = ftInteger
Name = 'P4'
ParamType = ptResult
end
item
DataType = ftWideString
Name = 'P5'
ParamType = ptResult
end
item
DataType = ftInteger
Name = 'P1'
ParamType = ptInput
end
item
DataType = ftInteger
Name = 'P2'
ParamType = ptInput
end
item
DataType = ftWideString
Name = 'P3'
ParamType = ptInput
end>
end
So it works:
Code: Select all
var
i: integer;
begin
for i:= 0 to 99 do
begin
ZStoredProc1.Unprepare;
ZStoredProc1.StoredProcName:= 'ABTEST';
ZStoredProc1.Params[2].AsInteger:= i;
ZStoredProc1.Params[3].AsInteger:= 100;
ZStoredProc1.Params[4].AsString:= 'a';
ZStoredProc1.ExecProc;
end;
Edit1.Text:= ZStoredProc1.ParamByName('P4').AsString +
' ' + ZStoredProc1.ParamByName('P5').AsString;
end;
Michal
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
Also did i read a lot of old threads about this Component yesterday. Annoying is that i was not able to find exactly this one where it was decided to trop this support. One i found with some proposed workarounds: http://zeos.firmos.at/viewtopic.php?t=769
miab3,
I must admit i slightly want that you help us here. I propose again to give you commit privilegs to the repositories. Nobody starts from the premise you must fix such issues by you selves, but really great for me would be if you add some tests to our testsuites. These tests do show us always if everything is ok or we broke on other places. Also are they very good reminders and show everybody there is work left todo. I've got 3 little good working companies and a private live too. So adding tests and fixing eats my time everywhere. What do you think. Can you do that?
miab3, olehs,
now i've started to implement the CAPI Prepared statements for PostgreSQL. What you guys are thinking about? And what has higher precedence?
Oleg i propose to complete this Statment because it makes the PostgreSQL API faster (i hope, because i found also some blogs where peoples report that the executions after xTimes becomes slower then rendered queries..). The Next point why i wanted to complete the two prepared Statments in dependencies of Client/Server-versions is for example this: http://zeosbugs.firmos.at/view.php?id=246. But how to check this? We need again some tests more here which are able to make Batch-Executions or something like this. Exaclty the same design could be used for the TZStroredProc too.
Oleg you mentioned that a complete rewrite of that Component for PostgreSQL is needed. Now i can not say why but i trust you on that. Is it possible to do this without creating an extra PostgreSQL component? This is not wanted for me..
Michal,
And according the NestedDataSets of oracle we actually did dicide to stop coding for that case. Zeos is portable for FPC/CBuilder/Delphi but the FPC does supports field-objects neither (actually).
So you guys what do you propose? I know a lot of work on all statments for all Drivers and i'm willing to do that job..
If this motivates you then let's do it (; Oleg for the first i would propose to add a new define to the Zeos.Inc for the comming patches. Which means keep the existing code like he is for the first and add the new code with a define around. So we do not break anything and can keep control about the latest changes. I know this brings some more work with but so we can avoid to drop the changes again.that other topic
Also did i read a lot of old threads about this Component yesterday. Annoying is that i was not able to find exactly this one where it was decided to trop this support. One i found with some proposed workarounds: http://zeos.firmos.at/viewtopic.php?t=769
miab3,
I must admit i slightly want that you help us here. I propose again to give you commit privilegs to the repositories. Nobody starts from the premise you must fix such issues by you selves, but really great for me would be if you add some tests to our testsuites. These tests do show us always if everything is ok or we broke on other places. Also are they very good reminders and show everybody there is work left todo. I've got 3 little good working companies and a private live too. So adding tests and fixing eats my time everywhere. What do you think. Can you do that?
miab3, olehs,
now i've started to implement the CAPI Prepared statements for PostgreSQL. What you guys are thinking about? And what has higher precedence?
Oleg i propose to complete this Statment because it makes the PostgreSQL API faster (i hope, because i found also some blogs where peoples report that the executions after xTimes becomes slower then rendered queries..). The Next point why i wanted to complete the two prepared Statments in dependencies of Client/Server-versions is for example this: http://zeosbugs.firmos.at/view.php?id=246. But how to check this? We need again some tests more here which are able to make Batch-Executions or something like this. Exaclty the same design could be used for the TZStroredProc too.
Oleg you mentioned that a complete rewrite of that Component for PostgreSQL is needed. Now i can not say why but i trust you on that. Is it possible to do this without creating an extra PostgreSQL component? This is not wanted for me..
Michal,
That could be true but i don't know it. I've fixed nearly all failing tests on the testsuites. But i can only fix what i clearly see and a code sequence which points me to the issue. Again: Would you be able to add some tests? No is equal ok, but you are one of the best bugreportes with a lot of patience we had here. And you do that a long time!Is it something similar to?:
http://zeosbugs.firmos.at/view.php?id=20
And according the NestedDataSets of oracle we actually did dicide to stop coding for that case. Zeos is portable for FPC/CBuilder/Delphi but the FPC does supports field-objects neither (actually).
So you guys what do you propose? I know a lot of work on all statments for all Drivers and i'm willing to do that job..
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/