Error in my base set up

Discuss the database features
Post Reply
User avatar
mary jhane
Posts: 73
Joined: Sat Oct 22, 2011 6:27 am

Error in my base set up

Post by mary jhane »

Hi good morning

I'm having a error in set up of my multi-mode
i follow all of the step by step and i get this one
Attachments
wew.JPG
OpenOffice 3.3 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Error in my base set up

Post by FJCC »

Here is a guess from my own experience. I get this error when trying to connect to an external HSQLDB if the database is not yet running. You have to start the external database manually. Was there some step in your procedure for starting the database?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Error in my base set up

Post by DACM »

Here's some advice from the referenced tutorial:
mydb.file.odb
'File mode' is seamless, allowing us to click directly on the .odb file for startup.

mydb.server.odb
But 'server mode' requires separate HSQLDB startup for Table access. We can automate this process using a script/batch file. For example, the provided Windows script file ('START.vbs') automates all aspects of 'server mode' through a single mouse click. So we do not start a 'server mode' database simply by clicking on the Base file directly. A script file should be used to start things in 'server mode' (click on 'START.vbs' for Windows users).
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
mary jhane
Posts: 73
Joined: Sat Oct 22, 2011 6:27 am

Re: Error in my base set up

Post by mary jhane »

Thanks for the reply,
It works but i have a question

*You need to open START or server.start to open "mydb.server.odb" in the sever but if you close the file "mydb.server.odb" the error above will come up in the client,
is there's other solution for this if you open START or server.start the base will work even you close the "mydb.server.odb", the only way to close this is STOP.vb or server.stop?

*how i can rewrite this...? i will use our time policy
sample if 5pm it will automatically shutdown, or 6am it will open..
(But i think, the other anti virus will detect it as virus or malware)
OpenOffice 3.3 on Windows XP
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Error in my base set up

Post by DACM »

mary jhane wrote:*You need to open START or server.start to open "mydb.server.odb" in the sever but if you close the file "mydb.server.odb" the error above will come up in the client,
is there's other solution for this if you open START or server.start the base will work even you close the "mydb.server.odb", the only way to close this is STOP.vb or server.stop?
Wow, good work!

So if I understand your post:
1. You've run the Windows installer (mydb.exe) for setting-up the built-in HSQLDB 1.8 in file/server mode.
2. You're working in a multi-user environment and have also run the same installer on the client(s).
3. You've modified the 'data-source' URL in "mydb.server.odb" with the IP Address of the server and shared the database folder.
4. You're starting the "multi-mode database" in server-mode by clicking START on the server. This also starts Base and opens the front-end file ("mydb.server.odb") on the server.
5. You have client access to the central database through Base by clicking directly on "mydb.server.odb" across the network.

And now you need to know how to keep the HSQLDB server running for client access even if you close Base on the server.

That's outlined in step (9) of the client-server tutorial: Running a "multi-mode database" in a multi-user environment
(9) Eliminate ;shutdown=true from the HSQLDB-server startup string as found in 'server.start.bat'. Otherwise, if you close the Base file (mydb.server.odb) on the main computer, the HSQLDB server is also shutdown automatically. This would not be good in a multi-user environment.
Image
So delete the text ;shutdown=true from the batch file (server.start.bat). Then use START normally. Now to shutdown the HSQLDB server app, click STOP.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
mary jhane
Posts: 73
Joined: Sat Oct 22, 2011 6:27 am

Re: Error in my base set up

Post by mary jhane »

