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

Discussion in 'Java' started by Lew, Mar 17, 2007.

  1. Lew

    Lew Guest

    From comp.lang.java.databases:

    GGP wrote:
    >> java.sql.SQLSyntaxErrorException: Table/View 'TABLENAME' does not
    >> exist.


    Lew wrote:
    > How come the error doesn't say "Table/View 'MASSCF' does not exist."?


    David Harper <-net.com> wrote:
    >> However, I'm guessing that somewhere in your code, you have set
    >>
    >> String tableName = "TABLENAME";
    >>
    >> Perhaps you intended to set the correct value elsewhere in your code,
    >> but you forgot to do so. Since we can't see the rest of your class
    >> definition, it's impossible to say for sure.


    Lew wrote:
    > David's points remain unaddressed.


    And the folks in the other groups to whom you multiposted deserve to know
    about it.

    f-u set to comp.lang.java.help

    -- Lew
    Lew, Mar 17, 2007
    #1
    1. Advertising

  2. Lew

    GGP Guest

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

    On Mar 16, 10:50 pm, Lew <> wrote:
    > From comp.lang.java.databases:
    >
    > GGP wrote:
    > >> java.sql.SQLSyntaxErrorException: Table/View 'TABLENAME' does not
    > >> exist.

    > Lew wrote:
    > > How come the error doesn't say "Table/View 'MASSCF' does not exist."?

    > David Harper <-net.com> wrote:
    > >> However, I'm guessing that somewhere in your code, you have set

    >
    > >> String tableName = "TABLENAME";

    >
    > >> Perhaps you intended to set the correct value elsewhere in your code,
    > >> but you forgot to do so. Since we can't see the rest of your class
    > >> definition, it's impossible to say for sure.

    > Lew wrote:
    > > David's points remain unaddressed.

    >
    > And the folks in the other groups to whom you multiposted deserve to know
    > about it.
    >
    > f-u set to comp.lang.java.help
    >
    > -- Lew


    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).
    GGP, Mar 18, 2007
    #2
    1. Advertising

  3. Lew

    David Harper Guest

    GGP wrote:
    [SNIP]
    > 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.


    Can I suggest that you write a simple test class which reads an SQL
    statement from System.in and tries to execute it. That would allow you
    to test whether the table name is case-sensitive (which I still think is
    a possible explanation) or whether you need to prefix the table name
    with the schema or put quotes around all of the identifiers.

    You'd be surprised how often a simple test class can help you track down
    the real cause of the problem inside a more complex application :)

    > 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.


    You might want to look at the java.util.prefs package or even
    java.util.Properties if you simply need to be able to store and retrieve
    values keyed by name.

    David Harper
    Cambridge, England
    David Harper, Mar 18, 2007
    #3
  4. Lew

    Lew Guest

    GGP wrote:
    > [SNIP]
    >> 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.


    David Harper wrote:
    > Can I suggest that you write a simple test class which reads an SQL
    > statement from System.in and tries to execute it. That would allow you
    > to test whether the table name is case-sensitive (which I still think is
    > a possible explanation) or whether you need to prefix the table name
    > with the schema or put quotes around all of the identifiers.


    I wonder about the schema prefix also. I noticed that the table is
    "DistanceCF" in the generated query and "GREGP.DistanceCF" in the reference query.

    -- Lew
    Lew, Mar 18, 2007
    #4
  5. Lew

    David Harper Guest

    Lew wrote:
    > GGP wrote:

    [SNIP]
    > I wonder about the schema prefix also. I noticed that the table is
    > "DistanceCF" in the generated query and "GREGP.DistanceCF" in the
    > reference query.


    Some database systems require the schema prefix (e.g. Oracle) and others
    don't (e.g. MySQL). I don't know what Apache Derby expects, though
    Greg's code

    > ResultSet rs = stmt.executeQuery("select CONTYPEID,
    > CONTYPENAME from CONTYPES where CONTYPEID = " + conversionTypeID);


    omits the prefix, and apparently works just fine, since he tells us that
    this query yields the table name from which he constructs the query
    which mysteriously fails.

    Curioser and curioser, said Alice.

    David Harper
    Cambridge, England
    David Harper, Mar 18, 2007
    #5
  6. Lew

    GGP Guest

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

    On Mar 18, 2:02 pm, David Harper <-net.com> wrote:
    > Lew wrote:
    > > GGP wrote:

    > [SNIP]
    > > I wonder about the schema prefix also. I noticed that the table is
    > > "DistanceCF" in the generated query and "GREGP.DistanceCF" in the
    > > reference query.

    >
    > Some database systems require the schema prefix (e.g. Oracle) and others
    > don't (e.g. MySQL). I don't know what Apache Derby expects, though
    > Greg's code
    >
    > > ResultSet rs = stmt.executeQuery("select CONTYPEID,
    > > CONTYPENAME from CONTYPES where CONTYPEID = " + conversionTypeID);

    >
    > omits the prefix, and apparently works just fine, since he tells us that
    > this query yields the table name from which he constructs the query
    > which mysteriously fails.
    >
    > Curioser and curioser, said Alice.
    >
    > David Harper
    > Cambridge, England


    Hi everyone,

    Problem solved! It turns out that David was right all along--it had
    everything to do with variable case. Because I was extracting the
    basic root of my concatenated variables from the first query from a
    table where the information was stored in a mixed-case format (e.g.,
    Mass, Distance, etc.), and that the table names I was trying to access
    were also mixed case (e.g., MassCF, DistanceCF, etc.), the second
    query kept failing. The first query worked because I created it using
    an SQL statement (i.e., CREATE TABLE ...), resulting in both the table
    name and the variable names automatically generated in an upper-case
    format. I constructed other tables 'by hand,' and therefore, both
    variable names and table names were mixed-case.

    Taking David's advice, I wrote a simple test class to try all sorts of
    different permutations on my sql string. It wasn't until I converted
    tableName.toUpperCase() that the table name issue went away, and I was
    suddenly faced with a variable name issue (same problem). I
    reconstructed all the tables using an upper-case format for naming the
    tables and variables (in addition to converting the result set from
    the initial query .toUpperCase()), and everything now works just fine.

    I would like to thank everyone who helped with this problem. I'm new
    to Java and on a very steep learning curve at the moment. This
    exercise had me toying with data/file streams (yes, I can almost read
    an Excel file now, but ran into exactly the same problem as I did
    reading my database tables--at least now I know how to fix it),
    writing small (and very valuable) test classes, and doing all sorts of
    things I wouldn't otherwise be doing if it wasn't for your expert
    advice. So, thank you--and I hope I can repay the favour sometime
    down the road!

    Cheers,

    Greg.
    GGP, Mar 19, 2007
    #6
  7. Lew

    David Harper Guest

    GGP wrote:
    > Problem solved!


    Hooray! Drinks for everyone! ;-)

    > It turns out that David was right all along--it had everything to do
    > with variable case.

    ....
    > I'm new to Java and on a very steep learning curve at the moment.


    Don't be too hard on yourself. I've been programming for 25 years,
    which means that I've made most of the usual mistakes already, several
    times over, so it's easier for me to recognise them :)

    Keep at it.

    David Harper
    Cambridge, England
    David Harper, Mar 19, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Eric DELAGE
    Replies:
    2
    Views:
    679
  2. Eric DELAGE
    Replies:
    1
    Views:
    846
    Jonathan Bromley
    Apr 5, 2005
  3. Bob
    Replies:
    1
    Views:
    290
    Steve C. Orr, MCSD
    Jul 22, 2003
  4. Jack Frost

    using inline stmts vs Page_Load event

    Jack Frost, Oct 31, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    412
    Kevin Spencer
    Nov 3, 2003
  5. GGP
    Replies:
    2
    Views:
    460
    Paul Tomblin
    Mar 17, 2007
Loading...

Share This Page