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
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Mar 03, 2010 9:12 pm    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

glj wrote:
Regarding the second problem ...

I was surprised to discover that the conversion from embedded db to client/server db changed autovalue fields in all tables containing them to NOT autovalued. What's with that? The problem here is that I can't add new records to any of the tables because the primary key in each was originally an integer field that was filled automatically. So I tried to change the field definition in those fields back to autovalue so that the client/server version is actually a usable database. Unfortunately, I found that editing of the tables is not allowed. What's with that?

Converting the embedded database to client/server has turned out to be more problematic than I thought it would.

This sounds like a user priviledges issue in the HSQLDB server itself?
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Thu Mar 04, 2010 9:50 am    Post subject: Re: How to: Migrate Base Projects to Multi-User Reply with quote

dacm wrote:
glj wrote:
Converting the embedded database to client/server has turned out to be more problematic than I thought it would.

This sounds like a user priviledges issue in the HSQLDB server itself?
I checked the .script file and it shows that user SA is being created with no password and is being granted DBA, so I don't know how a user privilege could be an issue.

I changed SA's password to a non-null value and modified the database frontend (the .odb file) to require a password. That did not improve the situation. I still can't edit field properties of tables. So my tables are forced to have INTEGER primary keys that do not autoincrement. That is beyond ridiculous.

I wonder if there is a way to manually add an AUTOINCREMENT switch to the primary key field definition in the CREATE MEMORY TABLE command in the .script file. I'll check the HSQL documentation and report back (if I can understand it).

BTW, I discovered that when I require passwords to access the database server, the shutdown code dacm gave earlier in this thread stops working. I don't know how to get the Java command to request SA's password so that the shutdown script goes to completion. Interestingly, the startup script works without so much as knowing who is trying to start the server.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Mar 04, 2010 10:18 am    Post subject: Reply with quote

glj:

You asked:
glj wrote:
I wonder if there is a way to manually add an AUTOINCREMENT switch to the primary key field definition in the CREATE MEMORY TABLE command in the .script file. I'll check the HSQL documentation and report back (if I can understand it).

I think ( yes, I know, Sliderule thinking, or, anything like that means trouble . . . with a capital T Smile ) your problem / solution might be with the use of a MEMORY table.

Put another way, OpenOffice Base will 'support' a CACHE table ( the default ), or a TEXT table, but, not a MEMORY table.

I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Thu Mar 04, 2010 12:39 pm    Post subject: Reply with quote

sliderule wrote:
Put another way, OpenOffice Base will 'support' a CACHE table ( the default ), or a TEXT table, but, not a MEMORY table.

Then I think this Howto has to clarify how to create the client/server database with CACHE tables, because as it is written now it creates MEMORY tables by default.

In any case, I read the HSQL documentation and found what I needed. Using Notepad, I opened the .script file and changed the CREATE MEMORY TABLE commands.

