My understanding is that, first stored procedure should be created in the database by dbadmin or any user who has access and from ESQL we can call the stored procedure using EXTERNAL NAME "schema.procedurename". Your code is getting syntax error from App Connect while executing a stored procedure as it doesn't match with the definition of database stored procedure. If this is not the correct understanding, please elaborate the steps, so that I can take a deeper look.
Original Message:
Sent: Tue June 25, 2024 10:24 AM
From: joe alAbiad
Subject: unable to call a procedure in esql.
Hi Uvais,
thank you so much for your reply.
the problem with the stored procedure is that it does not have nay declaration. it is an SQL Stored procedure with begin and end and a SQL statement body. i believe this is why it is working with the passthru. but the passthru is not returning output parameters, only result sets.
CREATE PROCEDURE DATA(
IN INPARM CHAR,
OUT RESULT CHAR nullable,
OUT ERRCDE CHAR nullable,
OUT ERRMSG CHAR nullable,
OUT OUTDATA CHAR nullable)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "schema.procedurename";
the problem is schema.procedurename is not declared in the database and does not have a statement (create procedure ....)
------------------------------
joe alAbiad
Original Message:
Sent: Tue June 25, 2024 09:33 AM
From: uvaise odam
Subject: unable to call a procedure in esql.
Hi Joe,
It seems the issue could be related to out parameter as it is not matching with data type defined in your SP and in your ESQL SP call. passthru statements are executed directly in database engine and you save the result in environment variables or OutputRoot, hence you wont find any issues in this method. I can check further if you can share some sample(s) of your
- Definition of your stored procedure
- Definition of ESQL stored procedure
- CALL statement in ESQL
Thanks
Uvaise
------------------------------
uvaise odam
Original Message:
Sent: Mon June 24, 2024 12:04 PM
From: joe alAbiad
Subject: unable to call a procedure in esql.
Hi Guys,
hope all is well.
I am trying to call a procedure using create procedure .... external...call, but i am receiving the flowing error.
Stored Procedures must match the ESQL CREATE PROCEDURE definition in the following ways: 1: The ESQL parameters must all be of SCALAR type. 2: The directions of each ESQL and Database parameter (IN / OUT / INOUT) must match. 3: The number of parameters in both the ESQL and database definitions must match.
however when i use the passthru it is working, but since i need to get data from the output parameters of the procedure, i am unable to use it.
Can you please help?
------------------------------
joe alAbiad
------------------------------