B4J Question jRDC2 SET IDENTITY_INSERT Tablename ON

David Hawkins

Member
Licensed 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?
 

Erel

Administrator
Staff member
Licensed User
How are the two commands declared in the config file?
 

David Hawkins

Member
Licensed 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
 

David Hawkins

Member
Licensed 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
 

Erel

Administrator
Staff member
Licensed User
I guess that you could have used ExecuteBatch with your previous configuration. This is based on the assumption that the commands must be executed in the same transaction.
 

David Hawkins

Member
Licensed 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.
 
Top