Hi guys still looking for a solution with Lucee Postgres Sto cfml #lucee

Hi guys still looking for a solution with Lucee - ...

mauzer

03/30/2022, 9:25 AM

Hi guys still looking for a solution with Lucee - Postgres Stored procedures if anyone can help. I have tried absoolutely everything. I even stripped the stord proc to one single parameter but still getting the same errors. Is there a driver or compatibility issue with Lucee and Postges/Stored Procs as it appears that this is the only possible diagnosis for me. ERROR: function dbo.spgetaoid(character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. I have tried changing procedure to all lowercase matching the stored proc in the database cfscript and tag based Reduced to just one parameter I have tried different data types Stored procedure in PostGres PGAdmin compiles and no errors on that end. Postgres Datasource is connected via Lucee admin in datasources no problem as it executes SELECT/UPDATE statements with no issues I have completely run out of ideas so if someone out there can help it would be greatly appreciated.

thisOldDave

03/30/2022, 10:01 AM

you coul run this to see if the procedure is in the public namespace

Copy code

select n.nspname as schema,
       p.proname as procedure
from pg_proc p
join pg_namespace n on p.pronamespace = n.oid
where n.nspname not in ('pg_catalog', 'information_schema')

mauzer

03/30/2022, 10:09 AM

ah the stored procedure is in a namespace schema called dbo

thisOldDave

03/30/2022, 10:12 AM

so I expect your user doesn't have permission on the dbo namespace try changeing the name of the proc, just drop the dbo. off the front

mauzer

03/30/2022, 10:14 AM

Ok done that. I moved it to public and still getting the same error

mauzer

03/30/2022, 10:15 AM

message has been deleted

mauzer

03/30/2022, 10:15 AM

message has been deleted

mauzer

03/30/2022, 10:15 AM

message has been deleted

mauzer

03/30/2022, 10:16 AM

Stripped it right down, put it in public and still getting this error

mauzer

03/30/2022, 10:16 AM

message has been deleted

thisOldDave

03/30/2022, 10:17 AM

is cf conecting as user root?

mauzer

03/30/2022, 10:18 AM

yes root

mauzer

03/30/2022, 10:19 AM

Hmmm should CF be set up in Postgres as a user ? Its connecting in the Lucee admin to the datasource with username and password but there CF is not setup as a user within Postgres itself. Should it be ?

thisOldDave

03/30/2022, 10:19 AM

can you call any of the standard functions something like

upper()

mauzer

03/30/2022, 10:20 AM

let me try

mauzer

03/30/2022, 10:21 AM

do i need to pass in parameters for upper?

mauzer

03/30/2022, 10:21 AM

message has been deleted

thisOldDave

03/30/2022, 10:25 AM

yes I would expect that to return

ABC

thisOldDave

03/30/2022, 10:26 AM

the datasource connection will need to be using the user root

mauzer

03/30/2022, 10:28 AM

how do Output the result to get ABC?

mauzer

03/30/2022, 10:28 AM

I have this: <cfstoredproc procedure="upper" datasource="#session.dsn#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="abc"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.aoid"> </cfstoredproc> <cfdump var="#upper#"> <cfabort>

mauzer

03/30/2022, 10:28 AM

but returns an error

mauzer

03/30/2022, 10:28 AM

so dont think that syntax is right

thisOldDave

03/30/2022, 10:30 AM

mauzer

03/30/2022, 10:31 AM

Ah thats it!! 🙂

mauzer

03/30/2022, 10:31 AM

message has been deleted

mauzer

03/30/2022, 10:31 AM

Yes got an output 🙂

thisOldDave

03/30/2022, 10:31 AM

and `

Copy code

<cfprocresult name="qResults">

mauzer

03/30/2022, 10:32 AM

Awesome both work :-)

mauzer

03/30/2022, 10:33 AM

message has been deleted

mauzer

03/30/2022, 10:35 AM

So bizarre just doesnt make sense why it doesnt like that procedure name

thisOldDave

03/30/2022, 10:35 AM

ok so you can connect and call stored procedures in public so It would suggest a problem with security or the proc itself

mauzer

03/30/2022, 10:36 AM

https://files.slack.com/files-pri/T06T9JEE9-F038VK5U2TZ/image.png▾

mauzer

03/30/2022, 10:36 AM

So does that syntax in that stored proc look ok to you ?

mauzer

03/30/2022, 10:36 AM

hmm yeah i guess

mauzer

03/30/2022, 10:37 AM

How do i check the security?

mauzer

03/30/2022, 10:38 AM

This is security of that stored porc

thisOldDave

03/30/2022, 10:43 AM

and you are definitely connecting as root?

mauzer

03/30/2022, 10:51 AM

yes looks ok:

thisOldDave

03/30/2022, 10:52 AM

no from coldfusion

mauzer

03/30/2022, 10:52 AM

oh its from Lucee

mauzer

03/30/2022, 10:53 AM

message has been deleted

mauzer

03/30/2022, 10:53 AM

yes root

thisOldDave

03/30/2022, 10:54 AM

except the lucee connection is to PROD and you are working on dev

mauzer

03/30/2022, 10:56 AM

no thats fine - just the label but its definetly thr right db

mauzer

03/30/2022, 10:56 AM

just way it was named and migrated form MS SQL

mauzer

03/30/2022, 10:56 AM

message has been deleted

thisOldDave

03/30/2022, 10:58 AM

ok I am running out of ideas then I am afraid

mauzer

03/30/2022, 10:58 AM

No worries really appreciate your help Thanks ever so much. I think it could be specific to Lucee tbh. Maybe drivers etc

thisOldDave

03/30/2022, 12:25 PM

could be but then surely upper wouldnt work either

mjclemente

03/30/2022, 12:29 PM

Have you tried removing the stored proc argument? That is, not passing anything to it and just having it return a value. That might help narrow down if the issue is calling the stored proc, or something with the argument type/name.

mjclemente

03/30/2022, 12:32 PM

Also, do you need to specify the schema? That is, dbo.public.storedProcName

mjclemente

03/30/2022, 12:33 PM

Haven't used stored procs with Lucee/Postgres, so I'm just spitballing here.

letskillowen

03/30/2022, 12:54 PM

@mauzer it was brought up in one of your previous threads but you keep saying “procedure” and your sql code is defining a “function”. is/was that not the issue? cfstoredproc isn't going to execute a function.

mauzer

03/30/2022, 1:02 PM

@mjclemente yep tried that too:-(

mauzer

03/30/2022, 1:03 PM

@letskillowen the function is calling the stored procedure

mauzer

03/30/2022, 1:04 PM

@letskillowen so I dont follow the function is set to call the stored proc : <cffunction name="getAOID" access="package" returntype="struct" > <cfargument name="aostruct" type="struct" required="true" >  <cfstoredproc procedure="dbo.spgetaoid" datasource="#session.dsn#"> <cfprocparam cfsqltype="cf_sql_numeric" value="#arguments.aostruct.clientID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.subjectID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.packageID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.clientOrderID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.resultAddress#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.customReportName#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.webBannerGraphic#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.reportGraphic#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.postProcessType#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.postProcessURL#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.additionalScales#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.aoid"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.status"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.hashedAOID"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.assessmentURL"> <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="this.participantID"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.languageUsed"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.resultAddress"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.resultProtocol"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.customReportName"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.reportType"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.jobCategoryTitle"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.webBannerGraphic"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.reportGraphic"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.postProcessType"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.postProcessURL"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.additionalScales"> <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="this.isAssessment"> </cfstoredproc>

mauzer

03/30/2022, 1:05 PM

@letskillowen AH do you mean.... CREATE OR REPLACE FUNCTION dbo.spgetaoid ( varg_clientID varchar(100) ) LANGUAGE plpgsql;

letskillowen

03/30/2022, 1:06 PM

yes your sql code is a function not a procedure

thisOldDave

03/30/2022, 1:06 PM

have you tried using a query?

Copy code

SELECT spgetaoid('foo');

mauzer

03/30/2022, 1:10 PM

ive changed the procedure to this: but still getting ERROR: function spgetaoid(unknown) does not exist

mauzer

03/30/2022, 1:11 PM

CREATE OR REPLACE PROCEDURE spgetaoid ( varg_clientID varchar(100) ) LANGUAGE plpgsql;

mauzer

03/30/2022, 1:20 PM

@thisOldDave yep tried that

letskillowen

03/30/2022, 2:56 PM

its not clear to me what you mean by “changed the procedure” it appears you have a function not a procedure defined in postgres and that is bombing cfstoredproc. as i think you stated in this thread or others, that other procedures defined in postgres work fine with cfstoredproc. when you changed your t-sql code to

create or replace procedure

did you also remove the function or do you now have both a function and a procedure of the same name? having both, probably not good. you also said you could execute the procedure (it was a function for clarity) directly from some app to postgres and it was fine. have you also tried just throwing that same syntax into a

queryExecute()

in cf and seen if it ran fine?

letskillowen

03/30/2022, 2:57 PM

the example above of

Copy code

SELECT spgetaoid('foo');

is not how you would call a stored proc but how one would call a function within a select statement

mauzer

03/30/2022, 3:17 PM

@letskillowen In Pgadmin in Postgres in the stored procedure itself, I changed the word function to procedure in the actual stored procedure as you were right it was nota function. A converter did this and I'm not certain why it put in function rather than stored procedure. No no other function or SP called the same.

2 Views

Open in Slack

Previous Next