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

lonk · October 28, 2019, 9:01am

 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 |

Why must the table namely customerhotline be opened in order to add the records?
On the other hand, running query - there is no need to open even the database. (This is so good.)

Ratslinger · October 28, 2019, 11:42pm

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 the connection):

Conn.getParent().flush

lonk · October 29, 2019, 5:01am

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 ?

lonk · October 29, 2019, 7:43am

Dear @Ratslinger ,

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

Ratslinger · October 29, 2019, 5:51pm

@lonk, comments not a problem.

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

Ratslinger · October 29, 2019, 8:34pm

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

Ratslinger · October 30, 2019, 3:58am

@lonk,

Just had a look at your SQL. Believe you can use this instead:

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

May save a bit of typing.

lonk · October 30, 2019, 3:59am

Dear @Ratslinger ,

Thank you so much in advance for your assistance.

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.

lonk · November 20, 2019, 5:53am

Dear @Ratslinger ,

Thank you so much.

Conn.getParent().flush works so well.

lonk · November 20, 2019, 6:29am

Dear @Ratslinger ,

And…

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

Ratslinger · November 20, 2019, 5:04pm

@lonk,

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

lonk · November 21, 2019, 8:11am

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 ?

Ratslinger · November 21, 2019, 7:24pm

@lonk,

Based upon this comment, it is an entirely different subject than the question. This is now about how to connect without a data source. If this is a direction you want to take, please ask as a new question. It does require different code and as yet have not tested specifically for Firebird.

It also appears in your code the notation about Firebird25 (server?). Are you using a different DB? If so why not v3.x which is the latest and also compatible with Base? Please note all of this in any new question.

I am not clear on where you are going with all this. This is not a good way to go if looking for portability.