Thread: CallableStatement, functions and ResultSets

CallableStatement, functions and ResultSets

From
"Scot P. Floess"
Date:
I hope someone can help me.  And thanks ahead of time!

Here is my problem:  I have a table and a function that simply performs
a select * from table.  The function returns a setof table.  I want to
use a CallableStatement and execute the function, but get an exception
stating "Cannot display a value of type RECORD"

Here is my table:

create table state_table
(
     abbreviation char ( 2 ) unique not null,
     name         text       not null
);


Here is the function:

CREATE OR REPLACE FUNCTION state_find () RETURNS SETOF state_table AS '
DECLARE
     _aRow state_table%ROWTYPE;

BEGIN
     FOR _aRow IN SELECT * FROM state_table LOOP
    RETURN NEXT _aRow;
     END LOOP;

     RETURN;
END;
' LANGUAGE 'plpgsql';

Here is the snippet of java code:

     private static void retrieveState ( final ResultSet rs ) throws
Exception
     {
    System.out.println
    (
        "Abbreviation:  <" + rs.getString ( 1 ) +
        ">  Name:  " + rs.getString ( 2 )
    );
     }

     private static void retrieveStates ( final Connection db ) throws
Exception
     {
    final CallableStatement stmt =
        db.prepareCall ( "{call state_find ()}" );

    final ResultSet rs = stmt.executeQuery ();

    while ( rs.next () )
    {
        retrieveState ( rs );
    }

    stmt.close ();
     }

Here is my exception:

java.sql.SQLException: ERROR:  Cannot display a value of type RECORD

         at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
         at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
         at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
         at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
         at org.jpim.populate.Retrieve.retrieveStates(Retrieve.java:58)
         at org.jpim.populate.Retrieve.main(Retrieve.java:83)

If I change my method as such:

     private static void retrieveStates ( final Connection db ) throws
Exception
     {
    final PreparedStatement stmt =
        db.prepareStatement ( "select * from state_find ()" );
    final ResultSet rs = stmt.executeQuery ();

    while ( rs.next () )
    {
        retrieveState ( rs );
    }

    stmt.close ();
     }

It all works.  I've seen on the archives that this is what one needs to
do.  And that's fine by me.  But my question is this:  should I just use
a PreparedStatement that does "select * from state_table" or have a
function that does the "select * from table" and then use a
PreparedStatement to "select * from function()"

My gut feeling is to use a PreparedStatement with "select * from
state_table"  I guess I'd really like to know which is more efficient?
And also, I'd like to know why one can't use a CallableStatement?

Thanks again!

Scot

--
Scot P. Floess - 27 Lake Royale - Louisburg, NC  27549 - 252-478-8087

Open Source Home Page
--------------------------------------
http://javapim.sourceforge.net
http://jplate.sourceforge.net
http://jwaretechniques.sourceforge.net

Open Source Project Host
-----------------------------------------------
http://sourceforge.net/projects/javapim
http://sourceforge.net/projects/jplate
http://sourceforge.net/projects/jwaretechniques



Re: CallableStatement, functions and ResultSets

From
Barry Lind
Date:
Scot,

You should definitely use the PreparedStatement, that will be much more
efficient.  Going through the stored function just adds a lot of
overhead without providing any added value.

As for the CallableStatement problem.  Off the top of my head I am not
sure why that isn't working.

--Barry

Scot P. Floess wrote:
> I hope someone can help me.  And thanks ahead of time!
>
> Here is my problem:  I have a table and a function that simply performs
> a select * from table.  The function returns a setof table.  I want to
> use a CallableStatement and execute the function, but get an exception
> stating "Cannot display a value of type RECORD"
>
> Here is my table:
>
> create table state_table
> (
>     abbreviation char ( 2 ) unique not null,
>     name         text       not null
> );
>
>
> Here is the function:
>
> CREATE OR REPLACE FUNCTION state_find () RETURNS SETOF state_table AS '
> DECLARE
>     _aRow state_table%ROWTYPE;
>
> BEGIN
>     FOR _aRow IN SELECT * FROM state_table LOOP
>     RETURN NEXT _aRow;
>     END LOOP;
>
>     RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
> Here is the snippet of java code:
>
>     private static void retrieveState ( final ResultSet rs ) throws
> Exception
>     {
>     System.out.println
>     (
>         "Abbreviation:  <" + rs.getString ( 1 ) +
>         ">  Name:  " + rs.getString ( 2 )
>     );
>     }
>
>     private static void retrieveStates ( final Connection db ) throws
> Exception
>     {
>     final CallableStatement stmt =
>         db.prepareCall ( "{call state_find ()}" );
>
>     final ResultSet rs = stmt.executeQuery ();
>
>     while ( rs.next () )
>     {
>         retrieveState ( rs );
>     }
>
>     stmt.close ();
>     }
>
> Here is my exception:
>
> java.sql.SQLException: ERROR:  Cannot display a value of type RECORD
>
>         at
> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
>         at
> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
>
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
>
>         at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
>
>         at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
>
>         at org.jpim.populate.Retrieve.retrieveStates(Retrieve.java:58)
>         at org.jpim.populate.Retrieve.main(Retrieve.java:83)
>
> If I change my method as such:
>
>     private static void retrieveStates ( final Connection db ) throws
> Exception
>     {
>     final PreparedStatement stmt =
>         db.prepareStatement ( "select * from state_find ()" );
>     final ResultSet rs = stmt.executeQuery ();
>
>     while ( rs.next () )
>     {
>         retrieveState ( rs );
>     }
>
>     stmt.close ();
>     }
>
> It all works.  I've seen on the archives that this is what one needs to
> do.  And that's fine by me.  But my question is this:  should I just use
> a PreparedStatement that does "select * from state_table" or have a
> function that does the "select * from table" and then use a
> PreparedStatement to "select * from function()"
>
> My gut feeling is to use a PreparedStatement with "select * from
> state_table"  I guess I'd really like to know which is more efficient?
> And also, I'd like to know why one can't use a CallableStatement?
>
> Thanks again!
>
> Scot
>




Re: CallableStatement, functions and ResultSets

From
"Scot P. Floess"
Date:
Barry:

Thanks for the response!

Your answer was what I thought to be the case.  So, when is it better to
use a PL/pgSQL function versus building the SQL and using a
PreparedStatement?

Much appreciated!

Scot

Barry Lind wrote:

> Scot,
>
> You should definitely use the PreparedStatement, that will be much
> more efficient.  Going through the stored function just adds a lot of
> overhead without providing any added value.
>
> As for the CallableStatement problem.  Off the top of my head I am not
> sure why that isn't working.
>
> --Barry
>
> Scot P. Floess wrote:
>
>> I hope someone can help me.  And thanks ahead of time!
>>
>> Here is my problem:  I have a table and a function that simply
>> performs a select * from table.  The function returns a setof table.
>> I want to use a CallableStatement and execute the function, but get
>> an exception stating "Cannot display a value of type RECORD"
>>
>> Here is my table:
>>
>> create table state_table
>> (
>>     abbreviation char ( 2 ) unique not null,
>>     name         text       not null
>> );
>>
>>
>> Here is the function:
>>
>> CREATE OR REPLACE FUNCTION state_find () RETURNS SETOF state_table AS '
>> DECLARE
>>     _aRow state_table%ROWTYPE;
>>
>> BEGIN
>>     FOR _aRow IN SELECT * FROM state_table LOOP
>>     RETURN NEXT _aRow;
>>     END LOOP;
>>
>>     RETURN;
>> END;
>> ' LANGUAGE 'plpgsql';
>>
>> Here is the snippet of java code:
>>
>>     private static void retrieveState ( final ResultSet rs ) throws
>> Exception
>>     {
>>     System.out.println
>>     (
>>         "Abbreviation:  <" + rs.getString ( 1 ) +
>>         ">  Name:  " + rs.getString ( 2 )
>>     );
>>     }
>>
>>     private static void retrieveStates ( final Connection db ) throws
>> Exception
>>     {
>>     final CallableStatement stmt =
>>         db.prepareCall ( "{call state_find ()}" );
>>
>>     final ResultSet rs = stmt.executeQuery ();
>>
>>     while ( rs.next () )
>>     {
>>         retrieveState ( rs );
>>     }
>>
>>     stmt.close ();
>>     }
>>     Here is my exception:
>>
>> java.sql.SQLException: ERROR:  Cannot display a value of type RECORD
>>
>>         at
>> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
>>         at
>> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
>>
>>         at
>> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
>>
>>         at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
>>
>>         at
>> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
>>
>>         at org.jpim.populate.Retrieve.retrieveStates(Retrieve.java:58)
>>         at org.jpim.populate.Retrieve.main(Retrieve.java:83)
>>
>> If I change my method as such:
>>
>>     private static void retrieveStates ( final Connection db ) throws
>> Exception
>>     {
>>     final PreparedStatement stmt =
>>         db.prepareStatement ( "select * from state_find ()" );
>>     final ResultSet rs = stmt.executeQuery ();
>>
>>     while ( rs.next () )
>>     {
>>         retrieveState ( rs );
>>     }
>>
>>     stmt.close ();
>>     }
>>
>> It all works.  I've seen on the archives that this is what one needs
>> to do.  And that's fine by me.  But my question is this:  should I
>> just use a PreparedStatement that does "select * from state_table" or
>> have a function that does the "select * from table" and then use a
>> PreparedStatement to "select * from function()"
>>
>> My gut feeling is to use a PreparedStatement with "select * from
>> state_table"  I guess I'd really like to know which is more
>> efficient? And also, I'd like to know why one can't use a
>> CallableStatement?
>>
>> Thanks again!
>>
>> Scot
>>
>
>
>
>

