Ask Your Question
0

Base throws general error when running GetConnection

asked 2016-12-10 03:01:12 +0200

LeftyMaus gravatar image

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.

edit retag flag offensive close merge delete

Comments

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

LeftyMaus gravatar imageLeftyMaus ( 2016-12-10 04:59:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-12-10 03:54:45 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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.

LeftyMaus gravatar imageLeftyMaus ( 2016-12-10 04:19:07 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2016-12-10 04:22:19 +0200 )edit

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 gravatar imageRatslinger ( 2016-12-10 04:30:56 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2016-12-10 04:42:44 +0200 )edit

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.

LeftyMaus gravatar imageLeftyMaus ( 2016-12-10 04:53:20 +0200 )edit

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 gravatar imageRatslinger ( 2016-12-10 05:02:49 +0200 )edit

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?

Ratslinger gravatar imageRatslinger ( 2016-12-10 05:10:08 +0200 )edit

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 gravatar imageLeftyMaus ( 2016-12-10 05:21:07 +0200 )edit

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

LeftyMaus gravatar imageLeftyMaus ( 2016-12-10 05:23:54 +0200 )edit

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 gravatar imageRatslinger ( 2016-12-10 05:26:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-10 03:01:12 +0200

Seen: 530 times

Last updated: Dec 10 '16