Calling PostgreSQL "stored procedure" from Lucee

dev
1

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?

2

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

image
image814×275 13.4 KB

I filed a new bug about the Postgres driver [LDEV-2962] - Lucee

3

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?

4

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?

5

Looks like a quirky bug introduced with Postgres v11

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

6

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?

7

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 [LDEV-2963] - Lucee, basically it seems PG 11 broke things and Lucee needs to adapt.

8

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:

9

I had this problem and first tried adding escapeSyntaxCallMode=callIfNoReturn to my JDBC connection string (as suggested by cassiomolin in Issue while executing PostgreSQL Stored Procedure - Stack Overflow ), and it worked, but something else broke so I changed my PROCEDURE to a FUNCTION as a workaround.