Database field has spaces and replaced with underscore in PI - data is not posted into SQL server

[Former Member]

Hi,

My databse table(TEST_2) has 3 fields and one of the field has spaces - field names - Employee,Name,Personnel Number

and I created in PI with following names - EMPLOYEE,NAME,PERSONNEL_NUMBER

I mapped action -SQL_DML

And access level mapped -  INSERT INTO TEST_2 (Employee,Name,[Personnel Number]) VALUES ($EMPLOYEE$,$NAME$,$PERSONNEL_NUMBER$)

Below is the target payload

<?xml version="1.0" encoding="UTF-8"?>

<ns1:MT_PMT_Test xmlns:ns1="http://proxytojdbc/poc">

   <STATEMENT>

      <TEST_2 ACTION="SQL_DML">

         <access>         - here I mapped above INESRT statement

            <EMPLOYEE>IA</EMPLOYEE>

            <NAME>TEST</NAME>

            <PERSONNEL_NUMBER>12</PERSONNEL_NUMBER>

         </access>

      </TEST_2>

   </STATEMENT>

</ns1:MT_PMT_Test>

in runtime monitoring is showing success but when I checked in database table there is no record inserted into table. please let me know what is wrong in th INSERT statement.

[Former Member · Accepted solution]

Hi.

Please check the Structure of INSERT Stament about Access Tag

<root>
   <StatementName>
                  <anyName action=” SQL_QUERY” | “SQL_DML”>
              <access>SQL-String with optional placeholder(s)</access>
              <key>
                  <placeholder1>value1</placeholder1>
                 <placeholder2>value2<placeholder2>   
              </key>
          </anyName >
      </StatementName>
</root>

http://help.sap.com/saphelp_nwpi711/helpdata/en/43/9519abb1146353e10000000a11466f/frameset.htm

[Former Member · Accepted solution]

Luis,

  After changed structure like this it got inserted records into SQL server...Thanks.

<?xml version="1.0" encoding="UTF-8"?>

<ns1:MT_NEW_PMT xmlns:ns1="http://proxytojdbc/poc">

   <STATEMENT>

      <TABLE ACTION="SQL_DML">

         <ACCESS>INSERT INTO TEST_2 (EMPLOYEE,NAME,[PERSONNEL NUMBER]) VALUES ('$EMPLOYEE$','$NAME$','$PERSONNEL_NUMBER$')</ACCESS>

         <KEY>

            <EMPLOYEE>IAS</EMPLOYEE>

            <NAME/>

            <PERSONNEL_NUMBER>12</PERSONNEL_NUMBER>

         </KEY>

      </TABLE>

   </STATEMENT>

</ns1:MT_NEW_PMT>

[baskar_gopalakrishnan2]

You can use logSQLStatement = true in the advance setting of jdbc receiver channel to log trace your SQL.  

1) is there any errors showing in the jdbc communication channel?

2) Oracle does not allow spaces in the column name while creation. Are you using  MS SQL server?

Please give a try as follows..

INSERT INTO TEST_2 (Employee,Name,"Personnel Number") VALUES ($EMPLOYEE$,$NAME$,$PERSONNEL_NUMBER$)

I'm afraid whether jdbc driver can interpret this..

Please try and share your results here.

[Former Member]

You can use logSQLStatement = true in the advance setting of jdbc receiver channel to log trace your SQL.

  - presently using this one. 

1) is there any errors showing in the jdbc communication channel?

  - no

2) Oracle does not allow spaces in the column name while creation. Are you using  MS SQL server?

  - I am connection MS SQL Server 2008, and this is allowed with spaces.

Please give a try as follows..

INSERT INTO TEST_2 (Employee,Name,"Personnel Number") VALUES ($EMPLOYEE$,$NAME$,$PERSONNEL_NUMBER$)

- this way it's not working and changed structure like below..now I am able to post the records.

<?xml version="1.0" encoding="UTF-8"?>

<ns1:MT_NEW_PMT xmlns:ns1="http://proxytojdbc/poc">

   <STATEMENT>

      <TABLE ACTION="SQL_DML">

         <ACCESS>INSERT INTO TEST_2 (EMPLOYEE,NAME,[PERSONNEL NUMBER]) VALUES ('$EMPLOYEE$','$NAME$','$PERSONNEL_NUMBER$')</ACCESS>

         <KEY>

            <EMPLOYEE>IA</EMPLOYEE>

            <NAME>test</NAME>

            <PERSONNEL_NUMBER>12</PERSONNEL_NUMBER>

         </KEY>

      </TABLE>

   </STATEMENT>

</ns1:MT_NEW_PMT>

Thanks Baskar.