Jump to content
drjdpowell

SQLite for OpenG

Recommended Posts

I tested the difference between the former insert template and the current one with BEGIN and COMMIT. It is quite obvious and about factor 300 to 400 faster. Thank you for the fantastic job implementing this!

BTW: saw that the project is hosted under bitbucket - excellent! in the readme there are two small faults: LabVIEW Versions: -> 2013 or later and Use VI Package Manager (2017 or later) according to the support page on lava

Share this post


Link to post
Share on other sites

Hi,

Thanks for a wonderfull sqlite api drjdpowell.

Is there away to read out how much memory is used by sqlite with this library or a general status of sqlite?

 

Thanks

Share this post


Link to post
Share on other sites

I want to optimize the upper part on the screenshot with the lower (to avoid too many transactions).

It seems that I am not aware of the correct handling at the moment. Thank you in advance for some instructions:

1. place the BEGIN and COMMIT outside. ok

2. the command in 2 and 3 is something like SELECT * FROM stueli WHERE d = 'xy'. Do I have to do that by bind or just as prepare statement on the picture?

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

4. here the 'insert many row template' is used without the BEGIN and COMMIT. So it is the same question as in 3: how to handle the connection.

there is also a vi snippet attachet if this would be easier to explain something as one can draw in it directly with LV

thanks a lot

 

 

2019-01-05 08_01_41-expand2.vi Block Diagram on MultiTool.lvproj_My Computer.png

draftvi.png

Share this post


Link to post
Share on other sites
3 hours ago, mischl said:

I want to optimize the upper part on the screenshot with the lower (to avoid too many transactions).

My general advice for refactoring code is to do it step-by-step. Don't try to fix everything at once, as it will likely break your code (as it did in your case).

Note:

  • I wasn't able to load your VI snippet. I'm not sure why though (either I'm to stupid to do it or Lava sanitizes images)
  • My suggestions below are based on your original VI

Step 1 - Improve readability

Arrays and for loops

The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array. I suggest you use one array for iteration and use the Array Index function to access elements from the other arrays. Here is an example:

657223384_AccessingElementsinaFor-Loop_vi.thumb.png.55fe24cd86c35bfcc367a2673c1de36c.png

You should play around with it to see the different outcomes. Note that indexing an element from an array of smaller size returns a default value (i.e. zero for numbers, an empty string for Strings and false for Boolean).

Shift registers

The outer for loop uses a shift register for the SQLite instance as well as the error cluster. The inner for loop, however, doesn't. This can be problematic for two reasons:

1) The SQLite instance in the inner for loop gets reset after each iteration.

2) An error in the inner for loop is reset after each iteration and only the last error is returned.

The solution depends on what you want to do. If you want to abort as soon as possible on any error, I suggest not using a shift register but making the for loop conditional instead (exit on error). Otherwise use shift registers on both loops.

Step 2 - Optimize your code

Use transaction outside of for loops

Transactions are very useful for improving performance if used correctly. In your second example, you already added the BEGIN and COMMIT Transaction using "MySavepoint". This is generally a good idea.

3 hours ago, mischl said:

1. place the BEGIN and COMMIT outside. ok 

Disable transactions on Execute SQL

The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions. This will already improve performance considerably.

Answering questions

3 hours ago, mischl said:

2. the command in 2 and 3 is something like SELECT * FROM stueli WHERE d = 'xy'. Do I have to do that by bind or just as prepare statement on the picture?

SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).

The "INSERT many rows Template code.vi" provided by the library (see Code Templates palette) visualizes that very well:

2002117129_INSERTManyRowsTemplate_vi.thumb.png.d2564885d0cde9253d04192b44088526.png

I highly suggest not using "SELECT *" but explicitly selecting particular columns instead. That way your SQL Statement becomes predictable.

3 hours ago, mischl said:

4. here the 'insert many row template' is used without the BEGIN and COMMIT. So it is the same question as in 3: how to handle the connection.

Here is an example that may or may not work (I can't test it for obvious reasons):

353253096_SELECTusingbindingparameters.thumb.png.1e4dea5001dc6f6bd687a2db46dc4556.png

I suggest making a VI for each statement (without transactions of course). That way you can test things in isolation and be certain that it works.

3 hours ago, mischl said:

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

I'm not sure what you mean by that. Can you explain what you mean in more detail?

 

  • Like 1

Share this post


Link to post
Share on other sites

thank you for the detailed feedback!

Quote

The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array.

all the arrays has exactly the same size

Quote

Shift registers

thanks for that!

Quote

The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions.

yes, but that is set already to false by default. isn't it?

Quote

SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).

correct, it isn't static and therefore I will bind them according your hint.

 

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

