Android Question jRDC2 :An error on sql.insert command

jkhazraji

Active Member
Licensed User
Longtime User
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
 
Sort by date Sort by votes

jkhazraji

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

and the insert sub is as follows:
B4X: 
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
 
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
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?
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
jkhazraji said:
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"
Click to expand...
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/
 
Last edited:
Upvote 0

jkhazraji

Active Member
Licensed User
Longtime User
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?
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
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)
 
Upvote 0
You must log in or register to reply here.

Similar Threads

H
  • Question
Android Question Jrdc2 - Error on "CreateCommand(Name As String, Parameters() As Object) As DBCommand"
Replies
2
Views
783
Hishow
H
J
  • Solved
B4J Question [B4X] jRDC2 - Issues with sql command not found
2
Replies
22
Views
5K
JDR
J
  • Question
Android Question jRDC2 parameter error
Replies
1
Views
2K
Csaba Balogh
Q
  • Question
Android Question [B4A] jRDC2 - Ambiguous error in debug mode
Replies
6
Views
2K
Q Candiani
Q
B
  • Question
Android Question jRDC2 Error in RDCConnector - ClassCastException
Replies
8
Views
2K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…