_SQLite_Exec - Limit für Befehlslänge?

**BugFix**

Ich stelle zum Befüllen der Tabellen einer SQLite-DB die Queries in einer Funktion zusammen. Dabei hänge ich alle Insert-Befehle hintereinander und führe danach aus:

AutoIt

Function _Insert($_kind, $_sCSV)   
    Local $aLines = FileReadToArray($_sCSV)
    Local $aSplit, $sQuery = 'BEGIN;'
    For $i = 1 To UBound($aLines) -1
        If $_kind = 'Tab1' Then
            $aSplit = StringSplit($aLines[$i], ',', 2)
            $sQuery &= _
            StringFormat("INSERT INTO tab1 VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s');", $i, _
                         $aSplit[0], $aSplit[1], $aSplit[2], $aSplit[3], $aSplit[4], $aSplit[5])
        Else
            $aSplit = _SplitStreetLine($aLines[$i])
            $sQuery &= _
            StringFormat("INSERT INTO tab2 VALUES (%d, '%s', '%s', '%s', '%s');", $i, _
                         $aSplit[0], $aSplit[1], $aSplit[2], $aSplit[3])
        EndIf
    Next
    Local $SQLiteResult = _SQLite_Exec($hSQL, $sQuery & 'COMMIT;')
    If $SQLiteResult <> $SQLITE_OK Then
        ConsoleWrite('!> ERROR! ' & @TAB & 'Inserting data in table [' & $_kind & '] has failed.' & @CRLF & _
        '!> The sql-query is stored to "@ScriptDir\err_insert.txt".' & @CRLF & _
        '!> Check this file.' & @CRLF)
        Local $fh = FileOpen(@ScriptDir & '\err_insert.txt', 2+256)
        FileWrite($fh, 'SQLite-Error = ' & $SQLiteResult & @CRLF & $sQuery)
        FileClose($fh)
        Exit
    EndIf
EndFunc

Alles anzeigen

Tabelle 1 hat nur ein paar Hundert Datensätze, das Insert funktioniert ohne Probleme.

Für Tabelle 2 stelle ich 1,8 Mio Datensätze zusammen. AutoIt hat kein Problem mit der Stringlänge.

Aber das _SQLite_Exec schlägt fehl, Fehlercode = 1 - Error calling SQLite API 'sqlite3_exec'. Natürlich sehr aussagekräftig.

Meine Vermutung ist, dass der Befehlsstring einfach zu lang ist (~103 Mio. Zeichen).

Bevor ich jetzt anfange zu stückeln: Ist euch ein Limit für den Befehlsstring bekannt? Ich möchte mich da nicht rantasten.

**Musashi**

Zitat von BugFix

Ist euch ein Limit für den Befehlsstring bekannt?

Hi BugFix :

(ein reiner Schuss ins Blaue )

Bei https://www.sqlite.org/limits.html findet man folgende Aussage :

Zitat

1. Maximum length of a string or BLOB :

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

-DSQLITE_MAX_LENGTH=123456789

The current implementation will only support a string or BLOB length up to 231-1 or 2147483647.

(... aber das hast Du wahrscheinlich bereits selbst gefunden)

**Kanashius**

Meines Wissens nach müssen nicht alle Inserts in ein _SQLite_Exec um in eine Transaktion ("BEGIN;...COMMIT;") zu kommen.

Du solltest also einfach

AutoIt

_SQLite_Exec($hSQL, 'BEGIN TRANSACTION;')
For $i=0 to 10 Step 1
    _SQLite_Exec($hSQL, StringFormat("INSERT INTO tab1 VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s');", $i, _
                         $aSplit[0], $aSplit[1], $aSplit[2], $aSplit[3], $aSplit[4], $aSplit[5]))
Next
_SQLite_Exec($hSQL, 'COMMIT;')

aufrufen können.

Ansonsten halt in mehrere Transaktionen aufteilen.

Ich weiß nicht, ob die dann trotzdem als ein command gelten, oder nacheinander ausgeführt werden und nur für ein mögliches rollback relevant sind.

Ansonsten dürfte das hier relevant sein:

Code

Maximum Length Of An SQL Statement

The maximum number of bytes in the text of an SQL statement is limited to SQLITE_MAX_SQL_LENGTH which defaults to 1,000,000,000.

If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host parameters for your data. Prepare short SQL statements like this:

    INSERT INTO tab1 VALUES(?,?,?); 

Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It also runs faster since the large string does not need to be parsed or copied as much.

The maximum length of an SQL statement can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,size) interface.

Alles anzeigen

Die länge einer einzelnen insert value (die Musashi erwähnte) dürfte dabei nicht so relevant sein.

Velted

Moin,

wenn Du mit Fehlercode = 1 meinst, dass @error = 1 ist, kann der Fehler aus

AutoIt

    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error

in _SQLite_Exec() stammen. Dann müsste $SQLiteResult den Wert 21 enthalten und in @extended sollte der ursprünglich vom DllCall gesetzte Fehler zu finden sein. Hast Du das mal geprüft?

