runPrepUpdate getKey returning wrong value

Ignition
1

Hello All,

I am running a very simple insert query that should return the identity key from a MSSql server. When I run the query below, and print the results I get a incrementing values (1316134921, 1316134922, 1316134923 for example). However, that is not the value I expected. It should have been "10000000000010, 10000000000011, 10000000000012...etc"

When I run the query directly on the SQL server, I get the correct value.

getRFID = '''INSERT INTO RFID_Sequence (prefix, job)
		   VALUES (?,?)
		   SELECT SCOPE_IDENTITY() '''
RFSeq = system.db.runPrepUpdate(getRFID, [RFIDPrefix,jobNumber], 'DBSource', getKey = True)
print RFSeq

Any idea what I am doing wrong?

2

The docs say that system.db.runPrepUpdate returns an integer, and 10000000000010 is too big for an integer. I suspect that 1316134921 is the value you get taking integer overflow into account, but I don't have time right now to verify.

2 Likes
3

Are there any triggers on the table? Is replication enabled on the database server? Either of those might return strange data.

What does the data look like in the database itself ("select top 10 * from RFID_Sequence order by [pkid] desc" from SSMS)? Does it have the expected values or the returned values?

4

I'd go with overflow:

longVal = 10000000000010
for i in range(10):
	print longVal+i, (longVal+i) & (2**32-1)

Output:

10000000000010 1316134922
10000000000011 1316134923
10000000000012 1316134924
10000000000013 1316134925
10000000000014 1316134926
10000000000015 1316134927
10000000000016 1316134928
10000000000017 1316134929
10000000000018 1316134930
10000000000019 1316134931
5 Likes
5

That is what it was...Once I changed my DB data type from bigint to int, I started getting the expected value. Thanks for the help.

6

You may already be thinking of this, but a word of caution with switching from bigint to int. If that column is an auto incrementing value, you are already over half way to the max value that a signed int column can hold. No idea how much time that gives you with your process, but I just wanted to make sure you're not missing that potential future pain point.

7

Thanks for the heads up. It should only increment by about 50k per year, so the 1.2 billion or so will be enough. :smiley:

2 Likes
8

An alternative is now available for those out there needing bigint primary keys:

1 Like