PK value inserted and shown by ListGrid differs from value actually inserted in DB

[kmkale]

Hi,

Version : v8.3p_2013-01-20/EVAL Deployment 2013-01-20
Browsers : Firefox 19.0.2 and Chrome 24.0.1312.56
JDK : 1.6
Database : Postgres 9.1
OS : Ubuntu Linux 12.10 64bit


We have a custom function called next_id() which generates the primary key for the table based on custom logic. The value returned by the next_id() is bigint. Please see the attached mcompany_table.png for table schema. When insert is called without the pk column companyid, the next_id() function generates the key and inserts it.

Please find the datasource def attached in file mcompany.ds.xml
I have tried in various ways to get the ListGrid shown in attached file test_mcompany.jsp to insert a row in the db table.
1) If we set the type of field companyid to sequence and set the sequence name as next_id(), the insert fails as the sequence name is called wrapped in nextval()
2) If we set autoQuoteCustomExpressions="false" customInsertExpression="next_id()" with a add operationbinding setting the value of companyid to next_id() the logs show the insert part of the operation succeeding but the subsequent select is sent with criteria as 'next_id()' which fails so no value is entered in the db.
3) If we set the companyid autoGenerated="true", the values are inserted in the database but the value in the database is different from the one shown by the listgrid. You can see this in the attached files value_in_db.png and Listgrid_view.png
4) Same behavior of different values in db and listgrid is observed if we use a add operation binding with allowMultiUpdate="true" and companyid field type set to either text or any etc.

This is weird. I cant understand how the listgrid is showing values different from those in the db. Restarting the smartclient server, clearing browser cache, accessing it from a different machine all show the same thing. Different values in db and listgrid. Surprisingly if we directly enter a row in the db with PgAdmin or PhpPgAdmin etc, that row is shown correctly by the listgrid with companyid value matching that with the value in db. You can see all the in the two png's attached. The rows inserted by the ListGrid all have differing companyid values than in the db. Completely baffled by this.
Please help..

[kmkale]

Attaching the remaining mcompany_table.png file

[ Huh had to resize and convert all screenshots to jpg with below 80% quality to fit the extremely restrictive resolution + file size limits imposed by this forum.]

[kmkale]

I think we have run into the javascript range of "integral precision"
As JavaScript uses floating-point numbers the accuracy is only assured for integers between: -9007199254740992 (-2^53) and 9007199254740992 (2^53)

Reducing the range produced by our next_id() function seems to be the answer.

Refs :
1) http://stackoverflow.com/questions/8663298/json-transfer-of-bigint-12000000000002539-is-converted-to-12000000000002540

2) http://www.irt.org/script/1031.htm

[Isomorphic]

Yes, your correct, JavaScript number limits will cause this.

Reducing the range produced by next_id() is a good solution and will save on bandwidth as well. An alternative if you can't do this is to deliver the values as Strings instead.

[kmkale]

Thanks for the confirmation.

As I'm fairly new to Smartclient, can you please tell me whats the simplest way of returning bigint ( or a timestamp with timezone ) data as (pre-formatted ) strings?

[Isomorphic]

type="text" and autoGenerated="true"