sqlexception with hsqldb-please help

[Vijay Nivas]

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]

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

[Vijay Nivas]

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]

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

[Vijay Nivas]

Hello,

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

thanks,
nivas.

[Jan Cumps]

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]

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]

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]

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]

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:

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
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:hsql://localhost/demo","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();}
}
}
 
}
}

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

[Vijay Nivas]

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]

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]

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