Re: Two of three SQL stmts execute: third results in table does not exist exception
On Mar 16, 5:47 pm, "GGP" <chaobo...@yahoo.ca> 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.