working with arrays and sql select stmt

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

38 Replies - 8574 Views - Last Post: 09 July 2011 - 08:25 AM Rate Topic: -----

#1 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

working with arrays and sql select stmt

Posted 03 July 2011 - 02:40 AM

Hello EveryOne,

I am creating a program which will display seven random values from seven columns.

Now, i have created an array of strings. In my database, my number of rows are not all filled up. In other words,in some rows,there might be values in three out of seven columns.Those empty column cells have to be empty for that particular row. The issue here is with my code. I am first checking to see whether the value is null or not.If it null,then i do the query again.If it is not,then i save the value in my array.But based on my code,i get an error.

Here's my code:
  // array to store values 
	     String [] values = new String[7];
	     
		 // array of oclumn names
		 String [] array = {"monday","tuesday","wednesday","thursday","friday","saturday","sunday"};  
   Resultset rset; 
   Statement stmt;

    
  try{
       
	// Load the JDBC driver
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    System.out.println("Driver loaded");
	
    // Establish a connection
    Connection connection = DriverManager.getConnection
      ("jdbc:odbc:JUMAWIND");
    System.out.println("Database connected"); 

    // Create a statement
    stmt = connection.createStatement();

   
 }




 for(int i =0; i < array.length; i++)  
	 {
	 
	     String queryString = ("select " + array[i] + " from menu ORDER BY RAND() LIMIT 1");
		                         
	     rset =   stmt.executeQuery(queryString);
	    while(rset.getString(array[i])==null)
	   {
	      if (rset.getString(array[i])==null) 
	     {
          
	      queryString = ("select " + array[i] + " from menu ORDER BY RAND() LIMIT 1");
		  rset =   stmt.executeQuery(queryString);
		}
	      
	  }         
		    values[i] = rset.getString(array[i]);
		   
		   System.out.println( array[i] + "'s Item: "  + values[i] );	  
    }
   }   



Here's the error:

java.sql.SQL Exception: Before start of result set




Any help will be appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: working with arrays and sql select stmt

#2 harmy01   User is offline

  • D.I.C Head
  • member icon

Reputation: 15
  • View blog
  • Posts: 179
  • Joined: 15-June 10

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 02:55 AM

You have to call next() before you can read values from the first row.

Try changing your for loop with the following.

for(int i =0; i < array.length; i++)  
 {
 
     String queryString = ("select " + array[i] + " from menu ORDER BY RAND() LIMIT 1");
	                         
     rset =   stmt.executeQuery(queryString);
    while(rset.next()) //-------------------->This is what i changed
   {
      if (rset.getString(array[i])==null) 
     {
         
      queryString = ("select " + array[i] + " from menu ORDER BY RAND() LIMIT 1");
	  rset =   stmt.executeQuery(queryString);
     }
      
  }         
	    values[i] = rset.getString(array[i]);
	   
	   System.out.println( array[i] + "'s Item: "  + values[i] );	  
   }
  }   


This post has been edited by harmy01: 03 July 2011 - 02:57 AM

Was This Post Helpful? 0
  • +
  • -

#3 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 03:42 AM

harmy01,
I changed my code to yours. I am now getting this error.

java.sql.SQLException: After end of result set
Was This Post Helpful? 0
  • +
  • -

#4 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3662
  • View blog
  • Posts: 16,823
  • Joined: 20-September 08

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 03:44 AM

Please post full stack trace of the exception
Was This Post Helpful? 0
  • +
  • -

#5 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 05:45 AM

Here's the stack trace:

Database connected
java.sql.SQLException: After end of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:565
0)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5570)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5610)
at Menu.main(Menu.java:55)
Was This Post Helpful? 0
  • +
  • -

#6 harmy01   User is offline

  • D.I.C Head
  • member icon

Reputation: 15
  • View blog
  • Posts: 179
  • Joined: 15-June 10

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 05:51 AM

post your menu.java class, just wanna have a look.
Was This Post Helpful? 0
  • +
  • -

#7 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 06:45 AM

harmy,i have pretty much posted the main content of my class. I have just left out public void main(String [] args) and the catch block at the end.That's it. The whole problem in the for loop.
Was This Post Helpful? 0
  • +
  • -

#8 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 08:00 AM

Any ideas ?

This post has been edited by imu_1: 03 July 2011 - 08:01 AM

Was This Post Helpful? 0
  • +
  • -

#9 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3662
  • View blog
  • Posts: 16,823
  • Joined: 20-September 08

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 08:17 AM

How many rows do you intend to get - one or several?
Was This Post Helpful? 0
  • +
  • -

#10 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 08:51 AM

Several rows. I dont know what exactly you mean by several rows,but as i said, i am trying to get one value from each of the seven columns in a for loop
Was This Post Helpful? 0
  • +
  • -

#11 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3662
  • View blog
  • Posts: 16,823
  • Joined: 20-September 08

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 08:54 AM

I've already given you a method to turn a ResultSet into a TableModel - you don't need to use an array
Was This Post Helpful? 0
  • +
  • -

#12 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 11:31 AM

i dont need to display my values in a table this time. Plus, i wanna display recipee names. So,once i get my values,i want to compare them to see if they are different or not. Becaue ei dont wanna have two recipees in on week. That's why am using array.The resulset method will really be messy.
But tell me one thing though, if i dont check for the null values,then i get the values from my table.But onc ei include the null validation,i get an error. That means the array method works.The problem arises when i include null validation. I think there must be a better way of re-writing my code.

I hope u undestood my point
Was This Post Helpful? 0
  • +
  • -

#13 pbl   User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8381
  • View blog
  • Posts: 31,956
  • Joined: 06-March 08

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 01:14 PM

Your select statement alays the same, unless I miss something, so your rset will always contained the same thing
Also better to rset.close() before assingning to it a new query, that will flush the records waiting to be processed
Was This Post Helpful? 1
  • +
  • -

#14 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3662
  • View blog
  • Posts: 16,823
  • Joined: 20-September 08

Re: working with arrays and sql select stmt

Posted 03 July 2011 - 03:24 PM

Quote

i dont need to display my values in a table this time. Plus, i wanna display recipee names.


In that case, use the similar

http://technojeeves....-to-nested-list
Was This Post Helpful? 0
  • +
  • -

#15 imu_1   User is offline

  • D.I.C Regular

Reputation: -6
  • View blog
  • Posts: 256
  • Joined: 03-June 11

Re: working with arrays and sql select stmt

Posted 04 July 2011 - 07:57 AM

Quote

Your select statement alays the same, unless I miss something, so your rset will always contained the same thing


SO, what are you suggesting apart from closing the result set?


@goose, i opened the link u gave me nd luked at the code, isnt that code printing the entire table instead of one data from each column ?
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3