Jump to content
dexto

SQLite large input

Recommended Posts

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 by dexto

Share this post


Link to post
Share on other sites
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.


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
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 by dexto

Share this post


Link to post
Share on other sites
dexto

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

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

Thoughts? Suggestions?

#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 by dexto

Share this post


Link to post
Share on other sites
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 by jchd
Fixed SQLiteBind.au3 for up-to-date SQLite.au3

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
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 by dexto

Share this post


Link to post
Share on other sites
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...


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
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 by dexto

Share this post


Link to post
Share on other sites
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 by jchd

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
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 by dexto

Share this post


Link to post
Share on other sites
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.

#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 by dexto

Share this post


Link to post
Share on other sites
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.


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

Share this post


Link to post
Share on other sites
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.


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


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
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.


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
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?

Share this post


Link to post
Share on other sites
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),  ...


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

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

×