Yes i already do that :) (and you're right)
[so far so good] just implementing open office to our office
Thanks for the reply... and thank you very much for the installer,
*so, what is the use of [;shutdown=true] in your installer??
*im confuse in rewritting it, because i need time in management or policy in the server
how i shutdown, automatically without pressing the stop or starting it without pressing the start?

i am now trying the Standalone Forms / Switchboard
*if i do it in the base, and exporting it, the sub forms/ sub sub form & etc... need a data source and if you do it in writer and connect it to the base the sub form dont need the data source

and what if im gonna use 2 database? example 1 for accounting and 1 for faculty?
what is the set up for that?
OpenOffice 3.3 on Windows XP
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Error in my base set up

Post by DACM »

mary jhane wrote:*so, what is the use of [;shutdown=true] in your installer??
This string shuts-down the HSQLDB server app upon closing the last connection. I included the string by default for server-mode as a single-user consideration. Server-mode allows you to move the database folder without changing the data-source URL, unlike file-mode. And server-mode remains running if Base crashes adding yet another layer of data protection. So server-mode is quite useful in single-user environments. Multi-user environments are typically setup by more advanced users that don't mind digging into tutorials and performing the file surgery in order to support a persistent client-server setup of HSQLDB.

mary jhane wrote:*im confuse in rewritting it, because i need time in management or policy in the server
how i shutdown, automatically without pressing the stop or starting it without pressing the start?
No modification of these files is necessary for such tasks. Simply create a Windows XP scheduled task that runs START.vbs daily at 6am, and another that runs STOP.vbs daily at 5pm, if you wish. But you should be aware of some considerations when shutting down the server app. The STOP script issues the SQL 'SHUTDOWN' command which rolls-back any transactions still in-progress. And since it's the end of the day, it may be better to issue 'SHUTDOWN COMPACT' which is already available in the next line of the associated batch file (server.stop.bat).

See the following changes to server.stop.bat in red:
server.stop.bat wrote::::::::: 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;"
mary jhane wrote:*You need to open START or server.start to open "mydb.server.odb" in the sever
The START.vbs script simply runs server.start.bat silently (without exposing the command-line window).
Similarly, STOP.vbs simply runs server.stop.bat silently.
mary jhane wrote:i am now trying the Standalone Forms / Switchboard
*if i do it in the base, and exporting it, the sub forms/ sub sub form & etc... need a data source and if you do it in writer and connect it to the base the sub form dont need the data source
That's correct. If you export the Base Forms, they will need to be reconnected to the data-source at the DataForm level (each MainForm & SubForm). If you leave the Forms inside the Base (.odb) file, then you can create a Push Button in a Writer document to open the Base file (which opens the Base GUI) and access the embedded Forms...thus, no re-connection is necessary. Unless I'm missing something here...?
mary jhane wrote:and what if im gonna use 2 database? example 1 for accounting and 1 for faculty?
what is the set up for that?
HSQLDB 1.8 supports up to 10 databases simultaneously, while HSQLDB 2.x supports an unlimited number.
Check the documentation for setup instructions, but as I recall it (server.start.bat) would look something like:
server.start.bat wrote:@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: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 pause
exit
And of course, the database names must be reflected in their associated file naming convention, etc. I would have to experiment because I haven't messed with multiple databases since I first started playing with server-mode a couple of years ago. I don't remember if the alias names (dbname.X xyz) are necessary with multiple databases, etc. I just don't remember the details.

With more than one database, you may need to shut them down individually. The following seems to work. Here's the changes to server.stop.bat in red:
server.stop.bat wrote:@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% %authentication
1%
"%javapath%" -jar "%jarpath%" %sql% %authentication2%

rem pause
exit
Alternatively, you can upgrade to HSQLDB 2.x and utilize separate schema within the same database, while separating the two groups (accounting and faculty) using role-based security. But that would be more complex unless you're already planning to implement users and roles.
Last edited by DACM on Sat Mar 31, 2012 6:44 am, edited 5 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
mary jhane
Posts: 73
Joined: Sat Oct 22, 2011 6:27 am

Re: Error in my base set up

Post by mary jhane »

thanks for the reply, it help me a lot, :D

yesterday the error come up again...
and i realize the IP i overwrite in EDIT> DATABASE> Properties , i will set my IP in static mode
"it is posible to run this server in dynamic IP?" [After a few hours (i solved it), i did it by reserve ip in server :D]

nice.. Windows XP scheduled task

Nice Standard alone form (If i transfer my files to the other client, it doesnt need any modification in data source) but just register the mydb.server
But how about the report? i cant migrate it using stand alone form

i am confuse in users and roles, but i also need it :( maybe i will search for some tutorial for it
and i am focused in security, improvement of this project and connection from the server.
OpenOffice 3.3 on Windows XP
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Error in my base set up

Post by r4zoli »

mary jhane wrote: But how about the report? i cant migrate it using stand alone form
This is not related to the original question.
Please ask it in separate post.
The report could not be separated, but you can use mail-merge feature of Writer, Calc with registered data source to create reports.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
mary jhane
Posts: 73
Joined: Sat Oct 22, 2011 6:27 am

Re: Error in my base set up

Post by mary jhane »

thanks, sorry for posting a unrelated question...


i appreciate your reply... for using a mail merge
OpenOffice 3.3 on Windows XP
Post Reply