IN and OUT params and TSQLUPDATE params

[marcocot]

Hello,
I am having this excepion raised when I try to pass to a stored procedure
an TZSQLUPDATE component param.

"SQL Error: OUT or INOUT argument 6 for routine venditori.APPEND_AGE_EMAIL is not a variable or NEW pseudo-variable in BEFORE trigger."

here what I wrote in INSERTSQL property:

CALL APPEND_AGE_EMAIL(
:IS_DEFAULT,
:ID_AGENTE_SRCDB,
:ID_AGENTE,
:E_MAIL,
:ID_STATO_EMAIL,
:ID_AGENTE_EMAILS_SRCDB);

:ID_AGENTE_EMAILS_SRCDB (the last one) is an out/input parameter in my storeprocedure. IS there a way to pass a value from a store procedure to a param (:ID_AGENTE_EMAILS_SRCDB)? Where did I go wrong?

thanks for any help

[mdaems]

Try to use this statement :

Code: Select all

SET @ID_AGENTE_EMAILS_SRCDB=:ID_AGENTE_EMAILS_SRCDB;CALL APPEND_AGE_EMAIL(:IS_DEFAULT,:ID_AGENTE_SRCDB,:ID_AGENTE,:E_MAIL,:ID_STATO_EMAIL,@ID_AGENTE_EMAILS_SRCDB);

Sorry, I didn't test it...

[marcocot]

Try to use this statement :

Code: Select all

SET @ID_AGENTE_EMAILS_SRCDB=:ID_AGENTE_EMAILS_SRCDB;CALL APPEND_AGE_EMAIL(:IS_DEFAULT,:ID_AGENTE_SRCDB,:ID_AGENTE,:E_MAIL,:ID_STATO_EMAIL,@ID_AGENTE_EMAILS_SRCDB);

Sorry, I didn't test it...

I could not try it myself, as I have changed my code... But I got the same problem with TZquery. If I try your code in TZquery, I got the message
"SQL Error: Commands out of sync; you can't run this command now".

CALL APPEND_EMAIL(:MSGID,:SUBJECT ,
:BODY,:ORIGINALE,:ADELIMITER,@INSERTED);
:INSERTED = @INSERTED;

It accepts @INSERTED but I have no way to read it

I have also tryied:
CALL APPEND_EMAIL(:MSGID,:SUBJECT ,
:BODY,:ORIGINALE,:ADELIMITER,@:INSERTED);

but when I try to read the value of the INSERTED param, I don't get the right value. I defined the last param (INSERTED) as INOUT param, I modified it in my store procedure but I can't figure out how to read it
any idea?

thanks again
Marco

[mdaems]

add string 'CLIENT_MULTI_STATEMENTS=1' to property ZConnection1.Properties

Also : to retrieve the value of the @variable try 'select @variable;'

Mark

[marcocot]

add string 'CLIENT_MULTI_STATEMENTS=1' to property ZConnection1.Properties

Also : to retrieve the value of the @variable try 'select @variable;'

Mark

thanks for your reply.
Unfurtunately nothing have changed. I tryied this simple procedure:

CREATE DEFINER = 'root'@'%' PROCEDURE `new_proc`(OUT _VALUE INTEGER(11))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET _VALUE = 1;
END;

setted CLIENT_MULTI_STATEMENTS=1 in zConnection (I already setted it).

In my ZQUERY's SQL property:

call new_proc(@VALUE);
SELECT @VALUE;

I call the ExecSQL method and I have raised the execption with message "SQL Error: Commands out of sync; you can't run this command now"

Is there anything wrong in what I wrote?
thanks
Marco

[marcocot]

add string 'CLIENT_MULTI_STATEMENTS=1' to property ZConnection1.Properties

Also : to retrieve the value of the @variable try 'select @variable;'

Mark

thanks for your reply.
Unfurtunately nothing have changed. I tryied this simple procedure:

CREATE DEFINER = 'root'@'%' PROCEDURE `new_proc`(OUT _VALUE INTEGER(11))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET _VALUE = 1;
END;

setted CLIENT_MULTI_STATEMENTS=1 in zConnection (I already setted it).

In my ZQUERY's SQL property:

call new_proc(@VALUE);
SELECT @VALUE;

I call the ExecSQL method and I have raised the execption with message "SQL Error: Commands out of sync; you can't run this command now"

Is there anything wrong in what I wrote?
thanks
Marco

I solve in this way:

Zquery1.SQL.Clear;
Zquery1.SQL.Add('call new_proc(@VALUE);');
Zquery1.ExecSQL;
Zquery1.SQL.Clear;
Zquery1.SQL.Add('SELECT @VALUE AS STATUS');
Zquery1.Open;
IF Zquery.FieldByName('STATUS).AsInteger = 1 then
showmessage('It Works!')

thanks your for your adivece !

Marco

[mdaems]

My fault. I should have known only first resultset of multiple statements can be shown. Your solution is excellent.
I hope others will also benefit.