@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

@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:

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.

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:

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... 😉

@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:

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:

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.

@Peter Amiri Awesome cheers. Ill check this out 🙂 Thats fantastic