Special Charactor in SQL Statement

HCL Domino Domino Forum
1

Hi All,

I am having a small problem here. I am trying to send LN docs to SQL Table on Cognos Server. I am having problem to send string value which has '(Single Quote) to the SQL Table as a record. How do we send string values with Special Charactor/s thru SQL statement?

SQL Statement in the LScript Agent:

sql=“Insert into CCF (DateTaken,Status,Category,CustomerNum,CustomerName,ExternalType,OrderNum,OrderDate,ShippedFrom,ShipDate,Part,DoorQty,FrameUnitQty,FramePcsQty,OtherQty,OtherDesc,AppearanceError,DeliveryError,MarkingError,PrepsError,StructConstrucError) values ('”+DateTaken+“‘,’”+Status+“‘,’”+Category+“‘,’”+CustomerNum+“‘,’”+CustomerName+“‘,’”+ExternalType+“‘,’”+OrderNum+“‘,’”+OrderDate+“‘,’”+ShippedFrom+“‘,’”+ShipDate+“‘,’”+Part+“',”+DoorQty+“,”+FrameUnitQty+“,”+FramePcsQty+“,”+OtherQty+“,'”+OtherDesc+“',”+AppearanceError+“,”+DeliveryError+“,”+MarkingError+“,”+PrepsError+“,”+StructConstrucError+“)”

Here is the SQL Statement I captured with values:

Insert into CCF (DateTaken,Status,Category,CustomerNum,CustomerName,ExternalType,OrderNum,OrderDate,ShippedFrom,ShipDate,Part,DoorQty,FrameUnitQty,FramePcsQty,OtherQty,OtherDesc,AppearanceError,DeliveryError,MarkingError,PrepsError,StructConstrucError) values (‘7/5/2006 7:51:35 AM’,‘OPEN’,‘PS - PROJECT SERVICES’,‘80090000’,‘“THE HALLGREN COMPANY INT’L”’,‘Distributor’,‘059974’,‘6/16/2006’,‘572 Milan Manufacturing’,‘6/22/2006’,‘DR’,22,0,0,0,‘’,0,0,0,1,0)

I am getting error at 5 value. Please advise me. I appriciate your help.

Thanks,

Murali.

2

Subject: Special Charactor in SQL Statement

Looks like you need to escape apostrophes in your strings, your CompanyName for example has one in it, which breaks the string.

sql=“Insert into CCF (DateTaken,Status,Category,CustomerNum,CustomerName,ExternalType,OrderNum,OrderDate,ShippedFrom,ShipDate,Part,DoorQty,FrameUnitQty,FramePcsQty,OtherQty,OtherDesc,AppearanceError,DeliveryError,MarkingError,PrepsError,StructConstrucError) values ('”+DateTaken+“‘,’”+Status+“‘,’”+Category+“‘,’”+CustomerNum+“‘,’”+Replace(CustomerName,“'”,“'”)+“‘,’”+ExternalType+“‘,’”+OrderNum+“‘,’”+OrderDate+“‘,’”+ShippedFrom+“‘,’”+ShipDate+“‘,’”+Part+“',”+DoorQty+“,”+FrameUnitQty+“,”+FramePcsQty+“,”+OtherQty+“,'”+OtherDesc+“',”+AppearanceError+“,”+DeliveryError+“,”+MarkingError+“,”+PrepsError+“,”+StructConstrucError+“)”

3

Subject: RE: Special Charactor in SQL Statement

Thank you Very much! Replace is working just fine. Cant we send the string as it is ie. with single quote/s etc? Why because we are going to use these values for further searches in other sources like… Cognos etc.

4

Subject: RE: Special Charactor in SQL Statement

The SQL statement will convert the ' to a single ’ when the value is inserted into the SQL database, so your values are in the end like they were in the Notes database.