[Solved] LibreOffice Base - Run SQL in a Macro

Discuss the database features
Post Reply
peterwt
Posts: 35
Joined: Mon Apr 19, 2010 4:04 pm
Location: South Wales

[Solved] LibreOffice Base - Run SQL in a Macro

Post by 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
Last edited by Hagar Delest on Wed Mar 04, 2015 10:53 pm, edited 1 time in total.
Reason: tagged [Solved].
Peter
LibreOffice 4.4.1.2 on Windows 8
User avatar
Greengiant224
Posts: 284
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: LibreOffice Base - Run SQL in a Macro

Post by 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

Win 7, Portable AOO 4.1.14, LibreOffice 6.2, 7.4.5 & 7.6.1 (Java 1.7.81 & 1.8.0_281) utilising HSQL 1.8.10 & 2.4.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
peterwt
Posts: 35
Joined: Mon Apr 19, 2010 4:04 pm
Location: South Wales

Re: LibreOffice Base - Run SQL in a Macro

Post by 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
Peter
LibreOffice 4.4.1.2 on Windows 8
leocam10
Posts: 4
Joined: Tue May 03, 2016 7:53 pm

Re: [Solved] LibreOffice Base - Run SQL in a Macro

Post by 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?
OpenOffice 4.1.2 on Windows 7
JPL
Volunteer
Posts: 132
Joined: Fri Mar 30, 2012 3:14 pm

Re: [Solved] LibreOffice Base - Run SQL in a Macro

Post by 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
Kubuntu 22.04 / LibO 24.2
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
leocam10
Posts: 4
Joined: Tue May 03, 2016 7:53 pm

Re: [Solved] LibreOffice Base - Run SQL in a Macro

Post by 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 !
OpenOffice 4.1.2 on Windows 7
Post Reply