[Solved] Real-Time Data Update/Refresh

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] Real-Time Data Update/Refresh

Postby DeMiNiX » Fri Sep 18, 2015 2:51 pm

Hey all,

So I created a split-database running in JDBC mode and using HSQL respectively for a new database here at work. I'm able to edit the data on multiple computers at the same time. However, the data doesn't refresh real-time for the other computers, unless they close out of the ODB file and open it back up. To make it easier to visualize, let me give you a scenario:

I'm entering data into a form on Computer A, while Computer B is viewing the form as well. When I'm completed entering data on Computer A, I save the form and refresh. However, Computer B doesn't see that new data, unless Computer B closes out of OpenOffice and opens it back up. Even if Computer B refreshes the form, it still doesn't update. I want Computer B to see the updated data in real-time.

I need to be able to incorporate real-time data update for project management (ie. showing the status of a client sample, what samples are in the queue, etc.)

Any help would be greatly appreciated! Thank you!
Last edited by DeMiNiX on Fri Sep 18, 2015 9:04 pm, edited 1 time in total.
OpenOffice 4.1.1, Windows 7 Home Premium 64-bit
DeMiNiX
 
Posts: 4
Joined: Fri Sep 18, 2015 2:37 pm

Re: Real-Time Data Update/Refresh

Postby Villeroy » Fri Sep 18, 2015 4:28 pm

There is a refresh button on a toolbar for every form, table, view and query.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26410
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Real-Time Data Update/Refresh

Postby Sliderule » Fri Sep 18, 2015 4:32 pm

DeMiNiX:

Welcome to the OpenOffice / LibreOffice Base forum.

¿ Can you please help me, help you to find the solution to your described problem ?

Please follow these steps for me:
  1. Start your OpenOffice / LibreOffice Base file ( *.odb )
  2. From the Menu: Edit -> Database -> Properties
  3. Please report back here, what is entered, under Datasource URL:

Example below, showing difference between running HSQL as a single user, vs, server. In the instance of the the server, of course, it ( HSQL ) must be started first. :super:

  1. Below, is an example of what I have entered, when using the database as a single user, and the name of my HSQL database files are bldtest :

    Code: Select all   Expand viewCollapse view
    hsqldb:file:C:\Users\User_Name\Documents\HSQL\data\bldtest;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

  2. Below, is an example of what I have entered, when using the database as a Server ( multiple user ), and the name of my HSQL database files are bldtest :

    Code: Select all   Expand viewCollapse view
    hsqldb:hsql://localhost/bldtest;default_schema=true
In both instances, the JDBC driver class is:

Code: Select all   Expand viewCollapse view
org.hsqldb.jdbcDriver

¿ What I want to confirm, are you running your HSQL database back-end as a single user or as a server ?

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1162
Joined: Thu Nov 29, 2007 9:46 am

Re: Real-Time Data Update/Refresh

Postby DeMiNiX » Fri Sep 18, 2015 5:25 pm

Sliderule wrote:DeMiNiX:

Welcome to the OpenOffice / LibreOffice Base forum.

¿ Can you please help me, help you to find the solution to your described problem ?



Thank you for the quick reply! As I was reading your description of the differences between HSQL single-user and HSQL Server, I realized I wasn't running a server. :crazy: So I went through the process of creating the .BAT files, ran it on Computer A, and all is good.... until I try Computer B.

Computer B is having some trouble connecting to the server. Let me read to you the "destination URL" description for mydb.server.odb:

Code: Select all   Expand viewCollapse view
hsqldb:hsql://localhost/;default_schema=true;get_column_name=false


You had mentioned that your database files were named "bldtest". The ODB file that I'm using is from a template, which automatically created the split-database files, so when I try to input my orignial "split-database" file name infront of localhost/, it won't let me. So I just dragged and dropped the tables and forms from my orignal ODB file into the mydb.server file, leave it blank infront of localhost/, and it seems to run just fine.

NOW, back to Computer B: I tried doing the following things;

- copying mydb.server.odb onto Computer B, and changing localhost to the IP of computer A (no luck, I am prompted "socket creation error"
- opening the same mydb.server.odb that Computer A is running (no luck, socket creation error)[/list]

Now, with all that being said, I'm running the SERVER on Computer B, with all of the database and server files located on a shared network drive.

Is there anything special about running a network off of a shared network drive, instead of a shared network machine? What would you suggest I do from here?

Thanks a lot for your help!
OpenOffice 4.1.1, Windows 7 Home Premium 64-bit
DeMiNiX
 
Posts: 4
Joined: Fri Sep 18, 2015 2:37 pm

Re: Real-Time Data Update/Refresh

Postby Sliderule » Fri Sep 18, 2015 5:55 pm

You said / asked:
Is there anything special about running a network off of a shared network drive, instead of a shared network machine? What would you suggest I do from here?

I do not know the answer for that question, since, I am running it ( the database back-end, HSQL ) on one machine, as a Server, but, allowing multiple programs get at the HSQL data concurrently.

What I would suggest, is, review the HSQL documentation.

I am assuming, you are running HSQL Version 2.3.3 ( or at least, one of the 2.3 versions ) rather than, HSQL 1.8.0.10 . . . a very old version ( with the hsqldb.jar file included in the OpenOffice download ).

HSQL documentation:
  1. I prefer, the PDF file . . . name of . . . guide.pdf
  2. If you want to get to HSQL documentation online, HTML, http://www.hsqldb.org/doc/2.0/guide/

    check out: http://www.hsqldb.org/doc/2.0/guide/running-chapt.html#rgc_hsql_server

    Look at
    1. Example 1.2. Java code to connect to the local http Server
    2. Example 1.3. Java code to connect to the local secure SSL hsql and http Servers
Let just add, yes, I am 99.9% sure, you will need to include the file name ( mydb ) in the Database URL connection.

Explanation: Please be sure to have ( create ) an additional BAT file to shut down the running HSQL server, if you have not done that already. :)

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1162
Joined: Thu Nov 29, 2007 9:46 am

