# BigInt causes UNKNOWN to UNKNOWN error **URL:** \ **Category:** Ignition\ **Created:** [October 10, 2012, 11:41pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262 "2012-10-10T23:41:29Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Jonathan](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonathan](https://forum.inductiveautomation.com/u/Jonathan)\ **Post date:** [October 10, 2012, 11:41pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/1 "2012-10-10T23:41:29Z")
this line of code causes the following error. seems to be because the column ID\_OUTLET in the database is a BigInt ```auto 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] ---
**Author:** ![Michael.Stofan](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.stofan/32/3342_2.png) [@Michael.Stofan](https://forum.inductiveautomation.com/u/Michael.Stofan)\ **Post date:** [October 11, 2012, 10:54pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/2 "2012-10-11T22:54:54Z")
If you copy and paste the query: ```auto SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN=1 ``` into the MSSQL Management Console, does it return properly? ---
**Author:** ![Jonathan](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonathan](https://forum.inductiveautomation.com/u/Jonathan)\ **Post date:** [October 11, 2012, 11:17pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/3 "2012-10-11T23:17:18Z")
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. ---
**Author:** ![Michael.Stofan](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.stofan/32/3342_2.png) [@Michael.Stofan](https://forum.inductiveautomation.com/u/Michael.Stofan)\ **Post date:** [October 11, 2012, 11:40pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/4 "2012-10-11T23:40:46Z")
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? ---
**Author:** ![Jonathan](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonathan](https://forum.inductiveautomation.com/u/Jonathan)\ **Post date:** [October 11, 2012, 11:42pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/5 "2012-10-11T23:42:42Z")
The Microsoft SQL Server JDBC Driver is a Java Database Connectivity (JDBC) 4.0 compliant driver. sqljdbc4.jar MS SQL Server 2005 ---
**Author:** ![Michael.Stofan](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.stofan/32/3342_2.png) [@Michael.Stofan](https://forum.inductiveautomation.com/u/Michael.Stofan)\ **Post date:** [October 12, 2012, 12:02am UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/6 "2012-10-12T00:02:19Z")
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. ---
**Author:** ![Jonathan](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonathan](https://forum.inductiveautomation.com/u/Jonathan)\ **Post date:** [October 12, 2012, 12:16am UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/7 "2012-10-12T00:16:08Z")
I have changed my query to as follows and it works fine ```auto result = system.db.runScalarQuery("SELECT ID_OUTLET FROM tblMPT_ChainOutlets WHERE ID_CHAIN"+str(chainID)) ``` ---
**Author:** ![markdobtech](https://avatars.discourse-cdn.com/v4/letter/m/71e660/32.png) [@markdobtech](https://forum.inductiveautomation.com/u/markdobtech)\ **Post date:** [October 12, 2012, 1:25pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/8 "2012-10-12T13:25:49Z")
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." ---
**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\ **Post date:** [October 12, 2012, 6:09pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/9 "2012-10-12T18:09:20Z")
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. ---
**Author:** ![Jonathan](https://avatars.discourse-cdn.com/v4/letter/j/aeb1de/32.png) [@Jonathan](https://forum.inductiveautomation.com/u/Jonathan)\ **Post date:** [October 12, 2012, 7:04pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/10 "2012-10-12T19:04:16Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1b83c9c2a0c0524310d08bf9ca04da6987cb12a3.proj) (9.17 KB) [BigIntTable.sql](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/ccc79ce51762d269d683dbfae4049e1a472bcf45.sql) (648 Bytes) ---
**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\ **Post date:** [October 12, 2012, 10:44pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/11 "2012-10-12T22:44:31Z")
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. ---
**Author:** ![markdobtech](https://avatars.discourse-cdn.com/v4/letter/m/71e660/32.png) [@markdobtech](https://forum.inductiveautomation.com/u/markdobtech)\ **Post date:** [October 13, 2012, 3:02pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/12 "2012-10-13T15:02:21Z")
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. ---
**Author:** ![James.Lorenz](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/james.lorenz/32/3290_2.png) [@James.Lorenz](https://forum.inductiveautomation.com/u/James.Lorenz)\ **Post date:** [October 15, 2012, 3:25pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/13 "2012-10-15T15:25:46Z")
Thanks for the information, I’ll be sure to add it to the bug ticket. ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [October 15, 2012, 5:28pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/14 "2012-10-15T17:28:44Z")
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: ```auto result = system.db.runPrepQuery("SELECT ID_OUTLET FROM tblChainTest WHERE ID_CHAIN=?",[int(chainID)]) ``` Regards, ---
**Author:** ![ccarruthers](https://avatars.discourse-cdn.com/v4/letter/c/b3f665/32.png) [@ccarruthers](https://forum.inductiveautomation.com/u/ccarruthers)\ **Post date:** [February 13, 2018, 11:14pm UTC](https://forum.inductiveautomation.com/t/bigint-causes-unknown-to-unknown-error/5262/15 "2018-02-13T23:14:44Z")
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.