in the example, the 'SQL statement' ends after the reset at 2. at 3 and 4 further ''SQL statement' are in used and only one can be finalized. but there are three now. how to handle that?

the 'Prepare' at 3 needs a 'SQL Connection', it is the one chained from 2, or?

 

sorry, it seems that I forgot to attach the snippet. here is it. no problem if there are broken wires, but it might be easier to wire an example with the correct usage of 'SQL Connection in' and 'SQL statement in' than to explain them by text.

 

draftvi.png

Share this post


Link to post
Share on other sites
18 hours ago, mischl said:

yes, but that is set already to false by default. isn't it?

You are right, my bad.

18 hours ago, mischl said:

is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

Yes and no.

The SQL Prepare function creates the SQL Statement. It allows you to run the same SQL query multiple times, but more efficient. I've attached an example in my last post, using your SQL query:

SELECT using binding parameters.png

SQL Prepare (second VI from left) receives an SQL Connection and the SQL query ("SELECT * FROM sueli WHERE d = ?"). Note, that it says "d = ?". The "?" will later be replaced in the loop by the actual value.

In the for loop, the first node does exactly that. It replaces "?" by an element from the list. Note that you can have multiple values, using multiple "?" in your query.

SQLite First Step (the next VI) executes the query once and you can read the results using the node that follows (Get Column String).

Before running the same query again, you must reset the SQL Statement. Note, that everything takes place within the loop.

When the loop ends, you need to finalize the SQL Statement or it stays in memory.

18 hours ago, mischl said:

in the example, the 'SQL statement' ends after the reset at 2. at 3 and 4 further ''SQL statement' are in used and only one can be finalized. but there are three now. how to handle that?

You must finalize each SQL Statement separately. In your example, everything is inside the loop, so you gain nothing by using prepared statements.

SQLite Prepare and SQLite Finalize must be placed outside the loop. I suggest you split those SQL Statements into separate VIs or it will be very confusing.

18 hours ago, mischl said:

the 'Prepare' at 3 needs a 'SQL Connection', it is the one chained from 2, or?

Yes, they all share the same connection.

18 hours ago, mischl said:

sorry, it seems that I forgot to attach the snippet. here is it. no problem if there are broken wires, but it might be easier to wire an example with the correct usage of 'SQL Connection in' and 'SQL statement in' than to explain them by text.

Actually, the snippet was attached, but Lava removes the source code of snippets right now (download the image and drag it into LabVIEW).

Can you copy the code into a new VI and post it here?

Share this post


Link to post
Share on other sites

If you are set on doing multiple queries in the same loop, here is another example that might help understand how it should work. The VI is executable, using dummy data.

2118115753_UsingMultiplePreparedStatementsInOneLoop_vi.thumb.png.d6253581d8650836a2227ad28a642bda.png

I've also attached the VI for LV2014, so you can play around with it.

 

Using Multiple Prepared Statements In One Loop.vi

Share this post


Link to post
Share on other sites

Thank you @LogMAN for all your explanations and your efforts to explain it in depth - very appreciated!

I am going to edit it according the description and I am very confident that this optimizes the code by factors.

Now as the new work year has started it will last some days until there is enough time to do it. I will keep you informed.

 

Vielen Dank nochmals und Grüsse aus der Schweiz

Share this post


Link to post
Share on other sites

thanks to the help of @LogMAN I was able to update the code into a transaction and it works like a charm and much faster, thank you again!

I was facing only one problem: '?-x' in a statement is processed as text and the question mark will not be exchanged with bind, use concat instead: ? || '-x'

 

Share this post


Link to post
Share on other sites
On 1/11/2019 at 8:30 AM, mischl said:

I was facing only one problem: '?-x' in a statement is processed as text and the question mark will not be exchanged with bind, use concat instead: ? || '-x'

Parameter bindings require you to follow specific syntax. It is explained here: https://www.sqlite.org/c3ref/bind_blob.html

Quote

In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates:

  • ?
  • ?NNN
  • :VVV
  • @VVV
  • $VVV

In the templates above, NNN represents an integer literal, and VVV represents an alphanumeric identifier. The values of these parameters (also called "host parameter names" or "SQL parameters") can be set using the sqlite3_bind_*() routines defined here.

'?-x' gets interpreted as parameter, which doesn't work. Two solutions come to mind:

1) Use the concatenation function like you suggested. This makes sense if the suffix is static for all parameters.

2) Do the concatenation before binding the parameter (i.e. in LV). If your suffix can change, make it part of the parameter.

Edited by LogMAN

Share this post


Link to post
Share on other sites

how to set up a REGEXP? actually it leads to an error.

according to https://www.sqlite.org/lang_expr.html

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

Share this post


Link to post
Share on other sites
2 hours ago, mischl said:

how to set up a REGEXP?

