Android Question jRDC2 :An error on sql.insert command

[jkhazraji]

Hi everybody,
Referring to this excellent jRDC2 tutorial : https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/
with a sql.insert command performed , the following error occurs:
ResponseError. Reason: java.sql.SQLException: An explicit value for the identity column in table 'table_name' can only be specified when a column list is used and IDENTITY_INSERT is ON.,
What is wrong , please

 

[jkhazraji]

The sql command in config.properties file is : sql.insert_member = INSERT INTO member_tables values (Null,?,?,?,?)

and the insert sub is as follows:

Sub InsertRecord (Name As String)
   Dim cmd As DBCommand = CreateCommand("insert_member", Array(Name, age, address, phone ))
   Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
   Wait For(j) JobDone(j As HttpJob)
   If j.Success Then
       Log("Inserted successfully!")
   End If
   j.Release
End Sub

 

[aeric]

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server

I'm trying to do this query INSERT INTO dbo.tbl_A_archive SELECT * FROM SERVER0031.DB.dbo.tbl_A but even after I ran set identity_insert dbo.tbl_A_archive on I am getting this error message ...

stackoverflow.com

 

[jkhazraji]

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server

I'm trying to do this query INSERT INTO dbo.tbl_A_archive SELECT * FROM SERVER0031.DB.dbo.tbl_A but even after I ran set identity_insert dbo.tbl_A_archive on I am getting this error message ...

stackoverflow.com

Thanks for your response .. but how is that implemented in the config.properties
Are you saying that I have to make a sql command for the identity column as : "sql.indentity_on : SET IDENTITY_INSERT table_name ON"
to set this feature on then I insert values including the 'id' filed then I set the IDENTITY_INSERT off with another sql command?
or the 'CreateRequest.ExecuteBatch' method is enough to handle that without the need for a SET IDENTITY_INSERT on or off?

 

[OliverA]

Are you saying that I have to make a sql command for the identity column as : "sql.indentity_on : SET IDENTITY_INSERT table_name ON"

Looks like you can run three statements in one (turn identity on; statement; turn identity off) or execute three statements back to back via ExecuteBatch (two of which you would have to define as commands in the config.properties file). See https://www.b4x.com/android/forum/threads/jrdc2-set-identity_insert-tablename-on.100894/

 

[jkhazraji]

Looks like you can run three statements in one (turn identity on; statement; turn identity off) or execute three statements back to back via ExecuteBatch (two of which you would have to define as commands in the config.properties file). See https://www.b4x.com/android/forum/threads/jrdc2-set-identity_insert-tablename-on.100894/

Thanks for your answer. If you set the identity on then you have to insert the id value manually which violates the rules of database autoincrement because the query itself should insert the id not the user. However, the question is : if you insert 'Null' for the id value , will the SQL command take it for a new autoincremented id value?

 

[aeric]

I will avoid execute INSERT query without specifying the columns and never had an issue above.
The good practice is include the column names.
Eg.
INSERT INTO member_tables (name, age, address, phone) VALUES (?, ?, ?, ?)

 

[OliverA]

Thanks for your answer. If you set the identity on then you have to insert the id value manually which violates the rules of database autoincrement because the query itself should insert the id not the user. However, the question is : if you insert 'Null' for the id value , will the SQL command take it for a new autoincremented id value?

The solution, as @aeric points out, is to specify the columns you are updating, skipping naming any auto-increment columns (if you are using MSSQL/Access/MySQL)