New Records Assigned Existing PKs

[kwpsd]

Version: 6.1.2 - build 1421

We developed a solution in Servoy using a MSSQL EXPRESS backend, and it runs without problems.

At the customers location, the solution runs in a production environment that uses a full-blown MSSQL backend. When users create a new record, Servoy is returning an empty record with a PK that already exists (see graphic below):

Screenshot showing new record with an existing PK value

9-21-2012 3-26-09 PM.png (43.56 KiB) Viewed 2936 times

The user fills out the record and saves it. However, the record is not being saved due to the duplicate PK (see log excerpt below):

2012-09-21 14:24:50,675 ERROR [RMI TCP Connection(2066)-10.196.20.216] com.servoy.j2db.util.Debug - Error executing sql: insert into barcode (barcode_id, barcode_index_number, barcode_mail_code, barcode_contact_name, barcode_contact_phone_ext, barcode_contact_department, barcode_contact_location, barcode_comments, barcode_date_last, barcode_flag_selected, barcode_history, barcode_status, barcode_flag_active, barcode_flag_manual) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) with params: [10 ,type: java.lang.Long, 'MSCMHP0' ,type: java.lang.String, '0000' ,type: java.lang.String, 'KIM W PREMUDA' ,type: java.lang.String, '9404' ,type: java.lang.String, 'BARCODE PROGRAM TEST' ,type: java.lang.String, 'SANTEE' ,type: java.lang.String, 'YOU MAY DELETE THIS RECORD - IT WAS USED FOR TESTING ONLY' ,type: java.lang.String, 2012-09-21 14:05:53.933 ,type: java.sql.Timestamp, 0 ,type: java.lang.Long, '09/21/2012 02:24:48 PM Fri
id: changed to 10
' ,type: java.lang.String, 'ADD' ,type: java.lang.String, 1 ,type: java.lang.Long, 0 ,type: java.lang.Long] [ ]
java.sql.SQLException: Violation of PRIMARY KEY constraint 'PK__barcode__C71E94DB0EA330E9'. Cannot insert duplicate key in object 'dbo.barcode'. The duplicate key value is (10).
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:504)
at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.datasource.Zg.invoke(Zg.java:10)
at $Proxy0.executeUpdate(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at com.servoy.j2db.dataprocessing.Zv.Za(Zv.java:551)
at com.servoy.j2db.dataprocessing.Zv.performUpdates(Zv.java:521)
at sun.reflect.GeneratedMethodAccessor123.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

We loaded Servoy Developer on the production server in order to use their MSSQL backend, and new records are assigned new and unique PKs.

Does anyone have any idea why Servoy server is assigning duplicate PKs to new records? Any assistance appreciated...thanks!

[ngervasi]

Looks like your servoy sequences are out of sync, go to the admin page, Database Servers and click "update servoy sequences for all tables on all active servers".

[kwpsd]

Thanks, Nicola...that fixed things (I have never had to use that button previously).

I freely admit that my understanding of Servoy internals is limited, however, I would like to know why your suggestion fixed the sequences. I thought that running Servoy server and developer on the same box using the same MSSQL repository would produce identical results. What is the difference between developer updating the sequences and Servoy server updating the sequences?

[ngervasi]

Servoy sequences are stored in a table inside the servoy repository so if you create a new record in developer the sequence in the server repository would get out of date.
If you want to connect to the same database from both server and developer you should use database sequences (db seq or db identity) instead of servoy sequences.