OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to: Migrate Base Projects to Multi-User
Goto page Previous  1, 2, 3, 4, 5  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Mon Mar 15, 2010 2:04 pm    Post subject: Reply with quote

I have a shortcut on the computer which links to the database on the server. Clicking the shortcut opens the database. The bottom of the screen shows JDBC and hsqldb:hsql://localhost/;default_shema=true

When I click to open a form or move to say Tables thats when I get the error message

I have checked the server startup and these are some of the lines in the window

Loaded properties from [E:\Network Data Folder\ManurewaDatabase\server.properties]
Initiating startup sequence...
Server socket opened successfully in 110ms
Database [index=0, id=0, db=file:Manurewa, alias=] opened successfully in 3078ns
Startup sequence completed in 3219 ns
2010-03-16 11:05:11.781 HSQLB server 1.8.0 is online

EDIT
I note early in this thread there is discussion and cross reference to How To: Replace HSQLDB with H2 embedded multi-user

So, do I need to do this first in order to make base truly multi user? All the techie talk is somewhat daunting and I wonder if someone could prepare a summary of what we need to do to have a multi user environment -then I could read up on the specific things I need to do and hopefully implement them

Thanx in advance
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Mar 15, 2010 10:08 pm    Post subject: Reply with quote

zazu wrote:
I have a shortcut on the computer which links to the database on the server. Clicking the shortcut opens the database. The bottom of the screen shows JDBC and hsqldb:hsql://localhost/;default_shema=true


Actually, the Base status screen is lying to you. Or you could simply say it reflects .odb file connection settings, whether or not you've actually connected successfully. Clicking on Tables tells the real story, and unfortunately you're not getting a connection. ' Edit > Database... > Properties... ' will take you directly to the test dialog where you can continue to try different settings, because as you've indicated with the HSQLDB startup screen your database server is fully functional, albeit on a separate computer across the network. But I'm away this week so I don't have the ability to check networked client settings, ports, and perhaps firewall settings to really help you. Can you use a file explorer to browse the server's databse folder from the client computer? It could be a sharing issue. Keep posting errors and perhaps we can help you, but networking issues are beyond the normal scope here and require some techie knowledge to solve in many cases.

zazu wrote:
I note early in this thread there is discussion and cross reference to How To: Replace HSQLDB with H2 embedded multi-user.

So, do I need to do this first in order to make base truly multi user?

No, H2 is not necessary. But come to think of it, the H2 documentation claims you can use the same Datasource URL that you'll find in that post/tutorial for clients in networked environments when adding AUTO_SERVER=TRUE to the Datasource URL. I haven't tested that claim yet with H2 in a network environment, and I can't this week (Spring Break), but I'm skeptical because it doesn't make sense that a client can find a server without an IP address or domain name. But if you're up for some testing this week you should try H2. I wouldn't be surprised if it solves your issue. Edit: H2's "mixed-mode" (AUTO_SERVER=TRUE) does indeed orchestrate seamless access to a single, shared database (share the folder) on a network. I'm finding H2 much easier to setup and use in all user environments than HSQLDB server. But there may be tradeoffs as I/we delve deeper into OOo-H2 integration.

zazu wrote:
All the techie talk is somewhat daunting and I wonder if someone could prepare a summary of what we need to do to have a multi user environment -then I could read up on the specific things I need to do and hopefully implement them
Hmmm.... I certainly don't mean to sound techie, but I think you'll find serving a database to multiple users accross a network to be full of variables and associated techie jargon. And I'm just trying to use the terms found within Base for easy transfer of information. Believe me, if I had anything to do with engineering software for networks, it would all be point&click while building the necessary stuff (how's that for non-techie?) in the background, and feature a raw-mode for techie power-users. You might try Wikipedia for any jargon that doesn't make sense in context. And the H2 documentation is particularly well written and may bring you up to speed quickly if you're willing to skim through it.

Last edited by dacm on Sat Apr 03, 2010 12:05 pm; edited 2 times in total
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Mon Mar 15, 2010 10:27 pm    Post subject: Reply with quote

