Jump to content
ATE-ENGE

Using SQL Databases and LabVIEW exe

Recommended Posts

Background:

I've been using LabVIEW for a few years for automation testing tasks and until recently have been saving my data to "[DescriptorA]\[DescriptorB]\[test_info].csv" files. A few months ago, a friend turned me on to the concept of relational databases, I've been really impressed by their response times and am reworking my code and following the examples with the Database Connectivity Toolkit (DCT) to use "[test_info].mdb" with my provider being a Microsoft jet oldb database.

However, I'm beginning to see the limitations of the DCT namely:

  • No support for auto-incrementing primary keys
  • No support for foreign keys
  • Difficult to program stored procedures

and I'm sure a few more that I don't know yet.

Now I've switched over to architecting my database in MySQL Workbench. Suffice to say I'm a bit out of my depth and have a few questions that I haven't seen covered in tutorials

 Questions (General):

 Using Microsoft jet oldb I made a connection string "Data Source= C:\[Database]\[databasename.mdb]" in a .UDL file. However, the examples I've seen for connecting to MySQL databases use IP addresses and ports.

  1. Is a MySQL database still a file?
  2. If not, how do I put it on my networked server \\[servername\Database\[file]?
  3. If so, what file extensions exist for databases and what is the implication of each extension? I know of .mdb, but are there others I could/should be using (such as .csv's vs .txt's)

 My peers, who have more work experience than me but no experience with databases, espouse a 2GB limit on all files (I believe from the era of FAT16 disks). My current oldb database is about 200mB in size so 2GB will likely never happen, but I'm curious:

  1. Do file size limits still apply to database files?
  2. If so, how does one have the giant databases that support major websites?

 Questions (LabVIEW Specific):

  1. I can install my [MainTestingVi.exe], which accesses the jet oldb database, on a Windows 10 computer that is fresh out of the box. When I switch over to having a MySQL database, are there any additional tools that I'll need to install as well? 

Share this post


Link to post
Share on other sites
Quote
  • No support for auto-incrementing primary keys
  • No support for foreign keys

These are part of the design of the database, I wouldn't imagine any api-level tool to have "support" for them except as a direct SQL call. I would absolutely do what you're doing and use a GUI to design the database, and then access it through the API. 

Mysql workbench is pretty powerful, but can be confusing. I've always used a tool called HeidiSQL for working with mysql/mariadb databases. Its nicer in my opinion for learning with.

Some other thoughts:

  • Mysql has a TCP server https://decibel.ni.com/content/docs/DOC-10453 which is faster for small interactions
  • the mysql command line is a pain to use but could be better for bulk imports (eg from a giant mass of old CSV files). As shown in the link, Heidi can help you.
  • Postgresdb is becoming more and more popular (among other things, mysql is now owned by oracle and spent a while sort of languishing -- for my personal load of several TB of indexed data, postgres performed significantly better out of box than a somewhat optimized mysql). If you decided to go this route there are two libraries to consider (in addition to the db connectivity toolkit).
  • What you described, having a bunch of old measurement data in csv files and wanting to catalog them in a database-esque way for performance and ease of use is literally the sales pitch of Diadem. Like, almost verbatim.

 

10 hours ago, ATE-ENGE said:

 Questions (General):

 Using Microsoft jet oldb I made a connection string "Data Source= C:\[Database]\[databasename.mdb]" in a .UDL file. However, the examples I've seen for connecting to MySQL databases use IP addresses and ports.

  1. Is a MySQL database still a file?
  2. If not, how do I put it on my networked server \\[servername\Database\[file]?
  3. If so, what file extensions exist for databases and what is the implication of each extension? I know of .mdb, but are there others I could/should be using (such as .csv's vs .txt's)

 My peers, who have more work experience than me but no experience with databases, espouse a 2GB limit on all files (I believe from the era of FAT16 disks). My current oldb database is about 200mB in size so 2GB will likely never happen, but I'm curious:

  1. Do file size limits still apply to database files?
  2. If so, how does one have the giant databases that support major websites?
  1. It may be 1 to N files depending on the configuration. Indices can sometimes be stored in separate files, and if you have a ton of data you would use partitioning to split the data up into different files based on parameters (eg where year(timestamp)=2018 you use partition 1, where year=2017 use partition 2, etc.). You don't reference the file directly. You usually use a connection string formatted like this: https://www.connectionstrings.com/mysql/
  2. You cannot, you must have a server machine which runs the mysql service. To the best of my knowledge, the only database which you can put on a share drive and forget about is sqlite, but they recommend against it. I had never used the MDB format before but it looks like that is similarly accessible as a file.
  3. As with 2, you generally don't edit the files manually. You access the database through the server which exposes everything through SQL.

 

  1. They do, but I think its in the TB range. If you reach a single database file that is over a TB, you should learn about and use partitioning which breaks it down into smaller files.
  2. Not sure, but I believe the truly giant websites like google have their own database system they use. More generally, they divide the workload across a large number of machines. As an example: https://en.wikipedia.org/wiki/Shard_(database_architecture)

 

10 hours ago, ATE-ENGE said:

 Questions (LabVIEW Specific):

  1. I can install my [MainTestingVi.exe], which accesses the jet oldb database, on a Windows 10 computer that is fresh out of the box. When I switch over to having a MySQL database, are there any additional tools that I'll need to install as well? 

You will need to install the database server somewhere. Assuming you've set up some server thats going to host your data, then you just need the client software. If you use the TCP-based connector mentioned above, that client software is just labview. However, that connector has no security implementation and gets bogged down with large data sets. If you want to use the database toolkit, you'll need the ODBC connector and perhaps to configure a data source as shown here, although you may be able to create a connection string at runtime.

  • Like 1

Share this post


Link to post
Share on other sites

well specifically its the return data thats the issue, and the answer is you have to benchmark for your data set. I'd expect the crossover point to be <1 MB but its been a while since I tried it. For inserting data, or fetching configuration, or asking the database to give you a processed result (avg of x over time period T) this works great, for grabbing data in bulk out of the db its uselessly slow. 

Share this post


Link to post
Share on other sites
On 5/21/2018 at 3:02 PM, ATE-ENGE said:
  1. Is a MySQL database still a file?
  2. If not, how do I put it on my networked server \\[servername\Database\[file]?
  3. If so, what file extensions exist for databases and what is the implication of each extension? I know of .mdb, but are there others I could/should be using (such as .csv's vs .txt's)
  1. No. It is a "service".
  2. It's a service, so you cannot put it on a drive, you have to install it then communicate over TCPIP
  3. See #1.

If you really want a file based relational database take a look at SQLite. SQLite supports DB files up to 140 terabytes-good luck finding a disk that size :) 2G partition sizes are only an issue in WinXP and with fat32 disks. Modern OS's and disk are not an issue.  Be warned, though. There are caveats in using SQLite on network shares. However. If it the use case is configuration which is written to rarely (and usually by one person) then it will work fine on a network share for reading from multiple applications. The locking issues mainly come into play when writing to the DB from multiple clients. Note also this is not a very efficient way to access SQLite databases and is an order of magnitude slower

If you are going to be logging data from multiple machines, then MySQL/PostgreSQL is the preferred route. I usually use SQLite and MySQ together - SQLite locally on each machine as a sort of "cache" and also so that the software continues to operate so as not lose data when the MySQL server is not available. In this way you get the speed and performance of SQLite in the application and the network wide visibility in MySQL for exploitation. It also gives you the ability for the machine to work offline.

If you are going with MySQL then it is worth talking with your IT department. They may be able to set it up and administer it for you or provide a machine specifically for your needs. They usually prefer that to having a machine on their network not under their control, with network wide visibility, and it will give you a good support route if you run into any difficulties..

Edited by ShaunR

Share this post


Link to post
Share on other sites

Oh yeah, if you have performance needs writing directly to the database can be quite poor (depending on exactly how you have the database set up, the indices involved, the disks involved, etc). Rather than logging to sqlite as shaun does I just write my data to a binary file as fast as I can, and have a background service shove it into the database as fast as it can. Essentially using the disk as a queue.

Edited by smithd

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 Atul
      Hello Everyone
       
      I am facing some issue while opening database connection using OLE DB as well as in ODBC  also
      Currently i am using Labview 64 bit version and using ADO functions (ActiveX Function) i am getting following error. Check Attachment
      Error -2146824582 occurred at Exception occured in ADODB.Connection: Provider cannot be found. It may not be properly installed.Help Path is C:\WINDOWS\HELP\ADO270.CHM and context 1240655 in AUTOMATION 64BIT.vi
       
      Any link to similar kind task or tutorial for study are most welcome.
      Thanks.

    • By GregFreeman
      I think I have found a fundamental issue with the DB Toolkit Open connection. It seems to not correctly use connection pooling. The reason I believe it's an issue with LabVIEW and ADODB ActiveX specifically is because the problem does not manifest itself using the ADODB driver in C#. This is better shown with examples. All I am doing in these examples is opening and closing connections and benchmarking the connection open time.
      Adodb and Oracle driver in LabVIEW.

       
      ADODB in C#
       
      namespace TestAdodbOpenTime { class Program { static void Main(string[] args) { Stopwatch sw = new Stopwatch(); for (int i = 0; i < 30; i++) { ADODB.Connection cn = new ADODB.Connection(); int count = Environment.TickCount; cn.Open("Provider=OraOLEDB.Oracle;Data Source=FASTBAW;Extended Properties=PLSQLRSet=1;Pooling=true;", "USERID", "PASSWORD", -1); sw.Stop(); cn.Close(); int elapsedTime = Environment.TickCount - count; Debug.WriteLine("RunTime " + elapsedTime); } } } } Output:
      RunTime 203
      RunTime 0
      RunTime 0
      RunTime 0
      RunTime 0
      RunTime 0
      RunTime 0
      RunTime 0
      RunTime 0
       
      Notice the time nicely aligns between the LabVIEW code leveraging the .NET driver and the C# code using ADODB. The first connection takes a bit to open then the rest the connection pooling takes over nicely and the connect time is 0. 
       
      Now cue the LabVIEW ActiveX implementation and every open connection time is pretty crummy and very sporadic. 
       
      One thing I happened to find out by accident when troubleshooting was if I add a property node on the block diagram where I open a connection, and if I don't close the reference, my subsequent connect times are WAY faster (between 1 and 3 ms). That is what leads me to believe this may be a bug in whatever LabVIEW does to interface with ActiveX.
       
      Has anyone seen issues like this before or have any idea of where I can look to help me avoid wrapping up the driver myself?
       
    • By GregFreeman
      I am running calls to a various stored procedures in parallel, each with their own connection refnums. A few of these calls can take a while to execute from time to time. In critical parts of my application I would like the Cmd Execute.vi to be reentrant. Generally I handle this by making a copy of the NI library and namespacing my own version. I can then make a reentrant copy of the VI I need and save it in my own library, then commit it in version control so everyone working on the project has it. But the library is password protected so even a copy of it keeps it locked. I can't do a save as on the VIs that I need and make a reentrant copy, nor can I add any new VIs to the library.
      Does anyone have any suggestions? I have resorted to taking NIs library, including it inside my own library, then basically rewriting the VIs I need by copying the contents from the block diagram of the VI I want to "save as" and pasting them in another VI.
    • By Night Koh
      Greetings, 
      I am currently learning LabVIEW Database which I had encountered the error stated on the title. I am using Xampp software for the database. May I know what is the problem behind this? Despite I had provided name for my table. Thank you.
       
       

      States Control.ctl
      User_Info.vi
    • By GregFreeman
      I have been getting some hard crashes in my built application and I have some strange feeling (although it's just a guess) that it is related to a combination of using the database toolkit in a pool of workers that are launched by the start async call node. Their job is to sit there and monitor a directory then parse data files and throw their contents at the database through a stored procedure call. All my hardware comms (2 instruments) are using scpi commands through VISA GPIB and TCP so I think the risk that those are causing crashes is relatively low.
      Active X inside a bunch of parallel threads seems far more risky, even though as far as I can tell adodb should be thread safe, and each thread manages its own, unshared connection. These crashes are happening ~once a day on multiple test stations. I have sent the crash dumps to NI but am waiting to hear back. Right now I am grasping at straws because when I look at the dump file it's pointing me to function calls in the lvrt dll which does nothing for me. 
      I am mostly just looking for any debugging suggestions or direction to getting this resolved more efficiently than just disabling code one loop at a time. I'm also curious if anyone has seen something similar. For the time being, I have reduced my number of workers to 1 in case it is a thread safety issue. I have also considered getting rid of that DB toolkit and leveraging .NET, even though I think that is just a wrapper around the same calls. Looking inside the database toolkit VIs alone scares me.
      FWIW I am using LabVIEW 2013 in this application.
      Thanks!
       
×
×
  • Create New...

Important Information

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