PostgreSQL operator does not exist, double underscore fields

dev
1

I’m having this issue in both Adobe ColdFusion and Lucee.

When I query PostgreSQL fields that have been created with this syntax:

CREATE TABLE VendorDB.enrollments (
	id bigserial NOT NULL,
	workflow_state VendorDB."enrollments__workflow_state" NOT NULL,
	CONSTRAINT enrollments_pkey PRIMARY KEY (id)
);

which is a database a vendor created and has provided remote access, so we can’t update it.

Anyway, if I try using CFQUERYPARAM on it, it fails with:

ERROR: operator does not exist: canvas.enrollments__workflow_state = character varying
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

The cfqueryparam in the SQL Statement looks like this:

and e.workflow_state IN (<cfqueryparam value="#StatesToShow#" cfsqltype="CF_SQL_VARCHAR" list="Yes">)

If I change it to NOT use CFQUERYPARAM and the query works fine, but I lose its advantages.

Anyone seen this before? Any workarounds you know of for being able to use CFQUERYPARAM with this type of field in PostgreSQL?

2

“enrollments__workflow_state” should be a user datatype.

So, at least, you should create that datatype.

If you have already created it, please post the DDL.

For list of custom datatypes, use " \dT+" on psql.

Custom datatypes are created like this:

3

DDL was posted above.

This was my SQL Client’s (DBeaver) version of “Generate DDL” for the table/fields I did not create and have zero admin permissions on (Vendor created/provided). Read-only access for me.

Perhaps the vendor created the TYPE being used? I’ll ask them.

4

Sorry, I meant DDL of user datatype “enrollments__workflow_state”.

If you can’t use psql, with DBeaver custom datatype is under “User types”, like here:image

Yes, it is.

5

Doyou need to do something along the lines described here?

cast(workflow_state AS VARCHAR) IN (<cfqueryparam value="#StatesToShow#" cfsqltype="CF_SQL_VARCHAR" list="Yes">)

Also… humour me… use queryExecute instead of <cfquery>, and just pass the param as {value=StatesToShow, list=true} (ie: without a type). For all I know queryExecute is using the same parameter-handling implementation as <cfqueryparam> does, but it’s an easy experiment and worth a punt.

6

Thanks Adam!

The cast() function did the trick, but to run your experiment, when attempting queryExecute I get:

Invalid construct: Either argument or name is missing.
When using named parameters to a function, each parameter must have a name.

Not sure what I’ve done wrong:

<cfscript>
CD2Courses = queryExecute("select c.id, c.sis_source_id, c.course_code, c.name, c.sis_batch_id, c.created_at, c.updated_at, c.workflow_state, c.account_id, c.grading_standard_id, c.group_weighting_scheme, c.enrollment_term_id, et.sis_source_id term_id, et.name term_name, a.name account_name
from courses c
where c.workflow_state IN (:TheState)", {TheState={value="available,claimed", list=true}, {datasource="CD2"}});
</cfscript>

Any advice appreciated.

Thanks!

7

Ah, I see. Yes, it looks like they are all “Enum type”. (for many of their tables, like enrollments, courses, etc.)

Is there a cfsqltype that would work on that without casting?

luceedev
luceedev676×618 15.4 KB

DDL for that appears to be:

CREATE TYPE "courses__workflow_state" AS ENUM (
	'created',
	'claimed',
	'available',
	'completed',
	'deleted');
8

It’s just a misplaced }. You’ve got yer DSN inside the params arg, not as a separate one.It should be:

`{TheState={value="available,claimed", list=true}}, {datasource="CD2"}`
9

Yep, queryExecute is just a wrapper around cfquery

10

I made a little test.
With CF_SQL_OTHER works :sweat_smile:

with driver PostgreSQL 9.4.1212

hth

1 Like
11

Ah, thank you! I knew it had to be something like that.

Yup, same result.

12

Indeed CF_SQL_OTHER works! Thanks!

Didn’t even see that in the docs!

1 Like