--
Scot P. Floess - 27 Lake Royale - Louisburg, NC  27549 - 252-478-8087

Open Source Home Page
--------------------------------------
http://javapim.sourceforge.net
http://jplate.sourceforge.net
http://jwaretechniques.sourceforge.net

Open Source Project Host
-----------------------------------------------
http://sourceforge.net/projects/javapim
http://sourceforge.net/projects/jplate
http://sourceforge.net/projects/jwaretechniques




jdbc/taglibs issue

From
Tanu Shankar Bhatnagar
Date:
I am trying a simple JSTL example. I am using tomcat4.0.1, jdk1.4.1_01,
red hat 8.0, jstl 1.0 implementation from sun, postgresql 7.3.2 with jdbc
drivers pg73jdbc3.jar.

In my example I create a data source and run a query. The server responds
with a "No suitable driver" message. I have tried the same query in a
Servlet, and it works fine. Is this a driver compatibility issue? I am
pretty sure my container is finding the pgsql jdbc drivers - I have
explicitly put them in the tomcat startup classpath, and in
java_home/lib/ext.

I have attached a snippet from my jsp and exception stacktrace. If someone
can help me fix this issue, I would greatly appreaciate it.

Thanks,
Tanu

Here is my jsp:
<sql:setDataSource
  var="example"
  driver="org.postgresql.Driver"
  url="jdbc:postgresql:pandavs"
  user="username"
  password="password"
/>

<sql:transaction dataSource="${example}">

  <sql:query var="deejays">
    SELECT * FROM category
  </sql:query>

</sql:transaction>

And here is the exception stacktrace:

javax.servlet.ServletException: Error getting connection: "No suitable
driver"
        at
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)
        at org.apache.jsp.query$jsp._jspService(query$jsp.java:241)
        at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)
        at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)
        at
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:646)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:483)
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:646)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:483)
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2349)
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:646)
        at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:644)
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:171)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:644)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:483)
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:646)
        at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:469)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:644)
        at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:483)
        at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at
org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
        at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
        at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:376)
        at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
        at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:518)
        at java.lang.Thread.run(Thread.java:536)

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

Re: jdbc/taglibs issue

From
Tony Grant
Date:
On Mon, 2003-03-17 at 21:11, Tanu Shankar Bhatnagar wrote:
> I am trying a simple JSTL example. I am using tomcat4.0.1, jdk1.4.1_01,
> red hat 8.0, jstl 1.0 implementation from sun, postgresql 7.3.2 with jdbc
> drivers pg73jdbc3.jar.

>
> Here is my jsp:
> <sql:setDataSource
>   var="example"
>   driver="org.postgresql.Driver"
>   url="jdbc:postgresql:pandavs"
>   user="username"
>   password="password"
> />

Your URL should be more like:

  url="jdbc:postgresql://server/db_name"

Cheers

Tony Grant

--
www.tgds.net Library management software toolkit,
redhat linux on Sony Vaio C1XD,
Dreamweaver MX with Tomcat and PostgreSQL


datasource issue

From
Tanu Shankar Bhatnagar
Date:
I ran into jdbc driver not being found by tomcat issue, which I solved by
putting pg73jdbc3.jar in common/lib in tomcat. Now I am trying to look up
a data source through JNDI in my servlet and jstl-jsp both, but it is
failing in both cases. Assuming that it has got to do with the drivers not
being found, I have tried the following:
1. put the drivers in common/lib, server/lib, shared/lib and of course
WEB-INF/lib
2. put the drivers in java_home/lib/ext
3. add the drivers explicitly in catalina.sh

None if this worked, though I managed to change the error message from 'No
suitable driver' to 'Cannot load JDBC driver class 'null''.

I have followed the instructions from tomcat site to create my datasource,
and to modify my web.xml appropriately.
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html

I am using tomcat4.0.1, jdk1.4.1_01, red hat 8.0, jstl 1.0 implementation
from sun, postgresql 7.3.2 with jdbc drivers pg73jdbc3.jar.

Any help on this issue will be appreciated.

Thanks,
Tanu

ps: Tony, I did change the db url to what you suggested, but that did not
make a difference.

Here is the error snippet I get:
java.sql.SQLException: Cannot load JDBC driver class 'null'
        at
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:529)
        at
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:312)
        at TestServlet.doGet(TestServlet.java:52)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

Code snippet from Servlet:
            Context initCtx = new InitialContext();
            Context envCtx = (Context) initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/test");
            Connection conn = ds.getConnection();

            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(CATEGORY_SQL);
            while(rs.next()) {
                // get stuff
            }

Code snippet from jsp:
<sql:setDataSource
  var="example"
  dataSource="jdbc/test"
/>

<sql:transaction dataSource="${example}">
  <sql:query var="deejays">
    SELECT name, description FROM category
  </sql:query>
</sql:transaction>


__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com