B4J Question jRDC2 SET IDENTITY_INSERT Tablename ON

[David Hawkins]

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: -

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]

How are the two commands declared in the config file?

 

[David Hawkins]

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]

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

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]

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]

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.