JAVA Sql error: Column not found

[suretd]

HI All,

Another Question:

I have created another program that queries the StockTracker database for all users and the stocks held by each user. The program compiles, but at run-time i get the error:

"Exception in thread 'main' java.sql.SQLException: Column not found at sun.jdbc.odbc.JdbcOdbcResultSet.findColumn<JdbcOdbcResultSet.java:1833>
at sun.jdbc.odbc.JdbcOdbcResultSet.getString<JdbcOdbcResultSet.java:395>
at StockByUser.main<StockByUser.java:43>"

I am using the column names that are located in my database. Why is my program not picking up on them? It seems that my error is in the last println statement of my code. Should I not be using the getString() method here?

I am attaching my code as well:

import java.io.*;
import java.sql.*;

public class StockByUser
{
	public static void main(String[] args)throws Exception
	{
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

		String url = "jdbc:odbc:StockTracker";

		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();
		Statement stmt1 = con.createStatement();
		Statement stmt2 = con.createStatement();

		System.out.println("Stock holdings by User\n");
		System.out.println("User ID User Name");
		System.out.println("        Stock - Description");
		System.out.println("-------------------------------------------");

		ResultSet rs = stmt.executeQuery("SELECT * FROM Users ORDER BY userID");
		ResultSet rs1 = stmt1.executeQuery("SELECT * FROM UserStocks ORDER BY userID");
		ResultSet rs2 = stmt2.executeQuery("SELECT * FROM Stocks ORDER BY symbol");

		String previousUser = null;
		String currentUser = null;
		while(rs.next())
		{
		    currentUser = rs.getString("userID");
		    if (previousUser == null || !previousUser.equals(currentUser))
		    {
		        System.out.println(currentUser+" "+rs.getString("firstName")+" "+rs.getString("lastName"));
		        previousUser = currentUser;
}
		        
		        System.out.println("\t"+rs.getString("symbol")+" "+rs.getString("name"));

		}
	}
}

[masijade]

Yes, you should be using getString, but you should not be using "select *". You should be naming the columns. Also, per definition, a JDBC Driver need only allow access to a column once and only in the order in which they are returned. Which means that unless the columns are delivered in the following order: userID, firstName, lastName, symbol, name , you may have problems accessing them (and with the ODBC Driver you probably will, especially if it is an Access DB behind it).

To repeat, I would suggest designating the column names rather than using "*", and the column "name" may be a problem, in and of itself, since there is a large chance that is a reserved word, meaning it needs to be surrounded by quotes (or in Access, square brackets, i.e. "\"name\"" or "[name]" ). If neither of those work, attempt using the index (i.e. getString(5) ).

[suretd]

HI All,

I have made some changes to the ResultSet statements ... and the while loop ... and now the code is working perfectly.

Here are the changes:

ResultSet rs = stmt.executeQuery("SELECT * FROM Users ORDER BY userID");
		ResultSet rs1 = stmt1.executeQuery("SELECT * FROM UserStocks ORDER BY userID");

		String previousUser = null;
		String currentUser = null;
		while(rs.next() && rs1.next())
		{
		    currentUser = rs.getString("userID");
		    if (previousUser == null || !previousUser.equals(currentUser))
		    {
				System.out.println();
		        System.out.println(currentUser+" "+rs.getString("firstName")+" "+rs.getString("lastName"));
			}

			ResultSet rs2 = stmt2.executeQuery("SELECT * FROM Stocks ORDER BY symbol");

			while(rs2.next())
			{
		       if (previousUser == null || !previousUser.equals(currentUser))
		       {
				   try
			   	   {
			   		    System.out.print("\t"+rs2.getString("symbol")+" "+rs2.getString("name"));
			   		    System.out.println();
			   	   }
			   	   catch(SQLException ex)
			   	   {
			   		    throw new SQLException("Getting stock symbol from Stocks Table failed "
			                              +ex.getMessage());
			   	   }
			   }
		   }

		} // end of loop thru UserStocks

	}
}

[masijade]

A better query would have been

SELECT a.userID as userID,  a.firstName as firstName, a.lastName as lastName, c.symbol as symbol, c.name as name
FROM Users a, UserStocks b, Stocks c
Where a.UserID = b.UserID
  And b.stockID = c.stockID  // or whatever the "ID Field" is here
ORDER BY userID, symbol

Learn to use SQL for what it's meant for.

http://www.w3schools.com/sql/