Thread: Using callable statements

Hi, id like to know how to use a callable statement that call aa function
that returns a SETFOF RECORD. How do i give the list of column names?

Thanks

JuanF
(a newbie in evolution)

I've read the docs, I've searched the web.

I'm running PGSql 7.3.4 under Cygwin 1.3.22.1, J2SE 1.4.2, latest stable JDBC v3 drivers (pg73jdbc3.jar). This is all running on the same machine, a dual AMD with 1GB memory.

Accessing the database works fine, but when I try to write to a newly created LargeObject it always fails with

org.postgresql.util.PSQLException - FastPath call returned ERROR:  lo_write: invalid large obj descriptor (0)

The code below illustrates this.

The code is actually going to be used within a Tomcat webapp, but I had to use Jdbc3ConnectionPool since that was the only pool that returned connections that were castable to a PGConnection. I get the same error from inside and outside Tomcat. Thought the error might be related to permissions writing large objects so I tried the following which didn't help.

        GRANT ALL ON "pg_largeobject" TO "web";

I also tried not using the connection pool, and instead just using the straight DriverManager.getConnection() instead, but that fails on the lo.write() call too.

I've had large objects working fine on another project (same dev machine) a coupla years ago (pg 7.2.x), so I don't think its a case of HOW I'm doing it, I suspect there's a bug.

Can someone shed some light?


import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.postgresql.PGConnection;
import org.postgresql.jdbc3.Jdbc3ConnectionPool;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

public class TestHarness
{
    public static void main(String[] args)
    {
        LargeObject lo = null;
        Connection conn = null;
        try
        {
            //get a connection that's PGConnection-castable.
            //PoolingDataSource- tried 'n died
            //Jdbc3PoolingDataSource- tried 'n died
            Jdbc3ConnectionPool source = new Jdbc3ConnectionPool();
            source.setServerName("ash");
            source.setDatabaseName("adserver");
            source.setUser("web");
            source.setPassword("web");
            source.setDefaultAutoCommit(false);

            //just to be sure - it always says true so that's good
            //Class.forName("org.postgresql.Driver");
            //conn = DriverManager.getConnection("jdbc:postgresql://ash/adserver", "web", "web");
            conn = source.getConnection();
            System.out.println(conn.getAutoCommit());

            //this only works for connections from Jdbc3ConnectionPool
            PGConnection pgconn = (PGConnection)conn;

            //create the largeobject and prepare to write to it
            LargeObjectManager lom = pgconn.getLargeObjectAPI();
            int oid = lom.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
            lo = lom.open(oid, LargeObjectManager.WRITE);

            //the thing to write
            File file = new File("D:\\downloads\\images\\banner.GIF");
            FileInputStream fis = new FileInputStream(file);

            //prepare a buffer (small enough to force multiple loop iterations
            //so we test it properly)
            byte buff[] = new byte[1024];

            //now write the image
            int bytes_read;
            while ((bytes_read = fis.read(buff, 0, buff.length)) > 0)
            {
                System.out.println("bytes_read=" + bytes_read);
                //ERROR HAPPENS NEXT
                //ERROR HAPPENS NEXT
                //ERROR HAPPENS NEXT
               lo.write(buff, 0, bytes_read);
            }

            //clean up
            fis.close();
            System.out.println("done");
        }
        catch (Exception e)
        {
            System.out.println(e.getClass().getName() + " - " + e.getMessage());
        }
        finally
        {
            if (lo != null)    try{lo.close();}catch (SQLException e){}
            if (conn != null) try{conn.close();}catch (SQLException e){}
        }
    }
}

Juan,

