SQL Cursors HELP ASAP
I need somebody to help me with sql cursors, in JSP.
This is my peace of code what is wrong with it?
Statement stmt = myConn.createStatement();
stmt.executeQuery("BEGIN WORK");
stmt.executeQuery("DECLARE item_cursor CURSOR FOR SELECT user_name FROM admin_info");
stmt.executeQuery("FETCH 10 FROM item_cursor");
ResultSet rs = stmt.getResultSet();
while(rs.next()){
if(rs.getString(1) != null){
user_name = rs.getString(1).trim();
}
%><P><%= user_name %></P><%
}
stmt.executeQuery("CLOSE item_cursor");
stmt.executeQuery("COMMIT WORK");
and this is the error that a get: No results where returned by the query
Please help anybody
thanx guys
[800 byte] By [
osilekpl] at [2007-9-26 3:10:23]
If you are using ORACLE drivers and classes.
This sample program shows Oracle JDBC REF CURSOR functionality, creating a PL/SQL package that includes a stored function that returns a REF CURSOR type. The sample retrieves the REF CURSOR into a result set object.
/*
* This sample shows how to call a PL/SQL function that opens
* a cursor and get the cursor back as a Java ResultSet.
*/
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class RefCursorExample
{
public static void main (String args [])
throws SQLException
{
// Load the driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connect to the database
// You can put a database name after the @ sign in the connection URL.
Connection conn =
DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");
// Create the stored procedure
init (conn);
// Prepare a PL/SQL call
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");
// Find out all the SALES person
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "SALESMAN");
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1);
// Dump the cursor
while (rset.next ())
System.out.println (rset.getString ("ENAME"));
// Close all the resources
rset.close();
call.close();
conn.close();
}
// Utility function to create the stored procedure
static void init (Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement ();
stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMP%ROWTYPE; " +
" function job_listing (j varchar2) return myrctype; " +
"end java_refcursor;");
stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2) return myrctype is " +
"rc myrctype; " +
" begin " +
"open rc for select * from emp where job = j; " +
"return rc; " +
" end; " +
"end java_refcursor;");
stmt.close();
}
}
ajdiaz at 2007-6-29 11:16:52 >
I can't help you until you answer my question.
Why won't just 'select user_name from admin_info' work?
That is a single SQL statement rather than the 8+ that you have.
According to my reference that is valid SQL syntax for PostgresSQL. So you need to provide information why that single statement will not work.
Presumably you want this to position in the set on a specific row...
Does postgres support stored procs? If so then you could do it that way.
Does the driver support some of the jdbc 2 stuff, then you could use that.
If not then you can write you own positional stuff simply by calling next() repeatedly without retrieving information.
