ShaunR

What's wrong with using "File>>Save For Previous Version"?

OK.

Definitive answer. Change the "SQLite Read Blob.vi" moveblock call to "LabVIEW" (note the case...it matters!) instead of "labview.exe" or "lvrt"

Let me know if it fixes your issue and I'll add it to the API library.

Hi,

I am trying to use the sqlite functions in an executable. DO I need to add something in the installer for this to work successfully?

Please guide me.

Thanks,

Subhasis

It is looking for labview.exe which it can not find, and so the SQLiteRread Blob could not load.

Hmmm. This could potentially be a nasty one

Open the SQLite project and Find "SQLite Read Blob.vi"

Open the "MoveBlock" call library node and change it from "Labview.exe" to "lvrt"

Click "OK"

If it starts to search for the dll, Help it by browsing to your "/program files / national instruments / shared / Labview Run-Time / [your version] directory and selecting "lvrt.dll".

Save the vi and the project.

You should now be able to open your project and build the app. It may ask you again to find the lvrt.dll. If it does, so the same "browse" again as the above.

I can put in a switch to detect the change between run-time and dev, but if its not automagically picking up the run-time dll, this will cause a huge headache in multiple version systems.

I'll look more closely into this, but the above should enable you to build your app until I find a better solution.

Maybe we should reserve LVA.org, just in case...

www.ni.info is available

@ShaunR

The number of changes is constantly being updated and saved in a shift register. Therefore a value change will not be missed.

You're right. In my quick scan of the examples I assumed that identifying which ones had changed was also a requirement. If it's only the number then it should count them OK.

a little suggestion.

I think it could be usefull to add an Order By array input in the select Vi.

You can use the "Order BY" in the where clause.

e.g

Note that you have to have a where statement of some description so you can use RowID>0 to return all rows in a table if you have no "Where" constraint.

C/C++, C#, Matlab, Step7, Visual Basic, Python, Indusoft.

@ShaunR; Php can't really replace LabVIEW can it

It beats the cr@p out of Labview for web services

But my response was to the question "what other languages do you use".

Would somebody please tell me why i can't convert crossrulez example so that it can work with a 16x14 array?

have you customized the arrays somehow?

I can not figure out what is going wrong.

Because his example only updates and checks when there is a change in data. Your version (without the event case) continuously evaluates the difference between the current and previous values so you miss the change. By the time you press the stop button, the previous and current values are the same (although different from the starting values) therefore you don't detect a difference.

I don't think I could live with non G code. I continue to use LV, until some other replaceable solution, preferably open source, came alone.

At ShaunR: Cool, 999 posts

Not any more

I meant move the column count out of the while loop.

IC. Yes I tried that and didn't notice much of a change, but then again 3% on 40ms is only about 1ms which is in the noise level. I would have expected more, But it seems DLL call overhead is virtually non existent when set to a subroutine.

You might want to put a note in the documentation on which execution system you're using. I've just been using the same as callers in mine. I haven't really experimented with tuning priorities in LabVIEW.

IF I ever write a manual. I will

If you don't use execution systems and/or priorities, then you are limited to 4 threads (+1 for the UI). I don't think most people worry about it (LV is very good t making things appear to be very multi-threaded), but with very IO oriented asynchronous designs,it improves performance immensely (if used correctly). ThreadConfg.vi is my favourite VI

I didn't find it that to be much of a problem (you would just need to add it to your SQLite_Error VI, and maybe call it a bit more often). It helps a lot with debugging, of course I'm working with raw SQL strings far more often than you are.

I'll keep it in mind for now.

I wonder if it is possible to do it the same way they did.

Nope. We've wanted the ability for years(along with control creation at run-time). We got xnodes instead (a compromise).

Hi, if Labview went bankrupt. What other alternatives are there to use?? What other languages do you use??

