# BASIC|Calc connects Base(Firebird): Why must table of database be opened for adding new records?

 Dim oSQL As String : Dim oStatement As Object
oSQL =  "insert into " & _
"""customerhotline""" & " " & _
"(""COMPLAINT"", ""DESCRIPTION"", ""SALESPERSONID"", ""ID"")" & " " & _
"values " & _
"('" & _
sComplaint & "', '" & _
sDescription & "', " & _
sSalespersonID & ", '" & _
"999" & "'" & _
")"
Dim oResult As Object : oResult = oStatement.ExecuteQuery(oSQL)


| Fedora 30 Workstation KDE of Fedora from Spins | LibreOffice 6.2.8.2-2 |

1. Why must the table namely customerhotline be opened in order to add the records?

2. On the other hand, running query - there is no need to open even the database. (This is so good.)

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

This is most likely to be answered by filing a bug report.

Reading records, as in your Query statement, is no problem. However, when writing records the data needs to be committed - applied to the database. With HSQLDB embedded (and other DB connections) this is done automatically when the .odb is closed. Firebird embedded is the exception. For some reason it was chosen that the user must actually save the .odb for the changes to be saved (committed).

Have not found a method to do this from Calc with the Base file opened or closed.

Here is the unusual part. If you have a Base file connected to a Firebird File (this is simply the Firebird DB external to the .odb) then updates to the DB work from Calc even with the Base file closed. There was a fix applied through tdf#106463 and may be a reason this works from Calc even though the actual problem was not related.

Edit 2019-10-29:

First, from comment question, you can create a connection without registering the database. Instead of using the registered name:

dbNAME = "REGISTERED_DB_NAME"
Db = Context.getByName(dbNAME)


use the .odb URL(this is Linux files):

 dbNAME = "file:///home/YOUR_DIRECTORY/YOUR_BASE_FILE.odb"
Db = Context.getByName(dbNAME)


Have also done some further testing using current & older LO versions.

To be clear, Firebird embedded is the problem. Using a current LO v6.3.2.2, HSQLDB embedded is updated from basic code with the Base file opened or closed and using registered or non-registered DB.

Firebird embedded does need the Base file to be opened and saved before the Calc file is closed. It need not be open during the entire process. This is again due to committing the data. Issuing a commit command from Calc SQL creates an error I have not been able to resolve as of yet. Issuing a commit command from Base Tools->SQL has no effect either.

It is important to note, when using this macro code from Calc, if the affected Base file is opened and closed before the Calc file, the connection is fully broken in Calc. Only recovery thus far is to close and re-open the Calc file.

I bring up this commit issue because of the aforementioned connection to using a Firebird file. Reload LO v6.0.2.1 which was before the bug fix noted above. With the Firebird file, could issue a commit command and the data was saved. Going back to the current version, there is no need to issue the command as the data is saved automatically. There is also no need to save the Base file as with the embedded version. Data is automatically saved.

To me this processing is inconsistent and should be reported as a BUG!

Edit 2019-11-11:

There is a workaround to the problem of Firebird embedded needing to be open to update it. This is noted in tdf#128607

After updating with SQL and before closing the connection, issue a flush command (Conn is ...

more

Dear @Ratslinger ,

A Calc user saves or reads the embedded database(HSQLDB/Firebird) via BASIC. Is registering the database an absolute must for connecting via BASIC on Calc ?

( 2019-10-29 06:01:30 +0200 )edit

Dear @Ratslinger ,

Sorry for many comments. Registerring is a must. I tried an unregisterred, the connection failed.

( 2019-10-29 08:43:02 +0200 )edit

In fact you can connect without registering. Will edit my answer shortly with this info and some other testing I have performed.

( 2019-10-29 18:51:38 +0200 )edit

One additional note, was able to submit Conn.commit() (where Conn is the connection in the macro) but it had absolutely no effect.

( 2019-10-29 21:34:55 +0200 )edit

oSQL =  "insert into customerhotline " & _
"(COMPLAINT, DESCRIPTION, SALESPERSONID, ID) "& _
"values ('" & _
sComplaint & "', '" & _
sDescription & "', " & _
sSalespersonID & ", '999')"


May save a bit of typing.

( 2019-10-30 04:58:34 +0200 )edit

Dear @Ratslinger ,

For a temporary fix/solution for an unexpected problem, I switched to registered and embedded HSQLDB.

And after installing JDK, everything went so well for HSQLDB is Java.

But in the future, I will work harder to make a U-Turn to Firebird some day.

Expecting to hearing such a good news from you shortly.

( 2019-10-30 04:59:16 +0200 )edit

Dear @Ratslinger ,

Thank you so much.

Conn.getParent().flush works so well.

( 2019-11-20 06:53:56 +0200 )edit

Dear @Ratslinger ,

And...

Expecting to hearing from you shortly about the good news of connecting .fdb with Calc BASIC.

( 2019-11-20 07:29:32 +0200 )edit

Unsure about your last comment. Thinking .fdb is Firebird DB? Also not certain about what you expect to hear from me.

( 2019-11-20 18:04:22 +0200 )edit

Dear @Ratslinger ,

Yes, it's Firebird database.

I got an error message:

BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: unsatisfied query for interface of type com.sun.star.embed.XStorage!.


from trying:

Function ConnectDB(dbFilename As String) As Object
Dim dbContext As Object : dbContext = createUNOService("com.sun.star.sdb.DatabaseContext")
Dim oDataSource As Object : oDataSource = dbContext.GetByName(dbFilename)
ConnectDB = oDataSource.GetConnection("","")
End Function
Sub Connect_Firebird25_External
Dim db As Object
db = ConnectDB("file:///home/fedora002/Documents/examples.fdb")
MsgBox "Database: examples.fdb is connected."
db.Close
db.Disposed()
MsgBox "Database: examples.fdb is disconnected."
End Sub


Is com.sun.star.sdb.DatabaseContext for only .odb , embedded database ?

( 2019-11-21 09:11:46 +0200 )edit

## Stats

Seen: 113 times

Last updated: Nov 12 '19