Hibernate: table/view does not exist

[Mathias Conradt <ma...@gmail.com>]

I try to exchange postgres with derby/javadb 10.3.1.4 and created a derby
database using ij (I'm new to derby).
I use CONNECT 'jdbc:derby:realty;'; for the connection in ij
and after the creation I can do selects on the table using ij.

Now I have problems with the configuration of Hibernate3 - it cannot find
the table.

My data source is:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">    
  <property
name="driverClass"><value>org.apache.derby.jdbc.EmbeddedDriver</value></property>
  <property name="jdbcUrl"><value>jdbc:derby:realty;</value></property>
  <property name="user"><value></value></property>
  <property name="password"><value>password</value></property>
</bean>

Hibernate gets the DB connection, but cannot find the table. I get 
java.sql.SQLException: Table/View 'USERDATA' does not exist.

I also tried to create a table in the APP schema using the SQuirrel SQL GUI,
and then changing the hibernate config to "jdbc:derby:realty;user=APP;", and
also tried setting the <user> to 'APP', but still it doesn't make any
difference.

Also for some reason which I haven't figured out yet, when I make a "select
* from app.userdata" via ij, it seems it accessess a different table than
when I do the same query from SQuirrel SQL. Although (I think) I am
accessing the table 'userdata' in the 'app' schema. Am I not doing so
automatically by using the APP user or 'app.'-prefix in the sql query?

Thanks for any advice.
-- 
View this message in context: http://www.nabble.com/Hibernate%3A-table-view-does-not-exist-tf4521125.html#a12897425
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Hibernate: table/view does not exist

[Mathias Conradt <ma...@gmail.com>]

Thanks, got it working now.


oysteing wrote:
> 
> 2. Explicitly state the location of your database when connecting:
>     URL: jdbc:derby:C:/server/javadb10.3.1.4/bin/realty
> 

-- 
View this message in context: http://www.nabble.com/Hibernate%3A-table-view-does-not-exist-tf4521125.html#a12925197
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Hibernate: table/view does not exist

[Øystein Grøvlen <Oy...@Sun.COM>]

Mathias Conradt wrote:
 > Thanks for the hint, the schema is app.
 >
 > It seems that when I use ij (EmbeddedMode) to connect to
 > jdbc:derby:realty;(create=true) and use hibernate, it 
creates/connects the
 > database in two different physical location. When I use ij, the database
 > folder is created under C:\server\javadb10.3.1.4\bin, but when I use
 > Hibernate, it must be somewhere else, at least I cannot find it under
 > C:\server\javadb10.3.1.4\bin.

Note that if you do not specify yourself where the database is to be
stored, it will be stored in current directory.  I am not a Windows
expert, but from your experience it seems like the current diretory
may be the directory where the executable is located.

You can take control of where the database is located in at least two
ways:

1. Set the property derby.system.home to the directory where you want
    your database.

2. Explicitly state the location of your database when connecting:

    URL: jdbc:derby:C:/server/javadb10.3.1.4/bin/realty

 > But now I found a solution, using the Server Mode instead of the Embedded
 > Mode, which somehow makes sure that I'm using the same (physical) 
database.
 > <property
 > 
name="driverClass"><value>org.apache.derby.jdbc.ClientDriver</value></property>
 > <property
 > 
name="jdbcUrl"><value>jdbc:derby://localhost:1527/realty;create=true</value></property>
 >

The server mode works because the default directory when starting the
server will be used regardless of which client is connecting to it.
However, there should be no need to use the client/server mode if you
do not need to support concurrent connections from multiple clients.

Hope this helps,

Øystein

Re: Hibernate: table/view does not exist

[Mathias Conradt <ma...@gmail.com>]

Thanks for the hint, the schema is app. 

It seems that when I use ij (EmbeddedMode) to connect to
jdbc:derby:realty;(create=true) and use hibernate, it creates/connects the
database in two different physical location. When I use ij, the database
folder is created under C:\server\javadb10.3.1.4\bin, but when I use
Hibernate, it must be somewhere else, at least I cannot find it under
C:\server\javadb10.3.1.4\bin.

But now I found a solution, using the Server Mode instead of the Embedded
Mode, which somehow makes sure that I'm using the same (physical) database.
<property
name="driverClass"><value>org.apache.derby.jdbc.ClientDriver</value></property>
<property
name="jdbcUrl"><value>jdbc:derby://localhost:1527/realty;create=true</value></property>


Dyre.Tjeldvoll wrote:
> 
> 
> select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s
> where t.tablename = 'USERDATA' and t.schemaid = s.schemaid;
> 
> This should also tell you if you have multiple copies of USERDATA in
> different schemas. 
> 
> 

-- 
View this message in context: http://www.nabble.com/Hibernate%3A-table-view-does-not-exist-tf4521125.html#a12913106
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Hibernate: table/view does not exist

[Dy...@Sun.COM]

Mathias Conradt <ma...@gmail.com> writes:

> I try to exchange postgres with derby/javadb 10.3.1.4 and created a derby
> database using ij (I'm new to derby).
> I use CONNECT 'jdbc:derby:realty;'; for the connection in ij
> and after the creation I can do selects on the table using ij.

The username you used (in ij) will determine which schema the table
ends up in. If you didn't specify a username it should end up in APP.

I don't know anything about Hibernate, but when you are in ij you
could run the following query to figure out where your table actually
has ended up:

select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s where t.tablename = 'USERDATA' and t.schemaid = s.schemaid;

This should also tell you if you have multiple copies of USERDATA in
different schemas. 

You don't have to rely on the default schema for the user you connect
as. You can create your own schema (CREATE SCHEMA ...) and explicitly
set that as you default schema (SET SCHEMA ...)

-- 
dt