Ask Your Question
0

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

asked 2019-10-28 10:01:04 +0100

lonk gravatar image
 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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-29 00:42:28 +0100

Ratslinger gravatar image

updated 2019-11-12 00:28:04 +0100

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)

edit flag offensive delete link more

Comments

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 gravatar imagelonk ( 2019-10-29 06:01:30 +0100 )edit

Dear @Ratslinger ,

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

lonk gravatar imagelonk ( 2019-10-29 08:43:02 +0100 )edit

@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 gravatar imageRatslinger ( 2019-10-29 18:51:38 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-10-29 21:34:55 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-10-30 04:58:34 +0100 )edit

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 gravatar imagelonk ( 2019-10-30 04:59:16 +0100 )edit

Dear @Ratslinger ,

Thank you so much.

Conn.getParent().flush works so well.

lonk gravatar imagelonk ( 2019-11-20 06:53:56 +0100 )edit

Dear @Ratslinger ,

And...

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

lonk gravatar imagelonk ( 2019-11-20 07:29:32 +0100 )edit

@lonk,

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

Ratslinger gravatar imageRatslinger ( 2019-11-20 18:04:22 +0100 )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 ?

lonk gravatar imagelonk ( 2019-11-21 09:11:46 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-28 10:01:04 +0100

Seen: 81 times

Last updated: Nov 12