Help on JDBC receiver Sql

[Former Member]

Hi all,

I am using receiver jdbc adapter to insert data into oracle database, but I need to use single quote to make value as string, for example:

insert into ztable (id , name, description) values( 1, 'Jonathan', 'this is description(simple)' );

if I don't use QUOTE here, I will get error because there are "(" ")" in the DESCRIPTION value.

I see some threads mentioned to add attribute hasQuot= YES to realize this function, but I have tried, nothing happened, is there any tricks to make this happen?

[bhavesh_kantilal]

Can you provide us with the target XML that is casuing this issue,.

normally, hasQuot = 'YES' does the trick. Maybe there is someother issue.

Regards

Bhavesh

[Former Member]

please see the following XML, I both used YES and Yes, but both not work

<?xml version="1.0" encoding="UTF-8"?>
<ns1:MT_HPQA xmlns:ns1="http://mes/so_download">
   <insert1>
      <R_ERP_SO_HEADER action="INSERT">
         <table>R_ERP_SO_HEADER</table>
         <access>
            <MESSAGE_NO/>
            <DATI_MESSAGE>20070627143132</DATI_MESSAGE>
            <MESSAGE_CODE>2030</MESSAGE_CODE>
            <SENDER/>
            <RECEIVER/>
            <MD_SEQ_NO hasQuot="Yes">0000121051#</MD_SEQ_NO>
            <SONO>0000121051</SONO>
            <USERID>CAOBO</USERID>
            <SOSAPTYPE>1</SOSAPTYPE>
            <PONO>123</PONO>
            <PODATE>00000000</PODATE>
            <SALESORG>5000</SALESORG>
            <SALESORGDESC hasQuot="YES">&#36801;&#38050;&#38050;&#38081;&#20135;&#21697;&#38144;&#21806;</SALESORGDESC>
            <DISTCHAN>20</DISTCHAN>
            <DISTCHANDESC>&#32463;&#38144;&#21830;</DISTCHANDESC>
            <DIVISION>16</DIVISION>
            <DIVISIONDESC>&#38050;&#26448; --- &#28909;&#36711;</DIVISIONDESC>
            <SALESGRP>042</SALESGRP>
            <SALESGRPDESC>&#38144;&#21806;&#20844;&#21496;:&#20998;&#38144;&#31185;(&#28909;&#36711;</SALESGRPDESC>
            <SALESOFFICE>0015</SALESOFFICE>
            <SALESOFFICEDESC>&#38144;&#21806;&#20844;&#21496;&#65306;&#28909;&#36711;&#38144;&#21806;&#22788;</SALESOFFICEDESC>
            <TRANSPORTCONDITIONCODE>01</TRANSPORTCONDITIONCODE>
            <TRANSPORTCONDITION/>
            <SOLDTO/>
            <SHIPTO/>
            <INVTO/>
            <DATEMOD>00000000</DATEMOD>
            <DATEENT>20070524</DATEENT>
            <TIMESTAMP>20070627143132</TIMESTAMP>
            <CUSTCERTFICATEID/>
            <CUSTPAYERID/>
         </access>
         <key>
            <SONO>0000121051</SONO>
         </key>
      </R_ERP_SO_HEADER>
   </insert1>
   <insert2>
      <R_ERP_SO_ITEM action="UPDATE_INSERT">
         <table>R_ERP_SO_ITEM</table>
         <access>
            <MESSAGE_NO/>
            <DATI_MESSAGE>20070627143132</DATI_MESSAGE>
            <MESSAGE_CODE>2030</MESSAGE_CODE>
            <SENDER/>
            <RECEIVER/>
            <MD_SEQ_NO>0000121051#</MD_SEQ_NO>
            <DETAIL_POS>000010</DETAIL_POS>
            <SONO>0000121051</SONO>
            <SOITEM>0010</SOITEM>
            <MATNO>38000001</MATNO>
            <RAILWAY/>
            <UNLOADPOINT/>
            <DKUNNR1/>
            <SOQUAN>0</SOQUAN>
            <UNIT>KG</UNIT>
            <DISPDATE>0000-00-00</DISPDATE>
            <DISPATCHREADYMAX>0000-00-00</DISPATCHREADYMAX>
            <DISPATCHREADYMIN>0000-00-00</DISPATCHREADYMIN>
            <CANCELTYPE/>
         </access>
         <key>
            <SONo>0000121051</SONo>
            <SOITEM>000010</SOITEM>
         </key>
      </R_ERP_SO_ITEM>
   </insert2>
</ns1:MT_HPQA>

[Former Member]

the output sql as following:

INSERT INTO R_ERP_SO_HEADER (MESSAGE_NO, DATI_MESSAGE, MESSAGE_CODE, SENDER, RECEIVER, MD_SEQ_NO, SONO, USERID, SOSAPTYPE, PONO, PODATE, SALESORG, SALESORGDESC, DISTCHAN, DISTCHANDESC, DIVISION, DIVISIONDESC, SALESGRP, SALESGRPDESC, SALESOFFICE, SALESOFFICEDESC, TRANSPORTCONDITIONCODE, TRANSPORTCONDITION, SOLDTO, SHIPTO, INVTO, DATEMOD, DATEENT, TIMESTAMP, CUSTCERTFICATEID, CUSTPAYERID) VALUES (NULL, 20070627143132, 2030, NULL, NULL, 0000121057#56DC81468CD2A4D4E10000000A01FA32, 0000121057, CAOBO, 1, 111111, 00000000, 5000, 迁钢钢铁产品销售, 20, 经销商, 16, 钢材 --- 热轧, 042, 销售公司:分销科(热轧, 0015, 销售公司：热轧销售处, 01, NULL, NULL, NULL, NULL, 00000000, 20070524, 20070627143132, NULL, NULL)

[bhavesh_kantilal]

1. Let the hasQuot have value YES.

2. In the receiver JDBC adapter, select option XML Schema Interpretor and Interpretation of Empty String Values as NULL value.

3. use logSQLStatement to check what is the SQL statement generated by the JDBC adapter as shown in note : 801367

Regards

Bhavesh

[Former Member]

I have done exactly what you have told, but looks still the same ：（

[Former Member]

another question is ,I don't know how to insert date data into oracle, do I need to do some conversion, if I get 2007-06-27 from SAP, what I need to do to convert this date to Oracle date type, then insert it into oracle DB?