Re: query manipulation

Turns out I was on the right track here. Got to trust my intuition
sometimes. It was merely a matter of changing this:

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

to this:

if(method.getReturnType() == PreparedStatement.class) {

and altering args.

Thanks to everyone and especially Mark for the code contribution.

-Randall

Randall Smith wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>