Sign in to follow this  
Followers 0
TunaSalad

SQLite and SQL scripts

4 posts in this topic

#1 ·  Posted (edited)

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 by TunaSalad

Why crabs don't give money to charity..... because they are shell-fish!! PS: Don't be a crab and share your scripts with the community! ;-)

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by TunaSalad

Why crabs don't give money to charity..... because they are shell-fish!! PS: Don't be a crab and share your scripts with the community! ;-)

Share this post


Link to post
Share on other sites

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()

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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!


Why crabs don't give money to charity..... because they are shell-fish!! PS: Don't be a crab and share your scripts with the community! ;-)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0