SQLite large input

[dexto]

I am trying to store a file larger then 1MB in a SQLite db.

Problem is that even though by default SQLite field can hold up to 950 MB or so and maximum length of an SQL statement is 1000000 (~970KB).

There is a way described for C/C++ (http://www.sqlite.org/limits.html):

• 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 1000000. You can redefine this limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824.

  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 is 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.

Is it possible to use SQLite "host parameters" in AutoIT with "sqlite.au3"?

Edited July 9, 2011 by dexto

[Zedna]

Is it possible to use SQLite "host parameters" in AutoIT with "sqlite.au3"?

Yes but somebody must firstly write AU3 wrappers for sqlite3_bind_XXXX() functions and sqlite3_limit(db,SQLITE_LIMIT_SQL_LENGTH,size) function.

SQLite.au3 UDF is AU3 wrapper only for base SQLite.dll functions.

[jchd]

@dexto,

I've no problem making the needed wrapper functions publickly available, but my first question is why do you feel it's needed.

SQLite per se has no problem holding very large fields or multi-hundreds-Gb of data and still be more efficient than most other RDBMS engines.

As a general rule of thumb, you should store filenames in a column instead of large blobs/files (even if I find it doubtful to call 1Mb "large").

The reason behind is that doing so slows down very significantly your SQL queries which can't use an index and result in a full table scan.

There is a strong (albeit complex) relationship between efficiency, page size, cache size and average/maximum row sizes.

In short, if at all possible, depending on your actual use case, avoid storing large blobs/fields and store filenames instead.

I agree that it's not possible in all situations. Can you give reasonable evidence this is your case?

If a firm YES, then making the DllCalls needed is a piece of cake, but not something for most users so I don't find it necessary to make the set of binding functions available in the mainstream UDF (too much risk of confusion/errors by casual users).

I'm fully aware that binding values is the obvious step for protecting against SQL injection, but bare SQLite is not for use over a network, lest untrusted network.

Also the need to bind every discrete value into every SQL statement proves less efficient than using the current interface. This is due to significant overhead introduced by DllCalls and friends, compared to direct C API use from a compiled language.

[dexto]

@dexto,

...why do you feel it's needed.

SQLite per se has no problem holding very large fields or multi-hundreds-Gb of data and still be more efficient than most other RDBMS engines.

As a general rule of thumb, you should store filenames in a column instead of large blobs/files (even if I find it doubtful to call 1Mb "large").

The reason behind is that doing so slows down very significantly your SQL queries which can't use an index and result in a full table scan.

There is a strong (albeit complex) relationship between efficiency, page size, cache size and average/maximum row sizes.

...

You are definitely right about large fields in DB being a bad practice.

In my case I'm using SQLite as a buffer in net server application with timeout of 1-10 min. after which buffer is emptied. Also the file (buffer) would never be more then 15MB its just 950KB is a bit to small. As far as performance application vacuums, re-indexes the database every now and then.

I would be also thankful if you can tell me where to start in creating. (never wrote API calls before)

EDIT: Also, as a bonus of using SQLite for tis prevents fragmentation since deleting data from DB will not reduce the database size on the disk (until vacuum)

Edited July 9, 2011 by dexto

[dexto]

ok... here we go.... :S

Example is work in progress... or work till the limit of examples I could salvage.

Thoughts? Suggestions?

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
_SQLite_Startup()
_SQLite_Open() ; open :memory: Database
_SQLite_Exec(-1, "CREATE TABLE aTest (id,data, PRIMARY KEY(id));") ; CREATE a Table

; payload
Local $largedata = ''
For $i = 0 To 15625; 1000064 bytes
    $largedata &= 'Lorem Ipsum  Lorem Ipsum  Lorem Ipsum  Lorem Ipsum  Lorem Ipsum '; 64 bytes
Next

$length = BinaryLen($largedata)
ConsoleWrite(StringFormat('Data length is %s bytes. \r\nNeed to use Bind? %s\r\n', $length, ($length > 1000000)))

Local $hQuery, $aRow, $sMsg
; bind var
_SQLite_Query(-1, "INSERT INTO aTest (id,data) VALUES (1, ?)", $hQuery) ; the query



;$encodedData = _SQLite_Encode($largedata); no need to encode the sqlblob is there?

; bind var assign
; call int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
_SQLite_Bind_Blob($hQuery, 1, $largedata, $length)

Func _SQLite_Bind_Blob($hQuery, $paramterIndex, $largedata, $bytes)
    If __SQLite_hChk($hQuery, 7, False) Then Return SetError(@error, 0, $SQLITE_MISUSE)

    ; TODO
    ;   last paramtere in dll call needs "SQLITE_STATIC" dont know how to pass
    ;               passing int 0 instead..?
    ;           #define SQLITE_STATIC   ((sqlite3_destructor_type)0)

    Local $vResult = DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_bind_blob", "ptr", $hQuery, "int", $i, "ptr", $largedata, "int", $bytes, "int", 0)
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error

    Return SetError(0, 0, $SQLITE_OK)
EndFunc   ;==>_SQLite_Bind_Blob



; Fetch data shuld exetute bind properly?
If _SQLite_FetchData($hQuery, $aRow, False, True) = $SQLITE_OK Then
    MsgBox(0, 'sql bind', 'horay')
Else
    MsgBox(0, 'sql bind', 'hm...')
EndIf

_SQLite_Close()
_SQLite_Shutdown()

Edited July 9, 2011 by dexto

[jchd]

I've done some work (untested as yet) in the direction you ask for.

Here it is. I'm sorry to be unable to even perform the first basic syntax check on this.

I'm currently quite busy with an important customer and I don't know when I'll be able to correct unavoidable cust&paste basic errors in the code provided.

I apologize for that inconvenience. Only try and carefully check with sample DB, not production (if ever it passes syntax check!).

I sincerely hope you can fix typos and basic errors by yourself. You'll need to refer to SQLite official docs to fully grab how this is supposed to work, but I've made some efforts to make it the most user-friendly as possible in _most_ situations.

Don't hesitate to pinpoint where I've gone wrong so that can benefit others in the future.

 

EDIT: revised and fixed version

See fixed version below.

Example usage:

testSQLbind.au3

 

Edit2:

Forgot to mention to those interested that parameter names can contain any "letter" Unicode codepoint, so @अआइईउऊऋ is a perfectly valid parameter name, like :Ŵœƣƨƹdž or $צהחלשױך ...

That feature makes it easy to give explicit names to the parameters whatever your native script is. Most symbol codepoints work as well, like @∴∃μ∈ℤ∕μ≌ℏ∬Ɣ⌊x⌋dx (yes that's a valid name!)

SQLiteBind.au3

Edited May 2, 2015 by jchd
Fixed SQLiteBind.au3 for up-to-date SQLite.au3

[dexto]

I've done some work (untested as yet) in the direction you ask for.

Here it is. I'm sorry to be unable to even perform the first basic syntax check on this.

I'm currently quite busy with an important customer and I don't know when I'll be able to correct unavoidable cust&paste basic errors in the code provided.

I apologize for that inconvenience. Only try and carefully check with sample DB, not production (if ever it passes syntax check!).

I sincerely hope you can fix typos and basic errors by yourself. You'll need to refer to SQLite official docs to fully grab how this is supposed to work, but I've made some efforts to make it the most user-friendly as possible in _most_ situations.

Don't hesitate to pinpoint where I've gone wrong so that can benefit others in the future.

Hey its understandable. Thank you. Lets see if we can make it work

EDIT: wow... That is some "no basic syntax check" coding... I wish all of my code was like that.

Edited July 9, 2011 by dexto

[jchd]

I don't let you down with the baby, just being too busy by myself (need to eat as most of us!).

I'll stand by with you, but there's "some" jet lag between us...

[dexto]

I don't let you down with the baby, just being too busy by myself (need to eat as most of us!).

I'll stand by with you, but there's "some" jet lag between us...

anyhow.. the baby is doing great. Humbling experience to read it.

Edited July 10, 2011 by dexto

[jchd]

Do you mean it _really_ works out of the download? I'm the first to be surprised!

Anyway, let me know here if something goes astray, it should be rather easy to fix it.

Good luck, I'm only there for a minute. Bye.

BTW don't forget that bindings are _not_ cleared by invoking _SQLite_QueryReset. Only the included _SQLite_ClearBindings does that. This is from SQLite design, not a personal choice.

Edited July 9, 2011 by jchd

[dexto]

Do you mean it _really_ works out of the download? I'm the first to be surprised!

Anyway, let me know here if something goes astray, it should be rather easy to fix it.

Good luck, I'm only there for a minute. Bye.

BTW don't forget that bindings are _not_ cleared by invoking _SQLite_QueryReset. Only the included _SQLite_ClearBindings does that. This is from SQLite design, not a personal choice.

Got it.. (i think)

Tests: IT WORKS

Tested so far

_SQLite_Bind_Numeric()

Edited July 10, 2011 by dexto

[dexto]

Numeric works. String and blob binds properly and SQL executes OK but returns "" when requested.

EDIT: string is in database its just not showing on return. blob is 0x00 in database.

expandcollapse popup

#include <SQLiteBind.au3>
#include <SQLite.dll.au3>
_SQLite_Startup()
_SQLite_Open() ; open :memory: Database
_SQLite_Exec(-1, "CREATE TABLE aTest (id, integer, string, blob, PRIMARY KEY(id));") ; CREATE a Table


Local $hQuery, $aRow, $iRval
; bind var
assertSQL(_SQLite_Query(-1, "INSERT INTO aTest (id, integer, string, blob) VALUES (1, ?, ?, ?)", $hQuery), '_SQLite_Query') ; the query

assertSQL(_SQLite_Bind_Numeric($hQuery, 1, 12345), '_SQLite_Bind_Numeric')

$string = "I'm a string"
assertSQL(_SQLite_Bind_String($hQuery, 2, $string), '_SQLite_Bind_String')

$binData = StringToBinary("Hi. How are you doing this?")
assertSQL(_SQLite_Bind_Blob($hQuery, 3, $binData), '_SQLite_Bind_Blob')


; Fetch data shuld exetute bind properly?
_SQLite_Exec(_SQLite_FetchData($hQuery, $aRow), '')

_SQLite_ClearBindings($hQuery); clean the bindings
_SQLite_QueryFinalize($hQuery); finalise query



assertSQL(_SQLite_Exec(-1, "SELECT id, integer, blob FROM aTest LIMIT 1;", 'SQLout'), '_SQLite_Exec')

Func sqlout($aRow)
    ConsoleWrite(StringFormat('\r\nDATA:\r\n"%s"\t"%s"\t"%s"\t"%s"\r\n\r\n', $aRow[0], $aRow[1], $aRow[2]))
EndFunc   ;==>sqlout


_SQLite_Close()
_SQLite_Shutdown()


Func assertSQL($iRval, $msg = '')
    If $iRval <> $SQLITE_OK Or $iRval <> $SQLITE_DONE Then
        ConsoleWrite(StringFormat('%s \tOK \tcode[%s]\r\n', $msg, $iRval))
    Else
        ConsoleWrite(StringFormat('%s \tERROR \tcode[%s]\r\n', $msg, $iRval))
        Exit
    EndIf
EndFunc   ;==>assertSQL

Edited July 10, 2011 by dexto

[jchd]

Hi dexto,

I've posted a fixed version in the post #6 with an example of usage.

Tell me if you have any issue with this.

[dexto]

Hi dexto,

I've posted a fixed version in the post #6 with an example of usage.

Tell me if you have any issue with this.

Thank you. Works like a charm!

I think this is worthy of being included into official UDF.

[jchd]

I'm afraid it would be way too confusing for most new users. Anyway it's now done and available (bugs included free of charge, of course).

Please report any problem by PM.

[Skysnake]

Awesome jchd!  You are a star!

I was looking at the SQLITE C example for Binding Values To Prepared Statements and thinking how does that translate to Autoit, and you have made it easy.  

int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));

 I would like to suggest however that this is included in the standard release.  Perhaps with updates in the documentation?  The standard (as it is now) is easy and fast, while binding variables is obviously the better, safer way of doing things...  Change the documentation to state that bindings are for advanced users? Or put this in the wiki?

Great work. Thank you.

[jchd]

Indeed, binding is much better and faster when the application is in C or some other fast language. Unfortunately, the burden of going thru DllCall so many times considerably increases the time spent in processing SQLite commands and queries, up to take much longer than preparing the statement at every invocation.

Add to this that binding requires in practice a new function for every statement (else linear code is awful) and don't forget that most users here have little to no experience with DB engines. I don't say binding in AutoIt SQLite is useless, but it should only be used by advanced users in specific situations where it actually offers benefits. This is a niche and I do believe that including bindings in the standard UDF would only confuse more users.

There are other things that need change in the standard UDF, like getting correct types, including NULL.

[Iczer]

I'm somewhat confused about Maximum Length Of An SQL Statement..

is this limit applies to single statement  "INSERT INTO tab1 VALUES(AAA,BBB,CCC);"

or to full length of the string i send to SQLite - "INSERT INTO tab1 VALUES(AAA,BBB,CCC);INSERT INTO tab1 VALUES(AAA,BBB,CCC);INSERT INTO tab1 VALUES(AAA,BBB,CCC);  ... "

Can i have some light on  this?

[jchd]

It applies to individual statements : the second example you give consists of three individual statements which are processed one after the other. I just checked with two statements, each of length > 950000 characters, joined in a single _SQLite_Exec and that works AFAICT.

 

BTW, your second example can be optimized:
INSERT INTO tab1 VALUES (AAA,BBB,CCC), (AAA,BBB,CCC), (AAA,BBB,CCC),  ...

[argumentum]

for the next guy:
this was written for 3.3.4.0, but for 3.3.12.x it needs to change $g_hDll_SQLite to $__g_hDll_SQLite in SQLiteBind.au3