Check out the current development (7.4) version of the docs.  (you can
find a link to them from developer.postgresql.org.  There is an example
in the jdbc documentation here on how to get a ResultSet back from a
function.  Note that this only works on the current development version
of the driver (i.e. download the 7.4 driver from jdbc.postgresql.org).

thanks,
--Barry


Juan Francisco Diaz wrote:
> Hi, id like to know how to use a callable statement that call aa function
> that returns a SETFOF RECORD. How do i give the list of column names?
>
> Thanks
>
> JuanF
> (a newbie in evolution)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Toby,

You need to have autocommit turned off in order to use large objects.
If I add a call to setAutocommit(false) for the connection everything
works fine.

thanks,
--Barry


Toby wrote:
> I've read the docs, I've searched the web.
>
> I'm running PGSql 7.3.4 under Cygwin 1.3.22.1, J2SE 1.4.2, latest stable
> JDBC v3 drivers (pg73jdbc3.jar). This is all running on the same
> machine, a dual AMD with 1GB memory.
>
> Accessing the database works fine, but when I try to write to a newly
> created LargeObject it always fails with
>
> org.postgresql.util.PSQLException - FastPath call returned ERROR:
> lo_write: invalid large obj descriptor (0)
>
> The code below illustrates this.
>
> The code is actually going to be used within a Tomcat webapp, but I had
> to use Jdbc3ConnectionPool since that was the only pool that returned
> connections that were castable to a PGConnection. I get the same error
> from inside and outside Tomcat. Thought the error might be related to
> permissions writing large objects so I tried the following which didn't
> help.
>
>         GRANT ALL ON "pg_largeobject" TO "web";
>
> I also tried not using the connection pool, and instead just using the
> straight DriverManager.getConnection() instead, but that fails on the
> lo.write() call too.
>
> I've had large objects working fine on another project (same dev
> machine) a coupla years ago (pg 7.2.x), so I don't think its a case of
> HOW I'm doing it, I suspect there's a bug.
>
> Can someone shed some light?
>
>
> import java.io.File;
> import java.io.FileInputStream;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
>
> import org.postgresql.PGConnection;
> import org.postgresql.jdbc3.Jdbc3ConnectionPool;
> import org.postgresql.largeobject.LargeObject;
> import org.postgresql.largeobject.LargeObjectManager;
>
> public class TestHarness
> {
>     public static void main(String[] args)
>     {
>         LargeObject lo = null;
>         Connection conn = null;
>         try
>         {
>             //get a connection that's PGConnection-castable.
>             //PoolingDataSource- tried 'n died
>             //Jdbc3PoolingDataSource- tried 'n died
>             Jdbc3ConnectionPool source = new Jdbc3ConnectionPool();
>             source.setServerName("ash");
>             source.setDatabaseName("adserver");
>             source.setUser("web");
>             source.setPassword("web");
>             source.setDefaultAutoCommit(false);
>
>             //just to be sure - it always says true so that's good
>             //Class.forName("org.postgresql.Driver");
>             //conn =
> DriverManager.getConnection("jdbc:postgresql://ash/adserver", "web",
> "web");
>             conn = source.getConnection();
>             System.out.println(conn.getAutoCommit());
>
>             //this only works for connections from Jdbc3ConnectionPool
>             PGConnection pgconn = (PGConnection)conn;
>
>             //create the largeobject and prepare to write to it
>             LargeObjectManager lom = pgconn.getLargeObjectAPI();
>             int oid = lom.create(LargeObjectManager.READ |
> LargeObjectManager.WRITE);
>             lo = lom.open(oid, LargeObjectManager.WRITE);
>
>             //the thing to write
>             File file = new File("D:\\downloads\\images\\banner.GIF");
>             FileInputStream fis = new FileInputStream(file);
>
>             //prepare a buffer (small enough to force multiple loop
> iterations
>             //so we test it properly)
>             byte buff[] = new byte[1024];
>
>             //now write the image
>             int bytes_read;
>             while ((bytes_read = fis.read(buff, 0, buff.length)) > 0)
>             {
>                 System.out.println("bytes_read=" + bytes_read);
>                 //ERROR HAPPENS NEXT
>                 //ERROR HAPPENS NEXT
>                 //ERROR HAPPENS NEXT
>                lo.write(buff, 0, bytes_read);
>             }
>
>             //clean up
>             fis.close();
>             System.out.println("done");
>         }
>         catch (Exception e)
>         {
>             System.out.println(e.getClass().getName() + " - " +
> e.getMessage());
>         }
>         finally
>         {
>             if (lo != null)    try{lo.close();}catch (SQLException e){}
>             if (conn != null) try{conn.close();}catch (SQLException e){}
>         }
>     }
> }
>
>

On Wed, Aug 27, 2003 at 12:36:24PM -0700, Barry Lind wrote:
> Toby,
>
> You need to have autocommit turned off in order to use large objects.
> If I add a call to setAutocommit(false) for the connection everything
> works fine.

Does this imply that setDefaultAutoCommit is broken, then?

> >            Jdbc3ConnectionPool source = new Jdbc3ConnectionPool();
> >            source.setServerName("ash");
> >            source.setDatabaseName("adserver");
> >            source.setUser("web");
> >            source.setPassword("web");
> >            source.setDefaultAutoCommit(false);

> >            conn = source.getConnection();
> >            System.out.println(conn.getAutoCommit());

-O

> On Wed, Aug 27, 2003 at 12:36:24PM -0700, Barry Lind wrote:
> > Toby,
> >
> > You need to have autocommit turned off in order to use large objects.
> > If I add a call to setAutocommit(false) for the connection everything
> > works fine.

yes, i know. and the autocommit was indeed set to false.

> > >            source.setDefaultAutoCommit(false);
see? i did set it.

> > >            System.out.println(conn.getAutoCommit());
and i verified it here.

t

Toby Doig wrote:
>>On Wed, Aug 27, 2003 at 12:36:24PM -0700, Barry Lind wrote:
>>
>>>Toby,
>>>
>>>You need to have autocommit turned off in order to use large objects.
>>>If I add a call to setAutocommit(false) for the connection everything
>>>works fine.
>
>
> yes, i know. and the autocommit was indeed set to false.
>
>
>>>>           source.setDefaultAutoCommit(false);
>
> see? i did set it.

I don't think this method currently does anything.

>
>
>>>>           System.out.println(conn.getAutoCommit());
>
> and i verified it here.
>

Yes you verified that AutoCommit was on.  At least when I run your test
program the above println prints 'true', which means autocommit is on
and thus the problem.

thanks,
--Barry

Oliver Jowett wrote:
>
>
> Does this imply that setDefaultAutoCommit is broken, then?
>
Yes I believe this method doesn't really do anything.  Although since it
is neither part of the jdbc api nor part of the documented postgresql
extentions to the jdbc api, I don't consider it a significant problem.
Perhaps it should just be removed, since I don't know if the jdbc spec
even allows a connection pool to return a connection in any state other
than the documented default of autocommit on.

thanks,
--Barry

aargh, my apologies barry. you were, of course, correct. the println()
call was dumping true and i was misreading it. call it tiredness. or
carelessness.

i actually wrap the pgs connection pool object around my own connection
pool so that all getConnection() calls now go through my own method. i can
then ensure that the autocommit is off (i also set transaction params).

t