Calling PostgreSQL "stored procedure" from Lucee

I am trying to call a “stored procedure” (i.e. no return value) from Lucee, but I get this error:

(ERROR: sp_notify(character varying, character varying, character varying, character varying) is a procedure Hint: To call a procedure, use CALL. Position: 15)

<cftry>
	<cfstoredproc procedure="sp_notify" datasource="#application.dataSource#" debug=Yes>
		<cfprocparam type="In"  variable="in_dest"      cfsqltype="CF_SQL_VARCHAR" value="to_me@hotmail.com">	
		<cfprocparam type="In"  variable="in_from"  	cfsqltype="CF_SQL_VARCHAR" value="from_me@hotmail.com">	
		<cfprocparam type="In"  variable="in_trans"   	cfsqltype="CF_SQL_VARCHAR" value="email">	
		<cfprocparam type="In"  variable="in_message"   cfsqltype="CF_SQL_VARCHAR" value="Test calling stored proc from Lucee">	
	</cfstoredproc> 
	
	<cfcatch type="database">
		<cfset feedbackMsg = 'Failed to call sp_notify !<br>(' & cfcatch.Message & ')'>
	</cfcatch> 
</cftry>

I can’t easily change the code in the database, as it is called from other places (and languages). I can make a call to a PostgreSQL stored function without issue.

Am I missing something here?

Which version of the PostgreSQL driver (extension) are you using?

Please always describe your stack when asking for help.

By default, Lucee installs the 9.4.1212 which is old, there is a newer Postgres 42.1.4 JDBC driver,
but oddly it doesn’t get flagged as needing to be upgraded, except within the extension
page

I filed a new bug about the Postgres driver https://luceeserver.atlassian.net/browse/LDEV-2962

Apologise, I thought I’d have a go with Lucee, so first couple of days of playtime.

I think I’ve followed your instructions for updating to 42.1.4. However, I still get the same error.

Is there any way to confirm which version of the driver, Lucee is actually using?

Look under bundles in the admin, look for the 42.1.4 bundle, it should show up as being active

Welcome to Lucee btw :slight_smile:

Alas, I haven’t used postgres in a long while, does that code sample work in ACF or did you just create it from scratch?

Looks like a quirky bug introduced with Postgres v11

https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us

Can you file a bug in JIRA and link it back to this post?

This is what is in the bundles on the admin, so looks good to me.

PostgreSQL JDBC Driver - JDBC 4.2 (org.postgresql.jdbc42)
Java JDBC 4.2 (JRE 8+) driver for PostgreSQL database 42.1.4 Aug 1, 2017 696kb PostgreSQL Global Development Group PostgreSQL active

Sorry, I have no idea what ACF is. This was “myFirstScript.cfm” tinkered to include some DB code, as I start to play.

It looks to me, that it still thinks it’s a stored function (& in fact stored functions work fine). I can’t see any switches to force it to do a “Call” to use a stored proc.

I am bit stuck, is there a way to escalate it, debug at a greater level or even look at the source code?

ACF is just our shorthand for Adobe ColdFusion, I just wanted to know if you were porting some existing ACF code over to Lucee.

I’ve filed a bug https://luceeserver.atlassian.net/browse/LDEV-2963, basically it seems PG 11 broke things and Lucee needs to adapt.

It was originally a SmithProject, however we have changed the DB from Oracle to PostgreSQL, so I am in the process of reworking the project to Lucee.

There are only a couple of stored procedure calls in this application, so I will write a stored function wrapper, for now. Thanks for your assistance :smile: