Question regarding _SQLite UDF.

[Marlo]

So i started using the SQLite UDF which is now shipped with AutoIT along with the a sqlite dll i found on the net and all is working fine but i have a slight issue when it comes to saving a database.

I have 60,000 or so entries which i need to very quickly write to a new SQLite table which can be done within seconds when using a memory database like so

expandcollapse popup

_SQLite_Startup()
If @error Then
MsgBox(0, @error, "Failed to start SQLite")
Return
EndIf

$hDB = _SQLite_Open()
If @error Then
MsgBox(0, @error, "Failed to create memory database.")
Return
EndIf

$Q = _SQLite_Exec($hDB, "Create table '" & $sName & "' (`auc` INT ( 11 ) NOT NULL , `item` INT ( 11 ) NOT NULL , `owner` VARCHAR ( 13 ) NOT NULL , " & _
"`bid` INT ( 11 ) NOT NULL , `buyout` INT ( 11 ) NOT NULL , `quantity` INT ( 11 ) NOT NULL , `timeleft` VARCHAR ( 11 ) NOT NULL)")

If @error Then
MsgBox(0, @error, "Failed to create table")
Return
EndIf



While True

$iItt += 1
$Line = FileReadLine($hFile)
If @error = -1 Then ExitLoop
$Info = StringRegExp($Line, '^.*?{.?auc":(\d*).*?"item":(\d*),"owner":"([\w]+)","bid":(\d*),"buyout":(\d*),"quantity":(\d*),"timeLeft":"([a-zA-Z_]+)"}', 1)
If $Info = 0 Then ContinueLoop

$Q = _SQLite_Exec($hDB, "INSERT INTO '" & $sName & "' VALUES ("&$Info[0]&", "&$info[1]&", '"&$info[2]&"', "&$info[3]&", "&$info[4]&", "&$info[5]&", '"&$info[6]&"');")
If @error Then
MsgBox(0, @error, $Q)
ContinueLoop
EndIf

If Mod($iItt, 100) = 0 Then
_Log($iItt & " itterations so far.")
EndIf

WEnd

But if i want to save the database to disc by using a permanent database like so

$hDB = _SQLite_Open(@scriptdir & "\database.aql")

Then the script takes about half an hour to write all 60,000~ entries to disc.

Is there a way i can use the memory database whilst it processes the information and THEN write the database to disc? I can see no functions related to this in the UDF

[jchd]

You have not less than three clean ways to do what you want.

A.) use the (the most efficient and simplest way)

B.) ATTACH your (or open a new) disk DB and use a CREATE ... AS statement:

CREATE table diskdb.tablename as SELECT * from memdb.sourcetablename;

(this won't copy other parts of the schema).

C.) use your code base but enclose your inserts in a transaction (which you should always choose to do even for a memory DB as soon as you have block inserts/updates).

Don't use single quotes around schema names (e.g. tables, columns, ...). Singles quotes are only used to delimit literal strings in SQL. Single quotes within literal strings MUST be escaped by doubling them (_SQLiteFastEscape).

Don't use back-quotes around schema names: prefer double quotes or square brackets for reserved names or names with spaces.

Edited November 13, 2012 by jchd

[Marlo]

This is awesome. Thanks mate.