Multiple Connections to DB
Mike Monkiewicz
believe these are being used in embedded mode, as it is impossible to
use H2 Console once the application has established a connection to
the db. Our professional services guys will need to peek into our
DBs, so I've been searching for a solution.
After reading the Tutorial, it seems switching from embedded to server/
mixed mode would do the trick. However, I'm not sure where to change
that with our setup. Our Java app uses JDBC, with Spring + Hibernate
as a buffer between our application code and the database, and
Glassfish for our server. Shoot, it even creates the db for us on
first use.
Spring XML:
<bean id="myDatasource"
class="org.springframework.jndi.JndiObjectFactoryBean"
destroy-method="close">
<property name="jndiName" value="jdbc/myDatasourceTX"/>
</bean>
<bean id="hibernateSessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="myDatasource"/>
<property name="configLocation"
value="classpath:hibernate.cfg.xml"/>
<property name="schemaUpdate" value="true"/>
<property name="mappingResources">
<list>
<value>hibernate.cfg.xml</value>
</list>
</property>
</bean>
Hibernate XML:
<hibernate-configuration>
<session-factory name="myFactory">
<property
name="hibernate.dialect">org.hibernate.dialect.H2Dialect</property>
<mapping class="blahblahblahblah.....
</session-factory>
</hibernate-configuration>
Datasource XML:
<resources>
<jdbc-connection-pool
name="myServiceH2Pool"
datasource-classname="org.h2.jdbcx.JdbcDataSource"
res-type="javax.sql.DataSource">
<property name="user" value="sa"/>
<property name="password" value="note"/>
<property name="url" value="jdbc:h2:file:$PATH"/>
</jdbc-connection-pool>
<jdbc-resource
enabled="true"
jndi-name="jdbc/myDatasourceTX"
object-type="user"
pool-name="myServiceH2Pool"/>
</resources>
Do I need to manually invoke the org.h2.tools.Server, or can I set
that somewhere in the XML? Any help would be greatly appreciated.
kefa
I believe there is a new connection mode that will create and manage
the server for you. But I'm not sure how to do that. So I second your
question: How do we allow multiple connections to embedded database?
Stefan
Mike Monkiewicz
Web.xml relevant code:
<context-param>
<param-name>db.url</param-name>
<param-value>jdbc:h2:file:$PATH</param-value>
</context-param>
<context-param>
<param-name>db.user</param-name>
<param-value>$USER</param-value>
</context-param>
<context-param>
<param-name>db.password</param-name>
<param-value>$PASSWD</param-value>
</context-param>
<context-param>
<param-name>db.tcpServer</param-name>
<param-value>-tcpAllowOthers</param-value>
</context-param>
<listener>
<listener-class>org.h2.server.web.DbStarter</listener-class>
</listener>
It didn't work, as the H2 db directory still contained a lock file
after the server established a connection. Still hoping someone can
point me in the right direction.
Thomas Mueller
Of course you could just use the server mode.
> I believe there is a new connection mode that will create and manage
> the server for you. But I'm not sure how to do that. So I second your
> question: How do we allow multiple connections to embedded database?
Yes there is a new (experimental) mode that automatically starts a
server when opening the database. To use it, append ;AUTO_SERVER=TRUE
to the database URL. You need H2 version 1.1.100 for this feature.
Example:
jdbc:h2:/data/dbs/test;AUTO_SERVER=TRUE
The first connection will open the database in embedded mode and will
start a server. Later connections will automatically use the server
mode. If the first connection is closed the later connections should
re-connect automatically.
You could try out if that works for you. As I said it's a new feature,
please tell me if there are some problems with that.
Regards,
Thomas
Mike Monkiewicz
AUTO_SERVER worked very well, and we'll be using that solution.
I also found a workaround for pre 1.1.100. Using the web.xml to start
the H2 server with DbStarter did work, I just needed to change my H2
console JDBC URL to use tcp instead of file format. However the
workaround was still undesirable, as a support tech would need to use
the embedded mode when the H2 server was down, and tcp/server
connection when it was up. So I'm mighty thankful for AUTO_SERVER.
As a heads up, it needs to be added to the JDBC URL documentation. =)
Thomas Mueller
> AUTO_SERVER.
> As a heads up, it needs to be added to the JDBC URL documentation. =)
Sure, it will be documented in the next release. However it is still
quite new code, so there could always be some surprises.
Regards,
Thomas
golgoth14
I'm using H2 database on Glassfish.
I've configured a connection pool with the following properties :
<jdbc-connection-pool is-connection-validation-required="true"
datasource-classname="org.h2.jdbcx.JdbcDataSource" connection-
validation-method="meta-data" steady-pool-size="5" res-
type="javax.sql.ConnectionPoolDataSource" description="slenTicket-1.2-
SNAPSHOT JDBC Connection Pool" max-pool-size="50" name="slenTicket-1.2-
SNAPSHOTPool">
<property value="jdbc:h2:./db/slenticketdb;auto_server=true"
name="URL" />
<property value="admin" name="user" />
<property value="password" name="password" />
</jdbc-connection-pool>
When the domain starts, I can't connect to the database with an
external process like SQuirrel SQL Client ?
How to allow other process to connect to a server started database ?
How to set the tcp port ?
Thank you,
Steve
On Oct 8, 8:46 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> I'm using H2 database on Glassfish.
> I've configured a connection pool with the following properties...
> <property value="jdbc:h2:./db/slenticketdb;auto_server=true"
> When the domain starts, I can't connect to the database with an
> external process like SQuirrel SQL Client ?
If you get an exception, could you post it please? It should work when
using the exact same URL as above
jdbc:h2:./db/slenticketdb;auto_server=true
however it's sometimes better to use an absolute path, or a path
relative to the user home directory (jdbc:h2:~/...). Otherwise you
could end up with two databases if the current working directory is
different in the two applications.
> How to allow other process to connect to a server started database ?
It's allowed automatically when using ;auto_server=true (even remote
access is allowed - but there is additional security using a randomly
generated key that is stored in the .lock.db file in addition to the
user password protection).
> How to set the tcp port ?
You don't need to set it, the port is picked automatically and saved
in the .lock.db file.
Regards,
Thomas
golgoth14
"jdbc:h2:tcp://localhost:9092/data/servers/glassfish/domains/myApp/
config/db"
The stacktrace is :
org.h2.jdbc.JdbcSQLException: Connection is broken [90067-100]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.message.Message.getSQLException(Message.java:149)
at org.h2.engine.SessionRemote.checkClosed(SessionRemote.java:434)
at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:322)
at org.h2.engine.SessionRemote.connectEmbeddedOrServer
(SessionRemote.java:227)
at org.h2.engine.SessionRemote.createSession(SessionRemote.java:221)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:87)
at org.h2.Driver.connect(Driver.java:57)
at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection
(SQLDriverManager.java:133)
at
net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.execute
(OpenConnectionCommand.java:97)
at
net.sourceforge.squirrel_sql.client.mainframe.action.ConnectToAliasCommand
$SheetHandler.run(ConnectToAliasCommand.java:279)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run
(TaskExecuter.java:82)
at java.lang.Thread.run(Thread.java:613)
Is important to have the possiblity to choose the port when an
external tool must be connected
to the database via the server.
Thanks
On Nov 25, 8:43 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
Thomas Mueller
> The stacktrace is :
> org.h2.jdbc.JdbcSQLException: Connection is broken [90067-100]
> When I connect Squirrel SQL, I use the absolute path like
> "jdbc:h2:tcp://localhost:9092/data/servers/glassfish/domains/myApp/
> config/db"
If you use the server mode, you need to start a server. I'm not sure
if you did that. See:
http://www.h2database.com/html/features.html#connection_modes
http://www.h2database.com/html/features.html#database_url
Maybe it's easier to just always use auto_server=true. Then you don't
have to think about starting the server, and what port number to use.
Regards,
Thomas
golgoth14
The connection pool of the Glassfish domain use the mixed mode with
this url : jdbc:h2:./db/slenticketdb;auto_server=true
This means the server listen on a tcp port choosen by itself, right ?
The tcp port is stored into a .lock.db file like :
#FileLock
#Mon Dec 01 18:42:53 CET 2008
id=11df3a544e507109f9dbe0d9fbc5f3856e04e20ed14
method=file
server=10.37.129.2\:49578
When a client like SQuirrel SQL must be connected to the database
server,
it should use the remote tcp url like : jdbc:h2:tcp://
10.37.129.2:49578/slenticketdb
But the following exception occurs :
org.h2.jdbc.JdbcSQLException: Wrong user name or password [8004-100]
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.message.Message.getSQLException(Message.java:149)
479)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:94)
at java.lang.Thread.run(Thread.java:613)
at org.h2.engine.SessionRemote.done(SessionRemote.java:493)
at org.h2.engine.SessionRemote.initTransfer(SessionRemote.java:111)
at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:316)
(SessionRemote.java:227)
at org.h2.engine.SessionRemote.createSession(SessionRemote.java:221)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:87)
at org.h2.Driver.connect(Driver.java:57)
at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection
(SQLDriverManager.java:133)
at
net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.execute
(OpenConnectionCommand.java:97)
at
net.sourceforge.squirrel_sql.client.mainframe.action.ConnectToAliasCommand
$SheetHandler.run(ConnectToAliasCommand.java:279)
at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run
(TaskExecuter.java:82)
at java.lang.Thread.run(Thread.java:613)
Do you see what I am trying to do ?
Thanks,
Steve
Thomas Mueller
> The connection pool of the Glassfish domain use the mixed mode with
> this url : jdbc:h2:./db/slenticketdb;auto_server=true
>
> This means the server listen on a tcp port choosen by itself, right ?
Yes.
> When a client like SQuirrel SQL must be connected to the database
> server,
> it should use the remote tcp url like : jdbc:h2:tcp://
> 10.37.129.2:49578/slenticketdb
No. Just use the same URL as above: jdbc:h2:./db/slenticketdb;auto_server=true
Regards,
Thomas
Thomas Mueller
>> The connection pool of the Glassfish domain use the mixed mode with
>> this url : jdbc:h2:./db/slenticketdb;auto_server=true
Actually there is a problem. You are using a relative path:
./db/...
That's a bit dangerous as it depends on the current working directory,
which is probably different depending on the application.
Better is an absolute path:
jdbc:h2:/data/db/slenticketdb;auto_server=true
I already wrote that before, but it's probably better to repeat it.
Regards,
Thomas