Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

setCursorName

2 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-06-13 21:05:02.0Z
Gene Barkin Posted on 1997-06-13 19:03:44.0Z
Message-ID: <33A19990.3C6A84C9@musc.edu>
Date: Fri, 13 Jun 1997 15:03:44 -0400
From: Gene Barkin <barking@musc.edu>
Reply-To: barking@musc.edu
Organization: Medical University of South Carolina
X-Mailer: Mozilla 4.0b5 [en] (Win95; I)
MIME-Version: 1.0
Subject: setCursorName
X-Priority: 3 (Normal)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 79
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:686
Article PK: 252438

Hi...

Where should I put the code that sets and retrieves the name of a
cursor?

Is the cursor name a class variable of the statement object, or is it
associated with a result set obtained by issuing an executeQuery() using
the statement? I'm confused by the fact that the example code shows:

stmt1.setCursorName("author_Cursor");
ResultSet rs = stmt1.executeQuery("SELECT
au_id,au_lname, au_fname
FROM authors WHERE city = 'Oakland'
FOR UPDATE OF au_lname");
String cursor = rs.getCursorName();

i.e. the statement sets the cursor name, but a result set retrieves
it....

When I try to do this in my code, I get an exception that tells me:

Unexpected exception : java.sql.SQLException: Statement already in use
as a cursor: Statement already in use as a cursor, sqlstate = JZ00E

Here is (some of) my code:

variable declaration:
static Statement _stmt = null;
static Statement _stmt2 = null;
static Statement _stmt3 = null;
static ResultSet _rs = null;
static ResultSet _rs2 = null;

...

in the constructor:

_con = DriverManager.getConnection(_server, props);
_stmt = _con.createStatement();
_stmt2 = _con.createStatement();
_stmt3 = _con.createStatement();
...

/* the constructor uses one of the statements (_stmt) to populate a
menu. That works fine. Likewise _stmt3 -- which is used to add a record
(and has no result set) works fine. I reuse _stmt (and the result set
_rs) to populate a pick list based on a menu selection -- that works
fine.

The other statements are called by the action events of buttons and menu
items to add and edit records.
*/

// the method that populates data fields based on a selection of an item
in the pick list (in the action event handler)

else if (e.target == bookList) {
textOut.appendText("retrieving data for: " + arg.toString().trim() +
"\n");
// build a sql statement to get the data for this book
StringBuffer titleQuery = new StringBuffer(
"select title, author, descrip, owner, heldby, since from books where
title = ");
titleQuery.append("'" + arg + "'");

// do the select and map the results to the data fields...
try {

=> causes error _stmt2.setCursorName("bookCursor");

boolean results = _stmt2.execute(titleQuery.toString());
if (results) {
_rs2 = _stmt2.getResultSet();


...

at this point I'd like to be able to use _rs2.getCursorName() so I can
later edit the record with a positioned update, but Since I haven't been
able to name it successfully, I'll have to wait to get there....

Anyone have an idea on how to do this? Where should the statement's name
be set?

If I put:

stmt = con.createStatement(); )
stmt.setCursorName("foo");

in the constructor, and then run the method that uses the statement to
create a result set I get this error:

Unexpected exception : java.sql.SQLException: Statement already in use
as a cursor: Statement already in use as a cursor, sqlstate = JZ00E


Thanks,

Gene Barkin
Software Engineer
Medical University of South Carolina
barking@musc.edu


Lance Andersen Posted on 1997-06-13 21:05:02.0Z
Message-ID: <33A1B5FE.6B74@sybase.com>
Date: Fri, 13 Jun 1997 17:05:02 -0400
From: Lance Andersen <lancea@sybase.com>
X-Mailer: Mozilla 3.01Gold (X11; I; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: barking@musc.edu
Subject: Re: setCursorName
References: <33A19990.3C6A84C9@musc.edu>
Content-Type: multipart/mixed; boundary="------------611558FD3911"
Newsgroups: sybase.public.jdbcconnect
Lines: 323
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:683
Article PK: 252436

Gene,

Attached is a program which demonstrates using setCursorName()
and getCursorName().

-lance

Gene Barkin wrote:
>
> Hi...
>
> Where should I put the code that sets and retrieves the name of a
> cursor?
>
> Is the cursor name a class variable of the statement object, or is it
> associated with a result set obtained by issuing an executeQuery() using
> the statement? I'm confused by the fact that the example code shows:
>
> stmt1.setCursorName("author_Cursor");
> ResultSet rs = stmt1.executeQuery("SELECT
> au_id,au_lname, au_fname
> FROM authors WHERE city = 'Oakland'
> FOR UPDATE OF au_lname");
> String cursor = rs.getCursorName();
>
> i.e. the statement sets the cursor name, but a result set retrieves
> it....
>
> When I try to do this in my code, I get an exception that tells me:
>
> Unexpected exception : java.sql.SQLException: Statement already in use
> as a cursor: Statement already in use as a cursor, sqlstate = JZ00E
>
> Here is (some of) my code:
>
> variable declaration:
> static Statement _stmt = null;
> static Statement _stmt2 = null;
> static Statement _stmt3 = null;
> static ResultSet _rs = null;
> static ResultSet _rs2 = null;
>
> ...
>
> in the constructor:
>
> _con = DriverManager.getConnection(_server, props);
> _stmt = _con.createStatement();
> _stmt2 = _con.createStatement();
> _stmt3 = _con.createStatement();
> ...
>
> /* the constructor uses one of the statements (_stmt) to populate a
> menu. That works fine. Likewise _stmt3 -- which is used to add a record
> (and has no result set) works fine. I reuse _stmt (and the result set
> _rs) to populate a pick list based on a menu selection -- that works
> fine.
>
> The other statements are called by the action events of buttons and menu
> items to add and edit records.
> */
>
> // the method that populates data fields based on a selection of an item
> in the pick list (in the action event handler)
>
> else if (e.target == bookList) {
> textOut.appendText("retrieving data for: " + arg.toString().trim() +
> "\n");
> // build a sql statement to get the data for this book
> StringBuffer titleQuery = new StringBuffer(
> "select title, author, descrip, owner, heldby, since from books where
> title = ");
> titleQuery.append("'" + arg + "'");
>
> // do the select and map the results to the data fields...
> try {
>
> => causes error _stmt2.setCursorName("bookCursor");
>
> boolean results = _stmt2.execute(titleQuery.toString());
> if (results) {
> _rs2 = _stmt2.getResultSet();
>
> ...
>
> at this point I'd like to be able to use _rs2.getCursorName() so I can
> later edit the record with a positioned update, but Since I haven't been
> able to name it successfully, I'll have to wait to get there....
>
> Anyone have an idea on how to do this? Where should the statement's name
> be set?
>
> If I put:
>
> stmt = con.createStatement(); )
> stmt.setCursorName("foo");
>
> in the constructor, and then run the method that uses the statement to
> create a result set I get this error:
>
> Unexpected exception : java.sql.SQLException: Statement already in use
> as a cursor: Statement already in use as a cursor, sqlstate = JZ00E
>
> Thanks,
>
> Gene Barkin
> Software Engineer
> Medical University of South Carolina
> barking@musc.edu

--
===============================================================================
Lance J. Andersen Email: lancea@sybase.com
Sybase Technical Support Phone:(617) 564-6336
77 South Bedford Street Fax: (617) 564-6148
Burlington, MA 01803

The Dark Knight Returns!!! Let's Go Penguins!!!
===============================================================================

//
// updatecursor class demonstrates how to use multiple cursors at the
// same time.
//
// updatecursor may be invoked with the optional parameters:
// -U username
// -P password
// -D debuglibraies
// -S server
//
import java.io.*;
import java.sql.*;
import java.util.*;
import com.sybase.utils.Debug;

class updatecursor {

static public final int MAXROWS = 5;
static Connection _con = null;
static String _user = "sa";
static String _password = "";
static String _url = "jdbc:sybase:Tds:alder:6689/pubs2";
static String _query =
"select au_id, au_lname, au_fname from #authors for update";
static String createQuery = "select * into #authors from authors" +
"\ncreate unique clustered index myind on #authors(au_id)";
static String _au_id= "486-29-1786";
static String _au_fname = "Lance";
static String _au_lname = "Andersen";
static String _query2 =
"select au_id, au_lname, au_fname from #authors where au_id ='" +
_au_id + "'";


public static void main (String args[]) {


// Parse the command line

if (!processCommandline(args))
{
System.out.println(
"Syntax:\n" +
"\tBinaryStream [-U <username>] [-P <password>] " +
" [-S <servername>]\n\t\t [-D <debug-class-list>]");
System.exit(1);
}


try {

// Load the Sybase Driver

// Debug.debug(true,"ALL");
Class.forName("com.sybase.jdbc.SybDriver");


// Attempt to connect to a driver.


Properties props = new Properties();
props.put("user", _user);
props.put("password", _password);


Connection _con = DriverManager.getConnection(_url, props);


// If we were unable to connect, an exception
// would have been thrown. So, if we get here,
// we are successfully connected to the URL

// Check for, and display and warnings generated
// by the connect.

checkForWarning (_con.getWarnings ());


createTempTable(_con);

// Modify our Row
updateRow(_con);

// Display Rows
displayRows(_con);


// Close the connection

_con.close();

}
catch (SQLException ex) {

// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together

System.out.println ("\n*** SQLException caught ***\n");

while (ex != null) {
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " + ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
}
catch (java.lang.Exception ex) {

// Got some other type of exception. Dump it.

ex.printStackTrace ();
}
}

//-------------------------------------------------------------------
// checkForWarning
// Checks for and displays warnings. Returns true if a warning
// existed
//-------------------------------------------------------------------

private static boolean checkForWarning (SQLWarning warn) throws SQLException
{
boolean rc = false;

// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together

if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println ("SQLState: " + warn.getSQLState ());
System.out.println ("Message: " + warn.getMessage ());
System.out.println ("Vendor: " + warn.getErrorCode ());
System.out.println ("");
warn = warn.getNextWarning ();
}
}
return rc;
}

private static void createTempTable( Connection con) throws SQLException
{
System.out.println("Executing: " + createQuery);
Statement statement = con.createStatement();
statement.executeUpdate(createQuery);
statement.close();
}

private static void updateRow(Connection con)
throws SQLException
{
String cursorName = new String("read_authors");
Statement stmt1 = con.createStatement();
Statement stmt2 = con.createStatement();

// Open our cursor
stmt1.setCursorName(cursorName);
System.out.println("Executing: " + _query);
ResultSet rs = stmt1.executeQuery(_query);

String cursor = rs.getCursorName();
while(rs.next())
{
if(rs.getString("au_id").equals(_au_id))
{
// Update our row using the current cursor position

System.out.println( "\n\nModifying: au_id= " + _au_id +
", au_lname= " + rs.getString("au_lname") +
", au_fname= " + rs.getString("au_fname") );
String query = "update #authors set au_lname = '" + _au_lname
+ "',au_fname= '" + _au_fname + "' where current of " + cursor;

System.out.println("Executing: " + query);
stmt2.executeUpdate(query);
}
}
}

private static void displayRows( Connection con)
throws SQLException
{

Statement stmt = con.createStatement();;
System.out.println("\nExecuting: " + _query2);

ResultSet rs = stmt.executeQuery (_query2);
dispResultSet(rs);

rs.close();
stmt.close();
}


static private boolean processCommandline(String args[])
{
//* DONE
String arg;
int errorCount = 0;
for (int i = 0; i < args.length; i++)
{
arg = args[i];
if (arg.regionMatches(0, "-", 0, 1))
{
try
{
switch(arg.charAt(1))
{
case 'D':
i++;
try
{
Debug.debug(true, args[i]);
}
catch (IOException ioe)
{
System.out.println("Error turning on debugging " +
ioe);
}
break;
case 'U':
i++;
_user = args[i];
break;
case 'P':
i++;
_password = args[i];
break;
case 'S':
i++;
_url = args[i];
break;
default:
System.out.println("Invalid command line option: " + arg);
errorCount++;
break;
}
}
catch (ArrayIndexOutOfBoundsException aioobe)
{
System.out.println("missing option argument");
errorCount++;
}
}
else
{
// The syntax has no non "-" arguments
errorCount++;
}
}

return(errorCount == 0);
}

private static void dispResultSet (ResultSet rs) throws SQLException
{
int i;

// Get the ResultSetMetaData. This will be used for
// the column headings

ResultSetMetaData rsmd = rs.getMetaData ();

// Get the number of columns in the result set

int numCols = rsmd.getColumnCount ();

// Display column headings

for (i=1; i<=numCols; i++) {
if (i > 1) System.out.print("\t\t");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");

// Display data, fetching until end of the result set

while (rs.next ()) {

// Loop through each column, getting the
// column data and displaying

for (i=1; i<=numCols; i++) {
if (i > 1) System.out.print("\t\t");
System.out.print(rs.getString(i));
}
System.out.println("");

// Fetch the next result set row

}
}
}