You would need to compile a loadable extension to add that functionality.  As an example, I compiled extension-functions.c in order to allow advanced math functions like standard deviation, so you could have a look at that.    I note that there is a regexp.c  extension on the SQLite website, so you could try and compile that using the loadable extension instructions.  

Edited by drjdpowell

Share this post


Link to post
Share on other sites
On 9/19/2012 at 6:13 PM, SDietrich said:

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

Hello,

I am working with LabVIEW and SQLite and I am using the SQLite library created by drjdpowell.

Moreover, I need to use SPATIALite as SDietrich used in the previous quoted post. To do so I am following the instructions of this Spatialite page and I am trying to include the file "mod_spatialite.dll" (SDietrich loaded "lib-spatialite-2.dll" but as this post is old I consider that now I should follow the instructions of the Spatialite web page). My question is, how can I load it?

I tried to do that with the VI shown in the next image:

31757327_lavapostspatialite.png.37184ad14abdf6b79b9cf4a68209a904.png

where "BBDD Path" is the link to my SQLite database and "dll file Path" the link to the "mod_spatialite.dll" file. When I execute this VI I get the 402860 error after executing "Execute SQL.vi", and I also get the message "SQLite returned SQLITE_ERROR(1), see www.sqlite.org/rescode.html#error" in the error source string, but it does not give information about the error.

My VI is based on the "Load Extended Math Functions.vi" of the SQLite library created by drjdpowell, and it works when I try to load the file "extension-functions.dll" (which is the file loaded by "Load Extended Math Functions.vi") instead of the "mod_spatialite.dll" file.

In case that it helps, I am using LabVIEW 2019 and the version of SQLite and Spatialite shown in the next image:

 

Can anyone help me to solve this problem?

 

Thank you in advance!

Víctor

lava post spatialite 2.png

Share this post


Link to post
Share on other sites

First thing I would try is to follow the "Useful Hint" given in the spatialite link and try and load the extension via the sqlite3 command-line tool to see if it works there or gives a more informative error message.

Share this post


Link to post
Share on other sites
On 1/26/2020 at 10:52 AM, drjdpowell said:

First thing I would try is to follow the "Useful Hint" given in the spatialite link and try and load the extension via the sqlite3 command-line tool to see if it works there or gives a more informative error message.

Thank you for your quick response. I follow your instructions and I did several experiments but I cannot get my problem solved. These are the experiments performed:

  • First experiment: loading "mod_spatialite.dll":

I wrote this line in the sqlite3 command-line tool: 

SELECT load_extension('mod_spatialite.dll');

As a result of this sentence I did not get any error message. It is interesting to note that the file "mod_spatialite.dll" was in the same folder than "sqlite3.exe". Nevertheless, I could not do that in LabVIEW, also after copying the DLL file in the same folder than the VI file.

 

  • Second experiment: loading "mod_spatialite.dll" with its absolute path:

I wrote this line in the sqlite3 command-line tool: 

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\VMM.lib\mod_spatialite-4.3.0a-win-amd64\mod_spatialite.dll');

As a result of this sentence I get this error message: "Error:    No se puede encontrar el módulo especificado" (in English it can be translated as "Cannot find the specified module"). It is insteresting to note that "'D:\Program Files (x86)\National Instruments\LabVIEW 2019\VMM.lib\mod_spatialite-4.3.0a-win-amd64\" is the absolute path of the location of the "mod_spatialite.dll" file.

 

  • Third experiment: loading "extension-functions.dll" with its absolute path:

I wrote this line in the sqlite3 command-line tool: 

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\extension-functions.dll');

As a result of this sentence I get the same error message: "Error:    No se puede encontrar el módulo especificado". It is insteresting to note that:

  1. "'D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\" is the absolute path of the location of the "extension_functions.dll" file.
  2. This sentence worked properly in the LabVIEW code shown in my previous post when selecting this path in the "dll File Path" control.

 

  • Fourth experiment: loading "extension-functions.dll" without its absolute path:

The last experiment consisted on trying to do not get an error message by repeating the procedure employed in the first experiment. To do so, I moved the "extension-functions.dll" file to the same folder than the "mod_spatialite.dll" and the "slqite3.exe" files. After that, I wrote this line in the sqlite3 command-line tool:

SELECT load_extension('extension_functions.dll');

As a result of this sentence I get again the same error message: "Error:    No se puede encontrar el módulo especificado".

 

To sum up, the description of the error obtained is "Error:    No se puede encontrar el módulo especificado" (in English it can be translated as "Cannot find the specified module"). Moreover, in the first experiment I was able to load the "mod_spatialite.dll" file without error, but it did not worked in LabVIEW. Finally, in the fourth experiment I get an error message even with a sentence that worked in LabVIEW.

 

Thank you in advance for your help!

Víctor

