BigInt causes UNKNOWN to UNKNOWN error

this line of code causes the following error. seems to be because the column ID_OUTLET in the database is a BigInt

result = system.db.runPrepQuery("SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN=?",[chainID])

[quote]Traceback (most recent call last):

File “event:cellEdited”, line 3, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN=?, , [1], )

caused by Exception: Error executing system.db.runPrepQuery(SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN=?, , [1], )
caused by GatewayException: The conversion from UNKNOWN to UNKNOWN is unsupported.
caused by SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

Ignition v7.5.3 (b1163)
Java: Sun Microsystems Inc. 1.6.0_33
[/quote]

If you copy and paste the query:

SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN=1

into the MSSQL Management Console, does it return properly?

yes. if i copy the query into MSSMS it returns a result set just fine. go ahead and test it out. create a table in MS SQL with a BigInt column type and insert a row in the table. then use the runPrepQuery function to select the value in the column from the table.

We gave it a shot here with no problems. If you go to the Configure page and looks at the database drivers, what version of the MSSQL JDBC Driver do you have? What version of MSSQL are you running?

The Microsoft SQL Server JDBC Driver is a Java Database Connectivity (JDBC) 4.0 compliant driver.

sqljdbc4.jar

MS SQL Server 2005

Just to rule out the prepared statement, try using system.db.runQuery instead and see if your results are different.

We’re going to get our hands on SQLServer 2005 and continue testing on our end.

I have changed my query to as follows and it works fine

result = system.db.runScalarQuery("SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN"+str(chainID))

I experienced the same issue when initially evaluating Ignition with a sqlexpress 2005 db. As we were switching to sql server 2008 for the true eval, didn’t give it much significance. Have not experienced the issue with 2008 but can duplicate it with the 2005. The issue is not present if using a datatype other than bigInt, such as numeric(x,0) or float.

From Microsoft 2005 docs:
"The bigint data type is intended for use in cases where integer values might exceed the range supported by the int data type. For compatibility, the int data type remains the primary integer data type in Microsoft SQL Server 2005.

Unless explicitly stated, functions, statements, and system stored procedures that accept int expressions for their parameters have not been changed to support implicit conversion of bigint expressions to those parameters. Thus, SQL Server only implicitly converts bigint to int when the bigint value is within the range supported by the int data type. A conversion error occurs at run time if the bigint expression contains a value outside the range supported by the int data type."

Thanks for the suggestion markdobtech. If you recall, was your query that was generating the error something similar? Were you using system.db.runPrepQuery()?

Jonathan, if you’re not using enterprise, is it possible you could upgrade to 2008? Also, do you mind posting more or all of that script? Just hoping that maybe us getting some more context can help. If you’re worried about security, you can just PM it to me, or I can get you a ticket number to upload it to.

Attached is a script to create a table in sql and insert data just like my production. I have also attached a window that has a table and a button that use the table in the database.

the button will error on the line using runPrepQuery. i have also included a commented out line in the button script that uses runQuery that works just fine.
BigIntWindow.proj (9.17 KB)
BigIntTable.sql (648 Bytes)

Thanks for the SSCCE, I was able to reproduce the problem. Looks like there may be something in the way Ignition handles prepared statements that confuses the JDBC as far as long types go. I will submit a bug ticket for this. Your workaround using runQuery (or any other way of casting the variable into a string before passing/using it in one of our DB functions) should be fine.

To satisfy my own curiosity on this, I did some testing and noticed the UNKNOWN conversion error using bigint also occurs in runPrepUpdate and runUpdateQuery.

Thanks for the information, I’ll be sure to add it to the bug ticket.

It appears that on the select, the returned value is a Java “BigInt” type, which the SQL Server JDBC driver doesn’t know how to handle on the update query.

I’ll have to dig a little deep to see how we can handle this more generally, since in essence right now we’re leaving all datatypes up to the drivers to support. In the meantime, if possible (due to the reduced value range), you can also get around it by casting the value to an int in the update query. For example:

result = system.db.runPrepQuery("SELECT ID_OUTLET FROM tblChainTest WHERE ID_CHAIN=?",[int(chainID)])

Regards,

I was having the same issue when running a system.db.runPrepUpdate. Apparently the SQL Server JDBC driver hasn’t been fixed to handle the BigInt type yet if the value is larger than the max int size. The above discussion was very helpful though. By classifying it as a string I was able to pass it to SQL Server without errors.

1 Like