dbcp, sybase, and preparedstatement pooling

["Jesus Salvo Jr." <je...@communicator.com.au>]

Anyone here using Sybase jConnect 5.5 with Commond-Dbcp-1.2.1 with prepared 
statement pooling ?

I am using commons-dbcp-1.2.1 as the connection pooling with Hibernate 3.1.3 
with JConnect 5.5 and ASE 12.5.3.x, using the following:

http://wiki.apache.org/jakarta-commons/DBCP/Hibernate

... which I then have the following relevant properties in my 
hibernate-cfg.xml:

    <property 
name="hibernate.connection.provider_class">com.mig.hibernate.connection.DBCPConnectionProvider</property>

    <property 
name="hibernate.connection.driver_class">com.sybase.jdbc2.jdbc.SybDriver</property>
    <property 
name="hibernate.connection.url">jdbc:sybase:Tds:192.168.0.80:4100?BE_AS_JDBC_COMPLIANT_AS_POSSIBLE=true&amp;DYNAMIC_PREPARE=true&amp;SELECT_OPENS_CURSOR=false&amp;PACKETSIZE=4096</property>
    <property name="hibernate.connection.username">XXXXXX</property>
    <property name="hibernate.connection.password">XXXXXX</property>
    <property name="hibernate.connection.pool_size">20</property>
    <property name="hibernate.connection.SQLINITSTRING">set TextSize 
2048000</property>
    <property name="hibernate.connection.autocommit">false</property>
    <property name="hibernate.show_sql">true</property>
    <property 
name="hibernate.dialect">org.hibernate.dialect.SybaseDialect</property>
    <property name="hibernate.use_outer_join">false</property>
    <property name="hibernate.max_fetch_depth">0</property>
    <property name="hibernate.jdbc.use_streams_for_binary">true</property>

    <property name="hibernate.dbcp.initialSize">10</property>
    <property name="hibernate.dbcp.validationQuery">select 1</property>
    <property name="hibernate.dbcp.defaultAutoCommit">false</property>
    <property name="hibernate.dbcp.initialSize">2</property>
    <property name="hibernate.dbcp.maxActive">20</property>
    <property name="hibernate.dbcp.maxIdle">5</property>
    <property name="hibernate.dbcp.minIdle">1</property>
    <property name="hibernate.dbcp.maxWait">30000</property>
    <property name="hibernate.dbcp.testOnBorrow">false</property>
    <property name="hibernate.dbcp.poolPreparedStatements">true</property>
    <!--property 
name="hibernate.dbcp.maxOpenPreparedStatements">20</property-->
    <property name="hibernate.dbcp.removeAbandoned">false</property>


I then ran Ribo to verify that prepared statements are indeed being pool / 
cached. I do indeed can see them being used, but somehow, some are not.

In Sybase, prepared statements are created as temporary stored procedures if 
you set the property DYNAMIC_PREPARE=true in the JDBC URL. Subsequent calls 
by the same connection for the same statement simply passes the parameters 
and then executed the temporary stored procedure.

The prepared statements as dynamic stored procedure I can see with the TDS 
DYNAMIC tokens in the Ribo output.

However, there are some statements that are exactly the same but instead are 
in a TDS LANGUAGE statement.



Here is an example of the TDS dynamic token:

DYNAMIC Token (0xE7); variable length
  Length [2]:                   261
  Type [1]:                     DYN_PREPARE (0x01)
  Status [1]:                   DYNAMIC_UNUSED (0x00)
  Name Length [1]:              6
  Name [6]:                     "dyn103"
  Statement Length [2]:         250
  Statement [250]:              "create proc dyn103 as insert into 
dbo.message_notification_history (protocol_message_id, notification, 
notified_on, additional_info, done_date, notification_sent_to_external, 
message_id, message_notification_historyid) values (?, ?, ?, ?, ?, ?, ?, ?)"

Here is an example of the TDS language token:

LANGUAGE Token (0x21); variable length.
  Length [4]:                   245
  Status [1]:                   PARAMETERIZED (0x01)
  Text Length [0]:              [244]
  Text [244]:                   "insert into dbo.message_notification_history 
(protocol_message_id, notification, notified_on, additional_info, done_date, 
notification_sent_to_external, message_id, message_notification_historyid) 
values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7)"


It's exactly the same text, but sent to the ASE server differently.


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org