system.db.runPrepQuery - Argument Syntax

Ignition
1

Ignition / Vision:
I have a Stored Function (PostgreSQL) that accepts an array of integers- The function executes as expected when called from pgAdmin as follows:
SELECT * FROM public.downtime_record(1, 1000, ‘05/10/2022’, ‘0800 : 0900’, ‘{10, 11}’, ‘{1, 2}’, 1000)

However, when trying to execute this function from a script, it fails-
system.db.runPrepQuery(‘SELECT * FROM public.downtime_record(?, ?, ?, ?, ?, ?, ?)’, [workcenterId, operationId, dateSlot, hourSlot, downtimeMinutes, downtimeId, partId])

I have my two array of integers being set as follows:
downtimeMinutes = [10, 20]
downtimeId = [4, 5]

This is the error that is generated:
GatewayException: Can’t infer the SQL type to use for an instance of org.python.core.PyList. Use setObject() with an explicit Types value to specify the type to use.

What is the proper syntax for setting the two array variables?-

Thank you-
Robert-

2

Based on what you provided in the query from pgAdmin it looks like the function is expecting strings.

So

downtimeMinutes = '{10,20}'
downtimeId = '{4,5}'

Taging @pturmel because I don’t use postresql on a regular basis.

3

Thanks for the reply- I had given the a try- downtimeMinutes = ‘{10,20}’
But, then the error returned is it can’t find a function that matches the input types-
Here is an image of the postgresql function with the arguments-

image
image1018×28 5.15 KB

4

You cannot pass arrays through parameters in vanilla JDBC. Some DB’s offer extensions for that, including PostgreSQL, but such are not accessible through Ignition’s abstractions.

Use separate parameters for each endpoint.

5

Perfect- Thank you for your time - much appreciated-

6

Follow up: