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&DYNAMIC_PREPARE=true&SELECT_OPENS_CURSOR=false&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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