Original:
Code:
CREATE MEMORY TABLE "aTable"("ID" INTEGER NOT NULL PRIMARY KEY, etc.

Modified:
Code:
CREATE MEMORY TABLE "aTable"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, etc.

Works perfectly.

This doesn't change the fact that table field properties can't be edited in the Base file, but now I don't care because I can edit them manually. I just don't understand why dragging and dropping tables from the embedded database to the client/server database caused the field property autovalue=Yes to change to No unless that is related to sliderule's comment above.

Once again a hearty "Thanks" to dacm for this Howto and to sliderule for providing some improvements.
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Fri Mar 05, 2010 9:04 am    Post subject: Reply with quote

dacm wrote:
Here's a snippit from the batch I use to shutdown the HSQLDB server:
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice\basis\program\classes\hsqldb.jar
%javapath% -jar "%jarpath%" -sql shutdown localhost -sa
%javapath% -cp "%jarpath%" org.hsqldb.util.ShutdownServer
exit


I had to make a few changes to get your code to work (and then not perfectly as discussed below):
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice\basis\program\classes\hsqldb.jar
"%javapath%" -jar "%jarpath%" --sql shutdown localhost-sa
"%javapath%" -cp "%jarpath%" org.hsqldb.util.ShutdownServer
exit

I originally thought it was working exactly right. But when I set a password for SA, the server did not shutdown. From error messages, I discovered that I needed a file called sqltool.rc to allow authentication as SA. According to the HSQL documentation it needed to look something like this:
Code:
urlid localhost-sa
url jdbc:hsqldb:hsql://localhost
username sa
password abc

But that still doesn't work quite right.

For reference purposes in the discussion below, here is what the top of the .script file looks like:
Code:
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE "Assets"("ID" ... etc ...
CREATE MEMORY TABLE "Categories"("ID" ... etc ...
CREATE MEMORY TABLE "Filter"("ID" ... etc ...
CREATE MEMORY TABLE "TargetAudience"("ID" ... etc ...
ALTER TABLE "Assets" ADD CONSTRAINT SYS_FK_64 FOREIGN KEY("CategoryID") REFERENCES "Categories"("ID")
ALTER TABLE "Assets" ADD CONSTRAINT SYS_FK_67 FOREIGN KEY("AudienceID") REFERENCES "TargetAudience"("ID")
ALTER TABLE "Assets" ALTER COLUMN "ID" RESTART WITH 1925
ALTER TABLE "Categories" ALTER COLUMN "ID" RESTART WITH 2
ALTER TABLE "TargetAudience" ALTER COLUMN "ID" RESTART WITH 7
CREATE USER SA PASSWORD ""
CREATE USER CATALOG PASSWORD ""
GRANT DBA TO SA
GRANT SELECT ON PUBLIC."Assets" TO CATALOG
GRANT SELECT ON PUBLIC."Categories" TO CATALOG
GRANT SELECT ON PUBLIC."TargetAudience" TO CATALOG
GRANT SELECT,UPDATE ON PUBLIC."Filter" TO CATALOG
SET WRITE_DELAY 10
SET SCHEMA PUBLIC

So I ran a test involving four cases:

Case 1: In .script file, SA PASSWORD is set to null as shown above; in sqltool.rc file, the abc following "password" is removed - that is no password for SA in either file. RESULT: The HSQL server SHUTS DOWN, but with the following error message:

Unterminated input: "shutdown"

Case 2: In .script file, SA PASSWORD is set to null; in sqltool.rc file, the password abc follows "password " - that is, there is a discrepancy between the two: the database server requires no password but the sqltool is expecting one. RESULT: The HSQL server SHUTS DOWN, but with the following error message:

Failed to get a connection to 'jdbc:hsqldb:hsql://localhost' as user "sa".
Cause: Access is denied

Case 3: In .script file, SA PASSWORD is set to abc; in sqltool.rc file, the password abc follows "password " - that is, both the server and sqltool agree on the password for SA. RESULT: The HSQL server DOES NOT SHUT DOWN and the following error message is displayed:

Unterminated input: "shutdown"
java.sql.SQLException: Access is denied

Case 4: In .script file, SA PASSWORD is set to abc; in sqltool.rc file, the abc following "password" is removed - that is, there is again a discrepancy between the two: the database server requires a password but the sqltool is expecting no password. RESULT: The HSQL server DOES NOT SHUT DOWN and the following error message is displayed:

Failed to get a connection to 'jdbc:hsqldb:hsql://localhost' as "sa".
Cause: Access is denied

Conclusion: Although the error messages differ, the following is a fact: The HSQL server shuts down OK as long as the server's SA user has no password, but does not shut down if the server's SA user has a non-null password.

What is the solution to this problem? Or is this a non-issue because the .log file allows the database server to correctly rebuild the tables from the .script file and the changes recorded in the .log file the next time the server is started? Maybe I shouldn't be wasting my time trying to get this to work. Maybe I should just let the computer as it is shutting down abort the HSQL server instance (do a "dirty" shutdown of the server). After all, it will recover nicely the next time it is started.... But that just doesn't seem like a good idea to me. I like to have a database in a clean state at all times.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Mar 05, 2010 12:05 pm    Post subject: Reply with quote

hsqldb:hsql://localhost/;default_schema=true;shutdown=false (or is it true?)

http://hsqldb.org/doc/guide/ch04.html
Quote:
Will shut down the database when the last connection is closed.

This mimics the behaviour of 1.7.1 and older versions. When the last connection to a database is closed, the database is automatically shut down. The property takes effect only when the first connection is made to the database. This means the connection that opens the database. It has no effect if used with subsequent, simultaneous connections.

Quote:
In 1.8.0, a connection property, shutdown=true, can be specified on the first connection to the database (the connection that opens the database) to force a shutdown when the last connection closes.


Or don't worry about it and just let the .log file rebuild the .data or .script files upon startup. The best way, if you're worried, is probably to add a macro that issues the SQL command to "checkpoint defrag" or "shutdown compact" to a button or close event in your database, if that's possible.


Last edited by dacm on Sat Mar 06, 2010 9:25 pm; edited 3 times in total
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Mar 05, 2010 12:35 pm    Post subject: Reply with quote

I should have some time tomorrow to look at the "non-editable field properties" issue.

This is most likely a problem with the JDBC driver or a setting. I do have 'Edit > Database > Advanced Settings > Special Settings > Ignore the priviledges from the driver' checked (and tested un-checked). I'm simply using the "sa" username without a password on a //localhost/ connection as outlined by myself and sliderule in this tutorial. This particular issue is not associated with migration between embedded and server modes -- I'm using newly created database file-sets and new .odb front-end files.

Bottom line, no JDBC driven RDBMS server that I have tried will allow you to edit the field properties after saving a new table to the database using Base 3.1.1. I've tried HSQLDB (1.8 and 2.0RC8) and H2 (1.1.118). This is true of "CACHED" or "MEMORY" tables. I've even extracted the '.properties' file from an embedded HSQLDB 1.8 .odb file and replaced the existing HSQLDB server file (after appropriate naming) to no avail. In this case, the server-mode HSQLDB database had the exact same properties as Base expects with embedded databases including "cached" tables by default. As a test, I've also extracted the embedded database files in whole from an embedded database and run them client-server with the same "un-editable field properties" result. I can add or delete fields, add a new field while adjusting the properties, and add entire tables -- but once it is saved the properties are locked (grayed-out).

So the question is, why are JDBC-driven RDBMS server databases exhibiting locked field properties? Is this a driver issue (seems probable), a user permissions issue (seems unlikely), a Base setting (?), a database property setting (?), a connection or protocol issue (localhost vs separate computers?), or a bug in the Base front-end with respect to the JDBC driver interface (?).

See the solution: The Frozen 'Field Property' Workaround (adjusting Table structures with Base including AutoValue Primary Keys)


Last edited by dacm on Wed Jan 04, 2012 6:27 pm; edited 1 time in total
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Fri Mar 05, 2010 9:18 pm    Post subject: Reply with quote

Thanks for the suggestions!
dacm wrote:
Code:
hsqldb:hsql://localhost/default_schema=true;shutdown=false
I had considered the above change in code when I saw it earlier in the HSQL docs, but then I didn't think that was possible because my library card catalog database will be connected to by remote clients intermittently through the school day. I certainly don't want the database server to shut down until the librarian shuts it down at the end of the day.

dacm wrote:
The best way, if you're worried, is probably to add a macro that issues the SQL command to "checkpoint defrag" or "shutdown compact" to a button or close event in your database, if that's possible.

That assumes there is an active client connection within reach of the librarian. Of course, the librarian could start a client connection and click the shutdown button, but I was really hoping to have a desktop shortcut to make the shutdown as convenient as possible.

As I have time I may look into this matter further. In the meantime, either I'll issue the SQL command SHUTDOWN from a client or I'll let the normal rebuild at startup take care of cleaning up the database at the next restart.
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Tue Mar 09, 2010 9:10 pm    Post subject: Reply with quote

Finally created a working shutdown batch file. The following two batch files start and stop my HSQLDB server whether or not I have assigned a password to SA:

start_HSQLDB_server.bat
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice\basis\program\classes\hsqldb.jar
"%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:myDB
exit

stop_HSQLDB_server.bat
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice\basis\program\classes\hsqldb.jar
"%javapath%" -jar "%jarpath%" --sql shutdown; localhost-sa
exit

where the sqltool.rc file looks like this:
Code:
urlid localhost-sa
url jdbc:hsqldb:hsql://localhost
username SA
password ABC

The password for SA as found in the .script file (which is opened by the Java command in start_HSQLDB_server.bat) and the password for SA as found in the sqltool.rc file must match, of course.

The main reason my original stop batch process didn't work was the absence of a very crucial semi-colon after "shutdown".
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Mar 10, 2010 12:13 am    Post subject: Reply with quote

Very Nice glj

Thank you for working out the password issue for shutdown!

I just ran through a battery of tests and have concluded with your exact code/setup except I would like to suggest:

stop_HSQLDB_server.bat
Code:
@echo off
set javapath=C:\Program Files\Java\jre6\bin\Java.exe
set jarpath=C:\Program Files\openoffice\basis\program\classes\hsqldb.jar
"%javapath%" -jar "%jarpath%" --sql "shutdown compact;" localhost-sa
exit


And if you don't mind I'll add this password/shutdown info to the tutorial itself ?
Back to top
View user's profile Send private message
glj
Power User
Power User


Joined: 05 Mar 2009
Posts: 73

PostPosted: Wed Mar 10, 2010 6:56 am    Post subject: Reply with quote

Good idea on the "shutdown compact;". I'll add that to my batch, too.

By all means, include whatever is helpful in your tutorial.

Thanks for all your work on this project, dacm. It certainly helped me achieve what I needed and hopefully will be helpful to others, too.
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 13, 2010 6:58 pm    Post subject: Reply with quote

I would like to know if the graphic example provided by sliderule will solve the problem we have with multi users finding it difficult to access the database at the same time. I am not techie, but we have a server, small local network and remote network via VPN

I followed sliderules graphic - however when I Test Class get error message saying JDBC Driver could not be loaded

Any assistance would be appreciated
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Mar 13, 2010 9:00 pm    Post subject: Reply with quote

zazu wrote:
I followed sliderules graphic - however when I Test Class get error message saying JDBC Driver could not be loaded

The screenshots are Part 3 of a multi-step process on page 1. The driver should be loaded successfully as long as you've successfully completed Step 2 (Setup OpenOffice for Java server access…) of the tutorial.

Once you get a successful 'Test Class'...

Step 3 screenshots are part of a building-block approach starting with running the ooBase client on the same computer as the HSQLDB server. That's what //localhost/ means in the Datasource URL. Once you begin setting up local clients on separate computers, Step 3c will change to reflect the IP address or domain assigned to the computer running HSQLDB server. Please read the notes under Step 3 in the tutorial, including the links to the HSQLDB documentation. For example, you'll enter the server's IP address from a local client:
hsqldb:hsqls://192.168.1.210/;default_schema=true

Access for your remote clients through VPN will require more experience than I have to offer. Here's some rather outdated documentation that you might find useful. If you're forced to switch protocol's to https (from hsqls) then you'll need to start HSQLDB server in web-server mode as well.
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 2:10 am    Post subject: Reply with quote

I have followed the procedures outlined on my server and can open and work on the database on the server.

I then started the process of Part 3 on a computer on the network substituting my server's ip address for localhost and the Test Class action resulted in:

The connection to the data source "Manurewa" could not be established

The driver class 'orghsqldb.jdbcDriver' could not be loaded.

I can access the database directly through the network but attempts to open tables/forms results in the same error message

What am I doing wrong and how do I correct it please
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 4:18 am    Post subject: Reply with quote

Can anyone help here?...I'm mostly unavailable due to Spring Break this week...

It may be a Step 2 or 3 issue. What are you using to access the "database" directly through the network? Are you seeing the folder/files or are you able to issue SQL through another tool?
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 3 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