SQLite and SQL scripts

[TunaSalad]

Hi all,

I'm new to the SQLite UDF and I was wondering what would be the best way to input a whole SQL script into SQLite in just one go using the functions provided in the UDF.

For instance, a SQL script like this one:

CREATE TABLE exam (ekey   INTEGER PRIMARY KEY,
                   fn       VARCHAR(15),
                   ln       VARCHAR(30),
                   exam   INTEGER,
                   score     DOUBLE,
                   timeEnter DATE);

CREATE TRIGGER insert_exam_timeEnter AFTER  INSERT ON exam
BEGIN

UPDATE exam SET timeEnter = DATETIME('NOW')
         WHERE rowid = new.rowid;
END;

I know it could easily be done in SQLite's command line interpeter by just typing

$ sqlite3 MyDatabase < MyScript

but I want to accomplish this task using AutoIt code.

Could I read the contents of the SQL script into a string (using FileOpen and FileRead) and then invoking _SQLite_Exec(-1, $sScriptContents) ???

Any advice is appreciated!

Edited February 4, 2008 by TunaSalad

[TunaSalad]

I answer to my self:

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup ()
$hDB = _SQLite_Open ("exam.db")

$sql = _ReadTextFromFile("MyScript")

Local $sOut
_SQLite_Exec($hDB, $sql, $sOut)
_SQLite_Close ()
_SQLite_Shutdown ()


Func _ReadTextFromFile($fileName)
    $file = FileOpen($fileName, 0)
                $text = FileRead($file)
    Return $text
EndFunc

Edited February 4, 2008 by TunaSalad

[PsaltyDS]

I answer to my self:

Glad you figured it out. I didn't know if you could do that or not myself.

Just to tweak it, AutoIt doesn't require you to FileOpen() before FileRead(), so you could shorten that to:

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
$hDB = _SQLite_Open("exam.db")

$sSQLScript = "MyScript.SQL"

Local $sOut
_SQLite_Exec($hDB, FileRead($sSQLScript), $sOut)
_SQLite_Close()
_SQLite_Shutdown()

[TunaSalad]

Thanks for the FileRead hint, didn't know that was posible as the example listed in the Help File for FileRead does use FileOpen before actually reading the file

Cheers!