Thread: 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