Jump to content
gcue

Array To SQLite

Recommended Posts

Posted (edited)

This function is to convert a 2d array to sqlite database.  Improvement suggestions welcome!  Original idea taken from here

#include <array.au3>
#include <sqlite.au3>

$sqlDB = @ScriptDir & '\test.sql'
FileDelete($sqlDB)

_SQLite_Startup()

$array = Build_Array()
$array_start_index = 0
$table = "aTest"
$columns = "Col1, Col2, Col3"

ArrayToSQL($array, $array_start_index, $sqlDB, $table, $columns)

_SQLite_Shutdown()

Func ArrayToSQL($array, $array_start_index, $sqlDB, $table, $columns)

    Local $hDB = _SQLite_Open($sqlDB)
    Local $ret = _SQLite_Exec(-1, 'CREATE TABLE if not exists ' & $table & ' (' & $columns & ');')

    If $ret <> $SQLITE_OK Then Exit (MsgBox(0, 'SQLITE ERROR', 'Create Table Failed'))

    $columns_array = StringSplit($columns, ",")
    $num_columns = $columns_array[0]

    Local $sSQLHead = 'INSERT INTO ' & $table & ' VALUES '
    Local $sSQLWork = $sSQLHead

    For $x = $array_start_index To UBound($array) - 1
        $sSQLWork &= '("'

        For $y = 0 To $num_columns - 1
            $sSQLWork &= $array[$x][$y] & '","'
        Next
        $sSQLWork = StringTrimRight($sSQLWork, 3)
        $sSQLWork &= '")'

        _SQLite_Exec($hDB, $sSQLWork)
        $sSQLWork = $sSQLHead
    Next

    _SQLite_Close($hDB)

EndFunc   ;==>ArrayToSQL

Func Build_Array()

    Local $iNumRecs = 800
    Local $aTest[$iNumRecs][3]

    For $i = 0 To $iNumRecs-1
        $aTest[$i][0] = Random(100, 199, 1)
        $aTest[$i][1] = Random(500, 599, 1)
        $aTest[$i][2] = Random(300, 699, 1)
    Next
    Debug($aTest)
    Return $aTest

EndFunc   ;==>Build_Array

Func Debug($variable1 = "", $variable2 = "", $variable3 = "", $variable4 = "")

    If IsArray($variable1) Then
        _ArrayDisplay($variable1)
    Else
        If $variable2 <> "" Then
            $variable1 &= @CRLF & $variable2
        EndIf

        If $variable3 <> "" Then
            $variable1 &= @CRLF & $variable3
        EndIf

        If $variable4 <> "" Then
            $variable1 &= @CRLF & $variable4
        EndIf

        ClipPut($variable1)
        MsgBox(0, "Debug", $variable1)
    EndIf

EndFunc   ;==>Debug

 

Edited by gcue

Share this post


Link to post
Share on other sites

Note that this boils down to a single insert with a number of values. Hence it's only one SQLite statement, which is automagically enclosed in its own transaction. Thus no need to wrap it in an outer transaction.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

hmm not sure which you are talking about - can you show me an example?

if there's an easier/faster way im all for it!

thanks for feedback :)

Share this post


Link to post
Share on other sites

YOU posted the example!
I mean you don't need BEGIN ... COMMIT around a single SQLite statement.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

oooo thought you were talking about a different method

thanks

 

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

×
×
  • Create New...