SQLite for OpenG

[Ficare]

Hello,

is it possible to execute some SQL statements on dates, if they are formatted into Blob ?

For example :

SELECT COUNT(*) FROM Results WHERE DATE(LVTime)=CURRENT_DATE

It doesn't seem to work, maybe I do something wrong ?

[drjdpowell]

SQLite's DATE() function works on text formats, including the text format this library can write, but not the Blob format (which is the LabVIEW binary timestamp).

 

You can easily search by time by calling 

SELECT COUNT(*) FROM Results WHERE LVTime>=? and LVTime<?

and binding the two end times.  Bind the end times with the same format that LVTime uses.

[igorbounov]

I've failed to convert binary data to string suitable for insert in a database as BLOB (via Bind Blob property). May be I'm too new to LabView yet.

So would anybody give me a hint how it can be done? Binary data are jpeg picture (from Read JPEG File.vi) and waveform data (from

Sound File Read.vi). I've used the "Flatten to String" function and "Variant to Flattened String" function. And also have tried a combination

of "Read From Binary File" + "Variant to Flattened String" functions.

In any case the blob data seen by DB Browser for Sqlite seem to be essentially different size. And there is no RIFF tag in case of JPEG file blob.

I know that using Blobs for storing data is not a good idea but I need this kind of experience.

Edited February 16, 2018 by igorbounov
mistypes

[FelipeCoutto]

@igorbounov I haven't worked with SQLite for blobs, but had some experience with the NI Database Toolkit. I had to flatten data to a string and then convert the string to a U8 byte array before being able to save it correctly. Maybe that helps 

[igorbounov]

On 16.02.2018 at 10:46 PM, FelipeCoutto said:

I had to flatten data to a string and then

Can you give me an example (to get the idea)?

[FelipeCoutto]

13 hours ago, igorbounov said:

Can you give me an example (to get the idea)?

Something like this should work... I use the NI DB Toolkit and an Oracle database to write to the blob using parameterized queries (you can't write binary data using pure string queries). Maybe with SQLite you have something similar.

[drjdpowell]

16 hours ago, igorbounov said:

Can you give me an example (to get the idea)?

I think you need to provide an example. Otherwise people can only guess what the issue is.  For putting a binary file into an Sqlite blob l would just use "Read from Binary" followed by "Bind Blob".

[igorbounov]

7 hours ago, drjdpowell said:

I think you need to provide an example.

There is almost nothing to provide as an example, it seems that I simply don't know how to prepare data for Bind Blob:

And here is the contents of resulted Blob as shown by DB Browser for SQlite:

Only five bytes :-(  .. from a jpeg image (about 80 kbytes). And here is another way - from jpeg to blob:

And here is its blob contents (4899028 bytes from the same 72821-bytes image).

So it looks like I do something (or may be everything) wrong.

Here is the way I try to flatten the data from wav-file:

There is nothing to be proud of because I get this blob contents from a 1982508-bytes of wav-file:

I can not understand why I should represent the binary data (some portion of memory - if it were a C-program)

as a text.

 

[Rolf Kalbermatter]

Quite a lot of things wrong indeed.

In the first you use Variant to Flatten to flatten the binary string that you read from the file into a Flatten data. But a Binary string is already flattened, turning it into a variant and then flattening it again is not only unnecessary but is synonym to forcing a square peg into a round one in order to force it through a triangle hole.

I'm not familiar with this implementation of the SQL Lite Library but the BLOB data seems to be a binary string already so I would assume that connecting the wire from the read file directly would be the best option. Personally I would have made the BLOB function only accept a Byte Array as that is more what a Blob normally is. In that case the String to Byte Array would be the right choice to use for converting the File Contents binary string into the Byte Array.

The second attempt is not flattening the JPEG binary content but the LabVIEW proprietary Pixmap format. This is uncompressed and likely 32 bits in nature so a decent sized JPEG image gets suddenly 6 to 10 times as large in this format. You could write that as blob into the database but the only client who can do anything with that blob without reverse engineering the LabVIEW pixamp format (which is not really difficult since it is simply a cluster with various elements inside but still quite some work to redo in a different programming language) is a LabVIEW program that reads this data and turns it back into the LabVIEW Pixmap format and then uses it.

In the third you take a LabVIEW waveform and flatten that. A LabVIEW waveform is also a LabVIEW proprietary binary data format. The pure data contents wouldn't be that difficult to extract from that stream but a LabVIEW waveform also can contain attributes and that makes the format pretty complicated if they are present (and to my knowledge NI doesn't really document this format on binary level).

The first is a clear error, the other two might be ok if you never intend to use anything else than a LabVIEW written program to retrieve and interpret the data.

Edited February 20, 2018 by rolfk

[igorbounov]

43 minutes ago, rolfk said:

I would assume that connecting the wire from the read file directly would be the best option.

No, it gives only one-byte Blob, but this is the correct byte! So this is the way in the right direction!

So I need extracting remaining bytes.

 

[igorbounov]

1 hour ago, rolfk said:

I would assume that connecting the wire from the read file directly would be the best option.

 

Yes, finally it works this way:

It is so simple! Thanks!

[drjdpowell]

Are you familiar with the "Probes" in LabVIEW?  I ask because you seem to be debugging by looking at the db file with a viewer program, but you could have quickly seen that "Read from Binary file" was only returning one byte by just putting a probe on the output.

[igorbounov]

17 hours ago, drjdpowell said:

Are you familiar with the "Probes" in LabVIEW?  I ask because you seem to be debugging by looking at the db file with a viewer program, but you could have quickly seen that "Read from Binary file" was only returning one byte by just putting a probe on the output.

Yes, it was a mistery to me when I've seen by means of probe only one byte of data until I have thoroughly read the documentation. And also it appeared that "little-endian" is not a default byte order for reading binary files.

I was also puzzled when "Bind Timestamp" for time field appeared to produce a 16-byte Blob that DB Browser for SQlite sees as pure binary data. This item was discussed a fiew pages ago so "Bind Timestamp (Unix)" looks like a more conveniant format for selecting, sorting and comparing.

When I've built a test executable application for my LabView program, application builder produced a dll for SQlite support - should it be shipped along with the exe-applications?

[igorbounov]

drjdpowell, is there something special in the way that SQLite for Labview uses for storing Blobs?

I can not recover back the binary data stored in a blob:

Here the wire to Unflatten from String comes from a "Sqlite EXEC SQL" VI, the data on this wire as shown in a String indicator look like

a JPEG data should look but "LabVIEW:  Unflatten or byte stream read operation failed due to corrupt, unexpected, or truncated data."

So something is wrong with binary string from Sqlite Query (may be it is not a binary string?).

[drjdpowell]

7 hours ago, igorbounov said:

look like a JPEG data

Then it's probably JPEG data.  Why are you trying to unflatten as a LabVIEW 2D Picture format (which is NOT JPEG data)?   I think you need to research data formats and how to convert between them.   Stop guessing randomly and read the documentation.  Also use your probes to verify that what you put into SQLite you get back unchanged.  You aren't having any problems with the SQLite part of your program; you just having issues understanding formats.

This might help you:

[AndrewJR]

I was wondering if this toolkit supports the SQLite backup functions for saving an in memory database to a file.  If not, are there alternatives to accomplish this efficiently?

Thanks for your help

[drjdpowell]

On 20/07/2018 at 0:57 PM, AndrewJR said:

I was wondering if this toolkit supports the SQLite backup functions for saving an in memory database to a file.  If not, are there alternatives to accomplish this efficiently?

Thanks for your help

I'm afraid  those functions have not been implemented yet. 

[Sergey L]

Hi James,

Thanks for the tool, it's great! A quick question: is it possible to create encrypted DB files (with the password hard-coded in the block diagram)?

Thanks,

Sergey

 

[drjdpowell]

There are (not free) extensions of Sqlite that add encryption, but I have not tried them: 

[mischl]

A great toolkit, thanks a lot!

When I used it the last time a year ago I had the problem that inserting a csv file took a long time due to the whole insert loops in labview compared to the import a table from a csv command in the DB Browser for SQLite.

Wouldn't it be possible to have a vi that imports a table from a csv directly with the dll what would be comparable in speed to the DB Browser tool?

thanks in advance

[drjdpowell]

6 minutes ago, mischl said:

I had the problem that inserting a csv file took a long time due to the whole insert loops in labview

Did you wrap your INSERTs in a transaction?  

[ShaunR]

On 8/21/2018 at 9:21 PM, drjdpowell said:

There are (not free) extensions of Sqlite that add encryption, but I have not tried them: 

The Sqlite API for LabVIEW has encryption and is free for non-commercial use.;)

[mischl]

Quote

Did you wrap your INSERTs in a transaction?  

It was made that way, like from the template

[drjdpowell]

I suspect your problem is not using BEGIN/COMMIT or some other way of wrapping many INSERTS in one transaction.  Try looking at Example1, which inserts a large number of points.

[mischl]

Thank you for the feedback and help James! I am going to examine SQLite Example 1 -- Create Table.vi