Base throws general error when running GetConnection

[LeftyMaus]

I’ve just installed Libre Office 5.2.3.3, and I am unable to run a macro. It was running without error yesterday, but won’t run today. The problem appears to be when the macro tries to process the standard function GetConnection(). When I attempt to run the macro, I get the error:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: General error: org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException: .

This message is not very helpful. It throws the error when it gets to the line containing getConnection. I’d like to find out what the problem is so I can fix it.

Sub UpdateSQL
    rem Run an SQL command on a table in LibreOffice Base
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    
    rem identify absolute address of database
    DatabaseAddress = "file:///C:/Douments and Settings/Administrator/Desktop/punchclock_v0.0.1.odb"
    
    rem create database object
    Db = Context.getByName(DatabaseAddress)
    
	rem connect to the database
    Conn = Db.getConnection("","") 

    rem create statement object
    Stmt = Conn.createStatement()

	rem get date value
	dim valDATE as date
	valDATE=now()

	rem formt as yyyy-mm-dd hh:mm:ss.fffffffff
	dim strDATE as string
	strDATE=format(valDATE,"yyyy-mm-dd hh:mm:ss") 
	
	rem compose SQL statement
	rem insert date values into table
	strSQL = "INSERT INTO ""tblClock"" (""PunchIn"",""PunchOut"") VALUES ('" + strDATE + "','2016-03-28');"
	
	rem execute SQL statement
    Stmt.executeUpdate(strSQL)

    rem close the databae connection
    Conn.close()
    
    rem notify the user
    msgbox("table updated")
End Sub

For what its worth, when I first started building the macro, I was getting the same error. But somehow I resolved it. The best I can recall is that I was attempting to troubleshoot by stepping though the macro. With the LibreOffice BASIC editor open, the save button changed to a save button with a star superimposed. Once I clicked the save icon, that seemed to have fixed it (for the time being).

Now the problem is back.

[LeftyMaus]

note: file:///C:/Douments and Settings/... is misspelled. Should be file:///C:/Documents and Settings/...

[Ratslinger]

Again I got this to run on first try. As mentioned before the change that works is using the registered DB name. Using an absolute (“File:///…”) throws an exception. Only other change made was actual SQL to update a table I already had.

[LeftyMaus]

I’m not so sure the absolute reference is causing the problem. Even now the performance is erratic. If I open LibreOffice BASIC editor and run the macro directly (F5), then I get the error. But if I activate the macro via a form button, it works.

[Ratslinger]

Sorry, I forgot to mention I also needed user “SA” for the test but that depends entirely on what you have set up.

[Ratslinger]

Just retested. Both form button and F5 react identically - absolute doesn’t work, registered name works. I’m running LO v5.2.3.3 on Mint 18 Cinnamon.

[Ratslinger]

Another test. This time changed DB and absolute and registered name work both with button AND F5. I had to be doing something wrong with the absolute before.

[LeftyMaus]

I’d like to try your suggestion using the registered DB name. But how exactly do I find it, and use it correctly? DB = Context.getByName(“RegisteredName”) gives me an error.

[Ratslinger]

From the menu Tools->Options, then in left pane under LibreOffice Base select Databases. Right pane now shows registered databases. If yours are not listed, New... , in dialog find your database and Open then accept the registered name suggested or put in your own. In the testing I was using DB’s that were both registered.

[Ratslinger]

I believe you’ve been right. Just deleted registration for DB I was connecting to and absolute still works with button and F5. What OS are you running? You mentioned you just updated LO. Where did you get the update from - LibreOffice , distro?

[LeftyMaus]

I’m running Microsoft XP on my old desktop machine. The file I downloaded is named LibreOffice_5.2.3_Win_x86.msi. I got it from https://www.libreoffice.org/download/libreoffice-fresh/.

[LeftyMaus]

I downloaded it on December 5, 2016. The filesize is about 212 Meg.

[Ratslinger]

Although rare, it’s always possible there is a bug in the Windows version. Seen at least once before. Seen more in distro versions (although only a few times) since they are second hand. Again, just a rare possibility.

[Ratslinger]

Another possibility is Java. What version do you have there? Don’t know if newer LO has other requirements. Running 1.8.0_111 openjdk here. I’m sure you should be at least ver 7.