<cfstoredproc> call to postgreSQL giving error

[karuna516]

Hi,

We are migrating from MSSQLserver to POSTGRESQL database.We are getting column doesn't exists, even thoough column exists in the perticular table.

org.postgresql.util.PSQLException: ERROR: column "exampleid" does not exists

Below is the code snippet for MSSQL server

<cfstoredproc procedure="spexample" datasource="#odbc1#" returncode="Yes">

         <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@ExampleID" value="#ExampleID#">

          <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="NewExampleID" dbvarname="@oIdentity" null="No">

 </cfstoredproc>

Any changes need to be done for PostgreSQL?

[Just Shoot Me]

And Exactly What Adobe program are you using?

[karuna516]

I am using Coldfusion 2018 and POSTGRESQL as DB on Windows server 2016 R2 OS

[BKBK]

During the migration, did the column-name accidentally include a space character or quotes, perhaps?

[karuna516]

No. The select and other queries works file on the same table using <cfquery>. The only problem with stored procedures. The issue is with all stored procedures.

[BKBK]

Did you delete the dbvarname attribute?

[BKBK]

Yet another idea: case-sensitivity. Perhaps ExampleID, exampleID, etc.?

[Just Shoot Me]

I moved your post over from the poorly named Community Help forum, which is for getting help using this forum system, to a better forum.


I hope this helps. Best of luck to you.

[karuna516]

Thank you...

[BKBK]

> Any changes need to be done for PostgreSQL?

The following change might or might not help but is worth making: delete the attribute dbvarname. It has been deprecated.

[Charlie Arehart]

I want to add a couple of clarifications here, and one of them could be the solution Karuna needs.

First, though: ntoe that it's NOTE that dbvarname is deprecated (as BKBK says in a comment).  Quite the contrary: it's that it IS NOW honored and must be correct, whereas it WAS literally IGNORED from about CF 7.0.2 until an update to each of CF10 and 11 a few years ago (and is still true in 2016 and 2018).

And one of the things that bite people most when migrating from an older release (before 9 or without that udpate in 10 or 11) is that the dbvarname MUST have a prefix before the varname. This is a requirement of some DBs and JDBC drivers, rather than a quirk of CF.

So Karuna, you are showing using an @ for the prefix in the dbvarname. That was indeed the prefix required for SQL Server (which you are migrating from). I have not found any docs to explain what PostGres uses as a prefix, if anything. (FWIW, Oracle uses an :.)

So while one option is indeed to remove the dbvarname as BKBK proposes, that will then require that your cfprocparams be in the exact order the args are defined in the SP. The whole point of dbvarname is to relieve that concern, allowing used of named args.

So first, try removing the @. It may be simply that Postgres uses no prefix. If that doesn't work, then try removing the dbvarname, but if it still fails then make sure you have the procparams in the right order relative to the SP.

Let us know how all this goes.