Example Code

Native LabVIEW TCP/IP Connector for mySQL Database

Code and Documents

Attachment

*Note* In addition to this unsupported example, NI partner SAPHIR has released an officially supported toolkit called SmartSQLVIEW that enables connecting to mySQL databases directly from LabVIEW Real-Time targets such as CompactRIO and PXI. The toolkit features a simple API, utilities to help connect to a database and view tables, and documentation/support from SAPHIR. You can view additional information and purchase here (http://www.saphir.fr/GB/products/toolkits/SmartSQLVIEW_for_MySQL.html).

 

Introduction

This example is a pure LabVIEW implementation to connect to a mySQL database (4.1 and later). Direct TCP/IP messages are sent and received from the example VI's and not through any abstraction layer such as ODBC. This example is meant for those needing to connect to a mySQL database from LabVIEW RT or other non-Windows platform (Linux, MacOS, touchpanel, PDA . . i.e. - any LabVIEW target with TCP/IP access). Those on Windows will find more stability and features with the LabVIEW Database Connectivity Toolkit which will allow connection to any database linked with Windows ODBC.

 

Steps to Complete

The attached zip file contains connect, query, and close methods. A basic example in included.

 

Additional Notes

Minimal testing has been done with this example - please post bug corrections when found. This link provides the low level details for connecting to a mySQL database.

This example does not support encryption or compression on data sent to or received from the mySQL server and sets the appropriate connection flags so that the server will not send such messages.

 

Performance

For queries with no or small sets of returned data, expect 10-20% improvement in processing time over the LabVIEW Database Connectivity Toolkit since this example does not have the additional overhead of sending data through ODBC. However - large returned datasets will take significantly longer (10x+) than the LabVIEW Database Connectivity Toolkit. Memory is not preallocated for returned datasets and dynamically building large string arrays causes significant time with memory allocation. I have not had time to look into this and provide a good method - if anyone has good ideas or a solution your feedback is welcome.

 

The attached Code is provided As Is, subject to the Sample Code License Terms. It has not been tested or validated as a product, for use in a deployed application or system, or for use in hazardous environments. You assume all risks for use of the Code and use of the Code is subject to the Sample Code License Terms which can be found at: http://ni.com/samplecodelicense

Example code from the Example Code Exchange in the NI Community is licensed with the MIT license.

Comments
TLE
Member
Member
on

This a great start, gives a cRIO the ability to log to mySQL at an impressive rate.  As stated already, retrieving data is very slow.  However for my application, the cRIO puts the data into the database, I then use a Windows application to mine the data.

I put together a quick example and am currently executing a 99 field insert query in 2 msec!  It is running in a 250 msec timed loop on a cRIO 9012 and the CPU usage is averaging about 9%.  The mySQL database is located on a Windows XP machine.

andcell
Member
Member
on

Great! But i have problem when try to connect in localhost! (WHY???)

MarcoPolo5
Member
Member
on

did you try "localhost" or "127.0.0.1"?

zlavick
Member
Member
on

Hello, have problem when getting rows with NULL values. "Variant To Data" can't convert empty variant value (if it is in array) to string.

I don't know would be it correctly, but maybe such values should be converted to '' (empty string) in lv_mysql_connector_v1_lv8\subVIs\lv_mysql_conn_return_packet.vi ?

The error appeared in SQL Query "DESCRIBE `table`": http://img96.imageshack.us/img96/3586/t5h4560h.png. The length of subarrays are not equal. '6' for first row and '5' for others.

halfsolution: I've made short fix: http://img441.imageshack.us/img441/8950/gegasg.png but it doesn't works if next rows are longer than previous one.

upd: now 'that error' arrived : http://img156.imageshack.us/img156/4937/43sadgf.jpg and SQL Create Table:

     CREATE TABLE  `technoap`.`logs` (

       `id` int(11) NOT NULL AUTO_INCREMENT,

       `text_utf` text,

       `col2` double DEFAULT NULL,

       `col3` blob,

       `col4` blob,

       `col5` smallint(5) unsigned DEFAULT NULL,

       `boolean` enum('true','false') DEFAULT 'false',

       PRIMARY KEY (`id`)

     ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Should be fixed, i think.

andcell
Member
Member
on

I had try "localhost" and 127.0.0.1, but the vi give to me the TCP/IP error. I don't have specified an important thing: the problem is only on Linux machine with mysql server ver. 5.0.77. In the vi lv_mysql_conn_init_server_data.vi i can view the protocol version, server version, server capabilities (A22C), etc, but after this the vi hang and give me the error "56" (TCP Read in lv_mysql_conn_receive_data_packet.vi). When i make a connection with a remote host is all OK. I had try on windows machine and is all ok, except for the problem of zlavick.

Bye! Andrea.

MarcoPolo5
Member
Member
on

zlavick - thanks for the feedback. You could also try changing the VI to build an array of strings vs variants (this also improves read performance ~5x because of the overhead of variants). The reason I kept it as variants was to mimic the LV Database Connectivity Toolkit. Perhaps that would also solve these issues you are having.

FYI: there have been a few comments on LAVA about this code about improving performance: http://lavag.org/topic/12161-accessing-mysql-from-a-rt-vxworks-target/page__p__73374

zlavick
Member
Member
on

Error?

When i read big binary data i'm getting only 254 bytes (but really it is 2402). I think trouble is in 'lv_mysql_conn_return_packet.vi'  ( http://img229.imageshack.us/img229/49/packeparseterror.png ).

I have only six columns in my SQL query: "SELECT col0,col1,col2,col3,col4,col6  FROM data", but function returns 78. That's because string was converted to byte-array (at lv_mysql_conn_receive_data_packet.vi)  and it can't get length more than 255 bytes. How it should be fixed correctly? How MySQL returns binary rows? [length]+[data] format or it splits row into "255"-bytes pieces? How they should be joined into one binary row?

UPD:

I don't know how it shoud be done correctly (how mysql packs big data), but here a fix, that solves my problem.

huwemajor
Member
Member
on

Possible error.

I've just started to use this set of vi's and they seem to work great for small data entries. However, if I try to save large blobs of data into the database I get the following error

Error 66 occurred at TCP Read in lv_mysql_conn_receive_data_packet.vi->lv_mysql_conn_return_packet.vi->lv_mysql_conn_query.vi->saveData.vi->dataBaseTest.vi
Possible reason(s):
LabVIEW:  The network connection was closed by the peer. If you are using the Open VI Reference function on a remote VI Server connection, verify that the machine is allowed access by selecting Tools>>Options>>VI Server on the server side.

It seems that saving binary blobs bigger than about 500kb causes this error to occur. Although this is the only error message I can get it to spit out now i did once get it to indicate that the packet size had been exceeded.

Any suggestions would be much appreciated. I suppose I could just not save the actual data in the database and just save a reference to the file position if needs be.

cheers

Huw

MarcoPolo5
Member
Member
on

Interesting problem - not sure where the issue lies. I would first confirm that the mySQL table / server is setup to handle such large binary blobs (i.e. - can you transfer the same data with the LV Database Connectivity Toolset or other input method?). I have solved an issue like this a different way. My project required keeping track of binary files (though not sorting through the info inside the fiels). I ended up FTP'ing the files to a location on the server, re-naming the server copy with a generic numeric value, and then storing the info about the new file name in a mySQL table. When I needed to retrieve the file, I could search the database table, find the new numeric file name, FTP it back and re-name it (all programatic of course). The whole re-naming issue was to ensure I could upload files with the same name but different contents if needed. The FTP seemed to be more reliable than filling up my database with tons of binary data. Hope that helps.

huwemajor
Member
Member
on

Dear Marc,

Thanks for the help. It wasn't your code at fault it was completely my mistake. The mysql packet size of my server was set to 1MB so it would timeout trying to send anything bigger. I upped the packet size to 16MB and it is all fine.

Many thanks for your help and excellent code.

Regards

Huw

Neil_S.
Active Participant
Active Participant
on

I know your post is from a while ago, but I was looking to use this approach to connect to a MySQL database on a Linux OS and ran into the same thing as andcell.  After a bit of investigation (including a bit of Wireshark sniffing) I was able to determine the problem is related to old MySQL style password hashing discussed here.  This particular implementation doesn't support old style password hashing and you probably have old_passwords=1 in your my.cnf file.  Changing it won't be enough since users previously added will still be using the old style hashing.  If a password is scrambled using the old style hashing and the client implements the new style of hashing than the MySQL server may request that the client also send the old style hashed version of the password.  It does this by using a special request packet that only contains a single byte 0xFE.  Since this code doesn't expect this byte it misinterprets it as an EOF and tries to make an additional TCP read rather than sending the password using different scrambling.

While avoiding old style passwords would avoid this entirely I may implement this modification if I get some time.  It would be nice if this was able to handle these old style of passwords to make it somewhat backwards compatible.

For more details you can take a look in the mysql source code.  In version 5.1.52 in sql-common/client.c on line 2412 you can see where the client sends the scrambled password again in response to the special packet.

contrrol
Member
Member
on

If connect form windows to windows. How to do it

markza
Member
Member
on

How easy would it be to modify this code to have access to a MS SQL 2008 database?  How different are MS SQL and mySQL?

MarcoPolo5
Member
Member
on

As far as I know MS does not publish the make-up of TCP/IP packets to communicate w/ MS SQL 2008 database. This would likely require significant effort and changes. This code was made for mySQL since it is open-source and documentation is available for the direct TCP/IP communication to the database.

smithd
Active Participant
Active Participant
on

Hey all,

Big thanks to marco polo for this example--its great, and it (to me, at least) makes it a whole lot easier to understand databases then all of the ODBC stuff. Of course, I then went ahead and edited the binary parsing process rather than trying to figure out ODBC, so maybe that is just me being silly.

Anyway, so I found a bug (the same bug zlavik found above. Basically, if you have large strings over 255 chars, they will be cut off by this example and split across multiple columns. I went through and found that the parsing process is slightly off the spec, and fixed it here:

https://decibel.ni.com/content/docs/DOC-17093

I hope this helps folks using this for larger strings, and thanks again to marcopolo for the great work.

Thanks,

D Smith

markza
Member
Member
on

Fair enough (darn!)...thanks for the insight.  Great job on the example.

ferrousduke
Member
Member
on

Have you tried it on Win CE based OS?

Olivier-JOURDAN
Active Participant Active Participant
Active Participant
on

I never used LabVIEW on Win CE. Developpment of LabVIEW for PDA module used to developped application on this OS seems to be sopped with LV2011 --> http://sine.ni.com/nips/cds/view/p/lang/en/nid/12222

Anyway if TCP routines are supported, you should be able to use this example. Concerning MySQL toolkt developped by SAPHIR,availble here http://sine.ni.com/nips/cds/view/p/lang/en/nid/210228, lvclass needs to be supported too.


Olivier Jourdan

Wovalab founder | DQMH Consortium board member | LinkedIn |

Stop writing your LabVIEW code documentation, use Antidoc!
jatinpatel1489@gmail.com
Member
Member
on

Hello

While trying to connect with mysql I am getting following error,

1045 | #28000Access denied for user 'admin'@'localhost' (using password: YES)

I used the default user name and password of the given vi.

can you please help me with what other thing do I have to add into this ?

CLAD
Passionate for LabVIEW
tshelleyamrc
Member
Member
on

Hello,

I notice knowone has posted here for a while put here goes...

I am researching the above code to connect to a MS SQL (not a mysql) database from my cRIO 9022 and or sbRIO. I have setup the sql database and can successfully communicate with it through the windows ODBC setup and MS SQL studio manager.. great. To note: the SQL DB instance is installed on a Virtual Machine and does not have a static IP.

From my understanding of ODBC and creating a connection to a SQL database, i also need to pass database paramters (name, port) so that a TCP/IP connection/route can be achieved with the SQL database browser, which will allow a connection to the DB i want to use. Please add comments**** Currently an IP address is not enough. I have also tried to pass a string of data IPADDRES/SQLinstance to the  LV TCP block, but this produces and error aswell. 

When i run the LV code above, a TCP open connection is made to virtual machine, although an error 56 occurs when i want to read bytes of data back, essentially there has been a time out because no data has been recieved. This i can fully understand because the VM will not send back data..

My question is how do i create the TCP link & connect to the SQL instance install on my VM, to allow queries to be established.

If anyone has any question i would be more than happy to try and explain.

Many Thanks to anyone who takes the time to answer/respond to my question.

MarcoPolo5
Member
Member
on

This code is meant for directly connecting to a mySQL database only. If you have a MS SQL database you must go through the ODBC (Microsoft does not publish the details of the backend communication directly to their DB so creating an equivelent driver for MS SQL would be very difficult).

If you have a Windows PC with the DB on it; I would create a seaparate LV program running on the Windows PC to receive TCP/IP communication from your RT device and then use the NI Database toolkit within that Windows PC LV program to connect to your MS SQL via ODBC.

tshelleyamrc
Member
Member
on

Hi Marco,

Thank you for your reply and clear explainantion. I will investigate your proposal, i have already communicated with the MS SQL DB using the windows NI toolkit. I will also investigate getting a mySQL database setup, SQLite is also another option.

The preferred option was to have the embedded system communicate directly with the database, as the number of embedded systems is likely to grow and it is not feasible to have a "MS windows gateway", to push the data into the database.

Tim

AndUrb
Member
Member
on

Hi

Maybe I am few years too late but i didn't find a solution to a NULL problem posted here and since it is an easy fix I might as well write about it..

When I went through the code and the hex values returned from TCP/IP I found out the problem is that the NULL value doesnt have length associated to it so you have to do it instead..

In lv_mysql_conn_return_packet.vi look in a "Row" packet type look for "Index Array" function and compare it to NULL value (251 decimal). If equal change it to 1.

Bare in mind this might couse problems if you have bigger set of data (251 decimal in length specifically) since you treat it as NULL instead of length.

Sapiophile
Member
Member
on

Hi all,

Since five years I'm using this tool kit and still it works like a charm! Thank you very much Mr. Developer. Now I'm struggling the first time with special characters. In Swiss-German we have this ä, ö, ü which are not proceeded correctly through the client. Can someone help?

Thank you guys!

Gedeon1310
Member
Member
on

Hi,

I may be writing at the wrong place, but does anyone know how to send/receive special characters using lv_mysql_connector ? I first thought errors were related to my database collation, but it actually occurs during ''check data type''.

As an example the following command '' :

insert into tablename (column_name) values ('è'); is correctly executed under MySQL Workbench, but the exact same commend passed through lv_mysql_connector returns error, (case 255, in lv_mysql_conn_check_paket_type.vi).

Any help/comment would be appreciated,

thanks

arno_phil
Member
Member
on

Thank you for this work around...

In my case, I add to replace 251 by 0 (not by 1)...

nanocyte
Active Participant
Active Participant
on

Is the communication secure (TLS/SSL) or are the authentication strings sent as plain/text?

GYY_HAPPY
Member
Member
on

This is a great example to help me solve the big problem.

derandyk
Member
Member
on

Hi guys...

 

Great implementation! Really like that basic low level stuff.

 

It works great, but is there any progression regarding the issue of large returned data sets?

 

My "Select * from ..." Query with a database of 8 columns returns 3000 entries... this takes approximately 15sec.... 😑

If i do the same Query in the Windows System over the mySQL Workbench, it takes 0.1sec... 

 

Has somebody the same issue? 

 

Thank you in advance!

Greetings

joerg.hampel
Active Participant Active Participant
Active Participant
on

It seems that many people are using these VIs. Some have implemented fixes and improvements for themselves. As there is no easy way to collaborate on code posted to the Example Programs, I got in touch with @MarcoPolo5 (the original poster), and with his consent we republished the code he shared on our GitLab server. Thanks and kudos, Marc!

 

The version in the repo is the one we are currently using, which has been improved by @swatts and @Mark_Yedinak, amongst others. We also added some fixes for UTF support. We intentionally left it exactly the way it is (eg not wrapping the TCP reference into a class) to keep backwards compatibility. 

 

The main distribution path for this is as VI package on vipm.io:

https://www.vipm.io/package/hse_lib_hse_mysqlnetcom/

 

The homepage of the new project (still quite empty) is found on our wiki: 

https://dokuwiki.hampel-soft.com/code/open-source/mysqlnetcom

 

Here is the link to the repository, in case you're interested in contributing:

https://code.hampel-soft.com/open-source/mysqlnetcom  

 

Please help us improve this great tool by contributing your bugfixes!!


DSH Pragmatic Software Development Workshops (Fab, Steve, Brian and me)
Release Automation Tools for LabVIEW (CI/CD integration with LabVIEW)
DQMH® (The Future of Team-Based LabVIEW Development)