Urgent-Servoy Please Respond - Save Data Failed

Classic Servoy
1

EDITED FOR UPDATE - URGENT
Hi Folks

Guess this is a lack of understanding of the way Servoy saves data but… I have a form based on a table with 2 required columns, one a DB_Identity and another a user entered data.

I use foundset.newRecord(true) to add a record to the table on which the form is based. After user input for the second required column I do a databaseManager.saveData() and get the rerror:

save failed for 1 or more records

com.servoy.j2db.dataprocessing.DataException: Parameter #7 has not been set.

Looking at the log I have this query being created by Servoy and failing:

Error executing sql: SET IDENTITY_INSERT fabric_system_paint_header ON insert into fabric_system_paint_header (fph_id, fph_system_code, fph_long_description, fph_name, fph_changed_by, fph_date_changed, fph_edited_not_calced) values (?, ?, ?, ?, ?, ?, ?) SET IDENTITY_INSERT fabric_system_paint_header OFF select scope_identity() with params: [null, ‘1-1’ ,type: java.lang.String, ‘Test’ ,type: java.lang.String, ‘Test2’ ,type: java.lang.String, NullValue with type: 12 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 93 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue, NullValue with type: 4 ,type: com.servoy.j2db.dataprocessing.ValueFactory$NullValue]

Only the two (fph_id, fph_system_code) columns are required, and db_identity is set to ‘on’, both in the Servoy sequence area and also in the MSSQL Db, so where does the failure on parameter #7 come from??? and why does the SET IDENTITY_INSERT fabric_system_paint_header change to OFF???

PS Both these columns are set as PK
Appreciate feedback.

EDIT: Using a test table and form - this works without the above error (though its not what I wanted to use):
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=Yes

This fails with the above error:
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=No;PK

And so does this fail with the above error:
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=Yes;PK

The structure below was working in Servoy 5x but fails now?
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=No;PK

We have several tables with these structures all of which (as far as I can tell) have been working fine to date - or at least until the upgrade to 6. It also looks completely acceptable in other tools such as Navicat and MSMMS (adding records works as anticipated). I’d appreciate it if you could point me i the direction of discovering if I have done something crazy in our system and caused this structure to now fail?
Ian

2

Hi Ian,

if you take a look at the ‘params’ part, you see the first ‘param’ being ‘null’.
So for some reason, the fph_id is passed a value ‘null’

When 1 param being null, the remainder of the params move 1 place.
In your case: param 2 becomes 1, 3 becomes 2, etc.
This is why you get the message on param #7

As for the cause of fph_id being null… got no id :?

[EDIT] are you sure you set the fph_id after you execute the new record command?

3

Thanks for that feedback Marc.

mboegem:
[EDIT] are you sure you set the fph_id after you execute the new record command?

Nope, I don’t set the ID as that is set as an Identity column in the db and also as a db_identity in Servoy, so I’m almost certain (and I believe this is how it happens in our other tables) the ID is set from the table seed.

Did I misunderstand how this should work Servoy? When a new record command is issued to the db or in Servoy memory - where DB_dentity is on does Servoy not take the next identity and use that to populate the ID column when a save data is issued???

4

Anyone from Servoy available to feedback on this please???

5

Edited but does not appear as a NEW POST in the forum.

6

Ian,

Servoy 6 generates the ‘SET IDENTITY_INSERT’ stuff when you set a value to an identity column.
Somewhere a value (null) is assigned, but this should never be done in a normal solution (this feature was added for import sample data).

If you don’t do this, please create a small sample solution and file a case.

Rob

7

rgansevles:
Ian,

Servoy 6 generates the ‘SET IDENTITY_INSERT’ stuff when you set a value to an identity column.
Somewhere a value (null) is assigned, but this should never be done in a normal solution (this feature was added for import sample data).

If you don’t do this, please create a small sample solution and file a case.

Rob

Rob - thanks for the feedback. Please see my edited text as you’ll see a sample is not really necessary as a test table with two columns is all that needed, not even any data:

Using a test table and form - this works without the above error (though its not what I wanted to use):
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=Yes

This fails with the above error:
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=No;PK

And so does this fail with the above error:
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=Yes;PK

The structure below was working in Servoy 5x but fails now?
Column: test_id; Type int; Identity; Allow Null=No;PK
Column: test_field; Type string; Allow Null=No;PK

Rob currently our live solution is now broken and we are analysing the Db’s to see how many of these table structures we have. I should know in a couple of hours. Some quick feedback on your test results would be welcome Rob - so we can make a plan to fix this before our Clients trip over it and explode!

8

Ian,

I could reproduce this from your description.

The problem is that a table with a combined pk of a db-identity column and another column has never been supported.
I don’t see how this can have worked and I also don’t think this is optimal since the db-identity column already makes the record unique.

If you remove the non-identity column from the pk list it should work.

EDIT: If you file a case in our crm, I will see if we can fix that for 6.0.2

Rob