Page 25 of 44
Posted: 23.08.2012, 08:27
by 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?
Posted: 23.08.2012, 10:39
by 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
Posted: 23.08.2012, 10:49
by 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?
Posted: 23.08.2012, 11:01
by 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 '').
Posted: 23.08.2012, 11:16
by EgonHugeist
olehs,
Lol, that was i was trying to say with my clumsy english too. Can you do this before your vacation?
Posted: 23.08.2012, 11:58
by olehs
EgonHugeist,
Yes
Posted: 23.08.2012, 12:02
by 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
Posted: 23.08.2012, 13:32
by 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..
Posted: 23.08.2012, 18:27
by 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?
Posted: 23.08.2012, 20:32
by 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.
Posted: 24.08.2012, 10:11
by 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..
Posted: 24.08.2012, 14:25
by 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
Posted: 27.08.2012, 14:52
by 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
Posted: 28.08.2012, 07:57
by 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..
Posted: 28.08.2012, 15:09
by 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