Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Re: Two of three SQL stmts execute: third results in table does notexist exception

Reply
Thread Tools

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

 
 
Lew
Guest
Posts: n/a
 
      03-17-2007
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 <(E-Mail Removed)-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

 
Reply With Quote
 
 
 
 
GGP
Guest
Posts: n/a
 
      03-18-2007
On Mar 16, 10:50 pm, Lew <(E-Mail Removed)> 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 <(E-Mail Removed)-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).

 
Reply With Quote
 
 
 
 
David Harper
Guest
Posts: n/a
 
      03-18-2007
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
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      03-18-2007
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
 
Reply With Quote
 
David Harper
Guest
Posts: n/a
 
      03-18-2007
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
 
Reply With Quote
 
GGP
Guest
Posts: n/a
 
      03-19-2007
On Mar 18, 2:02 pm, David Harper <(E-Mail Removed)-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.

 
Reply With Quote
 
David Harper
Guest
Posts: n/a
 
      03-19-2007
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Three Mobile --> Skype on three (Non-three [Symbian - Nokia] handsets) Harry Stottle UK VOIP 0 01-05-2010 08:59 AM
Two of three SQL stmts execute: third results in table does not exist exception GGP Java 2 03-17-2007 02:39 AM
PSL stmts in VHDL: how to describe asynchronous dependencies? Eric DELAGE VHDL 2 04-06-2005 05:15 AM
PSL stmts embedded in VHDL: how to do functional coverage w/it? Eric DELAGE VHDL 1 04-05-2005 04:29 PM
Newbie question about old include stmts Bob ASP .Net 1 07-22-2003 05:30 PM



Advertisments