Query Error: GatewayException: Unknown SQL type: 2003 caused by SQLException: Unknown SQL type: 2003

[Oscar_Simon]

I've encountered an issue while running a query, and I'm receiving the following error: GatewayException: Unknown SQL type: 2003, caused by SQLException: Unknown SQL type: 2003. My database is Postgres, and I'm unsure if the problem is related to the functions I'm using in my query, specifically ARRAY_LENGTH and ARRAY_AGG. I've already updated the JDBC driver for Postgres to version 42.7.2. Any insights or suggestions would be greatly appreciated. My query works in pgAdmin.

[pturmel]

You are trying to retrieve a column or expression result from SQL that yields an array data type. Those don't map to vanilla JDBC, so you can't pull them into Ignition (as arrays). Change your SQL expressions to perform the complete array operation in SQL and return an ordinary data type.

As it happens, Ignition's "Prep" queries hand off argument processing to the actual JDBC driver, so it is possible to send arrays to Postgres. See this if you are interested:

Taking a Dataset and Inserting it to a SQL temp table

If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase…