Array To SQLite

[gcue]

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

expandcollapse popup

#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 April 30, 2019 by gcue

[jchd]

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.

[gcue]

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

[jchd]

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

[gcue]

oooo thought you were talking about a different method

thanks

 

[gcue]

done -thanks again