SQLite - how to call .sql text file to create tables

Databases
1

I can’t seem to find the answer in the docs and would appreciate someone pointing me in the right direction.

I’ve created a text file named, “CreateAccounts.sql” which starts with

PRAGMA foreign_keys = ON;

and followed by numerous CREATE TABLE stubs

When using the Sqlite3.exe command program and reading in the sql file, all the tables are created. All works.

How do I do this in Xojo?

Steve.

2

Using SQLExecute. First load the text file into a string, and then pass the string to the method.

  • Karen
3

[quote=125343:@Karen Atkocius]Using SQLExecute. First load the text file into a string, and then pass the string to the method.

  • Karen[/quote]

Thanks Karen.

I assume using the TextInputStream and .ReadLine methods to build the string. It’s been a while, quick search of the docs for reading text files.

Steve.

edit:
ps. Thanks Karen, it worked

4

Karen, if the sql file have multiple sql statement, would one SQLExecute be enough??

5

As long as each statement ends with a “;” a single sqlexecute will be enough. But sending each statement with error checking will be better - you get granular error checking rather than on the entire file.

6

so can probably use SPLIT() with ; as the parameter to split up the sql command.

7

If it is well tested database schema used to setup/create your database, you are probably fine just sending the whole string to SQLExecute, no real need to check each line. If 1 statement or the whole thing fails, you have issues which likely can not be solved by knowing exactly which statement failed.

Perform the entire thing in a transaction as well. That way if it does fail, it can be rolled back, in the event it was a schema migration for example.

8

I would split the SQL into separate SQLExecutes. that way you can check for errors. @Jeremy Cowgar is right that if it well tested SQL then you can do it in one step. But I prepare for the worst when it comes to SQL. Have been bitten too many times.

9

The real question is what are you going to do if a single item fails? Also, what are you going to do with the error information? Is it going to be displayed to the user? Is an automated system in place to forward you the complete details, etc…

Trapping an error is one thing. Doing something valuable with it is something totally different and often times the most difficult thing to do correctly. Telling the user, "SQL Error: " + db.ErrorMessage is of no help, and potentially dangerous. Database error information should never be shown to the user in practice. The user would be better off to see "Could not create the database, an internal schema error occurred. Please contact the developer.". Now, it may be permissible to log the database error and be able to instruct the user on how to forward the log to you.

You have to figure in a situation like this, you are creating a new database, hence empty. The database create is going to succeed or fail based on permissions more than likely. Once the database is indeed created, the call to .SQLExecute is made. If that call fails, there is a programmer error at work here, i.e. bad SQL to start with. Trapping which item actually failed is of little value.

Now, let’s say you are adding a new company which involves creating a company record, a log record, an owner record, etc… Those for sure should be individual SQL calls and errors trapped individually. If one fails in those situations, it is probably due to a lack of data validation, i.e. Address is NOT NULL but you allowed an empty address. You could revert the insert, tell the user “Address must be filled in” and the retry the operation. If you do it in one large bunch, that gets much more difficult to handle properly.