Skip to Content
Author's profile photo Former Member

PreparedStatement with JDBC Receiver Adapter

Consider that you need to configure a JDBC Adapter on receiver side to mass update a specific DB table and lots of messages are processed by the Receiver JDBC Channel executing SQL statements that affects different table records.

In my example the recuirement is to update the table Employee updating the job fields:
UPDATE EMPLOYEE SET JOB = ‘MANAGER’ WHERE EMPNO = 000010
UPDATE EMPLOYEE SET JOB = ‘MANAGER’ WHERE EMPNO = 000020
UPDATE EMPLOYEE SET JOB = ‘MANAGER’ WHERE EMPNO = 000030
UPDATE EMPLOYEE SET JOB = ‘MANAGER’ WHERE EMPNO = 000050

The JDBC Adapter builds Dynamic SQL Statement (from XML SQL or Native SQL string) doing someting like this:


sqlString = “UPDATE EMPLOYEE SET JOB = ‘MANAGER’ WHERE EMPNO = ‘000010’”;
Statement stmt = con.createStatement();
stmt.executeUpdate(sqlString );  //<—- Compilation and Execution on DBMS

In this way, using the Statement class, the SQL statement is passed when the executeUpdate() method is called.
The RDBMS get the whole SQL Statement, compile it, and execute it.
This means that in my example the RDBMS will receive and compile four different statements.

According with the official SUN documentation, with java and jdbc it’s possible to use a smarter way to manage the SQL statement by using the PreparedStatement class.
This interface allow to create such as a model of a SQL Statement and pass it to the RDBMS for the preliminar phase of compilation of the statement:

sqlString = “UPDATE EMPLOYEE SET JOB = ? WHERE EMPNO = ?”;
PreparedStatement pstmt = con.prepareStatement(sqlString); //<—- Compilation on DBMS
pstmt.setString(1, “MANAGER”);
pstmt.setString(2, “000010”);
pstmt.executeUpdate(); //<—- Execution on DBMS

In this way When the object pstmt is created on Database Engine site the “model” of the statement is passed and compiled; after this the values are passed to the PreparedStatement object with parameter markers and then the statement execution.
The important thing is that with PreparedStatement the SQL Statement is compiled by the database engine once. With some database there is a cache for compiled statement that avoid to compile thousand of similar statement.

Having a look to the standard SAP documentation for the JDBC Adapter there’s no mention about the usage of PreparedStatement. Also the Note 831162 – FAQ: XI 3.0 / PI 7.0 / PI 7.1 JDBC Adapter doesn’t threat the topic.

Decompiling a little bit of JDBC Adapter seems there is the possibility to specify the advanced parameter sqlBindMode. This parameter by default is set to “false”, setting it to “true” the statement will be created with PreparedStatement interface.

image 

With the help of the parameter logSQLStatement we have a look to the SQL Statement in the audit log:

image 

On Database Engine side (in my example IBM DB2 Express-C v9.1) it’s possible to have a look to the monitor of sql statements:

image

Note that the number of compilation is one, but the number of executions is 84!!

PS: Looking at the java code of the JDBC Adapter it seems that the sqlBindMode parameter just works if Batch Mode option is set.
With my tests seems that there’s no relation between these two parameters.

 

***UPDATE***

Please have a look to OSS Note:

Note 1404778 – Prepared Statement usage in JDBC Reciever Adapter 

 

References:

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/PreparedStatement.html
http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/tjvpsxup.htm
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#35_02

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Hi Francis,

      Superb piece of information. Will be very useful for processing bulk messages using JDBC Adapter.

      Good Work.

      Regards,
      Sudharshan N A

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Thanks Sudharshan, hope it will be useful!
      Author's profile photo Shen Peng
      Shen Peng
      very useful for me.
      Author's profile photo Former Member
      Former Member
      Hi, my problem is that I update Oracle DB from PI and I'm writing huge XML in CLOB fields. So if I have XML that is larger than 4000 chars,
      Oracle doesn't let it. So I thought to use PreparedStatement. How secure is this solution of yours and what does the msgtype look like?
      Author's profile photo Former Member
      Former Member
      Hi Francis
      Could you please tell the XML SQL format for prepared statements.
      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Hi Sabysachi
      the xml sql structure doesn't affect the behaviour of the sqlbindmode parameter.

      You can build the message structure in the way you want and then the structure will be processed by the jdbc adapter creating the sql statements accordingly with the receiver channel configuration.

      Author's profile photo Former Member
      Former Member

      Hi FancescoMoreover i am using PI 7.0 .Will the SqlBindModen workin PI 7.0.