I want to run regularly SQL scripts which include UPDATE and other operations not permitted in Queries but which do work when I enter them in the “Run SQL” dialogue box. However I want to avoid having to paste into this box every time so how do I just run a saved file of SQL commands
You can read sql statements from a text file and execute them against the database. Each line of the text file is an sql statement: insert, delete or update. Or read your data in and construct the sql statements in basic code.
sub dosql
rem how to read text file
rem https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=33009
rem how to execute sql
rem https://ask.libreoffice.org/t/libreofficebase-how-to-execute-sql-from-basic-script/4816
rem check database connection
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
ThisComponent.CurrentController.connect
endif
rem full path to text file
txtfile="/home/bill/Documents/okmacro/sqls.txt"
f1 = FreeFile()
Open txtfile for Input as #f1
Do while NOT EOF(f1)
Line Input #f1, s
rem MsgBox(s)
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute(s)
Loop
end subI have been trying to convert MS Access databases to Base with HSQLDB back end. The first step using a Base front end and an Access back end was not too bad apart from the usual syntax peculiarities.
Getting INSERT queries to work from macros with the HSQLdb back end was another matter, until I found this useful post. Here is my modification of whalleys code above:
Sub doSql (s as string)
Rem Runs an SQL command, including INSERT as well as SELECT
Dim oStatement as object
if IsNull(ThisComponent.CurrentController.ActiveConnection) then
ThisComponent.CurrentController.connect
endif
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
oStatement.execute(s)
end sub
'and a little test to show the syntax of the command:
Sub testdoSQL
dim str as string
str = "INSERT INTO ""T1"" (""ID"", ""Val"") VALUES (3,'c')"
doSql(str)
end sub
Now all you have to do is pop the required sql statement into a string variable and use the doSql sub to run it.
Sorry if this is a bot late for allan, but it might help someone else.
Hi allanoptical:
I guess you could always embed your code in a macro script, and then link it to a button in a form dedicated to running your SQL scripts (not a very convenient solution really).
Another idea (never tried) is the store all your code blocks in a dedicated table (with cols say id_cd, code_label, code), and use a macro to execute the code from the selected row.
Perhaps someone else might help with the macro code for these ideas?
Anyway, I also would like to know if there is a simple way to do this! Can anyone else suggest anything? For example is there any way to remove the restriction on running table UPDATE type statements from the regular SQL Base listing?
Many thanks for the useful and prompt answer, it works!!!
There seem to be small differences in format requirements but I can live with these.
However I do think it would be nice if this could be provided as a standard feature.
Allan