[Solved] How to set up 2 databases

[mary jhane]

hi, im having a 1.8 hsqldb + base set up, but i wanted know the set up for 2 database

DACM Told me this code for script,

Code: Select all

@echo off

REM The database name is reflected twice after the 'CONTINUE' label below
set java=Java\jre6\bin
set jar=openoffice.org 3\Basis\program\classes

IF EXIST "%PROGRAMFILES(X86)%" GOTO Win64

:Win32
set javapath=%PROGRAMFILES%\%java%\Java.exe
set jarpath=%PROGRAMFILES%\%jar%\hsqldb.jar
GOTO CONTINUE

:Win64
set javapath=%PROGRAMFILES(X86)%\%java%\Java.exe
set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb.jar

:CONTINUE
rem start mydb.server.odb
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:mydb dbname.0=accounting -database.1 file:mydb2 dbname.1=faculty;hsqldb.default_table_type=cached

REM pause
exit

but where i can save my mydb.data, mydb.backup, mydb.properties and mydb.script
for separate database (accounting and faculty)

[DACM]

i wanted know the set up for 2 databases

• :CONTINUE
  rem start mydb.server.odb
  "%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:mydb dbname.0=accounting -database.1 file:mydb2 dbname.1=faculty;hsqldb.default_table_type=cached

I'm sorry but that script was off-the-top-of-my-head at the time. I did some testing and here's the results:

Assuming the following database names (or created anew by the following script)

accounting.script
accounting.properties
accounting.data
accounting.backup

faculty.script
faculty.properties
faculty.data
faculty.backup

Once again, HSQLDB 1.8 supports up to 10 databases simultaneously, while HSQLDB 2.x supports an unlimited number.
Check the documentation for setup instructions, but the associated start-up and shutdown [Windows] batch files would look something like (changes in red) for a database named 'accounting' and another named 'faculty':

server.start.bat

@echo off

REM The database name is reflected twice after the 'CONTINUE' label below
set java=Java\jre6\bin
set jar=openoffice.org 3\Basis\program\classes

IF EXIST "%PROGRAMFILES(X86)%" GOTO Win64

:Win32
set javapath=%PROGRAMFILES%\%java%\Java.exe
set jarpath=%PROGRAMFILES%\%jar%\hsqldb.jar
GOTO CONTINUE

:Win64
set javapath=%PROGRAMFILES(X86)%\%java%\Java.exe
set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb.jar

:CONTINUE
rem start mydb.server.odb
rem You'll need separate Base (.odb) files to access each database.
rem The datasource URL within those .odb files must reflect the database-alias name as follows:
rem accounting.server.odb
rem Edit > Database > Properties... > Datasource URL
rem hsqldb:hsql://localhost/accounting;default_schema=true;get_column_name=false
rem faculty.server.odb
rem hsqldb:hsql://localhost/faculty;default_schema=true;get_column_name=false

"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:accounting dbname.0 accounting -database.1 file:faculty dbname.1 faculty

rem ;hsqldb.default_table_type=cached
rem Evidently switches are not allowed when running multiple databases in this manner
rem Therefore manually edit the .properties or .script files as appropriate to reflect 'cached' as desired
rem (HSQLDB 1.8: .properties) hsqldb.default_table_type=cached
rem (HSQLDB 2.x: .script) SET DATABASE DEFAULT TABLE TYPE CACHED

rem Dedicated database folders are also supported as follows:
rem "%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:accounting\accounting dbname.0 accounting -database.1 file:faculty\faculty dbname.1 faculty

rem Database alias names (dbname.X xyz) are necessary with multiple databases

REM pause
exit

And of course, the database names must be reflected in their associated file-naming convention, etc.

With more than one database, you'll need to shut them down individually (I think). The following seems to work. Here's the changes to server.stop.bat in red:

server.stop.bat

@echo off

set java=Java\jre6\bin
set jar=openoffice.org 3\Basis\program\classes

IF EXIST "%PROGRAMFILES(X86)%" GOTO Win64

