CODEWITHSUNDEEP
jdbchsqldb
Archan Mishra
Archan Mishra

Reputation: 905

Seting Schema for HSQLDB in JDBC connection

How does one set the schema for HSQLDB in the JDBC URL itself.

Kinda how you can do in MySQL with jdbc:mysql://localhost:3306/

-EDIT-

My current DB url looks like this

jdbc:hsqldb:file:C:\\hsqldb\\dbname

Also inside the dbanme there is a schema newSchema, which can be set using set schema newSchema, how do i do this using the jdbc URL itself.

Upvotes: 5

Views: 4902

Answers (3)

will
will

Reputation: 5061

UPDATE:

I have been doing some more testing. The suggested solution appears to be ineffective.

For example; if I set the schema to Test I still may not use SQL on tables in that schema. Essentially that extra stuff I put in there has no effect, even though I thought it was OK. 

   select * from BOOKMARKS;
   SEVERE  SQL Error at '<stdin>' line 5:
   "select * from BOOKMARKS"
   user lacks privilege or object not found: BOOKMARKS

SquirrelSQL has an option for an initial schema, so queries there work OK. The sqlTool accepts the URI but doesn't change/select the schema. It is slightly frustrating that I can't see HOW to report the current schema name in use btw.

So this answer doesn't solve the problem; I'm leaving it here because it will save the next person time when they find a good answer to the challenge.

  • back to the drawing board ...

original suggestion ...

I'd like to do this too. For example with mysql the example is:

mysql -h hostname:port schema

The port number, being one port per database. Now in HSQLDB, the URL is one database within a Catalog (that's how they spell it). Here's the solution I came-up with. I have ...

  • databsee: dev
  • schemas
    • public
    • test

In my RC file, ~/.config/sqltool.rc ... 

  urlid dev_test
  url jdbc:hsqldb:hsql://localhost/dev;set schema test;
  username programmer
  password secret

I can use the connection ID dev_test to link to the test schema, the normal default is public. And you can run the sql tool with:

  java -jar  /usr/lib/hsqldb/lib/sqltool.jar   --rcFile=~/.config/sqltool.rc  dev_tmp

That approach worked with a Groovy Sql connection:

url:  'jdbc:hsqldb:hsql://localhost/dev;set schema test',
 ...

Worked just how I'd want it. So what I can do is append the schema I want to that URL connection string string. It may no be the solution but it will do what you describe.

Looking at the connection protocol: there is NO option for a schema nor for a catalog. So catalog will be part of your URL, eg. if you use a file base database, that is one catalog.

Upvotes: 0

Shashikant Soni
Shashikant Soni

Reputation: 191

have you already tried the standard protocol? "jdbc:hsqldb:hsql://[HOST]:[PORT]/myDBName" How are you trying to connect? through a standalone app or by creating a resource? You might have seen this but still check here if not done: http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_connection_url

Upvotes: 1

sachi
sachi

Reputation: 2132

I think need to specify the port number like as shown //192.0.0.10:9500

Upvotes: 0

Related Questions