Error while execute Store Procedure

[knancys]

While I execute my store procedure in second time, result a error.

e.g:
ZStoredProc.ParamByName('IDVENDEDOR').AsInteger := ....
ZStoredProc.ExecProc;

But if i exit the project and return, normal functioning of the SP, what I am not remembering to make?

[zippo]

What is the error?
SQL?
...
more data, please!

[dudeski]

Do you create your Stored Procedure on rum time?
If this is case then second time you are creating component you are creating component what already exist.

Pls be more specific (error discription etc.)

[knancys]

Well, I am not creating the ZStoredProc in run-time.
Executing some tests I evidenced that it does not have errors in Store Procedure.

Executing second time the ZStoredProc, return this erros:
SQL Error: Dynamic SQL Error parameter mismatch for procedure
CALCID_VENDEDORDETAIL. Error Code -902. Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements The SQL: EXECUTE PROCEDURE CALCID_VENDEDORDETAIL(?,?);.

Press Ok to ignore
Press Cancel to stop the program.

where:
CALCID_VENDEDORDETAIL is name of my Store Procedure and have two parameters, IDVENDEDOR (input) and IDRESULT (Result)

[Michael]

Hi knancys,

what database are you using? If it is Firebird, then I would suggest to read the "TZStoredProc"-Section of the article "The ZeosLib DBOs 6.1.5 - With Delphi 7 and Firebird 1.5" maybe this could give you a hint to solve the problem.

The idea is to call the StoredProc with an SQL-Statement via TZQuery... Sometimes TZStoredProc causes bad trouble but we could not fix this behaviour, yet.

Regards.

[knancys]

I am using Interbase 6 + Lazarus, but I go to try exactly thus

[knancys]

Sorry a question, but I am making this:

In ZQuery1.SQL:
execute procedure CALCID_VENDEDORDETAIL :idvendedor

In Unit:
ZQuery1.ParamByName('IDVENDEDOR').AsInteger := StrToInt(Edit1.Text);
ZQuery1.ExecSQL;


When I execute, returns this error:
SQL Error: message length error (encountered 0, expected Error Code: -901.
Unsuccessful execution cauded by system error that does not preclude successful excecution of subsequent statement The SQL: execute procedure CALCID_VENDEDORDETAIL ?


Add: The Store Procedure have two parameters, one input and other result

[knancys]

I am obtaining results, reading the Knowledge Base:
An introduction to the basic usage of ZeosLib DBOs and Firebird basics

But my ZConnection is AutoCommit=True and this is my code:

Code: Select all

ZStoredProc.ParamByName('IDVENDEDOR').AsInteger := ZTable.FieldByName('IDVENDEDOR').AsInteger;
ZConnection1.StartTransaction;

Try
     ZStoredProc.ExecProc;
except
     ZConnection1.Rollback;
end;
       
ZConnection1.Commit;

However if execute this code two followed times, returns this error:
Invalid operation in AutoCommit mode.

But if set Zconnetion to AutoCommit=False returns this error:
Invalid operation non in AutoCommit mode.

Some suggestion?

[tkszeos]

Try this code

[syntax="delphi"]
ZStoredProc.ParamByName('IDVENDEDOR').AsInteger := ZTable.FieldByName('IDVENDEDOR').AsInteger;
ZConnection1.StartTransaction;

Try
ZStoredProc.ExecProc;
ZConnection1.Commit;
except
ZConnection1.Rollback;
end;
[/syntax]

Nicola

[knancys]

tkszeos, Thank you

But I am with my first error:
Executing second time the ZStoredProc, return this erros:
SQL Error: Dynamic SQL Error parameter mismatch for procedure
CALCID_VENDEDORDETAIL. Error Code -902. Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements The SQL: EXECUTE PROCEDURE CALCID_VENDEDORDETAIL(?,?);.

[tkszeos]

It verifies the value of the field "IDVENDEDOR" (for example ShowMessage(ZTable.FieldByName ('IDVENDEDOR').AsString)) before is passed as parameter to the stored procedures, perhaps it is null

Nicola

[knancys]

Sorry a long time
Nicola,

I tested the parameter IDVENDEDOR and is correct, but some suggestion?

[zippo]

Try to execute tho procedure without parameters - write them directly in the SQL?

[Hikari]

The idea is to call the StoredProc with an SQL-Statement via TZQuery... Sometimes TZStoredProc causes bad trouble but we could not fix this behaviour, yet.

Hi Michael, I'm using TZQuery to call a stored procedure, plz take a look if I'm doing it right:

Code: Select all

void TQuestForm::CompleteQuest(int character, int quest, bool complete){
        char sql[200];
        sprintf(sql,"execute procedure CompleteQuest(%d,%d,%d)",character,quest,
                        complete?1:0);

                MainForm->ZQueryGeneric->SQL->Text=sql;
                MainForm->ZQueryGeneric->Open();
                MainForm->ZQueryGeneric->Close();
}

I simplified it a bit. The procedure receives 3 parameters and returns 1, so I build the sql string, set it on SQL property and open & close the TZQuery.
(I'm using BCB6 with Zeos 6.1.5 and Firebird 2.0 )

The objective of this procedure is to change some data on database.

It works as needed, but there's an inconsistency: on my app the data is changed immediately and shown accordingly, but on IBExpert it's not changed I must close my app to see the changes on IBExpert, and if I kill my app it's never changed on database. I suppose it's being cached on the app and is only commited when the ZConnection is closed.

I have other ZQuery and any change on Data Controls is auto commited.

Do you see anything wrong I'm doing or have any suggestion to solve it?


Tnx ^^

[amzeos]

Hi all,
in my application the problem in this topic is raised after upgrading from zeos 6.0.5 to 6.6.0-beta.

I use D7 and fb2.0.

My solution is:

procedure TForm1.Button1Click(Sender: TObject);
var
id: integer;
begin
ZStoredProc1.CleanupInstance;
ZStoredProc1.ParamByName('PAR1').AsString := 'T1';
ZStoredProc1.ExecProc;
id := ZStoredProc1.ParamByName('RES1').AsInteger;
end;

Using CleanupInstance is not reccomended but works (see D7 help).
I think there is a bug in TZStoredProc.

By
amzeos