:Win32
set javapath=%PROGRAMFILES%\%java%\Java.exe
set jarpath=%PROGRAMFILES%\%jar%\hsqldb.jar
IF EXIST "*.tmp" set jarpath=%PROGRAMFILES%\%jar%\hsqldb2\sqltool.jar
GOTO CONTINUE

:Win64
set javapath=%PROGRAMFILES(X86)%\%java%\Java.exe
set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb.jar
IF EXIST "*.tmp" set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb2\sqltool.jar

:CONTINUE

:::::::: Localhost server with user=SA, without password (HSQLDB 1.8 only)
:: "%javapath%" -cp "%jarpath%" org.hsqldb.util.ShutdownServer

:::::::: Issue the "shutdown;" command to hsqldb
set sql=--sql "shutdown;"

:::::::: "shutdown compact will reduce the space required and may improve access times" - HSQLDB documentation
:: set sql=--sql "shutdown compact;"

:::::::: Localhost server with user=SA, without password
set authentication1=--inlineRC url=jdbc:hsqldb:hsql://localhost/accounting,User=SA,Password=
set authentication2=--inlineRC url=jdbc:hsqldb:hsql://localhost/faculty,User=SA,Password=

:::::::: Localhost server with user=SA, with password prompt
:: set authentication=--inlineRC url=jdbc:hsqldb:hsql://localhost,User=SA

:::::::: Localhost serving multiple databases using alias, with password prompt
:: set authentication=--inlineRC url=jdbc:hsqldb:hsql://localhost/mydb_alias,User=SA

:::::::: Fully automated using a separate, secured, sqltool.rc file; in user home/profile folder during shutdown
:: set authentication=localhost-sa

"%javapath%" -jar "%jarpath%" %sql% %authentication1%
"%javapath%" -jar "%jarpath%" %sql% %authentication2%

REM pause
exit

[mary jhane]

Thanks for the reply DACM,
i have some clarifications about the script,

1.) It is possible to run without the start.vbs
-as far as i know, vbs is only connection for non dos command. therefore .bat is also working at linux platform and linux not supprot .vbs script

2.)this code is the connection for admin side
if i will add another user, i will add another line for this?? or add role from the sql tools then add user and Etc.?

Code: Select all

:::::::: Localhost server with user=SA, without password
set authentication1=--inlineRC url=jdbc:hsqldb:hsql://localhost/accounting,User=SA,Password=
set authentication2=--inlineRC url=jdbc:hsqldb:hsql://localhost/faculty,User=SA,Password=

3.) i cant see the path where i can save all of the script, since mydb.server was save at C:\Users\Public\databases\mydb

4.) REM is same as :: ? because when i open my text editor (Notepad ++ ), :: read as comment.

[DACM]

1.) It is possible to run without the start.vbs

START.vbs and STOP.vbs are not required. They simply run the associated batch file without exposing the command console on the Windows platform.

2.) set authentication1=--inlineRC url=jdbc:hsqldb:hsql://localhost/accounting,User=SA,Password=

I think you're mis-interpreting the code found in the server.stop.bat file. This line is one of the components used to shut-down the 'accounting' database using the SA username and password. It actually requires a DBA (database admin) user to shutdown a running database (properly). You only need to shutdown a database once, and that only requires one line per database (alias). Otherwise, individual users and roles are setup using SQL in each database.

3.) i cant see the path where i can save all of the script, since mydb.server was save at C:\Users\Public\databases\mydb

I don't really understand this question, but the script files should be co-located in the database folder (without further modifications). Nothing in the server-mode script needs to reflect the database folder path (C:\Users\Public\databases\mydb). You can move the database folder at will (on the same drive as Java without further modification).

4.) REM is same as :: ? because when i open my text editor (Notepad ++ ), :: read as comment.

Yes, you can use :: to designate a comment in a Windows batch file. No problem.

[mary jhane]

thanks for the reply,
now i understand more briefly,

i will test my base in this evening

thanks,
mary jhane