XML SQL structure / JDBC receiver

[iddorijsdijk]

I am struggling with the XML SQL structure. I created the following structure:

<ns0:DBSTRUCTURE xmlns:ns0="urn:mynamespace">

   <statement>

      <dbTableName action="INSERT"/>

      <table>MYTABLE</table>

      <access>

         <FIELD1>1</FIELD1>

         <FIELD2>DossierA</FIELD2>

         <FIELD3>3</FIELD3>

      </access>

      <access>

         <FIELD1>11</FIELD1>

         <FIELD2>DossierB</FIELD2>

         <FIELD3>33</FIELD3>

      </access>

      <access>

         <FIELD1>111</FIELD1>

         <FIELD2>DossierC</FIELD2>

         <FIELD3>333</FIELD3>

      </access>

   </statement>

</ns0:DBSTRUCTURE>

When I test this, I get the following information from the logging:

INSERT INTO  MYTABLE () VALUES ()

Unable to execute statement for table or stored procedure. 'MYTABLE' (Structure 'statement') due to java.sql.SQLSyntaxErrorException: ORA-00928: missing SELECT keyword

Of course, the names MYTABLE and FIELD1-3 are replaced with the real names in my scenario. I can connect to the DB from PI, and search for the DB table with the external definition object.

Any ideas on how to solve this are greatly appreciated!

[former_member183908 · Accepted solution]

can you confirm the structure <access> node should be under <TableName>

can you provide the payload in the receiver channel so that i can try to help you whats went wrong also use SQL tool and connect directly to DB and use this structure to insert data into DB.

Thanks

[iddorijsdijk · Accepted solution]

Thanks, I completely missed that part. The statement is now executed succesfully.

[former_member183908 · Accepted solution]

beautiful......Happy Learning

Thanks

[pvishnuvardan_reddy]

HI Rijsdijk,

The jdbc receiver structure should be as below:

It looks like some deviation is there between yours and the standard jdbc receiver structure.

Please check this and let me know if you still having issues.

Regards

Vishnu

[iaki_vila]

Hi Rijsdijk,

The problem is with the tag dbtablename, the structure would be:

<ns0:DBSTRUCTURE xmlns:ns0="urn:mynamespace">

   <statement>

      <dbTableName action="INSERT">

      <table>MYTABLE</table>

      <access>

         <FIELD1>1</FIELD1>

         <FIELD2>DossierA</FIELD2>

         <FIELD3>3</FIELD3>

      </access>

      <access>

         <FIELD1>11</FIELD1>

         <FIELD2>DossierB</FIELD2>

         <FIELD3>33</FIELD3>

      </access>

      <access>

         <FIELD1>111</FIELD1>

         <FIELD2>DossierC</FIELD2>

         <FIELD3>333</FIELD3>

      </access>
     </dbTableName>
   </statement>

Check also if you need to set the schema in the table tag, as MySchema.MYTABLE.

Regards.