Hi, Our SQL Server business datastore table keys are setup as BIGINT

chrisc834 over 9 years ago

Hi,

Our SQL Server business datastore table keys are setup as BIGINT datatypes. The 'Defining Custom Data Types' article below states that BIGINT is supported and to map using xsd:long type in CDT's:

forum.appian.com/.../Defining_a_Custom_Data_Type.html

We have implemented on this recommendation, however it appears the native Appian 'Number(Integer)' datatype cannot handle numbers bigger than the standard INTEGER range?

For example, when querying a datastoreentity and the key is 110523001, results are returned but when using the key 20275908001 no results are returned, even though a row exists in the table.

Please help, thanks

Discussion posts and replies are publicly visible

0 pavithrac over 9 years ago

Hi chris, but as per the documentation Integer numbers can range from -2147483647 to 2147483647 forum.appian.com/.../Data_Types.html
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 pavithrac over 9 years ago

As per my knowledge, The number you mentioned i.e. 20275908001 is greater than 2147483647 right. so it was out of range right.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chrisc834 over 9 years ago

How can I handle numbers outside of therange then? I have tried using the decimal type but they appear to get converted into scientific notation?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 pavithrac over 9 years ago

Need to check what are the other ways, but in my project we faced this problem for mobile number and we used text instead of number integer and used validations
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 chrisc834 over 9 years ago

Unfortunately, converting the table columns to text is not an option as the primary key is auto-generated so have to be kept as BIGINT, therefore I need to be able to handle on Appian side
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 pavithrac over 9 years ago

yes you are right. we cannot convert to text if its a primary key and I don't think that we can handle this on Appian side, but still i will check if we have any other way and let you know if i achieve it.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 PhilB
A Score Level 1
over 9 years ago

Where did you see the decimal types being turned into scientific notation? That can happen when entering data into a floating point field (frankly I think it's a bug) but it's possible to handle those larger decimal numbers in text fields by wrapping the value and the save in a todecimal() function.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ChristineH
Certified Lead Developer
over 9 years ago

If you create a view on the table, you can cast/convert the PK column to text. Just one idea on a work around. You can then use a cdt mapped to the view for reads and a cdt to the table for writes. Maybe?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel