Page 1 of 1
[Solved] Button with a macro to execute SQL
Posted: Mon Jun 05, 2023 4:29 pm
by jrubiob
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
Re: Button with a macro to execute a SQL instruction
Posted: Mon Jun 05, 2023 8:20 pm
by Villeroy
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
Re: Button with a macro to execute a SQL instruction
Posted: Tue Jun 06, 2023 10:18 am
by Cazer
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.
Re: Button with a macro to execute a SQL instruction
Posted: Tue Jun 06, 2023 11:30 am
by jrubiob
Villeroy and Cazer. thanks to you both
It worked great.
Thanks.
Re: Button with a macro to execute a SQL instruction
Posted: Tue Jun 06, 2023 11:57 am
by Villeroy
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:
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