Jump to content

Recommended Posts

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 ?

Link to post
Share on other sites
  • Replies 153
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Hello, I’ve been working with SQLite for a logging application and I thought I might offer my SQLite LabVIEW wrapper for possible inclusion in OpenG. There are at least two other implementations out

Latest version, with a bit of the documentation filled in. It now also works under Mac OS X (it links to the standard copy of SQLite3 included as a part of OS X): SQLite LabVIEW.zip

I've written my own SQlite implementation making this the fifth I'm aware of. All of them being yours, mine, ShaunR's, SmartSQLView, and a much older one written by someone at Philips. Handling Varia

Posted Images

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.

  • Like 1
Link to post
Share on other sites
  • 9 months later...

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

snip.png

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

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

binary_to_blob.png

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

blob_contents.png

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

jpeg_to_blob.png

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

blob_contents_2.png

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:

wav_to_blob.png

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

blob_contents_3.png

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

as a text.

 

Link to post
Share on other sites

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 by rolfk
  • Like 1
Link to post
Share on other sites
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.

 

Link to post
Share on other sites

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.

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

Link to post
Share on other sites

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:

recover_from_binary.png

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

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

JPEG to 2D Picture.png

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

Link to post
Share on other sites
  • 5 weeks later...

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By drjdpowell
      View File SQLite Library
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
      Submitter drjdpowell Submitted 06/19/2012 Category Database & File IO LabVIEW Version 2013 License Type BSD (Most common)  
    • By drjdpowell
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
    • By Thang Nguyen
      Hi,
      I would like to know if I can write stream data into two different tables with SQLite at the same time or not? Currently my system has one camera. I store the capture images and particle measurement in one table. I have just add one more camera to the system and would like to do the same thing and write to a second table in a parallel process.  I wonder if this is possible or not.
      I use SQLite library.
      Thanks in advance.
       
    • By McQuillan
      Hi Everyone,
      I (re)watched James Powell's talk at GDevCon#2 about Application Design Around SQLite. I really like this idea as I have an application with lots of data (from serial devices and software configuration) that's all needed in several areas of the application (and external applications) and his talk was a 'light-bulb' moment where I thought I could have a centralized SQLite database that all the modules could access to select / update data.
      He said the database could be the 'model' in the model-view-controller design pattern because the database is very fast. So you can collect data in one actor and publish it directly to the DB, and have another actor read the data directly from the DB, with a benefit of having another application being able to view the data.
      Link to James' talk: https://www.youtube.com/watch?v=i4_l-UuWtPY&t=1241s)
       
      I created a basic proof of concept which launches N-processes to generate-data (publish to database) and others to act as a UI (read data from database and update configuration settings in the DB (like set-point)). However after launching a couple of processes I ran into  'Database is locked (error 5) ', and I realized 2 things, SQLite databases aren't magically able to have n-concurrent readers/writers , and I'm not using them right...(I hope).
      I've created a schematic (attached) to show what I did in the PoC (that was getting 'Database is locked (error 5)' errors).
      I'm a solo-developer (and SQLite first-timer*) and would really appreciate it if someone could look over the schematic and give me guidance on how it should be done. There's a lot more to the actual application, but I think once I understand the limitations of the DB I'll be able to work with it.
      *I've done SQL training courses.
      In the actual application, the UI and business logic are on two completely separate branches (I only connected them to a single actor for the PoC) 
      Some general questions / thoughts I had:
      Is the SQLite based application design something worth perusing / is it a sensible design choice? Instead of creating lots of tables (when I launch the actors) should I instead make separate databases? - to reduce the number of requests per DB? (I shouldn't think so... but worth asking) When generating data, I'm using UPDATE to change a single row in a table (current value), I'm then reading that single row in other areas of code. (Then if logging is needed, I create a trigger to copy the data to a separate table) Would it be better if I INSERT data and have the other modules read the max RowId for the current value and periodically delete rows? The more clones I had, the slower the UI seemed to update (should have been 10 times/second, but reduced to updating every 3 seconds). I was under the impression that you can do thousands of transactions per second, so I think I'm querying the DB inefficiently. The two main reasons why I like the database approach are:
      External applications will need to 'tap-into' the data, if they could get to it via an SQL query - that would be ideal. Data-logging is a big part of the application. Any advice you can give would be much appreciated.
      Cheers,
      Tom
      (I'm using quite a few reuse libraries so I can't easily share the code, however, if it would be beneficial, I could re-work the PoC to just use 'Core-LabVIEW' and James Powell's SQLite API)

    • By Munch
      Good Afternoon,
      I have been having an issue trying to resolve an error all afternoon, and hope that you might be able to help.
      I am trying to execute the follow SQL "
      SELECT m.name as tableName,
             p.name as columnName
      FROM sqlite_master m
      left outer join pragma_table_info((m.name)) p
           on m.name <> p.name
           where columnName = 'UploadedFlag'
      order by tableName, columnName
      " That returns a list of the tables that Contain the 'UploadedFlag' Column.   I have testing the SQL on both HeidiSQL and SQLite Expert Personal applications and run and return what I expect, however when I run it through LabVIEW I get a 402860 Error with the description " SQLITE_ERROR(1): near "(": syntax error " 
      If anyone could point me in the direction of the syntax error then I would be very greatful.
      Regards
      Mark



×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.