Full Unicode/Ansi-Support in /testing branch

[olehs]

miab3,

you are probably calling ZStoredProc1.Open and then ZStoredProc1.FieldByName('p4')... ?

I think EgonHugeist was talking about ExecProc and then ParamByName('returnValue').

[miab3]

@EgonHugeist, @olehs

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]

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

[miab3]

@EgonHugeist, @olehs

Once correctly identified the types of input parameters, it seems to me that there should be no problem to recognize their names.

For FireBird for procedures works both Open and ExecProc.

Michal

[EgonHugeist]

miab3,

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;

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

[olehs]

EgonHugeist,

I wouldn't do that. There will be no speed decrease. But...
First of all PostgreSQL supports unnamed params. So we will have to give them names anyway.
Second, we will break working code for those who rely on current paramnames (or we'll have to add one more Option)

[EgonHugeist]

olehs,

First of all PostgreSQL supports unnamed params. So we will have to give them names anyway.

That was my impression here too. But a if ColumnResultSet.GetString() = '' then this '$'+IntToStr() can be used instead.

Second, we will break working code for those who rely on current paramnames

These are my worries too. I'm sure this code exists since veeeeeeery long times. So doing this without:

(or we'll have to add one more Option)

will indeed only result new trouble.

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

[olehs]

EgonHugeist,

That was my impression here too. But a if ColumnResultSet.GetString() = '' then this '$'+IntToStr() can be used instead.

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.

The TZPostgreSQLDatabaseMetadata do have access to the Connection.Properties. So we can add simply an Option we check before.

It should rather be ZStoredProc Option, not connection.

[olehs]

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'

[EgonHugeist]

olehs,

Hmm, made a funny sample

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?

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

[olehs]

EgonHugeist,

If you felt motivated then it would be fince to have this posibility.

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

[miab3]

@EgonHugeist

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^

And now:

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;

I get an error (attached) on the second call ExecProc:

Is it something similar to?:
http://zeosbugs.firmos.at/view.php?id=20

Michal

[olehs]

miab3,

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'

[miab3]

@olehs

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

First ExecProc work.

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;

If Unprepare before each use ExecProc.

Michal

[EgonHugeist]

olehs,

that other topic

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.

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,

Is it something similar to?:
http://zeosbugs.firmos.at/view.php?id=20

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