Executes commands in sqlite3.exe
#include <SQLite.au3>
_SQLite_SQLiteExe ( $sDatabaseFile, $sInput, ByRef $sOutput [, $sSQLiteExeFilename = "sqlite3.exe" [, $bDebug = False]] )
| $sDatabaseFile | Database filename |
| $sInput | Commands for sqlite3.exe |
| $sOutput | Raw output from sqlite3.exe |
| $sSQLiteExeFilename | [optional] Path to sqlite3.exe |
| $bDebug | [optional] Write sqlite3.exe exitcode through the callback function specified in _SQLite_Startup() (default is don't print) |
| Success: | $SQLITE_OK. |
| Failure: | a value that can be compared against $SQLITE_* constants. |
| @error: | 1 - Can't create new Database 2 - sqlite3.exe not Found 3 - SQL error / incomplete SQL 4 - Can't open input file |
Run sqlite3.exe, press enter and type .Help for more info about the SQLite3.exe Commands.
If #include <SQLite.dll.au3> is included the SQLite version is used.
Other versions can be found at the SQLite Web site.
#include <File.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <StringConstants.au3>
; Filenames
Local $sTsvFile = FileGetShortName(_TempFile(@TempDir, "~", ".tsv"))
Local $sDbFile = FileGetShortName(_TempFile(@TempDir, "~", ".db"))
; Create Tsv File
FileWriteLine($sTsvFile, "a" & @TAB & "b" & @TAB & "c")
FileWriteLine($sTsvFile, "a1" & @TAB & "b1" & @TAB & "c1")
FileWriteLine($sTsvFile, "a2" & @TAB & "b2" & @TAB & "c2")
; import (using SQLite3.exe)
Local $sIn, $sOut, $i, $sCreate = "CREATE TABLE TblImport (";
For $i = 1 To _StringCountOccurance(FileReadLine($sTsvFile, 1), @TAB) + 1
$sCreate &= "Column_" & $i & ","
Next
$sCreate = StringTrimRight($sCreate, 1) & ");"
$sIn = $sCreate & @CRLF ; Create Table
$sIn &= ".separator \t" & @CRLF ; Select @TAB as Separator
$sIn &= ".import '" & $sTsvFile & "' TblImport" & @CRLF
_SQLite_SQLiteExe($sDbFile, $sIn, $sOut, -1, True)
If @error = 0 Then
;Show Table (using SQLite3.dll)
Local $iRows, $iColumns, $aRes
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open($sDbFile)
_SQLite_GetTable2D(-1, "SELECT ROWID,* FROM TblImport;", $aRes, $iRows, $iColumns)
_SQLite_Display2DResult($aRes) ; Output to Console
_SQLite_Close()
_SQLite_Shutdown()
Else
If @error = 2 Then
ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF)
Else
ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF)
EndIf
EndIf
; Remove Temp Files
FileDelete($sTsvFile)
FileDelete($sDbFile)
; Output:
; rowid Column_1 Column_2 Column_3
; 1 a b c
; 2 a1 b1 c1
; 3 a2 b2 c2
Func _StringCountOccurance($sSearchString, $sSubString, $iCaseSense = $STR_NOCASESENSE) ; Returns Number of $sSubString in $sSearchString
Local $iOccCnt = 1
Do
If StringInStr($sSearchString, $sSubString, $iCaseSense, $iOccCnt) > 0 Then
$iOccCnt += 1
Else
ExitLoop
EndIf
Until 0
Return $iOccCnt - 1
EndFunc ;==>_StringCountOccurance