Delphi, PHP, and (when I'm dragged kicking and screaming like a 5 yr old girl being chased by a great white shark ) C/C++

If I modify your fetch all on LV2010 64 on win7 I gain about 13 ms on a select with 100000 records.

Thats about 3% which is hardly worth the effort (although I'm not sure what you mean by modifying.....in-lining?) If I in-line the VIs I get exactly the same performance for insertions as you. But slightly slower on select (only gaining about 5ms in the 10,000 test)

A few more suggestions on your version.

Drop the high priority execution setting, typically disk IO is considered low priority (starving data collection would be bad).

That won't happen. I use a high priority but in a different execution system and therefore force LV to run the queries in a different thread from the users application (assuming the user isn't using the same execution system of course). It basically forces a high priority thread rather than VIs which should mean it gets a higher priority on the scheduler. On my machine I always run with the maximum number of threads (~200) since a lot of my systems use asynchronous tasks at various priorities. This is the way things like VISA work Although I did notice it is set to "Standard" and should be set to "Other 1" (not quite sure how that got changed).

You should support for sqlite3_errmsg in your error handler, since that'll give a much explanation of the error (stuff like table names or which command is misspelled).

I did look at it. But found that I needed to check the error every dll call and extract the string if need be. So I went for passing the error code up the chain and converting it at the end.

You'll probably want update to use binding, since currently you can't update to a blob value.

Indeed. Missed that one.

Ah a shame the replace thing doesn't work on all cases. If you want to replace a control, for example, you can only replace it by a other control but not by a constant or indicator. There are some few exceptions however... for example you can replace a constant by a control... this is the result:

xD

You can use the "Toggle" or "Change to Control" properties for this.

http://www.screencast.com/users/Phallanx/folders/Jing/media/6d33f01c-4961-4c88-9944-e3290ab349a0

Excellent. So my "improvements" are indeed improvements.

I think we are getting to the stage where implementation is becoming the major difference. Obviously my version uses a much deeper nesting since I prefer a modular decomposition (and have abstracted further) as opposed to (say) putting many DLL calls in one VI - which would save the sub-vi overheads. But for the sake of performance vs maintenance that is acceptable (to me at least). I would also expect you to be squeezing a little more by using the 2010 in-lining feature (if you are not, then you should be) which is unavailable in 2009. But I take note of your suggestion to promote the get_column_count which (in theory) should skim a ms or two of the time (does't seem to make any noticeable difference on my machine though).

Is there a reason you're clearing bindings before finalizing the statement. It's my understanding the clear just sets all parameters to null, and since you're closing it doesn't matter what the bindings are.

Yes and no

I like the encapsulation so that If I decide to expose the "Bind Execute" into the Low Level palette, then the user would not have to worry about it. I'm not sure (now) if bindings are persistent or not across opening and closing the DB (I originally thought it was persistent) as it is not stated anywhere and when I checked I couldn't discern any difference in performance. So just erring on the side of caution really.

When I run out of things to do, I might look at it again (if I remember ).

Have you noticed any improvement in performance between this version (1.2.1) and version 1.1?

I have a variant interface for differentiating nulls and empty strings when needed (which I need in my case). But with a pure string interface, making an empty string null is more useful.

[edit] this seems to be wrong

If you want an empty string to be written as NULL then you need to make sure the value passed doesn't point to null (just replace the 0 in my fix with any other number).

[/edit]

It seems an empty arrays are pointer to 0 which is why SQLite makes it null, not that it doesn't point to a /00 character.So you don't need to fix your current version to keep empty string as null.

An empty array is slightly different.

Since I use self indexing for loops, the bind never gets executed. However. I've just noticed that the classic problem also occurs. The sql ref and the DB ref are passed as "0" to the Bind Clear meaning they never get freed. Whilst his is obviously undesirable, I would have expected SQLite to return a "Misuse" error. It doesn't Instead error 1097 occurs which isn't good (possible crash under the right conditions). I will put some defensive code around this

So inadvertently you have uncovered a bug, although not the original one

Exactly, without the fix the empty string is bound as a NULL (unless the pointer passed to the dll happens to point to a \00), which fails the constraint. With the fix it get's bound as an empty string and passes the constraint.

I figured out the 2x size 32 bit example slowed down. I had left the speed test front panel open, without the front panel being open it doesn't slow down.

I thought about this a while back.

Labview has no concept of "NULL" string. We can neither create it nor check for it. If we put a string control / constant down, we can only have an empty string (and any of our VIs that accept a string will have a string control).

So the choice becomes do we allow a write to a NOT NULL field to always succeed (which is what will happen with your suggestion) or do we define an empty string in LV as being the equivalent to a NULL string. I think the latter is more useful.

I figured out the 2x size 32 bit example slowed down. I had left the speed test front panel open, without the front panel being open it doesn't slow down.

Good job I cannot take back the rep point eh?

Found a bug will optimizing mine that affects yours as well.

If you insert a blank string it'll probably be bound as a NULL.

It can be fixed if the empty string passed to sqlite3_bind_text is null terminated (I'm not sure if this is a bug with sqlite or a undocumented feature to allow writing nulls with string binding).

Inserting as a string instead of a byte array also fixes it since LabVIEW will null terminate all strings passed to the CLN.

I get error code 19 with your 1st snippet

Abort due to contraint violation

SQLite_Error.vi:2>SQLite_Bind Execute.vi>SQLite_Insert Table.vi:2>Untitled 1

If the field is not declared with NOT NULL it succeeds.

I tried to replicate your result for x32 but couldn't..

Mine is still linear.

Its fairly consistent.

Here's up to 5 million.

I tried to replicate your result for x32 but couldn't..

Mine is still linear.

Time scale in the waveform graph ( waveform Data type) always displays that the data starts from 0. irrespective of the to (time stamp) assigned.

Is this a bug in LabVIEW?

Thanks,

Pandiarajan R

Your graphs' X axis property are set to "Loose Fit".

Loose Fit Property

Short Name: LooseFit

Requires: Base Package

Class: ColorGraphScale Properties

If TRUE, LabVIEW rounds the end markers to a multiple of the increment used for the scale.

My understanding of the problem is that the bound data isn't read until you step the statement, and labview may unallocate the data passed to the dll by that point (in this case it's reusing the data pointer from null terminated copy for both calls), by passing -1 sqlite makes it's own copy of the data before returning.

The conversion should be free (it's just a static type cast). Passing the array avoids LabVIEW making a null terminated copy, which should speed things up. But you need to be certain LabVIEW doesn't deallocate the memory the string data until after you've stepped the statement. I think the IPE can do that (that's how I interpret it's help entry), but I'm not absolutely certain what the IPE structure does at a low level. Without the IPE (assuming it does make that guarantee), your risking the compiler deallocating it. Different platforms, future versions of LabVIEW, and different optimization cases may break it. I'm using -1 just to be safe. I would suggest you at least use an IPE to be sure the relevant string data is in memory. If you can find documentation that the IPE will always delay deallocation, let me know then I'll switch over mine as well .

Indeed. It was an oversight. It should have been -1. I don't think an IPE is really the way forward as I don't see any performace difference between 0 and -1 (KISS).

The problem is LabVIEW is allocating a array (arrays need a continuous chunk of memory) of string pointers in the while loop autoindex. When that array fills LabVIEW makes a new array larger array and copies the old one into it. The copy is the main cause of the slow down. Now LabVIEW seems to increasing the size of the new array in chunks (at least when the array is large). And since in 64 bit the pointers are twice the size the array needs to be resized and copied twice as often. Since the copies cost depends on how much data is getting copied, this leads problem getting exponentially worse with increasing array size.

If I'm correct the size of data elements should not affect when the exponetional error starts showing up, and the 32 bit should look like the 64 bit when reading the twice the number of rows as the 64 bit.

Which is the case.

This can be avoided by growing the array exponentially (for example doubling it's size when filled), but you'd have to code that by hand (and it's slower since I used to do that before I saw the autoindex in yours). You could check if the returned array has reached a size, this say number of columns * number of rows read = 500000 (and half that limit in 64bit), then switch to doing exponentional growth by hand.

I consider it as a Labview limitation rather than the API, In theory they should behave identically regardless of the implementation specifics. Differences between compiling in different IDEs is a little disconcerting since I think we all assume that what works in one will work identically in the other. But it looks like one of those "not a bug. not desired" effects.

But good call. on finding a probable explanation (your C experience obviously shining through). I think it will be rare occasions that anyone will be querying that many records at a time and it is still an order of magnitude faster than other DB implementations (like Access). You never know, they might optimise it in LV 2011 2015.

Version 1.2.1 just released.

Upgrading to 1..2.1 is highly recommended to address an issue with bulk inserts on LV x32.

You'r correct there is a bug in SQLite_Bind.vi change the 0 on the bottom to -1 and it'll work.

I'll release the next version a little earlier than planned (later today) since it will eradicate this (well spotted). Funnily enough. It only seems to happen on LVx32. x64 is fine.

I must of ran my 32 bit test in 64 bit since the problem no longer shows in 32bit.

I would suggest just binding everything as a string, then you can speed up the binding by not checking for \00 and just inserting the data as if it was there (string length is constant time in LabVIEW but searching for /00 requires checking each byte of the string). Then you just need to add support for reading strings containing \00.

The next release passes an array of bytes to the bind function, which is faster than passing a string even with the conversion to a U8 array. It also removes the aforementioned "bug".

The API already supports reading strings containing \00 (since V1.1). The field just needs to be declared as a blob. I did agonise about making it generic (just involves a direct replacement of "Fetch column" with "Read Blob"), but decided the performance advantage of not using the generic method outweighed the fact that you just have to define a field type.

I'm saying that internally LabVIEW string ARRAYS are an array of pointers to pascal strings. The CLN interface has nothing to do with it.

Well. I don't think that is the issue, since the later tests should have reduced the allocation to a smaller difference and I would have expected the x32 to be more like the x64 - which it isn't. Sufficed to say, there is a difference and, that LV x64 is vastly less efficient at building large arrays of strings than x32 (which I find surprising).