Ask Your Question
0

Calc basic connects Base embedded Firebird

asked 2019-07-07 19:38:39 +0100

lonk gravatar image

Can we write codes of Basic on LO Calc in order to save/edit/read data on embedded Firebird of LO Base?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-07-07 21:13:45 +0100

Ratslinger gravatar image

Hello,

Yes this can be done with Firebird and other databases. It can be done from Calc and other modules. Here is sample code (tested):

Sub UpdateSQL
    Dim Context as Object
    Dim Db as Object
    Dim Conn as Object
    Dim Stmt as Object
    Dim strSQL as String
    oSheet = ThisComponent.Sheets.getByName("Sheet14")
    inv_date = oSheet.getCellRangeByName("A10").value
    TestDate = Format(inv_date, "YYYY-MM-DD")
rem Run an SQL command on a table in LibreOffice Base
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
rem Change this for your registered Base name
    dbNAME = "REGISTERED_DB_NAME"
    Db = Context.getByName(dbNAME)
rem connect to the database
    oHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
    Conn = Db.ConnectWithCompletion(oHandler)
rem create statement object
    Stmt = Conn.createStatement()
rem compose SQL statement
rem insert date values into table (sample data & table)
    strSQL = "INSERT INTO TABLE3 (""NAME"",""Note"") VALUES ('Input 101', " & TestDate & ");"
rem execute SQL statement
    Stmt.executeUpdate(strSQL)
rem close the database connection
    Conn.close()
rem notify the user
    msgbox("table updated")
End Sub

All is done with SQL - UPDATE; INSERT; SELECT

Above reads the contents of a cell on a sheet and uses that as part of the input to create a new record in the Base table.

Routine uses Registered DB name but URL of Base is possible.

edit flag offensive delete link more

Comments

@Ratslinger

Can you please give us the sample code for creating an embedded Firebird database together with a table from Calc after checking existing?

lonk gravatar imagelonk ( 2019-10-14 05:58:26 +0100 )edit

@lonk,

Have never myself using code or seen any even remotely related code for:

creating an embedded Firebird database

Since creating any Base file, regardless of what database it connects to, requires dialog selections this would all need to be investigated.

Even creating a table would be a task. The SQL to create is simple enough but the field types would require some educated guessing.

Ratslinger gravatar imageRatslinger ( 2019-10-14 07:06:55 +0100 )edit

@Ratslinger

Once upon a time, more than 2 decades ago, I used to do that in Lazarus IDE/Free Pascal/Firebird 1.x on my PC. Is it totally different from LO BASIC Calc?

lonk gravatar imagelonk ( 2019-10-15 05:57:52 +0100 )edit

@lonk,

I have no problem using calc and conecting to an existing database - regardless of through Base or directly to a database server. But your question deals with creating an embedded Firebird database. Have even gone through the command line parameters and nothing. Can start the dialog for creating a new Base file but that is as far as it goes.

Creating a table as stated may be tricky (data types & fields sizes in the definition) but after that it is just sending SQL.

Now your Firebird 1.x was an existing container. For Firebird embedded this container needs to be created (Base file).

Ratslinger gravatar imageRatslinger ( 2019-10-15 06:09:47 +0100 )edit

@Ratslinger

Thank you so much again for your concern.

One more question, please.

From your sample code as shown above, I have tried:

dbNAME = "Test_DB"
strSQL = "INSERT INTO Table1 (Field1, Field2) VALUES ('Fruit', " & TestDate & ");"

MsgBox strSQL said:

INSERT INTO Table1 (Field1, Field2) VALUES ('Fruit', 2019-10-14);

But there was an error message:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Table unknown
*TABLE1
*At line 1, column 13
caused by
'isc_dsql_prepare'
.

At the statement:

Stmt.executeUpdate(strSQL)

Help me please.

lonk gravatar imagelonk ( 2019-10-15 08:48:39 +0100 )edit

Hello @lonk,

You have missed (and I wasn't exactly clear either) something important. Depending upon the database used, table and field names as presented will be taken as either all caps or all lowercase unless surrounded by quotes. Surrounded by quotes mixed case can be applied. Now to surround a field by quotes in a quoted string you must surround you fields with double quotes. So your statement should be:

strSQL = "INSERT INTO ""Table1"" (""Field1"", ""Field2"") VALUES ('Fruit', " & TestDate & ");"

When in doubt it is always safer to surround with double quotes.

Ratslinger gravatar imageRatslinger ( 2019-10-15 18:44:49 +0100 )edit

@Ratslinger

Thanks you so much for your explanation.

It's a little bit different from MS Excel VBA. (I usually copy SQL from SQL View of database and place in VBA code.)

And thank you again for being so patient. I'm learning LO Calc BASIC, and I appreciate you for your kind assistance.

lonk gravatar imagelonk ( 2019-10-16 01:52:02 +0100 )edit

Dear @Ratslinger ,

Regarding your code in your answer dated July 8, 2019, I could insert 1 record. But after running Sub UpdateSQL in order to insert the second record. There was an error message mentioning:

BASIC runtime error. An exception occurred Type: com.sun.star.lang.DisposedException Message: Component is already disposed..**

At the line :

Stmt.executeUpdate(strSQL)

I closed and opened Calc file and ran Sub UpdateSQL in order to insert the second record gain.

The error message was still the same.

Help me please.

lonk gravatar imagelonk ( 2019-10-22 12:03:51 +0100 )edit

@lonk,

Have just re-tested the above code (used my registered DB name) as presented. With the exception of modifying the value for the "Name" field (key field in the table) had no problem running the script multiple times.

Only solution is for you to present the code you are using as it must be somehow different in the sequence of events. Would also need basic information on the table being used such a structure of the primary key field.

Ratslinger gravatar imageRatslinger ( 2019-10-22 18:58:32 +0100 )edit

Dear @Ratslinger ,

Thank you so much for your kind assistance. Regarding your advice, I have added a field namely 'ID' with 'AutoNumber/Integer/Primary key'. This matter solves the problem.

But :

1 The database must be opened at all times.

2 Closing database, the code ran smoothly with no error message but no records added into the database.

I prefer no. 2 for the next step and in the near future, I will connect database in file server.

Help me please and thank you in advance.

lonk gravatar imagelonk ( 2019-10-23 13:35:00 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-07 19:38:39 +0100

Seen: 87 times

Last updated: Jul 07