JDBC: How to select only those records which contains col1 is NULL

[Former Member]

Hi All,

In JDBC SELECT scenario.

I wants to select only those records which contains col1 is NULL.

I am trying as follows:

<key>

<col1 isNull="TRUE" />

</key>

but in moni/monitoring i am getting Following Error

006-12-01 19:35:20 Error Unable to execute statement for table or stored procedure. 'BG_SALARY_SEND_SAP_D' (Structure 'SELECT') due to java.sql.SQLException: FATAL ERROR document format: structure 'SELECT', key element 'key' contains no values

2006-12-01 19:35:20 Error Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. 'BG_SALARY_SEND_SAP_D' (structure 'SELECT'): java.sql.SQLException: FATAL ERROR document format: structure 'SELECT', key element 'key' contains no values

Please suggest how to do select rows with col1 is null.

Regards

[bhavesh_kantilal · Accepted solution]

Rohan,

isNull= true is used to make the KEY element not mandatory.

In your case, if the select query is

select emp from emptest where empno is null

, then the datatype will be,

<root>
<StatementName>
<dbTableName action=”SELECT”>
  <table>emptest</table>
<access>
<emp></emp>
</access>
<key1>
<b><empno hasQuot="NO">is null</empno></b>
</key1>
</dbTableName>  
</StatementName>
</root>

I dont have a DB access to try this, but I think this should work.

If this doesnt work, then you would need to use SQL_QUERY,

<root>
<StatementName>
<anyName action=” SQL_QUERY”>
<access>select emp from emptest where empno is null</access>
</anyName >  
</StatementName>
</root>

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

Bhavesh

[Former Member · Accepted solution]

Thanks Bhavesh..

Your second solution works.

Your first solution do not work. It gives me "com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'BG_SALARY_SEND_SAP_D' (structure 'SELECT'): java.sql.SQLException: ORA-00936: missing expression".

Regards

[Former Member]

Hi Rohan,

Check these..

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

cheers,

Prashanth

P.S Please mark helpful answers

[moorthy]

Hi,

This is because of XML document format error in the XI.

Check the message type , validity of the XML message. Write the Select query in the JDBC adapter, not in the message/data type

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

moorthy

[Former Member]

Hi Krishna,

In which column we can write select query.

Is there any web log exist for direct writing select query in JDBC adapter?

Because I am able to select only web logs where data type & message mapping are used to build select query.

Please let me know if such web log exist.

Regards.

[moorthy]

Hi,

You are going to use Receiever JDBC adapter with Select Query right ? i.e your scenario is Source >XI>JDBC ??

If so /people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

If you want to use JDBC>XI>Target Scenario, then you can check this blog-

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

Regards,

Moorthy