B4J Question jRDC2 SET IDENTITY_INSERT Tablename ON

David Hawkins

Active Member
Licensed User
Longtime User
Hi, I have an MS SQL server for which I have the 'sa' password and I am trying to send a command, please see below: -

B4X: 
Private Sub SetInsertOn
'Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("set_Insert_On", Null)
Dim j As HttpJob = CreateRequest.ExecuteCommand(cmd, Null)
Wait For(j) JobDone(j As HttpJob)

If j.Success = True Then
InsertData
End If
j.Release
End Sub


The command set_Insert_On=SET IDENTITY_INSERT SWArchive ON

j.Success says true after execution but I can't follow up with an insert into the 'SWArchive' table as it comes up with the error '(SQLException) java.sql.SQLException: Cannot insert explicit value for identity column in table 'SWArchive' when IDENTITY_INSERT is set to OFF' which tends to indicate that the command has not been successful.

Is there something wrong with the command I am sending or the way that I am doing it?
 
Sort by date Sort by votes

David Hawkins

Active Member
Licensed User
Longtime User
Hi Erel

There are in fact three commands
1. set identity_insert on
2. insert the data
3. set identity_insert off

the commands as I have them in my config file are below

sql.set_Insert_On=SET IDENTITY_INSERT SWArchive ON
sql.set_Insert_Off=SET IDENTITY_INSERT SWArchive OFF

sql.Insert_into_Archive=INSERT INTO SWArchive (id,DateArrived,collfrom,Registration,PartNumber,Colour,wheeltype,Strip,STRAIGHTENING,Start,Finish,initst,Comments,MachStart,machfinish,initmach,StructTest,qc,CUSTOMER,CUSTOMERID,COMPANY,COMPANYID,DateCreated,Completed,Username,primer,WheelDesc,wheelmake,WheelSize,Damaged,DateLeft,Orderno,Invoiced,networklocation,diskno,
dateworked,notes,wheeltypeid,repairtype,OffMonitor,OnMonitor,Monitor,tyrecharge,logisticcharge,tps) SELECT id,DateArrived,collfrom,Registration,PartNumber,Colour,wheeltype,Strip,STRAIGHTENING,Start,Finish,initst,Comments,MachStart,machfinish,initmach,StructTest,qc,CUSTOMER,
CUSTOMERID,COMPANY,COMPANYID,DateCreated,Completed,Username,primer,WheelDesc,wheelmake,WheelSize,Damaged,DateLeft,Orderno,Invoiced,networklocation,diskno,
dateworked,notes,wheeltypeid,repairtype,OffMonitor,OnMonitor,Monitor,tyrecharge,logisticcharge,tps from SWMain WHERE completed = 1

I think the problem is that the identity insert is an MSSQL session and therefore both the 'set set_Insert_On' command and 'Insert_into_Archive' command have to be executed in the same session. I have seen that you can send a batch command with jRDC2 but don't know how to do it. A hint on how to do it would be helpful.

Regards

David
 
Upvote 0

David Hawkins

Active Member
Licensed User
Longtime User
OK after futher investigation the following works: -

config.properties command is set to the following

sql.ExecuteAll=SET IDENTITY_INSERT SWArchive ON;INSERT INTO SWArchive (id,DateArrived,collfrom,Registration,PartNumber,Colour,wheeltype,Strip,STRAIGHTENING,Start,Finish,initst,Comments,MachStart,machfinish,initmach,StructTest,qc,CUSTOMER,CUSTOMERID,COMPANY,COMPANYID,DateCreated,Completed,Username,primer,WheelDesc,wheelmake,WheelSize,Damaged,DateLeft,Orderno,Invoiced,networklocation,diskno,
dateworked,notes,wheeltypeid,repairtype,OffMonitor,OnMonitor,Monitor,tyrecharge,logisticcharge,tps) SELECT id,DateArrived,collfrom,Registration,PartNumber,Colour,wheeltype,Strip,STRAIGHTENING,Start,Finish,initst,Comments,MachStart,machfinish,initmach,StructTest,qc,CUSTOMER,
CUSTOMERID,COMPANY,COMPANYID,DateCreated,Completed,Username,primer,WheelDesc,wheelmake,WheelSize,Damaged,DateLeft,Orderno,Invoiced,networklocation,diskno,
dateworked,notes,wheeltypeid,repairtype,OffMonitor,OnMonitor,Monitor,tyrecharge,logisticcharge,tps from SWMain WHERE completed = 1;SET IDENTITY_INSERT SWArchive OFF

effectively concatenating the three commands into a single string then execute the following command

B4X: 
private Sub SetInsertOn
 
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("ExecuteAll", Null)
    Dim j As HttpJob = req.ExecuteCommand(cmd, Null)
    Wait For(j) JobDone(j As HttpJob)
   
    If j.Success = True Then
        DeleteData
    End If
   
End Sub

and voila it all works as it should.

Regards

David
 
Upvote 0

David Hawkins

Active Member
Licensed User
Longtime User
Yes I could have and yes it appears that the commands, at least the first two would have to be executed in the same transaction.

Thank you for making me think a bit more deeply into the problem enabling me to understand the way jRDC2 works more thouroughly.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

M
  • Question
B4J Question Set members of an instance via a Map with reflection?
Replies
5
Views
978
agraham
  • Question
B4J Question [JRDC2] about command NOT into config.properties
Replies
1
Views
617
Erel
  • Question
B4J Question [SOLVED] jRDC2 - how I can read the ID (type SERIAL) field from MariaDB?
Replies
2
Views
1K
Erel
  • Question
B4J Question [SOLVED] JRDC2 unique identifiers insert
Replies
2
Views
1K
makis_best
K
  • Article
B4J Tutorial Easier SQL Inserts
Replies
3
Views
3K
Robert Valentino
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…