zeos7, store procedure y firebird error -501

pbrucco · Post by **pbrucco** » 08.02.2010, 22:46

Hello.. Y work with lazarus (Lazarus 0.9.29 r23650 FPC 2.5.1 i386-win32-win32/win64), zeos 7 (revision 788) and firebird 2.1.3.

I have a store procedure that inserts a record. Works well using IBEXPRESS.

In a form I have a grid that displays a table. There is a button to add records which opens another form. There are a component that is associated with the stored procedure.

procedure TfrmAgregarMesa.aceptar_btnClick(Sender: TObject);
begin
spAgregarMesa.ParamByName('codigo').AsInteger := StrToInt(nromesa_edt.Text);
spAgregarMesa.ParamByName('total').AsFloat := 0.00;
spAgregarMesa.ParamByName('codmozo').AsInteger := 1;
spAgregarMesa.ParamByName('impresa').AsString:= 'N';
spAgregarMesa.ParamByName('serie').AsString:= '';
spAgregarMesa.ParamByName('comp').AsInteger:= 0;
spAgregarMesa.ExecProc;
end;

Insert the register, but the exception occurs: "Error code -501. Attemp to reclose a closed cursor. the cursor identified in a FETCH or CLOSE statement is not open"

Can I help me? very thanks

Post by **seawolf** » 08.02.2010, 23:34

Not sure, try ExecSQL instead of ExecProc

pbrucco · Post by **pbrucco** » 08.02.2010, 23:42

Hello seawolf..
not work!! the same error...

pbrucco · Post by **pbrucco** » 09.02.2010, 00:21

The component TZStoredProc not working properly. if I use the component TZReadOnlyQuery not get the error -501:

Configuring this parameters in this component:
connection: the connection
params: (add 6 params)
sql: EXECUTE PROCEDURE AGREAR_MESA (:codigo, :total, :codmozo, :impresa, :serie, :comp)

then.....
qryAgregarMesa.ParamByName('codigo').AsInteger := StrToInt(nromesa_edt.Text);
qryAgregarMesa.ParamByName('total').AsFloat := 0.00;
qryAgregarMesa.ParamByName('codmozo').AsInteger := 1;
qryAgregarMesa.ParamByName('impresa').AsString:= 'N';
qryAgregarMesa.ParamByName('serie').AsString:= '';
qryAgregarMesa.ParamByName('comp').AsInteger:= 0;
qryAgregarMesa.ExecSQL;

works ok...

would be good to solve the problem with that component

Post by **mdaems** » 09.02.2010, 01:21

Hi pbrucco,

Can you log what's sent to the server using the TZSQL monitor?
Best would be if you make a small test with a connection, a TZSQLMonitor, a TZReadonlyQuery and a TZStoredProc.
Just connect, execute the query and execute the storedproc. Then, show us the log file from the monitor.
I suppose there's some difference between the automatically formed statement and the one you made.

Mark

pbrucco · Post by **pbrucco** » 09.02.2010, 14:54

Hello..

This is the log. First run the TZReadOnlyquery, then the TZStoredProc.. and crash!!

2010-02-09 10:48:53 cat: Prepare, proto: firebird-2.1, msg: Statement 1 : EXECUTE PROCEDURE AGREGAR_MESA (?, ?, ?, ?, ?, ?)
2010-02-09 10:48:53 cat: Bind prepared, proto: firebird-2.1, msg: Statement 1 : 1,0,1,'N','',0,
2010-02-09 10:48:53 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION COMMIT
2010-02-09 10:48:53 cat: Execute prepared, proto: firebird-2.1, msg: Statement 1
2010-02-09 10:48:57 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION COMMIT
2010-02-09 10:48:57 cat: Execute, proto: firebird-2.1, msg: AGREGAR_MESA
2010-02-09 10:48:57 cat: Other, proto: firebird-2.1, msg: Dynamic SQL Error SQL error code = -501 Attempt to reclose a closed cursor, errcode: -501, error: The cursor identified in a FETCH or CLOSE statement is not open.
2010-02-09 10:49:06 cat: Transaction, proto: firebird-2.1, msg: COMMITT TRANSACTION "D:\lz\resto\resto.fdb"
2010-02-09 10:49:06 cat: Connect, proto: firebird-2.1, msg: DISCONNECT FROM "D:\lz\resto\resto.fdb"

Thanks!!

pbrucco · Post by **pbrucco** » 09.02.2010, 15:13

separate logs:

using TZReadOnlyQuery:
2010-02-09 11:12:30 cat: Connect, proto: firebird-2.1, msg: CONNECT TO "D:\lz\resto\resto.fdb" AS USER "SYSDBA"
2010-02-09 11:12:30 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION STARTED.
2010-02-09 11:12:35 cat: Prepare, proto: firebird-2.1, msg: Statement 1 : EXECUTE PROCEDURE AGREGAR_MESA (?, ?, ?, ?, ?, ?)
2010-02-09 11:12:35 cat: Bind prepared, proto: firebird-2.1, msg: Statement 1 : 55,0,1,'N','',0,
2010-02-09 11:12:35 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION COMMIT
2010-02-09 11:12:35 cat: Execute prepared, proto: firebird-2.1, msg: Statement 1
2010-02-09 11:12:37 cat: Transaction, proto: firebird-2.1, msg: COMMITT TRANSACTION "D:\lz\resto\resto.fdb"
2010-02-09 11:12:37 cat: Connect, proto: firebird-2.1, msg: DISCONNECT FROM "D:\lz\resto\resto.fdb"

Using TZStoredProc
2010-02-09 11:10:22 cat: Connect, proto: firebird-2.1, msg: CONNECT TO "D:\lz\resto\resto.fdb" AS USER "SYSDBA"
2010-02-09 11:10:22 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION STARTED.
2010-02-09 11:10:26 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION COMMIT
2010-02-09 11:10:26 cat: Execute, proto: firebird-2.1, msg: AGREGAR_MESA
2010-02-09 11:10:26 cat: Other, proto: firebird-2.1, msg: Dynamic SQL Error SQL error code = -501 Attempt to reclose a closed cursor, errcode: -501, error: The cursor identified in a FETCH or CLOSE statement is not open.
2010-02-09 11:10:30 cat: Transaction, proto: firebird-2.1, msg: COMMITT TRANSACTION "D:\lz\resto\resto.fdb"
2010-02-09 11:10:30 cat: Connect, proto: firebird-2.1, msg: DISCONNECT FROM "D:\lz\resto\resto.fdb"

pbrucco · Post by **pbrucco** » 09.02.2010, 15:18

with autocommit=true or false is the same

Post by **mdaems** » 11.02.2010, 23:17

2010-02-09 11:10:26 cat: Execute, proto: firebird-2.1, msg: AGREGAR_MESA

This is a strange line. It means something is broken terribly. I'd like some Firebird + Lazarus user to give this a serious look.
Normally the behaviour of a TZStoredProc should be comparable to the behaviour of a TZQuery with the 'only' difference that a TZStoredProc automatically creates the SQL statement. And that seems to be exactly what's going wrong in your case.
What may have caused your problem is the new prepared statement approach we're using.
If you want ME to have a look at this problem I need a test case that is build as a complete independent embedded firebird application. So the zip should contain the project files, all embedded firebird related dll's (and other necessary files) and the database file. So I should jusrt unzip the package, and compile the project. I'm not a FB server user and I'm not planning to be one.

You can reach me using the normal channels. Private message, or zeoslib at gmail dot com.

Mark

esabbagh · Post by **esabbagh** » 27.03.2010, 05:39

I'm seeing the same error message, using Firebird embedded 2.1 and Delphi 2009. I'm trying to activate a ZQuery, which is configured with SQL.Text = 'Select * from vConfig'. vConfig is a view with permissions set so that only a specific role can select from it -- and I've got the rolename parameter set correctly. I've got a ZUpdateSQL associated with the ZQuery as well, and the vConfig view has triggers set up on it to handle the inserts, deletes, and updates. Pretty straightforward, yet I also receive this error, and there's not a stored procedure in sight...

esabbagh · Post by **esabbagh** » 27.03.2010, 17:15

OK, things have progressed a bit... I moved the ZEOS components out of a data module I had set up, and placed them on my main form, and the error messages changed. Whereas before the debugger refused to step down into the ZConnection.Connect call, now apparently the connection succeeded -- revealing some problems in preparing the ZQuery. The details of the ZQuery SQL aren't important, I don't think, since it's just a "SELECT *" from a view -- but the fact that the connection succeeded after moving things out of the datamodule probably is. Why would preparation depend on what kind of TForm descendant the ZEOS components were placed on?

esabbagh · Post by **esabbagh** » 27.03.2010, 17:36

Finishing up -- the root problem was that I was stupidly storing some XML in a VARCHAR field that was 16Kb long, instead of in a BLOB, and the row length overflowed. This must have been the root cause of the error that prevented the prepare, and thus prevented the connect (since I was activating the ZQuery in an AfterConnect event). But why couldn't I see the informative error message until I moved the ZEOS components on to the main form and out of the data module?