Re: query manipulation

Mark Lewis wrote:
> Randall,
>
> I happen to have an example right here that might be useful for you.
> The attached class will wrap a driver (in this case hard-coded to
> "org.postgresql.Driver") and transparently implement prepared statement
> caching.  Shouldn't be too hard to adapt it to your case.
>
> Disclaimer: this is for demonstration only, not a production-quality
> implementation.  Don't use this in your product without reading through
> it, understanding the limitations and adding some sanity checking.
> Also, this is based on a real class we use for testing here, but the
> process of simplifying it for the list may have broken stuff.
>
> -- Mark
>
> On Mon, 2007-03-19 at 16:37 -0400, Dave Cramer wrote:
>> Randall,
>>
>> Yeah, you would have to wrap the jdbc driver with a proxy driver.
>> It's not that hard to write one.
>>
>> Dave
>> On 19-Mar-07, at 4:19 PM, Randall Smith wrote:
>>
>>> I'm trying to find a way to manipulate some poorly written queries
>>> in which I don't have access to the application source.  I can't do
>>> it at the Postgresql server, so I'm thinking of attacking it at the
>>> jdbc driver.
>>>
>>> I was thinking of a wrapper that would look at and alter the SQL
>>> before it went to the server.  Anyone know of something like this?
>>> Specifically, I'm trying to drop "FOR UPDATE column name" off of
>>> some queries.  They were written for Oracle, but Postgresql uses
>>> "FOR UPDATE table name".  I'm not a Java programmer, but I won't
>>> let that stop me if it is required.
>>>
>>> Randall
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 7: You can help support the PostgreSQL project by donating at
>>>
>>>                http://www.postgresql.org/about/donate
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>> ------------------------------------------------------------------------
>>
>> package com.mir3.sawtooth.service.piimpl;
>>
>> import java.lang.reflect.InvocationHandler;
>> import java.lang.reflect.InvocationTargetException;
>> import java.lang.reflect.Method;
>> import java.lang.reflect.Proxy;
>> import java.sql.Connection;
>> import java.sql.Driver;
>> import java.sql.DriverManager;
>> import java.sql.DriverPropertyInfo;
>> import java.sql.PreparedStatement;
>> import java.sql.SQLException;
>> import java.util.Enumeration;
>> import java.util.Properties;
>>
>> import org.apache.commons.collections.LRUMap;
>>
>> /**
>>  * Wraps a real JDBC driver to implement prepared statement pooling.
>>  * @author Mark Lewis
>>  * Mar 19, 2007
>>  */
>> public class PoolingDriver implements Driver {
>>
>>     public PoolingDriver(Driver realDriver) {
>>         this.realDriver = realDriver;
>>     }
>>
>>     public Connection connect(String url, Properties info) throws SQLException {
>>         return wrap(realDriver.connect(url, info));
>>     }
>>
>>     public boolean acceptsURL(String url) throws SQLException {
>>         return realDriver.acceptsURL(url);
>>     }
>>
>>     public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) throws SQLException {
>>         return realDriver.getPropertyInfo(url, info);
>>     }
>>
>>     public int getMajorVersion() {
>>         return realDriver.getMajorVersion();
>>     }
>>
>>     public int getMinorVersion() {
>>         return realDriver.getMinorVersion();
>>     }
>>
>>     public boolean jdbcCompliant() {
>>         return realDriver.jdbcCompliant();
>>     }
>>
>>     private Connection wrap(final Connection con) {
>>         ClassLoader loader = getClass().getClassLoader();
>>         return (Connection)Proxy.newProxyInstance(loader, new Class[]{Connection.class}, new InvocationHandler() {
>>             public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
>>                 try {
>>                     if(method.getReturnType().isInstance(PreparedStatement.class)) {
>>                         String sql = (String)args[0];
>>                         PreparedStatement ps = (PreparedStatement) statementCache.get(sql);
>>                         if(ps == null) {
>>                             ps = (PreparedStatement) method.invoke(con, args);
>>                             statementCache.put(sql, ps);
>>                         }
>>                         return ps;
>>                     }
>>                     else {
>>                         return method.invoke(con, args);
>>                     }
>>                 }
>>                 catch(InvocationTargetException ex) {
>>                     throw ex.getCause();
>>                 }
>>             }
>>
>>             private LRUMap statementCache = new LRUMap(100);
>>         });
>>     }
>>
>>     private Driver realDriver;
>>
>>     static {
>>         try {
>>             // Load the real class, then deregister its driver and register mine in its place.
>>             Class realClass = Class.forName("org.postgresql.Driver");
>>             Driver realDriver = null;
>>             for(Enumeration e=DriverManager.getDrivers(); e.hasMoreElements(); ) {
>>                 Driver d = (Driver) e.nextElement();
>>                 if(realClass.isInstance(d)) {
>>                     realDriver = d;
>>                     DriverManager.deregisterDriver(d);
>>                     break;
>>                 }
>>             }
>>             PoolingDriver driver = new PoolingDriver(realDriver);
>>             DriverManager.registerDriver(driver);
>>         }
>>         catch (Exception ex) {
>>             ex.printStackTrace();
>>         }
>>     }
>> }
>>
>> ------------------------------------------------------------------------
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings

This particular statement:

     if(method.getReturnType().isInstance(PreparedStatement.class))

is returning false for my prepared statement.  I'm printing the type
like this:

     System.out.println(method.getReturnType());

and it shows type type is:

     interface java.sql.PreparedStatement

I'm using Jython to test, and my test program looks like so:

from java.lang import *
from java.sql import *
Class.forName("randall.PGProxyDriver")
con = DriverManager.getConnection('jdbc:postgresql:mydb','myuser','mypass')
stmt = con.prepareStatement('select * from mytablename where myfield = ?')
stmt.setString(1, myvalue)
rs = stmt.executeQuery()

How do I match "interface java.sql.PreparedStatement" ?

Randall