topic Re: Connection to MySQL Database in CSLUG - Central South LabVIEW User Group (UK)

Connection to MySQL Database

NickRH — Thu, 06 Aug 2015 15:06:01 GMT

Hi all,

Can anyone here help with an error when connecting to an Oracle SQL database?

I`m using the Database Connectivity Toolkit in LV2014 and have created a UDL to connect the databse. The UDL connects and tests just fine but I get the error message below when I try to connect and list tables.

Error Code -2147467259

NI_Database_API.lvlib:DB Tools Open Connec (String).vi->NI_Database_API.lvlib:DB Tools Open Connec (Path).vi<ERR>ADO Error: 0x80004005

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->NI_Database_API.lvlib:DB Tools Open Connec (Path).vi

Does that ring any bells for anyone ?

Thanks in advance,

Nick H

Re: Connection to MySQL Database

swatts — Thu, 06 Aug 2015 16:20:57 GMT

Is it a 32 bit 64 bit issue?

32 bit LabVIEW won't play nice with a 64 bit database.

Re: Connection to MySQL Database

NickRH — Fri, 07 Aug 2015 07:05:46 GMT

Thats a good point, I`m on 32bit Labview and 64bit Windows but i`m not sure what the server IT is using has .

Thank you , i`ll look into it .

Re: Connection to MySQL Database

swatts — Fri, 07 Aug 2015 08:29:49 GMT

The other complication is the odbc admin will be 64 bit, try using this.

Re: Connection to MySQL Database

James_McN — Fri, 07 Aug 2015 08:43:30 GMT

Hi Nick,

Not sure you need to worry about whether the server is running 64 or 32 bit but you just need to make sure that the drivers you have installed locally are 32 bit. Using the last window Steve posted will show you the 32 bit drivers installed.

James

Re: Connection to MySQL Database

NickRH — Fri, 07 Aug 2015 09:27:34 GMT

Thank you Steve and James, I`m looking into that as we speak.

Out of curiosity which method for connection would you both prefer, using the NI DB Tools or through a .NET connection and MySQL connectors ?

Re: Connection to MySQL Database

swatts — Fri, 07 Aug 2015 09:40:30 GMT

Is it MySQL or Oracle Nick?

If it's MySQL I use a modified TCP connection tool kit that chats to the server through port 3306 (I think)

I only use the database connectivity stuff if I really have to. So if I'm using ODBC I will use the very old LabSQL toolkit (It's quicker, easier, licence free)

I object to my customers paying extra for something that should be part of the language by default.

I can email you the libraries if you need them.

Re: Connection to MySQL Database

NickRH — Fri, 07 Aug 2015 09:54:48 GMT

Hi Steve,

Well I didnt personnally setup the DB but IT have informed me that its a MySQL DB so I was wrong to say Oracle ... sorry !

I like the idea of using TCP to connect with LabSQL, if you could email over the libraries that would be great, thank you !

I`ll send you a message with my email.

Cheers,

Nick

Re: Connection to MySQL Database

NickRH — Mon, 10 Aug 2015 09:36:39 GMT

Finally got it all up and running , now just to have a fight with SQL 😉

It was all down to the 64 bit / 32 bit ODBC admin.

Thank you both for your help !

Nick

Re: Connection to MySQL Database

swatts — Mon, 10 Aug 2015 09:41:15 GMT

I can do a demo on SQL creation using Navicat and MySQL if you're interested, I find it makes SQL really simple.

If you cannot wait until our next meeting I can pop up a video.

It's a paid program (about £80) but really saves me time.

Re: Connection to MySQL Database

NickRH — Mon, 10 Aug 2015 10:09:49 GMT

Thanks Steve, I`ll have a look into Navicat. Hopefully by the time we all meet again i`ll have it up and running. I`ll also try our IT department as they already use several SQL databases and we might have something I can just use.

Hopefully I`ll be able to attend the next meet, i`m in a new company now so still getting a feel for attending events, etc

Re: Connection to MySQL Database

swatts — Mon, 10 Aug 2015 19:15:17 GMT

I've uploaded a video on SQL, Navicat etc

Hope it helps

Re: Connection to MySQL Database

NickRH — Tue, 11 Aug 2015 07:07:32 GMT

Thank you Steve, I`ll have a look at that.

I`ve been given a copy of MyWorkbench from MySQL by IT, seems ok so far .....

Re: Connection to MySQL Database

swatts — Tue, 11 Aug 2015 08:28:25 GMT

I always found myworkbench to be great for database design but not so good for SQL creation. That said it gets updated fairly often.

I think it may be a good subject for the group.

Re: Connection to MySQL Database

NickRH — Tue, 11 Aug 2015 08:34:07 GMT

I would have to agree, I`ve tried the `Create New Table` SQL code generator in My Workbench and the SQL code did not work. I had to modify it myself ..... not great. But its reasonably friendly to use apart from that.

I`m not sure how many of us use SQL but I would imagine the majority do. I think your right it might be a useful subject.

Re: Connection to MySQL Database

Ian_Billingsley — Fri, 21 Aug 2015 15:02:47 GMT

Hi Steve,

Im currently working with a My SQL database and would really appreciate it if you could let me have a copy of the modified tcp connection tool kit if its easy to get your hands on?

Are you guys still planning to discuss the my sql database subject at the next user group meeting?

Cheers

Ian

Re: Connection to MySQL Database

swatts — Fri, 21 Aug 2015 20:15:54 GMT

I'll try and get it to you Tuesday mate, I'm having 2 days off in Brighton

Re: Connection to MySQL Database

CRoebuck — Fri, 21 Aug 2015 20:47:21 GMT

Folks,

I've been using the MySQL toolkit from Saphir for the past 18 months. It comes with good documentation and makes connection very simply for first timers. I agree that paying for something feels kind of wrong when there are free toolkits available but in this case the customer needs access in future to the source. Having them use a supported albeit paid for toolkit made sense.

There are enough syntax changes between MySQL and SQL Server 2012 to make building queries enough of a pain anyway.....

Chris

Re: Connection to MySQL Database

Ian_Billingsley — Fri, 21 Aug 2015 21:32:31 GMT

Thank you both.

Steve, enjoy the delights of Brighton!

Chris, thanks for the tip. I shall have a look at the Saphir toolkit.

Hope your both well.

Re: Connection to MySQL Database

swatts — Tue, 25 Aug 2015 09:14:20 GMT

Right Ian,

Back from Brighton.

The files you need are here

>>CSLUG>> MySQL.zip

it's in LV2013 and if you open the lvlib you'll find an example.

If you need help creating queries, I use this tool https://www.youtube.com/watch?v=y2aSaiWKrv0

It's about £80 from Navicat

With regards the next meeting, I think it's going to end up being drinks after the CLD Summit (as most of us will be around then)