Bei von SQLite gelieferten Fehlern sollte @error auf -1 gesetzt sein und eine Fehlermeldung per ConsoleWrite() erstellt werden, wenn das Skript nicht compiliert ist,

Concara

Hallo Bugfix!

Ich kann dir nur empfehlen, dass du nach Hausnummer 200000 einen Insert machst.

Ich importiere DNS Logfiles in eine SQLite Datenbank und dort bekomme ich einen Speicherfehler da der Insertstring zu groß wird.

Um diesen zu umgehen habe ich dann eben nach 200K einen insert gemacht und die Insertstring Variable "gelöscht".

Auf dein Beispiel angewendet. (Ist jetzt nicht wirklich getestet aber du verstehst sicher was ich damit meine)

Begin Transaction;

Insertstring

Commit Transaction;

Ist meiner Erfahrung nach die schnellste Methode eines Datenimports.

AutoIt

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <File.au3>

Func _Insert($_kind, $_sCSV)
    Local $aLines = FileReadToArray($_sCSV)
    Local $aSplit
    Local $sQuery
    Local $iCount200K = 0 
    Local $sDatabaseFileName = "C:\Sqlite.db3"; Ich deklariere die meistens Global und dann natürlich nicht in der Funktion
    For $i = 1 To UBound($aLines) -1
        If $iCount200K = 200000 Then ;Nach 200000 inserts werden sie in die Datenbank geschrieben
            _sql_write($sDatabaseFileName, $sQuery)
            $sQuery = ""
            $iCount200K = 0
        EndIf
        If $_kind = 'Tab1' Then
            $aSplit = StringSplit($aLines[$i], ',', 2)
            $sQuery &= _
            StringFormat("INSERT INTO tab1 VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s');", $i, _
                         $aSplit[0], $aSplit[1], $aSplit[2], $aSplit[3], $aSplit[4], $aSplit[5])
                         $iCount200K += 1
        Else
            $aSplit = _SplitStreetLine($aLines[$i])
            $sQuery &= _
            StringFormat("INSERT INTO tab2 VALUES (%d, '%s', '%s', '%s', '%s');", $i, _
                         $aSplit[0], $aSplit[1], $aSplit[2], $aSplit[3])
                         $iCount200K += 1
        EndIf
    Next
    _sql_write($sDatabaseFileName, $sQuery)
EndFunc


Func _sql_write($sDatabaseFileName, $sInsert)
    _SQLite_Startup()
    Do
        Sleep(Random(20, 250, 1))
        _SQLite_Open($sDatabaseFileName)
    Until Not @error
    _SQLite_Exec(-1, "Begin Transaction;")
    If Not _SQLite_Exec(-1, $sInsert) = $SQLITE_OK Then
        ConsoleWrite("SQLite Error " & _SQLite_ErrMsg() & @CRLF)
        _FileWriteLog("Fehler.log", "SQLite Error " & _SQLite_ErrMsg())
        _SQLite_Close()
        _SQLite_Shutdown()
        Return 0
    EndIf
    _SQLite_Exec(-1, "Commit Transaction;")
    _SQLite_Close()
    _SQLite_Shutdown()
    Return 1
EndFunc   ;==>_sql_write

Alles anzeigen

**AspirinJunkie**

Zitat von Concara

Ist meiner Erfahrung nach die schnellste Methode eines Datenimports.

Die meiner Erfahrung nach schnellste Methode eines Datenimportes in sqlite ist die >>.import-Funktion<< des CLI-Tools von sqlite.

Man benötigt also noch die Datei >>sqlite3.exe<< in irgendeinem erreichbaren Pfad (z.b. das Skriptverzeichnis).
Dann kann man das Tool von AutoIt aus über die Funktion >>_SQLite_SQLiteExe()<< verwenden.

Der Ablauf wäre dann folgender: Aus den Daten, welche man importieren möchte eine csv/tsv erstellen (fix in einem Loop erledigt).
Und dann den Import durch die sqlite3.exe erledigen lassen.
Auf die Art umgeht man den Bau des SQL-Strings und braucht sich um dessen Größenbeschränkungen auch nicht mehr zu sorgen.

Der prinzipielle Aufbau ist hierbei recht simpel:

AutoIt

#include <SQLite.au3>

; ---- hier die Daten in eine Datei wegschreiben - z.B. in einem csv-Format

; Import der Daten in die Tabelle "testtable" der Datenbank
Global $s_InputFile_Path = @ScriptDir & "\mydata.txt"
Global $s_DataBase_Path = @ScriptDir & "\mydatabase.db"
Global $s_Out

_SQLite_SQLiteExe($s_DataBase_Path, _
    ".separator ';'" & @CRLF & _
    ".import '" & $s_InputFile_Path & "' testtable" & @CRLF _
    , $s_Out)

Alles anzeigen

**BugFix**

Zitat von AspirinJunkie

Die meiner Erfahrung nach schnellste Methode eines Datenimportes in sqlite ist die >>.import-Funktion<< des CLI-Tools von sqlite.

Klingt gut, Danke. Werde ich testen.