Thanks, i'll keep working and learning
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Wed Mar 17, 2010 12:11 am    Post subject: Reply with quote

I ran an embedded base on the server and I noticed that when a user closed the base the obd.lck file did not close. Also the "quickstart"(?) icon was in the system tray. As soon as I exited the quickstart icon the odb.lck file closed. Therefore is it possible that this quickstart function keeping the odb.lck file open, also prevents multi user access when a multi user database is being used? Question
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Mar 17, 2010 12:51 am    Post subject: Reply with quote

I don't think so. That's an external mechanism that is used to alert other users/applications that they don't have exclusive access.

I think your issue is a network issue like file sharing. So can you see the database files or folder acroos the network using Windows Explorer or My Network Places? That's a good place to start troubleshooting this issue.
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Wed Mar 17, 2010 11:16 pm    Post subject: Reply with quote

I still seem to be stuck. I have checked my Kaspersky Security parameters and everything is ok there. I can open up Base on my pc, click on open existing database and there to behold is a database with JDC and hsqldb....=true in the info line. The Customer Form is listed in the forms window, but clicking to open results in the cannot load driver error.

On my local pc I then proceed with the process of setting up a connection to the database and again at Step 3c get the cannot load driver error again (I had substituted localhost with the server ip address)

Is there something we need to do on the networked pcs or the local copy of base to make this happen. I should mention that we have copied and pasted tables/queries/macros/forms from an embedded database that is saved on the server Embarassed
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Fri Mar 19, 2010 3:14 pm    Post subject: Reply with quote

I think I've fixed the problem. I needed to set up the Java environment in the Database>Tools>Options>Java for each PC that needs access to the database

Now, when PCs open the database the main form autexecs as required, but a new problem has been encountered. I have another form (which is a help file) and a report that should open from a new menu we've created or a button on the main form.

They used to open now sometimes they open and sometimes I get the dialog message that no connection could be made to the database Question

Edit Is this likely because the form and report have been created in another database and copied/paste into the multi-user database?
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Mar 20, 2010 10:31 pm    Post subject: Reply with quote

zazu wrote:
I think I've fixed the problem. I needed to set up the Java environment in the Database>Tools>Options>Java for each PC that needs access to the database

The tutorial was not clear on this, so I added a NOTE at STEP 1 to clarify this issue.

zazu wrote:
...now sometimes they open and sometimes I get the dialog message that no connection could be made to the database Question

Edit Is this likely because the form and report have been created in another database and copied/paste into the multi-user database?

I don't know what would cause that. The only migration issue we've seen is related to flipping the Primary Key's 'auto-increment' property in each table to "No" (disabled) during drag&drop. Since column properties are greyed (un-editable in this case) using the Base GUI (right-click Edit features), I think it is best to extract (as opposed to drag&drop) your tables from the single-user .odb file using 7-zip or renaming the .odb extension to .zip and use Windows Explorer for the extraction process. See "Migrating an Embedded database:" notes and steps on page 1 of this tutorial.
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Sat Mar 20, 2010 10:39 pm    Post subject: Reply with quote

I was able to copy/paste the tables ok and overcame the autovalue problem by amending the script file as mentioned earlier in this thread by glj (4 March 2010)
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Mar 20, 2010 10:56 pm    Post subject: Reply with quote

That was just a hunch. Otherwise, the only time I've seen intermittent table access issues is when OpenOffice has crashed in another instance/window requiring task or computer restart. Just another hunch.
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Sun Mar 21, 2010 1:21 pm    Post subject: Reply with quote

How do you get the data to update?

I have a main form "Customer" and a number of sub forms - one called "Matter" (this is for a law firm)

The Matter subform has two column (table layout) MatterNo and Description. It might look likr this:

1 House sale
2 Employment Contract
3 Car accident

Another subform relates to the billing and I have a drop down box that is based on the Matter subform/table. Example I might write a letter to the employer about the employment contract and need to identify this in the billing subform. The billing detail is fine and then I use a drop down box which gives me a list of matters for this customer - in this case 1, 2 and 3 and I choose 2.

