Re: CallableStatement, functions and ResultSets

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

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
>

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

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

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

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