<cfstoredproc> call to postgreSQL giving error

New Here ,
Jul 23, 2020 Jul 23, 2020

Copy link to clipboard

Copied

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?

Views

121

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 24, 2020 Jul 24, 2020

Copy link to clipboard

Copied

And Exactly What Adobe program are you using?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 24, 2020 Jul 24, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jul 26, 2020 Jul 26, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 26, 2020 Jul 26, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jul 27, 2020 Jul 27, 2020

Copy link to clipboard

Copied

Did you delete the dbvarname attribute?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jul 29, 2020 Jul 29, 2020

Copy link to clipboard

Copied

LATEST

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 24, 2020 Jul 24, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 24, 2020 Jul 24, 2020

Copy link to clipboard

Copied

Thank you...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jul 26, 2020 Jul 26, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jul 28, 2020 Jul 28, 2020

Copy link to clipboard

Copied

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.


/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines