SQLite for OpenG

[Matt W]

That's not the reason

I thought your's had /0 support (maybe it did way back when I wrote mine). I'm not sure where the difference is then (maybe inlining vs subroutine). You still have far nicer icons than does.

[Rolf Kalbermatter]

Because to use the Moveblock you have to use 3 API calls rather than one (Get the pointer, the size and then Move it).

Well you have to have a memory buffer, here string to write into. So you start either with a string that is for sure long enough and most of the times way to long, or you do a first API call to retrieve the necessary buffer length. Then creating that array or string buffer to pass to the retrieval function should be pretty much the same for stings or byte arrays. No need for a MoveBlock usually.

Note: I see that the function in question seems to return a string pointer. so that seems to be the reason for the MoveBlock(). However that function configured to return a string is then very costly as LabVIEW has to scan the whole string for the null byte, allocate a buffer to copy the string into it and return that string buffer. Now there seems to be a function call before to determine the expected size anyhow to verify that the string returned all of the data. So we are at two function calls already. And if then the VI decides that: drat we didn't get all the data, it has to allocate a new buffer anyhow, and copy the data with MoveBlock, and throwing away the incomplete string that was just created. This is really slow and while it works, it's only acceptable if you don't care about performance or it's clear that embedded zero bytes are a big exception.

The retrieve pointer API configured to return a pointer sized integer will cost a few nanoseconds but the same API call configured to return a string will be a lot more expensive because it has to determine the string size, allocate a buffer, and do what you might end up doing with the MoveBlock() once more afterwards. It simply can't be faster than the a seperate MoveBlock() call, when you configure the function to return the pointer itself instead of a buffer because it is in fact simply the execution of a get pointer, allocate string array, MoveBlock the data from the pointer into the string array, with the additional strlen call on the pointer to determine the assumed size of the buffer, that might end up to be to small.

So the solution with always just 3 API calls will be simpler and in the worst case just about as quick but in some cases considerably faster.

[Matt W]

My adaptive method with 20000 small strings (5-8 bytes) takes 46.21 ms with the 3 apis method it takes 54.77 ms so mine is 18% faster. Of course if the strings have /0 in them then things slow down drastically. If every string has a \0 then the 3 api calls is 54.5, while mine is 68.1, so the 3 api method is 25% faster. But I consider \0 to be an exceptional case and I am adding a different interface to handle those if really needed. If the strings are longish (~100 bytes no /0) then mine takes 66.5 ms, the 3 api call takes 75.85 ms so even with the worse strlen overhead mines still 14% faster. The strings need to be about 1700 bytes long before the 3 api and mine are the same speed. At that length with /0 on the end (worst case) mine takes 323 ms instead of 273 ms (18% faster without /0). The ideal solution would be a C wrapper where you could preallocate the string.

[Rolf Kalbermatter]

My adaptive method with 20000 small strings (5-8 bytes) takes 46.21 ms with the 3 apis method it takes 54.77 ms so mine is 18% faster. Of course if the strings have /0 in them then things slow down drastically. If every string has a \0 then the 3 api calls is 54.5, while mine is 68.1, so the 3 api method is 25% faster. But I consider \0 to be an exceptional case and I am adding a different interface to handle those if really needed. If the strings are longish (~100 bytes no /0) then mine takes 66.5 ms, the 3 api call takes 75.85 ms so even with the worse strlen overhead mines still 14% faster. The strings need to be about 1700 bytes long before the 3 api and mine are the same speed. At that length with /0 on the end (worst case) mine takes 323 ms instead of 273 ms (18% faster without /0). The ideal solution would be a C wrapper where you could preallocate the string.

Do you configure the CLNs to execute in any thread? At least the MoveBlock is a safe function to call like that, not sure about the sqlite functions of course. And it would seem you are doing the getSize() call in any case to determine if the String contains the entire data? Also try to set the debug level in the MoveBlock() CLN to low. Once that works there is little benefit in debugging in that function call.

And of course a C wrapper that does the getSize(), DSNewHdl(), getPtr() and MoveBlock() all in one, won't be possible to be beaten by any LabVIEW diagram function.

[Matt W]

Do you configure the CLNs to execute in any thread? At least the MoveBlock is a safe function to call like that, not sure about the sqlite functions of course. And it would seem you are doing the getSize() call in any case to determine if the String contains the entire data?

And of course a C wrapper that does the getSize(), DSNewHClr(), getPtr() and MoveBlock() all in one, won't be possible to be beaten by any LabVIEW diagram function.

All CLN's are configured to run in any thread (it runs very slow without that). I am calling a CLN for the strings proper size in either case. I agree that the performance seems odd but that is how it acts.

Perhaps when reading a string LabVIEW doesn't have to make an immediate copy of it. So maybe the string allocation is masked into a later data copy. But I don't see how LabVIEW could determine that optimization is safe (it would be in my case). Maybe DETT can explain what's going on.

[drjdpowell]

I wouldn't worry too much about performance to begin with. Getting everything mapped out and functioning is (IMHO) more important since the optimisation does not prevent it's use and can take a while due to it being an iterative process (this can be achieved with each stable release).

Hi Shaun,

I agree (which is why I hadn’t spent much time on benchmarking till recently). Getting a working implementation in OpenG is more important, as optimization can happen later. And SQLite is very valuable even when less than 100% optimized; I’ve written a couple of applications with it so far and the speed is not an issue.

If you are looking at making it directly compatible with other apps for viewing, you will need to insert using the "string to UTF8" and recover using the "UTF8 To String" vis as the methods Matt and I use do not honor this. UTF8 Conversion

I’m not sure I want to make that conversion an implicit part of the API. Users may want the full UTF-8 (which I don’t think is recoverable once it goes through "UTF8 to String”). And if they are using regular LabVIEW text (ANSI, I think) then it is a subset of UTF-8. I think it is better to document that the SQLite character encoding is UTF-8 and that ANSI is a subset, and let the User deal with any issues explicitly. Perhaps I should include those conversion primitives in the palettes.

— James

[ShaunR]

Hi Shaun,

I’m not sure I want to make that conversion an implicit part of the API. Users may want the full UTF-8 (which I don’t think is recoverable once it goes through "UTF8 to String”). And if they are using regular LabVIEW text (ANSI, I think) then it is a subset of UTF-8. I think it is better to document that the SQLite character encoding is UTF-8 and that ANSI is a subset, and let the User deal with any issues explicitly. Perhaps I should include those conversion primitives in the palettes.

That's the point. IF you are going to make it viewable by other applications, they inherently assume the encoding by the pragma call (PRAGMA encoding; ). Sqlites default encoding scheme is UTF8 but you can set it to others so text in the DB "should" be one of the defined types (none of which LabVIEW supports natively). If, for example, Chinese characters are inserted ( which, in labview are MBCS) then they will not display correctly in other apps.

Edited July 3, 2012 by ShaunR

[drjdpowell]

That's the point. IF you are going to make it viewable by other applications, they inherently assume the encoding by the pragma call (PRAGMA encoding; ). Sqlites default encoding scheme is UTF8 but you can set it to others so text in the DB "should" be one of the defined types (none of which LabVIEW supports natively). If, for example, Chinese characters are inserted ( which, in labview are MBCS) then they will not display correctly in other apps.

Yes, but can I be certain that the string the User provides is actually meant to be interpreted in LabVIEW's standard encoding? Strings can be anything; LabVIEW only really applies an encoding for display purposes. The User could already be working with UTF-8 or any other encoding, and applying the so-called “String-to-UTF8” function would scramble that.

[Rolf Kalbermatter]

Yes, but can I be certain that the string the User provides is actually meant to be interpreted in LabVIEW's standard encoding? Strings can be anything; LabVIEW only really applies an encoding for display purposes. The User could already be working with UTF-8 or any other encoding, and applying the so-called “String-to-UTF8” function would scramble that.

The old problem of character encoding when it comes to crossing application borders. Why not create two polymorphic VIs. One specifically doing conversion from the current local to whatever the DB is using as default, and one passing the string entirely unaltered for the case where the user knows his data is already in the right encoding. Even more useful although almost impossible to implement fully would be if you can specify the local encoding and the VI does all the necessary conversion to whatever the db encoding is supposed to be. This is already a nightmare to do, when the db encoding stays constant, but if that can be configured too, then OMG!!!

[drjdpowell]

An update on the use of the library path in the CLN node:

I found through testing that some of my subVIs ran considerably slower than others, and eventually identified that it was do to details of how the class wire (from which the library path is unbundled) is treated. Basically, a subVI in parallel to the CLN node (i.e., not forced by dataflow to occur after it) would cause the slowdown. I suspect some magic in the compiler allows it to identify that the path has not changed as it was passed through several class methods and back through a shift register, and this magic was disturbed by the parallel call.

This being a subtle effect, which future modifiers may not be aware off, I’ve rewritten the package to use In-Place-Elements to access the library, thus discouraging parallel use.

The old problem of character encoding when it comes to crossing application borders. Why not create two polymorphic VIs. One specifically doing conversion from the current local to whatever the DB is using as default, and one passing the string entirely unaltered for the case where the user knows his data is already in the right encoding. Even more useful although almost impossible to implement fully would be if you can specify the local encoding and the VI does all the necessary conversion to whatever the db encoding is supposed to be. This is already a nightmare to do, when the db encoding stays constant, but if that can be configured too, then OMG!!!

I’m considering having multiple “Bind Text” versions: "Bind Text (UTF8)”, “Bind Text (UTF16)” (might as well add UTF16 as SQLIte supports it), and “Bind Text (system)” or something like that. And corresponding “Get Column” versions.

[drjdpowell]

In the end I decided to add a “Text Encoding” property to the "SQL Statement” class, with choices of UTF-8, system (converted to UTF-8 with the primitives Shaun linked to), and UTF-16. System is the default choice. I also added the system-to-UTF-8 conversion primitives on all things like SQL text or database names (thanks Shaun).

I also used the sqlite3_errmsg text to give more useful errors (thanks Matt).

[SDietrich]

Patch to allow the loading of extensions, e.g. SPATIALite

Hi all,

my current project demands a lightweight spatial database and so SQLite and SPATIALite came to my mind. Thanks to the excellent work of drjdpowell SQLite integrates smoothly in LabVIEW and with the attachedl VI loading extensions like SPATIALite becomes possible, too.

Simply execute it with "on/off = true" and then run a query like

SELECT load_extension('lib-spatialite-2.dll')[/CODE]

and you have your extension running.

So James, please include it in the next release. Thanks!

Sebastian

Enable Load Extension.vi

[drjdpowell]

SDietrich, please see the updated package in the Code Repository. I have not tested it with an actual extension, so please report any problems. Thanks.

[Matze]

Hello,

 

I'm using your library and it works fine the most time.

 

I do all SQLite Operation sequentially and not in parallel. But sometimes I get this error:

Fehler 1097 bei Knoten zum Aufruf externer Bibliotheken in SQLite.lvlib:Pointer-to-C-String to String.vi->SQLite.lvlib:Connection.lvclass:Prepare.vi:1->SQLite.lvlib:Connection.lvclass:Execute SQL.vi->db_log_data.vi->_Main.viSQL: "SELECT id FROM log_data ORDER BY id ASC LIMIT 1" Mögliche Ursachen: LabVIEW: Es trat ein Ausnahmefehler im externen Code auf, der vom Knoten "Aufruf externer Bibliotheken" aufgerufen wurde. Diese Ausnahme könnte Fehler im Speicher von LabVIEW verursacht haben. Speichern Sie alle Projekte an einem neuen Ort und starten Sie LabVIEW erneut.

This error occures not always within the same VI. Sometimes the error occures within a DELETE query, sometime within a SELECT query.

The VI works for example 1000 times and then the error occures. After restarting my program it works 700 times and the the error occures.

 

Maybe is a synchonisation problem, but I don't think so, because I only use "Execute SQL" sequentially.

 

Do you have any idea what could cause such an error?

 

Regards

Matze

[drjdpowell]

Google translation:

 

 

Error 1097 when Call Library node in SQLite.lvlib: pointer-to-C string to String.vi-> SQLite.lvlib: Connection.lvclass: Prepare.vi: 1 -> SQLite.lvlib: Connection.lvclass: Execute SQL.vi-> db_log_data.vi-> _Main.viSQL: "SELECT id FROM log_data ORDER BY id ASC LIMIT 1"

 

Possible reasons: LabVIEW: There was an exception in the external code that is called by the node "Call Library." This exception may have caused errors in memory of LabVIEW. Save all projects to a new location and restart LabVIEW again.

 

 

 

That error comes for the Call Library Node calling “MoveBlock” in “Pointer-to-C-String to String”.  It’s writing into an array of bytes allocated by LabVIEW, so I can’t see how an error could occur.  And I have not seen this error myself.  Anyone else have this problem?

[Rolf Kalbermatter]

error 1097 means that the function somehow run into a problem like addressing invalid memory. This could happen if you pass a pointer to the MoveBlock function that does not point to memory long enough for the function to work on. For instance if you would pass an LabVIEW String which has been preallocated with 1000 characters and tell it to copy 2 * 1000 = 2000 bytes into it because the source is in Unicode. Or you forgot to preallocated the output buffer altogether or you do some other miscalculation when calculating the number of bytes to copy into the target pointer.

[drjdpowell]

Matze,

Can you probe to see what the pointer is when the error happens?  The 1097 error happens if the pointer is zero.

[rawlukn]

Hello,

 

I'm using your library and it works fine the most time.

 

I do all SQLite Operation sequentially and not in parallel. But sometimes I get this error:

Fehler 1097 bei Knoten zum Aufruf externer Bibliotheken in SQLite.lvlib:Pointer-to-C-String to String.vi->SQLite.lvlib:Connection.lvclass:Prepare.vi:1->SQLite.lvlib:Connection.lvclass:Execute SQL.vi->db_log_data.vi->_Main.viSQL: "SELECT id FROM log_data ORDER BY id ASC LIMIT 1" Mögliche Ursachen: LabVIEW: Es trat ein Ausnahmefehler im externen Code auf, der vom Knoten "Aufruf externer Bibliotheken" aufgerufen wurde. Diese Ausnahme könnte Fehler im Speicher von LabVIEW verursacht haben. Speichern Sie alle Projekte an einem neuen Ort und starten Sie LabVIEW erneut.

This error occures not always within the same VI. Sometimes the error occures within a DELETE query, sometime within a SELECT query.

The VI works for example 1000 times and then the error occures. After restarting my program it works 700 times and the the error occures.

 

Maybe is a synchonisation problem, but I don't think so, because I only use "Execute SQL" sequentially.

 

Do you have any idea what could cause such an error?

 

Regards

Matze

 

I am receiving the same error on the odd occasion using this library. So far it has only occurred when running a built EXE and not when running from source. I have yet to figure out a reproducible way to trigger it:

 

Error 1097 occurred at Call Library Function Node in SQLite.lvlib:Pointer-to-C-String to String.vi->SQLite.lvlib:Connection.lvclass:Prepare.vi->Save data to row.vi->Save test entry to Database.vi->Tests Window GUI.vi->PGAT Main.vi SQL: "UPDATE measurements SET "MUX HV Positive [V]"=? WHERE ROWID=36" Possible reason(s): LabVIEW:  An exception occurred within the external code called by a Call Library Function Node. The exception might have corrupted the LabVIEW memory. Save any work to a new location and restart LabVIEW.

 

Are you receiving it when running from source, compiled, or both?

 

drjdpowell:

If I manage to figure out how to trigger the problem reliably I will probe the pointer and post. Unfortunately, there is a chance this is only a problem for my compiled EXE.

 

Many thanks for putting this library out to the public. It has been very useful to me so far.

[rawlukn]

drjdpowell

 

I just noticed an issue with my code (see image attached) that could be related. I forgot to use a shift register to keep track of the "SQLite Connection" which keeps track of the db handle. Glancing at your code this could be a problem if the db handle changes in a 'Prepare Statement' but then isn't used on the next iteration of my loop.

 

Is it possible that the db handle could change during a singe SQLite session? From a quick read on the sqlite website it doesn't appear to be the case. If it did change I could believe this might trickle down to errors later, but I'd assume the error would original at the library call in 'SQLite Prepare' and not the pointer to C string.

[Phillip Brooks]

I just noticed an issue with my code (see image attached) that could be related. I forgot to use a shift register to keep track of the "SQLite Connection" which keeps track of the db handle. Glancing at your code this could be a problem if the db handle changes in a 'Prepare Statement' but then isn't used on the next iteration of my loop.

 

Is it possible that the db handle could change during a singe SQLite session? From a quick read on the sqlite website it doesn't appear to be the case. If it did change I could believe this might trickle down to errors later, but I'd assume the error would original at the library call in 'SQLite Prepare' and not the pointer to C string.

 

I ran into a problem like this once with the NI DB Toolkit. 

 

The false assumption is that the handle shouldn't change between iterations, so a shift register is not required, correct? WRONG!

 

See: http://lavag.org/topic/15180-you-know-its-gonna-be-a-long-day-when/

[drjdpowell]

I am receiving the same error on the odd occasion using this library. So far it has only occurred when running a built EXE and not when running from source. I have yet to figure out a reproducible way to trigger it:

 

I still cannot see what might be causing this error.  You should defiantly have a shift register in your code, to guard against the case where the for loop is called zero times (is there any LabVIEW developer out there who didn’t learn this the hard way?).  But I can’t see how this could cause the described error.  

 

Here is the code of the “Prepare” method:

 

 

The error can only happen if:

1) sqlite3_prepare_v2 dll call runs without error.

2) “Statement” handle is returned greater than zero.

3) “Remaining SQL tail” pointer returns zero or invalid.

I would expect most problems (invalid dll path, for example) to violate (1) or (2).

[Yair]

The error can only happen if:

3) “Remaining SQL tail” pointer returns zero or invalid.

 

I don't know the SQLite code at all, but a quick look at the image (and the docs¹) brings up the following question²: if the pointer is supposed to return a pointer to "the rest of the SQL", is it OK that you're giving it the full length of the SQL string as the max length? Won't that cause it to read past the end of the SQL string? Is the SQL string guaranteed to have a NULL terminator? Even if it does, does MoveBlock respect that or will it copy whatever's there even if it is a NULL (I'm assuming it would and that then you're potentially reading someone else's memory, which could explain the exception).

 

 

 

1.  at http://www.sqlite.org/c3ref/prepare.html it says:
    

   If pzTail is not NULL then *pzTail is made to point to the first byte past the end of the first SQL statement in zSql. These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled.

    
   
2. Ignoring the fact that the doc only refers to a case where it's not NULL (your initial value is, but that's the ** and it refers to the actual value, so I don't know what's right here) and that I don't understand the distinction it makes between the value and the two layers of indirection (it refers to all three and I'm assuming it's consistent).
   

Edited November 5, 2013 by Yair

[drjdpowell]

 

I don't know the SQLite code at all, but a quick look at the image (and the docs¹) brings up the following question²: if the pointer is supposed to return a pointer to "the rest of the SQL", is it OK that you're giving it the full length of the SQL string as the max length? Won't that cause it to read past the end of the SQL string? Is the SQL string guaranteed to have a NULL terminator? Even if it does, does MoveBlock respect that or will it copy whatever's there even if it is a NULL (I'm assuming it would and that then you're potentially reading someone else's memory, which could explain the exception).

In this code I had the problem that the SQL statement is passed into the “prepare” dll function by pointer, but I don’t have access to that pointer, so I can’t calculate the length of the remaining part of the statement.  So I instead copy the maximum amount, including any invalid junk off the end, and then walk the string till I hit the null terminating byte (done inside “pointer to C string”, where the junk bytes are discarded).  

 

Can “reading someone else’s memory” cause an exception?  If so, I’m surprised I’ve never had this error myself.  If someone can confirm this as the problem, then I can rewrite “Prepare” to convert the LabVIEW string to a pointer first, then do things by pointer.

 

[Yair]

Can “reading someone else’s memory” cause an exception?

 

No idea. My C is limited and rusty enough that I can't say what can actually cause an exception (certainly not inside MoveBlock, which is a black box, although I'm assuming a relatively simple one), so I would just consider that a guess. Like you, I would also expect that if this was possible, then it would be common enough to notice.

 

One option for rawlukn, if this happens often enough, is to log the relevant params when there's an error and then you can at least see what the SQL looks like, what the pointer is, what the size is, etc.

[drjdpowell]

Well, this has just become a lot more relevant for me as a customer just emailed me this: 

 

 

First time I’ve seen it.  I use Windows XP while the client uses Windows 7; I wonder if 7 is more unforgiving of “reading someone else’s memory”?