Skip to Content
avatar image
Former Member

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

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 18, 2012 at 11:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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>

  • Jul 18, 2012 at 11:30 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.