Assigning bigint datatype in Python

Ignition
1

Hello,
I want to send information into multiple databases in a postgresql environment.
I have a sql function that returns the id that was inserted from the previous execution.
The problem that I am facing is that the id of that table is a bigint type and it seems that I need to do a conversion.

My current code example:

import system
identificador = -1
nombre = system.tag.read('[default]CARGAS/PruebaTxt').value
lstbd = ['scia', 'scia2']
for bdname in lstbd:
	txId = system.db.beginTransaction(database=bdname, timeout=5000)
	identificador = system.db.runScalarPrepQuery(query='SELECT public.prueba3_f(?,?)', args=[
		identificador,
		nombre
	], database=bdname, tx=txId)
	system.db.commitTransaction(txId)
	system.db.closeTransaction(txId)

If I visit the Logs I see:
“Caused by: org.postgresql.util.PSQLException: Can’t infer the SQL type to use for an instance of java.math.BigInteger. Use setObject() with an explicit Types value to specify the type to use.”

The thing is that I cannot find any documentation on the “setObject” in ignition’s sdk or does anyone know a workaround for this?

Thanks in advance

2

I’m pretty sure the equivalent you need is to set it to is a “Long”.

https://www.tutorialspoint.com/python/python_numbers.htm

For further reading have a look here at the setObject() and setLong() methods in Java:

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

These examples may also help you:

https://www.programcreek.com/java-api-examples/?class=java.sql.PreparedStatement&method=setObject

3

Hi Jonathan,
Thank you for answering. We do not have access to the PreparedStatement interface in Java.
The command that is causing the issue is “system.db.runScalarPrepQuery”.
tried casting the result to long like the first link you gave and it still crashes.
I am considering changing the id datatype in postgresql. I believe this will fix the problem…

4

You may need to upgrade the Postgres JDBC driver.

2 Likes
5

Yeah, I use Postgres all the time and I haven’t seen these problems in ages.

6

Updating the postgres driver worked! (had postgresql-9.0-801.jdbc4.jar installed)
Had to tweak the database function and python code in order to work with bigint but now it seems to work just fine.

Changed the datatype to decimal(38,0) in the input parameter of the database function and internally I convert it to bigint.
On python code: instead of “identificador = -1” I use “identificador = long(-1)” .
When I call runScalarPrepQuery, postgresql recognizes the input parameter as numeric type (thats why I changed the function).

7

I suffered the same problem, but to aid others the solution does depend on what version of Ignition you are running on and how you might have upgraded - at least in my experience.
I first encountered it in 7.9.9, so I applied the latest LTS version 7.9.18 - no change, I even updated the database driver the latest available (postgresql-42.2.23.jar) and the underlying JRE to latest available on the gateway - no success.
I then tested on another gateway which was 8.1.7 which had never have any 7.9 history - the SQL queries worked, so it represented others experiences above.
So I then proceded to upgrade the 7.9.18 gateway to 8.1.7 by in place update - then retested and the SQL queries still failed.
Out of desperation I blew away the VM and started from scratch and build a clean new 8.1.7 gateway, ran the test and it worked!
So it appears that the underlying database drivers / SQL bridge modules on the gateway have some legacy issues if you upgrade from 7.9.x to 8.1.x
Hope this helps someone avoid the wasted time & deep frustration that I just experienced. When in doubt a fresh new gateway will give you less grief!

1 Like