Full Unicode/Ansi-Support in /testing branch

[EgonHugeist]

@miab3,

Added your test to the suites yesterday night. And i can confirm the issue. Now. I have no clue if firebird does support a direct unprepare call. The issue is: after calling the procedure with the parameters a second call will be executed to fetch the return values. Zeos uses allways the prepared statement for firebird and prepares "select * from abtest" which makes it impossibe to call an update query afterwards. I think we can fix it by changing the code sequence on the TZStoredProc or (better) we can unprepare the statement handle and prepare a new call. However i think this can be fixed..

Now i thought about having a generic test for that component. But i need the proposed DDL statement now for all PlainDrivers. Can you do this for me? Commit rights? Or attached.

@olehs,

The differences between these two PlainDrivers are amazing! I'm affraid! So i confirm you thought about rewriting the PostgreSQL API for that component. Actually i made the test without fails. I want to inform Mark about new comming fails before waiting them in an expected behavior. According the names Parameters: Yes it word be a good choice to return the $x name for unnamed params but the real name for named once. So it would be a good start, if you can do this. What do you think about my define proposal?

[miab3]

@EgonHugeist

Not so good to know the procedures for different db but:

for Oracle:

Code: Select all

PROCEDURE SYSTEM.ABTEST   (p1 NUMBER, p2 NUMBER, p3 VARCHAR,
  p4 OUT NUMBER, p5 OUT VARCHAR) IS
BEGIN
  p4 := p1 * 10 + p2;
  p5 := p3 || p3;
END;

It is recognized by ZEOS but does not work ExecProc (ORA-06550).

for MSSQL:

Code: Select all

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [ABTEST]
@p1 int,
@p2 int,
@p3 varchar(10),
@p4 int output,
@p5 varchar(20) output
AS
select @p4 = @p1 * 10 + @p2;
select @p5 = @p3 + @p3;

It is recognized by ZEOS and works ExecProc (with error output string).

Michal

[EgonHugeist]

miab3,

Not so good to know the procedures for different db

Yes indeed but it seems for me this is an component which is not often used here..
So it would be great if Oleg can help here.

Does SQLite support StoredProcedures? MySQL?

The others i'll add tonight.

Oleg,

i propose to stop working in the CAPI prepared statments and will have a look to Oracle and MySQL. Double coding on the same issue produces only lead of pain and is mostly time wasting for both. What do you think?

[olehs]

EgonHugeist,

According the names Parameters: Yes it word be a good choice to return the $x name for unnamed params but the real name for named once.

Well actually I was talking about leaving unnamed params without a name (an empty string '').

[EgonHugeist]

olehs,

Lol, that was i was trying to say with my clumsy english too. Can you do this before your vacation?

[olehs]

EgonHugeist,

Yes

[miab3]

@EgonHugeist

In MySQL, it seems that they are.
That I was able to produce.

Code: Select all

CREATE DEFINER = 'root'@'%' PROCEDURE `ABTEST`(
        IN `p1` INTEGER,
        IN `p2` INTEGER,
        IN `p3` VARCHAR(10),
        OUT `p4` INTEGER,
        OUT `p5` VARCHAR(20)
    )
    DETERMINISTIC
    NO SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  set `p4` = `p1` * 10 + `p2`;
  set `p5` = concat(`p3`, `p3`);
END;

But it does not recognize by ZEOS.

Michal

[EgonHugeist]

olehs,

Yes

Sounds good!

miab3,
Yap it seems for me the same issue ):

And i got an answer of Mark according this issues. It seems that the stored Procedures can return Multiple resultsets. And that was not supported before. Now i don't know some procedure which can return those resultsets. Do you have got an idea? Btw. I personally think this was the same issues like with the TNested fields of oracle. But i Don't know it exactly..

[olehs]

EgonHugeist,

Done with parameter names. Here is how it works:
if PG version is < 8.0, then params are still named $0, $1 and so on (I couldn't find the way to retrieve names for older versions).
on PG 8.0+ Parameter names are retrieved, unnamed params' names are empty strings.

But there is one exception: when absolutely all parameters are unnamed (in, out, inout...) then in TZStoredProc.Params they will be named $0, $1, $2... (it wasn't my idea, it just happened be itself. PG returns NULL in paramnames array if all parameters are unnamed and I think it becomes a nice feature).

Michael, should I disable this code with some DEFINE or this is not such a big change?

[EgonHugeist]

olehs,

i see no real issus, so commit the patch. Then i'll have a look. Nice would be a TZConnection.Properties value to keep the old behavior. But i think we're looking forward..

Thank you Oleg and have a nice vacation.

[EgonHugeist]

miab3,

good news the Interbase/Firebird issue you have reported should be gone. Rev.1668

Michal do you have a clue how i can get the MySQL and oracle procedures running with a script and the TZSQLProzessor? I'm not able to get the right syntax running with the testsuites..

[miab3]

@EgonHugeist

For FireBird for the greater number (66 000) ExecProc:

Code: Select all

var
  i: integer;
begin
  ZStoredProc1.StoredProcName:= 'ABTEST';
  ZStoredProc1.Params[2].AsInteger:= 50;
  ZStoredProc1.Params[3].AsInteger:= 100;
  ZStoredProc1.Params[4].AsWideString:= 'a';
  for i:= 0 to 66000 do
  begin
    ZStoredProc1.Params[3].AsInteger:= i;
    ZStoredProc1.ExecProc;
  end;
  Edit1.Text:= ZStoredProc1.ParamByName('P4').AsString +
    ' ' + ZStoredProc1.ParamByName('P5').AsString;
end;

application allocates an additional 80MB
and gets an error as attached:

At 64 000 lives,
I think something is not released.

r1669, Delphi XE2-32.

Michal

[miab3]

@EgonHugeist

For such a code(FireBird):

Code: Select all

    ZStoredProc1.StoredProcName:= 'ABTEST';
    ZStoredProc1.Params[2].AsInteger:= 50;
    ZStoredProc1.Params[3].AsInteger:= 100;
    ZStoredProc1.Params[4].AsString:= 'a';
    ZStoredProc1.ExecProc;
    ZStoredProc1.ExecProc;

For Delphi 2006/2007 I get an error as in the attached:

For Delpi XE2 is fine.

r1669.

Michal

[EgonHugeist]

@miab3,

Both issues confirmed. It seems for me like a gneric issue. This week i've not very much time. But i hope i can help again..

[miab3]

@EgonHugeist

Michael,
Would be nice if you solved the procedures.

Zeos7_branches_ testing_r1669(r1675)
http://svn.code.sf.net/p/zeoslib/code-0 ... s/testing/
compiles and runs on (I have tested):

- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++32,
- Lazarus Win 1.1/fpc 2.7.1 32/64,
- Lazarus Lin 1.1/fpc 2.7.1 64 on Debian 64-bit (LMDE 201204).


Michal