How do I run an SQL file in Base [closed]

edit

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 http://ask.libreoffice.org/en/question/21205/libreofficebase-how-to-execute-sql-from-basic-script/
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 sub

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?