However the problem is that if I am working in this form and have just entered say 3 Car accident in the matter subform and then move my cursor to the billing subform and click on the drop down box 3 is not there. Even if i move to another customer and come back it is still not there. I have to close/open the form for the data to be refreshed.

[Solved] I read in a different post on the forum (http://www.oooforum.org/forum/viewtopic.phtml?t=97086&highlight=refresh) about the Refresh button on the form
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Tue Mar 23, 2010 1:17 am    Post subject: Reply with quote

If I want to open a second database in multi user mode do I create a second batch file like:

@echo off
set javapath=C:\Program Files\Java\jre6\bin\java.exe
set jarpath=C:\Program Files\OpenOffice.org 3\Basis\program\classes\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:Database1
exit

replacing Database1 with Database2

or do I add an extra line like

@echo off
set javapath=C:\Program Files\Java\jre6\bin\java.exe
set jarpath=C:\Program Files\OpenOffice.org 3\Basis\program\classes\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:Database1
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:Database2
exit
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Mar 23, 2010 7:09 pm    Post subject: Reply with quote

We could look it up in the documentation...but I think it's a single line:
zazu wrote:
If I want to open a second database in multi user mode...

Code:
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:MyDatabase0 -dbname.0 aliasdb0 -database.1 file:MyDatabase1 -dbname.1 aliasdb1
exit

You'll probably need an alias for each database (-dbname.0 aliasdb0) (lower-case alias name) in order to specify one specific database in each Datasource URL (see the note at step 1e and referencing step 3c):

hsqldb:hsql://localhost/aliasdb0;default_schema=true

Each .odb file is limited to one database specified by alias name. I have no real experience with alias's or serving multiple HSQLDB databases. Perhaps you can report your findings.

Depending on your objectives, it might be better to create and use multiple schema (folders within one database) rather than serve multiple databases. But I haven't tested this with HSQLDB server and I'm not sure how this method would interact with "default_schema=true" in the Datasource URL. AFAIK, "default_schema=true" is an OOo requirement with HSQLDB server (for now).
Back to top
View user's profile Send private message
zazu
General User
General User


Joined: 06 Mar 2010
Posts: 37
Location: New Zealand

PostPosted: Thu Apr 01, 2010 5:51 pm    Post subject: Reply with quote

What happened to my tables ??

Successfully created a multi user database for a law firm. (thanks to everybody who helped)

Client has been using it for a while, but all of a sudden there are no tables.

I know that in the front end you cannot see tables, but I would expect to see them and perhaps edit them in the back end - but they are not there.

Alledit functions in the back end seem greyed out.

There is no back up copy, but there is a script file
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Apr 01, 2010 9:35 pm    Post subject: Reply with quote

zazu wrote:
What happened to my tables ??

I'm not following you're post. Are you saying the *.data, *.backup, and *.properties files are physically missing from the database folder?

And why no backups?
Quote:
Beyond computer/network considerations, an additional form of data durability is achieved through regular backups. Disk mirroring or striping-with-parity (RAID) protects against disk failure, while automated backups using synchronization software can provide date-time-stamped copies at regular intervals, even to the "cloud."

It may be too little too late, but I've added the following to the main tutorial post:

In step (3) of the migration instructions...

(3) Rename the four database files. The files are currently named: script, backup, properties and data. These filenames will become file-extensions upon renaming them using your database filename; you must use the database filename you designated in the command line (batch file) used to start the HSQLDB server. If using the database name designated in Step 1e of this tutorial the extracted file-set names become: mydb.script, mydb.backup, mydb.properties and mydb.data. EDIT ADD: " While the database is in use, another file named mydb.log (in this case) is created which holds all changes generated during the current database session.
Note: The .script, .data, and .log files are critical to HSQLDB database backup and recovery because they make-up your database and should be backed-up regularly. The .log file will not exist after issuing (use: Tools > SQL... ) the SQL command: SHUTDOWN COMPACT. "


Last edited by dacm on Fri Apr 02, 2010 6:41 am; edited 2 times in total
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page Previous  1, 2, 3, 4, 5  Next
Page 4 of 5

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group