[Solved] LibreOffice Base - Run SQL in a Macro

[peterwt]

Very new to base but have used MS Access a lot in the past.

I want to run some SQL in a Macro to add a record to a Table.
Simple Table with ID field auto increment Key field and Name text field. The SQL is:-

INSERT INTO "Table1" ("Name") VALUES ('Fred')

This works fine when I use Tools>SQL
So I create a Macro:-

Sub AddRecord

INSERT INTO "Table1" ("Name") VALUES ('Fred')

End Sub

This gives errors that , is required in various places so the SQL line becomes:-

INSERT INTO, "Table1", ("Name"), VALUES ('Fred')
I then get Error "BASIC runtime error.
Sub-procedure or function procedure not defined."

There is obviously more required in the Macro. I have searched for this extensively but can not find any information. I would be grateful for the full Macro details to run this.

Regards

Peter

[Greengiant224]

peterwt:
To insert/update/delete SQL commands from a macro you need
to connect to the database first with a UNO service, then pass the SQL command/s.
The easiest way is to use TOOLS>SQL which you have already done.
Search the forum for database macro's insert sql

Code: Select all

Sub Insert2Table
REM INSERT RECORDS INTO DATABASE TABLE
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get  the database context
DB=Context.getByName("DB1") '<- change database name
Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank

Stmt=Conn.createStatement()
strSQL="INSERT INTO TABLENAME(FIRSTNAME,LASTNAME) VALUES('Humbert','Smithson')"  '<- change tablename, column name/s and data to be inserted
Stmt.executeUpdate(strSQL) '<- update the inserted data

Conn.close() '<- close the db connection
End Sub

Greengiant224

[peterwt]

Hi Greengiant224,

Many thanks for your prompt reply.

I did find a problem with your proposal. When run I got an error "Message: Table not found in statement [INSERT INTO Table1]."
I put a message box to display the variable strSQL after its creation and realised looking at it that the table name and field names in this query need to be in parenthesis. I changed the strSQL creation to:-

strSQL="INSERT INTO ""Table1"" (""Name"") VALUES ('New Name')"

and this works.

Thanks again - I realised from my MS Access experience that the database connection needed to be made but could not see how to do it.

Regards

Peter

[leocam10]

Please, using the posted code, how can i read a value from database. For example, if i want to read the Max value from column in database and insert it in a string var?

[JPL]

If you know how to use the

Code: Select all

DMax()

function in MSAccess, then probably you should have a look at the Access2Base library. Its API emulates a significant part of the MSAccess API.

Documentation is available HERE.

It is available as an extension for OpenOffice or shipped as a standard feature with LibreOffice.

JPL

[leocam10]

Thanks for the reply , JPL ! I managed to find what I was looking for the link, (https://wiki.openoffice.org/wiki/Docume ... ase_Access), especially in relation to the getString () method ... Thanks !