[Solved] Button with a macro to execute SQL

[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

[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

[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.

[jrubiob]

Villeroy and Cazer. thanks to you both
It worked great.

Thanks.

[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