Hello
Is there a way to create a button to execute a SQL instruction.
The sql that I need to execute is:
UPDATE "CHECKLIST AAI"SET "CHECK" = fALSE
UPDATE "CHECKLIST AAI"SET "OK" = fALSE
UPDATE "CHECKLIST AAI"SET "NA" = fALSE
Thanks
[Solved] Button with a macro to execute SQL
[Solved] Button with a macro to execute SQL
Last edited by jrubiob on Tue Jun 06, 2023 12:42 pm, edited 1 time in total.
Libreoffice 7.5.3 Windows 11
Re: Button with a macro to execute a SQL instruction
Put the SQL commands into the button's "additional info" field, separated by semicolons.
Code: Select all
Sub RunSQLButton(e)
Const cMaxLen = 1000
Const cTitle = "Command "
oModel = e.Source.Model
frm = oModel.getParent()
oCon = frm.ActiveConnection
aTags() = split(oModel.Tag, ";")
n = uBound(aTags)
for i = 0 to n
s = aTags(i)
sMsg = s
if len(s) > cMaxLen then sMsg = Left(s, cMaxLen) & cHR(10) &" [...]"
if len(s)>0 then
x = Msgbox(sMsg, 35, cTitle & i +1 &"/"& n +1 )
if x = 2 then exit sub 'Cancel
if x = 6 then'Yes
oStmt = oCon.prepareStatement(s)
on error goto errMsg
r = oStmt.executeUpdate()
Msgbox r &" records affected", 64, cTitle
endif
endif
next
exit sub
errMsg:
error(err)
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Button with a macro to execute a SQL instruction
Yes, it's definitely possible to create a button to execute your SQL commands in LibreOffice. Villeroy's solution is a good one. You'd basically put your SQL commands into the button's "additional info" field, each separated by a semicolon.
Just make sure that you have a valid database connection setup, because the macro is trying to use the connection from the form's "ActiveConnection" property.
If you're not familiar with writing macros, you might find the syntax a bit daunting. Don't worry, it's a learning process! This LibreOffice Macro Guide : https://documentation.libreoffice.org/e ... ted-guide/ might be helpful to understand how to get started with macros.
Just make sure that you have a valid database connection setup, because the macro is trying to use the connection from the form's "ActiveConnection" property.
If you're not familiar with writing macros, you might find the syntax a bit daunting. Don't worry, it's a learning process! This LibreOffice Macro Guide : https://documentation.libreoffice.org/e ... ted-guide/ might be helpful to understand how to get started with macros.
OpenOffice 4.1.14
OS
OS
Re: Button with a macro to execute a SQL instruction
Villeroy and Cazer. thanks to you both
It worked great.
Thanks.
It worked great.
Thanks.
Libreoffice 7.5.3 Windows 11
Re: Button with a macro to execute a SQL instruction
If you are very sure about the consequences of your mass updates, you can comment out the msgbox lines and set x=6.
A silent version of the same:
A silent version of the same:
Code: Select all
Sub RunSQLButton_silently(e)
Const cTitle = "Command "
oModel = e.Source.Model
frm = oModel.getParent()
oCon = frm.ActiveConnection
aTags() = split(oModel.Tag, ";")
n = uBound(aTags)
for i = 0 to n
s = aTags(i)
if len(s)>0 then
oStmt = oCon.prepareStatement(s)
on error goto errMsg
r = oStmt.executeUpdate()
REM Msgbox r &" records affected", 64, cTitle
endif
endif
next
exit sub
errMsg:
error(err)
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice