PostgreSQL cfquery error

Question

I have just started using PostgreSQL as a datasource for ColdFusion Server 2021 Developer Ed. Used DBeaver to export a legacy MS Access 2007 database into a PostgreSQL 12 database which sits under a schema

Datasource registered OK. I ran a cfquery against a table.

______________

<CFQUERY
name="qryFrameworkListRender"
datasource="PostgreSQL35W"
>
SELECT
PipiObjectClassFrameworkID,
PipiObjectClassFrameworkCodePage,
PipiObjectClassFrameworkName,
PipiObjectClassFrameworkDescription,
PipiObjectClassFrameworkExample,
PipiObjectClassFrameworkNote
FROM
tblpipiobjectclassframework
WHERE
(((PIPI_OK)=Yes))
ORDER BY
PIPI_Sort, PipiObjectClassFrameworkName;
</CFQUERY>

______________

This error shows up. I'm puzzled. The table name is spelt correctly.

-----

Error Executing Database Query.

ERROR: relation "tblpipiobjectclassframework" does not exist Position: 234

The error occurred in C:/ColdFusion2021/cfusion/wwwroot/pipi/framework.cfm: line 29

27 : <CFQUERY
28 : 	name="qryFrameworkListRender"
29 : 	datasource="PostgreSQL35W"
30 :     >
31 : SELECT

SQLSTATE	42P01
EXCEPTIONS	<small><i>not set</i></small>
DATASOURCE	PostgreSQL35W
SQL	SELECT pipiobjectclassframeworkid, PipiObjectClassFrameworkCodePage, PipiObjectClassFrameworkName, PipiObjectClassFrameworkDescription, PipiObjectClassFrameworkExample, PipiObjectClassFrameworkNote FROM tblpipiobjectclassframework WHERE (((PIPI_OK)=Yes)) ORDER BY PIPI_Sort, PipiObjectClassFrameworkName;

Resources:

Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser	Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:94.0) Gecko/20100101 Firefox/94.0
Remote Address	127.0.0.1
Referrer	http://127.0.0.1:2030/pipi/index.html
Date/Time	20-Nov-21 10:23 PM

Stack Trace

at cfframework2ecfm1980838859.runPage(C:/ColdFusion2021/cfusion/wwwroot/pipi/framework.cfm:29) at cfframework2ecfm1980838859.runPage(C:/ColdFusion2021/cfusion/wwwroot/pipi/framework.cfm:29)

org.postgresql.util.PSQLException: ERROR: relation "tblpipiobjectclassframework" does not exist
  Position: 234

Mike John Peters · Accepted Answer

Hi BKBK

Thanks for the tip. I'm very new to PostgreSQL and last time I used CF was Coldfusion MX. So step learning curve.

I tried this and it works. The SQL dialect is different.

<CFQUERY
name="qryFrameworkListRender"
datasource="PostgreSQL35W"
>
SELECT
PipiObjectclassFrameworkID,
PipiObjectClassFrameworkCodePage,
PipiObjectClassFrameworkName,
PipiObjectClassFrameworkDescription,
PipiObjectClassFrameworkExample,
PipiObjectClassFrameworkNote
FROM
"pipi-dbo-namespace".tblpipiobjectclassframework;
</CFQUERY>

BKBK · Answer

PostgreSQL is case-sensitive for quoted names. So, if you created the table as "tblPipiObjectClassFramework", the correct table-name to use in the query would be:

... FROM "tblPipiObjectClassFramework" WHERE ...

See https://stackoverflow.com/questions/31506805/org-postgresql-util-psqlexception-error-relation-app-user-does-not-exista

Error Executing Database Query.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded