Can we write codes of Basic on LO Calc in order to save/edit/read data on embedded Firebird of LO Base?
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.
Can you please give us the sample code for creating an embedded Firebird database together with a table from Calc after checking existing?
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.
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?
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).
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.
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.
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.
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.
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.
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.
This being an embedded DB, the Base file must be open to access the data when using this macro connection. When it is not opened the database and data is in a compressed form.
Another possibility you may want to consider is the use of a form. Testing shows this works without the Base file being open. Although, and this leads back to another comment of yours, When Calc was open and record updated, tried opening Base to view record. All OK. Closed Base. Back to Calc to enter another record and got the error - Component is already disposed..**. Had to close all & opening Calc all worked again.
Using this concept, I was able to use data from cells and through a macro create new records. Not certain just exactly what or how you are using this as it can affect the macro design. Guess a better explanation of exactly how you intend this to work is needed.