using PreparedStatement with database

[ewan91]

hi

i am using PreparedStatement to return a resultSet of just one row.

it seems to work, (compiles and runs) but as soon as i try and extract any values I get the
SQL Error: Invalid Cursor State,

i've tried a variety of ways and am now stuck!!!

thanks
ewan

 

[wushutwist]

Post some code, hard to help otherwise. Wushutwist

 

[ewan91]

this is the code that sets up connection and executes the SQL


public void get97Result( )
{
try // establish the Connection
{
Connection connec = DriverManager.getConnection( "jdbcdbc:EwanProject" ) ;

String seat = "RAYLEIGH"; // just hard coded this in to test

PreparedStatement get97 = connec.prepareStatement("SELECT * " +
"FROM 97VOTE " +
"WHERE SEAT LIKE ? "

get97.setString(1,seat);
res = get97.executeQuery( );
this.first( );

extractValues( res ); // this func will extract the values
connec.close() ;

}


this is the function that when is used i get the SQL error, invalid cursor



public void extractValues( ResultSet res ) throws SQLException
{
String values[ ] = new String[4];

for ( int i = 0; i < 4; i++)
{
values = res.getString( i + 1);
}
}


hope it make some sort of sense
thanks
ewan

 

[meadandale]

Remember that when a ResultSet is returned, the cursor points above the first record in the ResultSet. In order to access the first record, you need to call res.next() to move the pointer.

I'd recommend changing your code to:

public void extractValues(ResultSet res) throws SQLException
{
String values[] = new String[4];
// Continue only if RS has rows
if (res.next())
{
for (int i = 0; i<4; i++)
values = res.getString(i+1);
}
}

Of course, this doesn't allow you to do anything with the values so I'm not sure why you are doing this. It makes more sense to use the ResultSet where you create it unless you make use of ResultSetMetaData to get info about the result set before you get the info. Otherwise a helper method like this is not at all re-usable. For instance:

public String [] extractValues(ResultSet res) throws SQLException
{
ResultSetMetaData rsmd = res.getMetaData();
int columns = rsmd.getColumnCount();
String values[] = new String[columns];

// Continue only if RS has rows
if (res.next())
{
for (int i = 0; i<columns; i++)
values = res.getString(i+1);
}
return values;
}

Of course this only returns the first row but you could extend the idea to process an entire result set using a List or something else.

Regards,

Charles

 

[LeonTang]

Charles is right. There is no point in extracting the values from a resultset when you can use it directly. However, no offense Charles, but there is a flaw in the program. You can't assign a String to an array; values=res.getString(i+1). So this is what I think should be correct:-

public String [] extractValues(ResultSet res) throws SQLException
{
ResultSetMetaData rsmd = res.getMetaData();
int columns = rsmd.getColumnCount();
String values[] = new String[columns];

// Continue only if RS has rows
if (res.next())
{
for (int i = 0; i<columns; i++)
values = res.getString(i+1);
}
return values;
}

Best Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[meadandale]

You are right Leon. I re-edited this a couple of times cause I was working on a couple of different angles and I forgot the array indexing in the final version...

Unfortunately I don't see what you did to fix my problem.

It should read:

values = res.getString(i+1);

Regards,

Charles

 

[meadandale]

Well Leon, it would appear that there is a problem with converting the array brackets in the message. I may not have left them out after all but neither of our posts show them and I know that I put them in the last post.

 

[ewan91]

thanks for your help

i've got it sorted now

the res.next( ) line which i'd forgotton was the key

ewan

 

[LeonTang]

Hi Charles,

I didn't notice mine was missing too...haha.... anyway glad that ewan got his solution

Best Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best