system.db.runScalarPrepQuery

I would like to know why one of these lines of code works and the other does not-
table = Postgres Database
column_1 = character varying
column_2 = integer
column_3 = integer

The following code returns the single and expected value from the table-

value = system.db.runScalarPrepQuery(“SELECT column_1 FROM table WHERE column_2 = 123 AND column_3 = 456”)

This line of code, however does not. It returns the error: "Error: operator does not exist: integer = character varying.

value = system.db.runScalarPrepQuery(“SELECT column_1 FROM table WHERE column_2 = ? AND column_3 = ?”, [partNumber, scrapCode])

The variables values are partNumber = 123 and scrapCode = 456

When they are “Hard Coded” it works, otherwise it errors-

Sounds like a type coercion problem, probably in the JDBC layer. What Ignition version and JDBC driver version do you have (and have you tried updating the driver? Ignition updates will not update your drivers for you).

You could also try casting the value you’re substituting into the statement; see here: https://www.postgresqltutorial.com/postgresql-cast/

PGriffith: Thank you for the reply- You got me thinking about the data types- So, I printed the types of both the partNumber and scrapCode- (Both are being pulled from a property value)-

partNumber was integer, but scrapCode was unicode-
I set scrapCode = int(scrapCode)

That took care of the issue- Many thanks!-

1 Like