Re: Real-Time Data Update/Refresh

Postby Sliderule » Fri Sep 18, 2015 7:10 pm

One additional note, the example below, is what I use to START my HSQL Server:

Code: Select all   Expand viewCollapse view
@java -classpath "C:\Program Files (x86)\HSQL-20\lib\hsqldb.jar" org.hsqldb.server.Server --database.0 file:bldtest --dbname.0 bldtest xdb

Notice above, the parameter, --dbname

Per HSQL documentation:

Chapter 14: http://www.hsqldb.org/doc/2.0/guide/listeners-chapt.html#lsc_hsql_server wrote:Listeners

As described in the Running and Using HyperSQL chapter, network listeners (servers) provide connectivity to catalogs from different JVM processes. The HyperSQL listeners support both ipv4 and ipv6 network addressing.

HyperSQL Server

This is the preferred way of running a database server and the fastest one. This mode uses the proprietary hsql: communications protocol. The following example of the command for starting the server starts the server with one (default) database with files named "mydb.*" and the public name (alias) of "xdb".

Code: Select all   Expand viewCollapse view
java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

Note the use of the words . . . public name (alias) above. :)

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1162
Joined: Thu Nov 29, 2007 9:46 am

Re: Real-Time Data Update/Refresh

Postby Villeroy » Fri Sep 18, 2015 7:36 pm

And this is my start.bat:
Code: Select all   Expand viewCollapse view
CD "C:\Program Files (x86)\hsqldb\lib"
java.exe -cp hsqldb.jar org.hsqldb.server.Server --props D:\Daten\hsqldbdata\server.properties

pause


the --props file looks like this:
Code: Select all   Expand viewCollapse view
server.database.0=file:D:/Daten/hsqldbdata/db0/chargen;ifexists=true
server.dbname.0=Charges

server.database.1=file:D:/Daten/hsqldbdata/db1/bema_goz;ifexists=true
server.dbname.1=TEST

server.database.2=file:D:/Daten/hsqldbdata/db2/Kassenbuch;ifexists=true
server.dbname.2=CashRegister

server.database.3=file:D:/Daten/hsqldbdata/db3/journal;ifexists=true
server.dbname.3=Journal

server.address=192.168.15.1
server.acl=D:/Daten/hsqldbdata/acl.txt

I get the same "no socket" error when the server.address entry is missing or plain wrong.

And this is the server.acl referenced in the last line:
Code: Select all   Expand viewCollapse view
allow 127.0.0.1
allow 192.168.15.0/24
allow 192.168.0.0/24

It allows connections from the localhost 127.0.0.1, from 192.168.15.??? and 192.168.0.***
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26410
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Real-Time Data Update/Refresh

Postby DeMiNiX » Fri Sep 18, 2015 9:03 pm

Villeroy wrote:And this is my start.bat:
Code: Select all   Expand viewCollapse view
CD "C:\Program Files (x86)\hsqldb\lib"
java.exe -cp hsqldb.jar org.hsqldb.server.Server --props D:\Daten\hsqldbdata\server.properties

pause


the --props file looks like this:
Code: Select all   Expand viewCollapse view
server.database.0=file:D:/Daten/hsqldbdata/db0/chargen;ifexists=true
server.dbname.0=Charges

server.database.1=file:D:/Daten/hsqldbdata/db1/bema_goz;ifexists=true
server.dbname.1=TEST

server.database.2=file:D:/Daten/hsqldbdata/db2/Kassenbuch;ifexists=true
server.dbname.2=CashRegister

server.database.3=file:D:/Daten/hsqldbdata/db3/journal;ifexists=true
server.dbname.3=Journal

server.address=192.168.15.1
server.acl=D:/Daten/hsqldbdata/acl.txt

I get the same "no socket" error when the server.address entry is missing or plain wrong.

And this is the server.acl referenced in the last line:
Code: Select all   Expand viewCollapse view
allow 127.0.0.1
allow 192.168.15.0/24
allow 192.168.0.0/24

It allows connections from the localhost 127.0.0.1, from 192.168.15.??? and 192.168.0.***


That was it. That did the trick. I needed to add those commands in my properties file, and create the acl file that allowed the connections. I am now able to simultaneously enter data from multiple users, and the data will show (once everyone refreshes their page, but I'll add an auto-refresh script).

Thank you so much Villeroy and Sliderule for your help! You two are awesome! I couldn't have done it without you!
OpenOffice 4.1.1, Windows 7 Home Premium 64-bit
DeMiNiX
 
Posts: 4
Joined: Fri Sep 18, 2015 2:37 pm


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest