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

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

  1. GGP

    GGP Guest

    I attempt to collect data from a database three times in one method,
    all using one connection. The first two queries execute without any
    problem. However, the third fires an sql exception:

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

    I connect to the database using a connection class, as follows:

    public class MyDBConnection {

    public static final String DRIVER_NAME =
    "org.apache.derby.jdbc.ClientDriver";
    public static final String DATABASE_URL = "jdbc:derby://localhost:
    1527/cfdb";
    public Connection myConnection;

    public MyDBConnection() {}

    public void init() {
    Class.forName(DRIVER_NAME);
    myConnection =
    DriverManager.getConnection(DATABASE_URL,"usnm","pswd");
    }
    .... [getters and setters deleted]

    I then query the database from within a UI constructor like this:

    public class MainUCInterface extends javax.swing.JFrame {

    MyDBConnection myConnection = new MyDBConnection();
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    public MainUCInterface() throws SQLException {
    initComponents();

    myConnection.init();
    con = myConnection.getMyConnection();

    stmt = con.createStatement();
    rs = stmt.executeQuery("select CONTYPEID, CONTYPENAME from
    CONTYPES order by CONTYPENAME"); //This query executes fine

    Statement stmt2 = null;
    ResultSet rs2 = null;
    stmt2 = con.createStatement();
    rs2 = stmt2.executeQuery("select CONTYPEID, CONTYPENAME from
    CONTYPES where CONTYPEID = " + iConvTyp); //iConvTyp is an integer,
    and this query also executes fine.

    ResultSet rs3 = null;
    Statement stmt3 = null;
    stmt3 = con.createStatement();

    String tableName = strConvTyp + "CF";
    String unitName = strConvTyp + "Name";
    String unitID = strConvTyp + "ID";

    String sqlListData = "select " + unitID + ", " + unitName + "
    from " + tableName;
    rs3 = stmt3.executeQuery(sqlListData); //This is where the
    error occurs. All the strings in stmt3 are fine (correct spelling,
    etc.)

    A couple of additional notes:

    1. The first two queries are on the same table. I tried
    reconstructing the third table, and I just finished rebuilding the
    whole database from scratch--it didn't help.

    2. There is no relational structure to the database. It's just a
    group of tables that hold data I need to retrieve to provide
    functionality to the app. I don't know if this is a relevant point or
    not.

    3. Metadata on the database indicates that there are no tables present
    (which would account for the error, but not the successful queries).
    I'm not sure why con.getMetaData isn't seeing my tables.

    Any ideas? I really appreciate the help.

    Greg.
    GGP, Mar 17, 2007
    #1
    1. Advertising

  2. GGP

    Daniel Pitts Guest

    On Mar 16, 5:47 pm, "GGP" <> wrote:
    > I attempt to collect data from a database three times in one method,
    > all using one connection. The first two queries execute without any
    > problem. However, the third fires an sql exception:
    >
    > java.sql.SQLSyntaxErrorException: Table/View 'TABLENAME' does not
    > exist.
    >
    > I connect to the database using a connection class, as follows:
    >
    > public class MyDBConnection {
    >
    > public static final String DRIVER_NAME =
    > "org.apache.derby.jdbc.ClientDriver";
    > public static final String DATABASE_URL = "jdbc:derby://localhost:
    > 1527/cfdb";
    > public Connection myConnection;
    >
    > public MyDBConnection() {}
    >
    > public void init() {
    > Class.forName(DRIVER_NAME);
    > myConnection =
    > DriverManager.getConnection(DATABASE_URL,"usnm","pswd");
    > }
    > ... [getters and setters deleted]
    >
    > I then query the database from within a UI constructor like this:
    >
    > public class MainUCInterface extends javax.swing.JFrame {
    >
    > MyDBConnection myConnection = new MyDBConnection();
    > Connection con = null;
    > Statement stmt = null;
    > ResultSet rs = null;
    >
    > public MainUCInterface() throws SQLException {
    > initComponents();
    >
    > myConnection.init();
    > con = myConnection.getMyConnection();
    >
    > stmt = con.createStatement();
    > rs = stmt.executeQuery("select CONTYPEID, CONTYPENAME from
    > CONTYPES order by CONTYPENAME"); //This query executes fine
    >
    > Statement stmt2 = null;
    > ResultSet rs2 = null;
    > stmt2 = con.createStatement();
    > rs2 = stmt2.executeQuery("select CONTYPEID, CONTYPENAME from
    > CONTYPES where CONTYPEID = " + iConvTyp); //iConvTyp is an integer,
    > and this query also executes fine.
    >
    > ResultSet rs3 = null;
    > Statement stmt3 = null;
    > stmt3 = con.createStatement();
    >
    > String tableName = strConvTyp + "CF";
    > String unitName = strConvTyp + "Name";
    > String unitID = strConvTyp + "ID";
    >
    > String sqlListData = "select " + unitID + ", " + unitName + "
    > from " + tableName;
    > rs3 = stmt3.executeQuery(sqlListData); //This is where the
    > error occurs. All the strings in stmt3 are fine (correct spelling,
    > etc.)
    >

    My guess is that strConType + "CF" isn't producing a valid table
    name... Have you verified that by debugging?

    > A couple of additional notes:
    >
    > 1. The first two queries are on the same table. I tried
    > reconstructing the third table, and I just finished rebuilding the
    > whole database from scratch--it didn't help.
    >
    > 2. There is no relational structure to the database. It's just a
    > group of tables that hold data I need to retrieve to provide
    > functionality to the app. I don't know if this is a relevant point or
    > not.

    Its not necessarily relevant to your question, but it may explain the
    difficulty you have working it...
    It appears to me you have the following concepts:
    you have a "Con" (what ever that is), which is an abstract concept...
    You are expecting all "Cons" to have a type, and a name.
    Also, Cons have an associated "Unit". Its not clear from your code
    what kind of relationship (1->1, many->1, many->many) that Units have
    to Con...
    In any case, I might suggest altering your schema a little bit to
    include relational data... That IS the main purpose of a database (if
    not, use files for god sakes).

    Table Contype should have a primary key, contypeId (maybe is the
    primary key), and contypeName
    Table Unit should have a primary key (possible unitId), a conTypeId
    (may be a foreign key to Contype, might not be), and Name.

    you're query could then be simplified: SELECT Contype.*, Unit.* FROM
    Contype, Unit WHERE Unit.conTypeId = Contype.conTypeId.

    >From that, you should be able to get all the information you need.


    Hope this helps,
    Daniel.
    Daniel Pitts, Mar 17, 2007
    #2
    1. Advertising

  3. GGP

    Paul Tomblin Guest

    In a previous article, "GGP" <> said:
    > String sqlListData = "select " + unitID + ", " + unitName + "
    >from " + tableName;
    > rs3 = stmt3.executeQuery(sqlListData); //This is where the
    >error occurs. All the strings in stmt3 are fine (correct spelling,
    >etc.)


    Print out the sqlListData string, and paste it into an interactive SQL
    client to verify that it's correct. 99% of the time, you've forgotten a
    space or a comma or something.


    --
    Paul Tomblin <> http://blog.xcski.com/
    Do you have a point, or are you saving it for a special occasion?
    -- David P. Murphy
    Paul Tomblin, Mar 17, 2007
    #3
    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:
    678
  2. Eric DELAGE
    Replies:
    1
    Views:
    844
    Jonathan Bromley
    Apr 5, 2005
  3. Geoff
    Replies:
    3
    Views:
    440
    Patrick.O.Ige
    Jan 16, 2006
  4. LT
    Replies:
    7
    Views:
    2,089
    Phlip
    Jul 25, 2004
  5. Lew
    Replies:
    6
    Views:
    405
    David Harper
    Mar 19, 2007
Loading...

Share This Page