http://coldfusion.com logo
#lucee
Title
# lucee
m

mauzer

03/31/2022, 4:52 PM
@Peter Amiri I was trying this: qryResult = queryExecute("CALL dbo.spgetaoid(params = {clientID: arguments.aostruct.clientID)", {}, {datasource="dshdev"}); but not too sure of the syntax in passing in parameters. This does sem to work as it recognises the procedure but I cant seem to get the parameters right
🙏 1
a

Adam Cameron

03/31/2022, 8:58 PM
@mauzer pls use threads when having a discussion instead of a new post for every comment. The docs for
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:
Copy code
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.
🙏 1
Seems odd that the inability to call PGSQL procs with
<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
)
🙏 1
m

mauzer

04/01/2022, 8:28 AM
Ok I have this statement but I'm getting an error with datasource. The datasource is correct but I think the syntax is incorrect.
qryResult = queryExecute('CALL dbo.spgetaoid(:clientID), {clientID=100})', {datasource="dshdev"})
I'm getting this error:
@Adam Cameron Hi Adam Tried this but I'm getting this error:
Is this because we need to include / define (I assume so) the parameter types. I have tried including them in the syntax but still get errors
a

Adam Cameron

04/01/2022, 10:15 AM
I have tried including them in the syntax but still get errors
I'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... 😉
And, please... don't post pictures of code and error messages. Provide the actual text. We might need to copy and paste it etc.
👍 1
p

Peter Amiri

04/01/2022, 4:03 PM
@mauzer I had a chance to do some testing here. I set up a Postgres server inside a Docker container and setup a basic table:
Copy code
create 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:
Copy code
insert into accounts(name,balance)
values('Bob',10000);

insert into accounts(name,balance)
values('Alice',10000);
Then I created a stored procedure:
Copy code
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.
Copy 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:
Copy code
<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:
Copy code
<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.
1
m

mauzer

04/01/2022, 4:05 PM
@Peter Amiri Awesome cheers. Ill check this out 🙂 Thats fantastic
@Adam Cameron Sure will do sorry Adam
3 Views