Ask Your Question
0

How do I run an SQL file in Base

asked 2014-06-07 18:32:47 +0100

allanoptical gravatar image

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

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
1

answered 2015-01-21 15:13:42 +0100

david.mann.315 gravatar image

updated 2017-01-02 19:30:41 +0100

doug gravatar image

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

edit flag offensive delete link more
1

answered 2014-06-08 01:03:07 +0100

w_whalley gravatar image

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
edit flag offensive delete link more
0

answered 2014-06-08 11:58:10 +0100

allanoptical gravatar image

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

edit flag offensive delete link more
0

answered 2014-06-08 00:22:51 +0100

frofa gravatar image

updated 2014-06-08 00:29:53 +0100

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?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

4 followers

Stats

Asked: 2014-06-07 18:32:47 +0100

Seen: 4,295 times

Last updated: Jan 02 '17