Share this post


Link to post
Share on other sites

Are you using matching bitness?  You have to use the same bitness, 32 versus 64, for all DLLs and LabVIEW.

Share this post


Link to post
Share on other sites
On 2/4/2020 at 11:45 PM, drjdpowell said:

Are you using matching bitness?  You have to use the same bitness, 32 versus 64, for all DLLs and LabVIEW.

First of all thank you again for your quick response. And sorry for the delay, but I was out and I could not check it until today.

I took some time to try to use the same bitness in all DLLs and LabVIEW as you suggested but it did not worked. I had the same error in all the trials I did.

Moreover, I was surprised because I repeated the same experiment than the explained in my previous post with the two extension functions DLLs (extension-functions.dll and extension-functions_64.dll, which I suppose that are the files for 32 and 64 files respectively) and I get no errors when trying to load any of these files. These are the sentences employed:

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\extension-functions.dll');
SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\extension-functions_64.dll');

For these reasons I think there is another problem that is not allowing me to load the SpatiaLite DLL files.

 

Thank you in advance for your help!

Víctor

Share this post


Link to post
Share on other sites

Hi, I have some questions.

In the VI: "SQLite.lvlib:Connection.lvclass:Prepare.vi". What is the function of the input "SQL Statement subclass"?

On the other hand, I am trying to get the total number of rows after a select. Simply comment, that it is not posible to count the number of rows at the output, because the LIMIT function is used to limit the máximum number of rows at the output. 

First of all, I thought the input "SQL Statement subclass", the purpose was to put perform operations on a previous select, but I don't know if I am using this function correctly.

Thank a lot.

image1.png

Share this post


Link to post
Share on other sites

That input is to allow one to (optionally) make a LVOOP child class of SQL Statement.  Normally one doesn't use this, but you can use a child class to add functionality.  For example, I have used it to define new Bind and Get Column methods that store data in a more compressed format.  But you probably can ignore it.

Share this post


Link to post
Share on other sites

Thank you very much, for the quick answer. In any case, would you have a simple example, that could illustrate this case?

Thank a lot.

Share this post


Link to post
Share on other sites
10 hours ago, anonimo anonimo said:

Thank you very much, for the quick answer. In any case, would you have a simple example, that could illustrate this case?

I have a project where Absorption Spectra are saved in an SQLite database.   Data is stored in a custom format so as to take up a minimum of space.  I have a child class of "Connection" that adds a lot of application-spec code, and a matching child of "SQL Statement.  Here is the VI that does a Prepare:

1102593775_SQLitePreparechildclass.png.59a75b4fdc714f2db66ca5397158ee52.png

Here, multiple bits of information are passes into the child class, about the Timezone of the data and the offset time (times are stored as ms relative to an offset, to save space).

The "SQL Statement with Scaled Data" child class has several additional Bind and Get Column vis, which have the scaling in then, allowing the calling code to easily work with different quantities (Absorption or Transmission, Local or UTC Timestamps):

184477778_SQLiteChildStatementextraBinds.png.50686d646d7ab62282aa6d50f2c94e42.png

Here is the Bind Absorption method (the db actual contains a specially chosen 3-byte format that has to be converted to Absorption):

1783621633_BindAdsorption.png.cf55fb470d1302c770556c178a9e6659.png

So basically, this is extending the Connection and SQL Statement classes to be more capably for a specific application.  Note that one does not have to do this, as one can use regular subVIs instead.  

Share this post


Link to post
Share on other sites

drjdpowell,

it appeared so that I've got an installation of Labview 2018 (Full development system) for Linux (my main OS). The distribution, althogh full development sysytem, has no VIPM. Moreover, the most recent VIPM is VIPM 2017 which (for Linux) is shipped with Labview 2015 SP1 RTE and it doesn't see installed Labview 2018.

And - the final blow - I see that your SQLite Add-on for Labview is available only for Windows! For LV 2017 maximum!

Is there any workaround?

Share this post


Link to post
Share on other sites

Go get VIPM from JKI directly. 

I have not tested the library on Linux (other than Linux RT), but it should be able to work, if one has a copy of the sqlite3.so shared library installed in the right place.  

Does anyone else use this library on Linux?

Share this post


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.
       
      Now hosted on the NI LabVIEW Tools Network.
       
      ***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 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
    • By drjdpowell
      For comment, here is a beta version of the next SQLite Library release (1.11).   It has a significant new feature of a "Parameter(s)" input to the "Execute SQL" functions.  This can be a single parameter or a cluster of multiple parameters.  Uses Variant functions and will be not as performance as a more explicit preparing and binding of a Statement object, but should be easier to code.

       
      drjdpowell_lib_sqlite_labview-1.11.0.86.vip
×
×
  • Create New...

Important Information

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