This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes  sqlexception with hsqldb-please help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark " sqlexception with hsqldb-please help" Watch " sqlexception with hsqldb-please help" New topic
Author

sqlexception with hsqldb-please help

Vijay Nivas
Greenhorn

Joined: Nov 01, 2007
Posts: 12
hello,

I am trying to use hsqldb in jboss-4.0.5.GA .I tried the following small jdbc code .


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

class JDBC_test
{
public static void main(String args[])
{
Connection conn=null;
Statement select=null;
try
{
Class.forName("org.hsqldb.jdbcDriver");
System.out.println("Driver loaded...");
}
catch(Exception e)
{
System.out.println("Failed to load hsql driver.");
return;

}
try
{
conn = DriverManager.getConnection("jdbc:hsqldb:file:mydemo","sa","");
System.out.println("connected to hsql..");
select = conn.createStatement();
System.out.println("after create statement..");
}
catch(Exception e) {System.out.println("create statement failed");}
try
{

ResultSet result = select.executeQuery("select id,item from demo");
System.out.println("Got results:");
while (result.next())
{ // process results one row at a time
int key = result.getInt(1);
String val = result.getString(2);

System.out.println("key = " + key);
System.out.println("val = " + val);
}
} catch (Exception e) {
e.printStackTrace();}

finally
{
if(conn!=null)
{
System.out.println("conn is not null");
try { conn.close(); }
catch(Exception e){e.printStackTrace();}
}
}

}
}
I have a db named mydemo with table demo created with few columns in the stand alone mode.
If I try the same query in hsqldb it works fine.
But I am getting the following error while running thru this jdbc code

Driver loaded...
connected to hsql..
after create statement..
java.sql.SQLException: Table not found in statement [select id,item from demo]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
at JDBC_test.main(JDBC_test.java:36)
conn is not null

Can anyone please help me out with this error?

thanks and regards,
nivas.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

You are trying to select from a table demo.
This table does not exist.

If you are following the instructions from a tutorial, check if you didn't skip the preparation part where the table is created.

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Vijay Nivas
Greenhorn

Joined: Nov 01, 2007
Posts: 12
hello,
Thanks for your reply.
I have created a table demo with 2 columns id,item and there is one record.If I execute the same query in hsqldb analyser, it is showing the record.
but I dont understand why not thru this jdbc code...Is there anything else I should do?

thanks & regards,
nivas.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

Nivas,
Are you executing the query with the same user id from the command line and through the code?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Vijay Nivas
Greenhorn

Joined: Nov 01, 2007
Posts: 12
Hello,

Yes, I am executing the query with the same username in both.

thanks,
nivas.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

Originally posted by vijay nivas:
Hello,

Yes, I am executing the query with the same username in both.

thanks,
nivas.
Are you connecting to the mydemo schema in both situations?

Regards, Jan
Vijay Nivas
Greenhorn

Joined: Nov 01, 2007
Posts: 12
Hello,

Yes,I am connecting to the db,mydemo.I have attached the picture of my database screen for your reference:


thanks & regards,
nivas.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2504
    
    8

Vijay,

can you post the table's create statement?
This person has the same problem.

Regards, Jan

(to post an image on the ranch, you have to upload it to some internet site, and then link to it. Showing pictures that are located on your computer does not work.)
Vijay Nivas
Greenhorn

Joined: Nov 01, 2007
Posts: 12
Hello,

I am trying to know hsqldb with a simple table.I have given the details I found in the mydemo.script file.

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE DEMO(ID INTEGER,ITEM VARCHAR(10))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 20
SET SCHEMA PUBLIC
INSERT INTO DEMO VALUES(100,'car')
INSERT INTO DEMO VALUES(101,'shoes')
INSERT INTO DEMO VALUES(102,'laptop')

Sorry ..I didnt know that I cannot upload picture directly.
thanks & regards,
nivas.
v ray
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
You are running the database in a standalone mode, this means(from the hsqldb user guide):


The main drawback is that it is not possible by default to connect to the database from outside your application. As a result you cannot check the contents of the database with external tools such as Database Manager while your application is running.



My question is, is your database manager connected to the database while you are trying to execute this program?

Btw, I copied your program and ran it locally, the only change I did was I ran hsqldb in a server mode(instead of stand alone mode), and the program works fine for me, here's the code:

[ November 04, 2007: Message edited by: v ray ]
Vijay Nivas
Greenhorn

Joined: Nov 01, 2007
Posts: 12
Hello v ray,

Thanks a million ! I ran the database in server mode and I got satisfactory results.

Thanks for pointing out my mistake.

regards,
nivas.
Raj Chila
Ranch Hand

Joined: Mar 18, 2004
Posts: 128

Vijay,

First of all I think you were not making any mistake trying to access a hsql db in a ' stand alone ' mode, its another issue if it is a best practice or not depending on your over all system implementation.

My suggestion is, if you are doing this as a tutorial, then you should try it out and not think that you are doing a mistake by accessing it in a stand alone mode.

Remember that when you are accessing a hsqldb in a stand alone mode, you should actually provide the absolute path to the db file, for example if your database is in the following folder "C:\temp\hsqldb" and the database name is demodb, then your file db url should look like this..

"jdbc:hsqldb:file:C:\temp\hsqldb\demodb"

Try this out.
abhi jo
Greenhorn

Joined: Feb 25, 2013
Posts: 1
Hi All,

I am facing the below exception when i try to connect to the the hsqldb database. (i have used just simple java prog for this purpose)

When i run this query on the backend it gets executed very well.

java.sql.SQLException: Table not found in statement [ select * from "PUBLIC"."xxx"]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
at com.test.test.Test.main(Test.java:28)

I am using the
Driver as org.hsqldb.jdbcDriver
connection url as jdbc:hsqldb:mem://localhost/db_name", "sa", ""

Can somebody help me on that.

thanks in advance ,
Abhishek J
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sqlexception with hsqldb-please help