mauzer
03/31/2022, 4:52 PMAdam Cameron
queryExecute
for Lucee are reasonably clear (https://docs.lucee.org/reference/functions/queryexecute.html). The function signature is: QueryExecute( sql=string, params=any, options=struct )
. So the SQL statement is the first param; the params are the second paam, and options are third.
You are trying to put your params in with the SQL string.
I suspect you want something like this:
qryResult = queryExecute("CALL dbo.spgetaoid(:clientID)", {clientID=arguments.aostruct.clientID}, {datasource="dshdev"})
Hopefully the param to that proc is just an in
one, cos no idea how to pass an out
one. I also dunno how this will work if yer proc returns more than one resultset.
But for procs that simply take inputs and return some records, this should work.<cfstoredproc>
is a bug that is OK to leave around and open for approaching a coupla years? This is not exactly edge-case functionality, and I don't think the alternative approach suggested covers all the bases <cfstoredproc>
does (although that could be a shortfall in my understanding of how call
can be used in queryexecute
)mauzer
04/01/2022, 8:28 AMqryResult = queryExecute('CALL dbo.spgetaoid(:clientID), {clientID=100})', {datasource="dshdev"})
I'm getting this error:Adam Cameron
I have tried including them in the syntax but still get errorsI'm trying my very best telepathy to see what code you wrote and what the error is, but it's just not working I'm afraid you're just gonna need to tell us what it is instead... 😉
Peter Amiri
04/01/2022, 4:03 PMcreate table accounts (
id int generated by default as identity,
name varchar(100) not null,
balance dec(15,2) not null,
primary key(id)
);
Then inserted a couple of records into the table:
insert into accounts(name,balance)
values('Bob',10000);
insert into accounts(name,balance)
values('Alice',10000);
Then I created a stored procedure:
create or replace procedure transfer(
sender int,
receiver int,
amount int
)
language plpgsql
as $$
begin
-- subtracting the amount from the sender's account
update accounts
set balance = balance - amount
where id = sender;
-- adding the amount to the receiver's account
update accounts
set balance = balance + amount
where id = receiver;
commit;
end;$$
All of this was in DBeaver and on the Postgres server.
Now let's get to the CF code.
<cfquery name="data" datasource="postgres">
SELECT * FROM accounts;
</cfquery>
<cfdump var="#data#">
Just to see if I can get data back from Postgres and I could.
Then I tried using <cfstoredproc>
as well as the script version and I was getting errors. So the code that didn't work is this:
<cfstoredproc procedure="transfer" datasource="postgres">
<cfprocparam cfsqltype="CF_SQL_INTEGER" value="2">
<cfprocparam cfsqltype="CF_SQL_INTEGER" value="1">
<cfprocparam cfsqltype="CF_SQL_INTEGER" value="1000">
</cfstoredproc>
<cfscript>
cfstoredproc( procedure="transfer",datasource="postgres" ) {
cfprocparam( cfsqltype="CF_SQL_INTEGER", value="1" );
cfprocparam( cfsqltype="CF_SQL_INTEGER", value="2" );
cfprocparam( cfsqltype="CF_SQL_INTEGER", value="1000" );
}
</cfscript>
Both of these generated errors.
But I was able to call the stored procedure using the <CFQuery>
or queryExecute
methods. Here is the code for doing that:
<cfquery datasource="postgres">
call transfer(1,2,1000)
</cfquery>
<cfscript>
data = queryExecute(
"call transfer(:from,:to,:amount)",
{
from={value=2, CFSQLType='CF_SQL_INTEGER'},
to={value=1, CFSQLType='CF_SQL_INTEGER'},
amount={value=1000, CFSQLType='CF_SQL_INTEGER'}
},
{datasource = "postgres"}
);
</cfscript>
I think there are some issues with the way the Postgres driver handles SQL types though. For instance when I first created the stored procedure I made the third parameter a DECIMAL cause it was supposed to be an amount. But when I tried calling the stored procedure I was getting an error using CF_SQL_DECIMAL. But when I made it an integer like the first two arguments then I could make the call. Hopefully this is useful in your quest to call a stored procedure in Postgres.mauzer
04/01/2022, 4:05 PM