Facing issue with executestoreprocedure()

Hi Vinod,

Could you please try running the SP in the sqldeveloper and try to run it using execute stored procedure smart service and post us the results.

Hi Shanmukha ,

IT is working fine when ran from sqldeveloper but same when tried to call Appian with executestoredprocedure function it is returing o items.. Please see below.

with(
/* local!resultSet:*/fn!executestoredprocedure(
dataSourceName: cons!DATA_SOURCE,
procedureName: "SP_#####_REPORT",
inputs: {
{
name: "fromDate",
value: todate(
ri!fromDate
)
},
{
name: "toDate",
value: todate(
ri!toDate
)
}
}
)

),

I am passing input to SP like below:
create or replace PROCEDURE sp_###_REPORT(
fromDate IN timestamp, toDate IN timestamp)


select * from ABC where createdDate between TO_DATE(fromDate,'DD/MM/YYYY') and TO_DATE(toDate,'DD/MM/YYYY')

SP is very complex , i am just hinghlighting required part where seems I am doing some mistake.

Thanks.

Hi Vinod,

Please check if your stored procs queries are performing any create,update, delete operations in DB because AFAIK executestoredprocedure function only works for Select scripts.

Hi Vinod,

If please try find out the logs in appian as well as database side? When even you make any request to DB that will be recorded some where in the system you might need help with the DBA to find out so that you can get the details of what you are exactly requesting from Appian side in terms of parameters and what exactly database is returning so that we can narrow down the issue.

Vinod Tate ,

We can write DML operations as well in the SP and that can be called from Appian for doing DML operations on data.

I think calls from fn!executestoredprocedure are automatically wrapped in a transaction. If your database has GTID enabled (which I believe later versions of Appian require) you may need to check if the stored proc is CREATING or DROPPING temporary tables and refactor as needed.

See link for GTID restrictions.
dev.mysql.com/.../replication-gtids-restrictions.html

Does your stored procedure return multiple result sets? You might be required to index your local!result variable if that's the case.

In the stored procedure If you're writing to an OUT parameter, instead update so it is an INOUT parameter

after executing, the parameter can be accessed like: local!result.parameters.inoutparametername

if the stored procedure executes a query, those results will be returned in the 'result' field: local!result.result

there are also some issues where numbers need to be cast as a char in the stored procedure to be returned correctly