[Solved] Database data connection problem

[Nocton]

I am sharing (via Dropbox) a database in file mode across four PCs, 3 running Windows 7 and one XP. One user (W7) gets the error message:

Macro code error.
Error 1: An exception occurred
Type: com.sun.star.sdbc SQLException
Message: the connection to the data source "FEMCC_file" could not be established..(line:77)

All other users have no problem.
The system is as in DACM's [Tutorial] Splitting an "embedded HSQL database"
I have checked his 'Run' folder (copied from the Dropbox folder) and the files in it. It runs perfectly OK on my PC, so the problem is definitely with his instance of OO.
He has checked that Java is enabled and has uninstalled and re-installed OO, but still the problem persists.

Does anyone have any idea what the problem could be?

Regards
Nocton

[Villeroy]

Tools>Options>Base>Databases should have an entry "FEMCC_file" pointing to the right .odb.

[Nocton]

No, that is not the answer. The database is registered, but registration is normally only needed if one wants to link to external applications such as Calc or Writer?
This is an odd problem peculiar to the particular user. But it would be good to solve it, especially as reinstalling OO does not help.

Regards

Nocton

[Villeroy]

But the error message tells me that something named "FEMCC_file" can not be found. I think this might be a registered name. Otherwise the error message would refer to a full URL like file:///path/FEMCC_file.odb.
What is the code around line 77?

[Nocton]

The Basic editor does not show line numbers, but the relevant code is:

Code: Select all

 'setup Data Source URL
       sURL_prefix = "jdbc:hsqldb:"
       sURL_args = ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
       sURL = sURL_prefix & "file:///" & sPath & dbName & sURL_args
       ThisDatabaseDocument.DataSource.URL = sURL
       ThisDatabaseDocument.DataSource.Settings.JavaDriverClass = "org.hsqldb.jdbcDriver"

sPath is set by:

Code: Select all

 'get the current path to this .odb file
       sPathURL = ThisDatabaseDocument.URL
       sPath = ConvertFromURL(sPathURL)

dbName is set by reading from a table and is correct.
Your reasoning, Villeroy, suggests that sPath could be wrong. However, since it is read from the actual database document it should automatically pick up the correct location. and indeed it does when I run the files from the problem PC on my PC in a Test folder. I cannot conveniently visit the problem PC to look more closely at the problem, so cannot look into the macro operation more closely. However, later this week I shall try to talk the user through setting a breakpoint and watch on the code to see what the value of sURL is on his PC.

Regards,

Nocton

[Villeroy]

OK, there is an external HSQLDB and you want to use it in file mode. Go visit the files of that external HSQLDB. The file name prefixes for the *.script file, the *.properties file and the *.data file should be the same. There should be a foo.script, a foo.properties and a foo.data file and possibly some more foo.* files.
Download download/file.php?id=17008
Do not extract the zip but point the extension installer to it.
Run macro Tools>Macros>Run>Free_HSQLDB>Free_HSQLDB>Main against your odb.
It shows a dialog where the first part allows you to point to the *.script file of your external HSQLDB.
The second part allows you to point this individual database to a distinct hsqldb.jar that should be used exclusively with this database. Without this setting you need to add the desired hsqldb.jar to the Java Class Path in the Java options of OpenOffice, but then you can not use embedded HSQLDB anymore.

This tool of mine applies the right connection settings for this particular database. It is not meant to be run again and again on file open. If it throws some strange error about the type of your document not being a database document, just restart the office with your database document and try again.

[Nocton]

Thank you, Villeroy. But all the things you suggest are correct and the peculiarity of the case is that exactly the same files work OK on three PCs; but only the fourth, as far as can be seen configured the same way and with the same version of OO installed, gives this error. Furthermore, the application was working OK until a few weeks ago. So it must be something has changed on the user's PC, external to the application. As I said, I shall try to talk the user through trapping the error so I can see what if anything is wrong in the value of the variables at the point at which the error occurs.

Regards

Nocton

[Villeroy]

The Basic editor does not show line numbers, but the relevant code is:

Code: Select all

 'setup Data Source URL
       sURL_prefix = "
       sURL_args = ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false"
       sURL = sURL_prefix & "file:///" & sPath & dbName & sURL_args
       ThisDatabaseDocument.DataSource.URL = sURL
       ThisDatabaseDocument.DataSource.Settings.JavaDriverClass = "org.hsqldb.jdbcDriver"

sPath is set by:

Code: Select all

 'get the current path to this .odb file
       sPathURL = ThisDatabaseDocument.URL
       sPath = ConvertFromURL(sPathURL)

dbName is set by reading from a table and is correct.

If sPath looks like "C:\path\FooBase.odb" and if dbName looks like "FooName", then by mere concatenation sURL looks like
"jdbc:hsqldb:file:///C:\path\FooBase.odbFooName; ......"
which would be wrong because we forgot to strip the name of the database document "FooBase.odb"
The system path with backslashes within the URL is correct because this is how HSQL works.

[Nocton]

No, as I said everything works OK on other PCs and the string is correct and looks like:
"jdbc:hsqldb:file:///C:\Test\Run\FEMCC; ......"

Regards

Nocton

[Villeroy]

Wrong Java (64 vs 32 bit), disabled Java, (re)moved hsqldb.jar?

[Nocton]

Wrong Java (64 vs 32 bit)

That was it, Villeroy! The user had recently updated Java but also accepted the option of deleting older versions, which would include his previous 32 bit version used by OO. I got him to definitely install a 32 bit Java and all is now working as it should. Thank you again for your help and persistence. We got there in the end.

Regards,

Nocton