Example Program Drafts

cancel
Showing results for 
Search instead for 
Did you mean: 

Native LabVIEW TCP/IP Connector for mySQL Database

*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

Comments
Member TLE
Member

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.

Member andcell
Member

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

Member MarcoPolo5
Member

did you try "localhost" or "127.0.0.1"?

Member zlavick
Member

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.

Member andcell
Member

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.

Member MarcoPolo5
Member

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

Member zlavick
Member

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.

Member huwemajor
Member

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

Member MarcoPolo5
Member

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.

Member huwemajor
Member

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

Active Participant Neil_S.
Active Participant

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.

Member contrrol
Member

If connect form windows to windows. How to do it

Member markza
Member

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?

Member MarcoPolo5
Member

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.

Active Participant smithd
Active Participant

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

Member markza
Member

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

Member ferrousduke
Member

Have you tried it on Win CE based OS?

Active Participant Olivier-JOURDAN Active Participant
Active Participant

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 | Certified LabVIEW Architect |
Member jatinpatel1489@gmail.com
Member

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
Member tshelleyamrc
Member

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.

Member MarcoPolo5
Member

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.

Member tshelleyamrc
Member

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

Member AndUrb
Member

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.

Member Sapiophile
Member

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!

Member Gedeon1310
Member

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

Member arno_phil
Member

Thank you for this work around...

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

Active Participant nanocyte
Active Participant

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

Member GYY_HAPPY
Member

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