SQL Script vs SQL statement

Databases
1

the SQLExecute and SQLSelect commands seem to be limited to a single SQL operation.
Is there a easy way to execute a “SQL Script”… ie a string that contains multiple SQL commands.

First thought was … no problem. Split by “;”… but that won’t work because a ; could be in a quoted string, or interior to a CREATE Trigger
Second thought… Parse string, breaking on “;” that was not inside quotes, or between BEGIN / END keywords. but that won’t work because of the complexity of escaping quotes not to mentioni SQLlite not caring about the use of Double Quotes vs Single Quotes… It could be done, but rather complex.

2

If I’m creating an entire database structure I tend to make a constant and then do a single SQLExecute (with transaction control). I believe that internally, the semi colon is the delimiter for SQL statements and every database I’ve ever worked on figures it out. Doing them all individually is icky.

3

Yes, that was my point… is there a way to not be “icky” using SQLExecute and/or SQLSelect

4

That was Bob’s point, they are not.

5

ok… then why does this not work

this only executes the first line

alter table a1 rename to a2;
alter table a2 rename to a3;

FYI… with or without BEGIN TRANSACTION

6

no idea but this works

Dim db As New SQLiteDatabase

Call db.connect

db.SQLExecute("create table foo( bar , baz ) ; " + endofline + "insert into foo(bar, baz) values(1,2) ;" + endofLine + "insert into foo(bar, baz) values(3,4) ;")
db.SQLExecute("alter table foo rename To foo_too; alter table foo_too rename To foo_ever;")

Dim rs As recordset = db.sqlselect("select * from sqlite_master")

While rs <> Nil And rs.eof <> True
  
  TextArea1.AppendText rs.field("sql").StringValue + endofline
  
  rs.movenext
Wend

rs = db.sqlselect("select * from foo_ever")

While rs <> Nil And rs.eof <> True
  
  TextArea1.AppendText "bar,baz = " + rs.field("bar").StringValue + ", " + rs.Field("baz").StringValue + endofline
  
  rs.movenext
Wend
7

for all intents I am doing pretty much this

db.SQLExecute(ta.text) // where ta is a text area
8

well I lifted the “alter table” line and put it as the initial value of a text area
altered my code to run “textarea1.text” instead of the original line
still works

guess you’ll have to debug yours to see why as I’ve no additional ideas at this point

9

Dave,

Could it be possible that because line 1 was not committed yet table a2 does not yet exist? (It’s still named a1)

I would try changing the second line to alter table a1 rename to a3;
If the table ends up being called a3 then you know this is the case.

10

I figured it out… :frowning:
Since I don’t know what kind of SQL statement(s) the user is going to type into the TextArea… I used SQLSelect not SQLExecute, because if there is a need to return a recordset I need it… and for my previous testing it worked fine if it was just one statement. And if the SQL statement was not a SELECT statement it returned a NIL Recordset which was fine

11

[quote=328656:@Dave S]this only executes the first line

alter table a1 rename to a2;
alter table a2 rename to a3;[/quote]

I just tried that and it works for me. I’m curious to find out was is going on.

12

HOW did you try it?

13

Dave,

do you tried SQLite on the Terminal (commands stored in a file ?

I spend a bit of time month ago there, but I forgot what I was doing there.

BTW: this is not related at all with Xojo. The text file is directly used by the terminal.

I searched a bit on my HD, but find nothing. I may trashed the testing folder whan I stopped tries…

14

Emile… doesn’t matter what terminal mode does or does not do… thats not the point…

SQLExecute seems to work… but I can’t use that in this situation because sometimes the submitted SQL needs to return a recordset.

As I stated above. SQLSelect given an SQL statement that is NOT a select , executes, and returns a NIL recordset… but it only executes the FIRST statment in the string provided to it.

SQLExecute will execute multiple queries, but cannot return a Recordset

So as an example

Alter table rename a1 to a2;
select * from a2;

there is no way (that I can see to execute that as a script.
SQLSelect would do only the alter table, SQLExecute would not return the required record set.

15

Sorry Dave.

16

I used the example project and changed the create table button to create a table named a1 rather than ‘Team’. In the add records button I added the code to alter the table. If the second alter didn’t successfully change the name to ‘Team’ then the insert would fail.

I wonder how Valentina Studio does this. In Valentina I can execute multiple statements and even multiple selects.

17

The problem with just using SQLExecute on a script is the error routines. I have just tried a large script that I have and added a couple of deliberate errors.

The result is that the errorMessage returned from the routine is the first error it encountered and the rest of the script was not run. In other words the script aborted at the error. Also, it did not rollback.

I appreciate that one should do a rollback and present the first error but, I think, the user should be aware of as much as possible.

In SQLite Control (my free offering) I split the script into the individual statements and SQLExecute each one, trapping the errors as I get so that I can present the user with all the errors encountered. Also, I rollback the whole transaction (the whole script).

As Dave is creating an SQLite manager then I feel that this is the way to go so that the user can play with his script until it runs error free.

18

For anyone who is interested… I have a class (based on the ideas that both Simon and myself posted above), that seems to work properly… The heart of which is the tokenizer I wrote for the SQLFormatter topic a week or so back.

as a test … I passed it this string

s="update mytable set [my variable] = ';x;y  ;z   ;' ; CREATE TRIGGER st_tri after UPDATE of "column2" , "column3" ON stress_test begin SELECT * FROM fred ; END ; Alter table fred rename to suzy ;"

and it properly split it into the correct 3 SQL statements

  • update mytable set [my variable] = ‘;x;y ;z ;’ ;
  • CREATE TRIGGER st_tri after UPDATE of “column2” , “column3” ON stress_test begin SELECT * FROM fred ; END ;
  • Alter table fred rename to suzy ;