Re: Two of three SQL stmts execute: third results in table does not exist exception

On Mar 16, 10:50 pm, Lew <l...@nospam.lewscanon.com> wrote:


Thanks, Lew. You're right, I should have kept everyone up to speed
(I'm quite new to this, so please forgive me). So, since I've asked a
confusing question, I'll try to clarify here. The code has changed a
little since I originally asked the question, so I've supplied the
entire class definition below (warts and all).

public class ListHandler {

    int iConvTyp;

    /** Creates a new instance of ListHandler */
    public ListHandler(int conversionTypeSelectedID) {
        int iConvTyp = conversionTypeSelectedID;
    }

    public Vector lstModelVector (WindowEvent evt, int
conversionTypeSelected) throws SQLException {
        int x = 0;
        setIConvTyp(conversionTypeSelected);

        //Open a new connection to the ConversionFactors database
        MyDBConnection myConnection = new MyDBConnection();
        myConnection.init();
        Connection con = myConnection.getMyConnection();

        //Prepare a new SQL statement, result set, and string
variables to hold the SQL data
        String strConvTypID = null;
        String strConvTyp = null;

        //Create and execute an SQL statement to determine what kind
of conversion is initially set by the combobox.
        //Ultimately, this determines which units need to be displayed
in each listbox.
        Statement stmt = con.createStatement();

        //The first SQL stmt is to find out which conversion table to
use (e.g., mass, area, distance, etc.).
        int conversionTypeID = getIConvTyp();
        ResultSet rs = stmt.executeQuery("select CONTYPEID,
CONTYPENAME from CONTYPES where CONTYPEID = " + conversionTypeID);

        //Read the data from the results of the SQL query.
        while (rs.next()) {
            strConvTypID = rs.getString("CONTYPEID");
            strConvTyp = rs.getString("CONTYPENAME");
        }

        //Declare some utility variables that hold the appropriate
table name, conversion-type name, and the
        //conversion-type ID (index).
        String tableName = strConvTyp + "CF";
        String unitName = strConvTyp + "Name";
        String unitID = strConvTyp + "ID";

        String sqlListData = "select " + unitID + ", " + unitName + "
from " + tableName; //This is where the problem occurs
        System.out.println(sqlListData);

        //Create and execute the SQL statement
        Statement stmtList = con.createStatement();
        ResultSet rsList = stmtList.executeQuery(sqlListData);

        //Advance cursor one position so it points to the appropriate
data (it starts just before the first record)
        rsList.next();
        int dID = rsList.getInt(unitID);
        String distID = rsList.getString(unitName);

        //Define a vector to hold the list data
        String strUName = null;
        int intUIndx = 0;

        //Read the result set and populate the vector
        Vector vecList = new Vector();
        while (rsList.next()) {
            strUName = rsList.getString(unitName);
            intUIndx = rsList.getInt(unitID);
            vecList.addElement(makeObj(strUName));
        }

        //Close the database connection
        con.close();

        return vecList;
    }

There are now only two queries (not three, as in the original post),
but the problem remains the same. Someone suggested that I should
print out sqlListData and execute the statement directly. I did that,
which resulted in the same basic error.

The error generated was: java.sql.SQLSyntaxErrorException: Table/View
'DISTANCECF' does not exist.

The statement I used in an attempt to recreate the error (i.e.,
sqlListData) was "select DistanceID, DistanceName from DistanceCF".
However, when I query the table directly using the following syntax,
the data I require are generated properly: select "DistanceID",
"DistanceName" from "GREGP"."DistanceCF"

I really don't understand why this particular sql statement requires
such a different syntax from the first. Moreover, I don't know how to
generate that sort of syntax (with embedded quotation marks) to define
sqlListData.

Finally, someone else mentioned that it seemed like a wasted database
given that I have no relational structure, and I'm just using the
database tables as means to hold data ("just use files for god
sakes"). I agree completely! The data need to be updated (i.e., new
rows added), edited, and deleted. I wish I knew how to do that with
files (I do with database tables)! It definitely would be a better
option.

Thanks to everyone who has responded.

Sincerely,

Greg.

P.S. I'm sorry I don't remember who asked, but 'con' is just my
connection variable (see code above).