query SQL from LabVIEW

[Ian_Ren]

I would like to get the serial number of a part just being tested. This info is stored in a SQL database. The info I got from the database administrator is as follows.  

Host: partdb.mlp.abcd.comPort: 1433

Database Name: part

Read Only User Id: yyyy

Password: xxxx

 Example query for engine serial number between certain time period:  select *from tdheader h inner join tdstage son h.s_sno = s.s_snowhere h.testCellNo = 5and h.endTime between '3-15-2010 7:00:00' and '3-15-2010 8:00:00'

How do I query the database from LabVIEW? Thanks.

Ian

[Ian_Ren]

Example query for engine serial number between certain time period:  

select *

from tdheader h inner join tdstage s

on h.s_sno = s.s_sno

where h.testCellNo = 5

and h.endTime between '3-15-2010 7:00:00' and '3-15-2010 8:00:00'

[Dennis_Knutson]

Do you have the Datebase Connectivity Toolkit? That's the easiet way. If you want something free but requires a bit more knowledge of SQL, try LabSQL.

[Ian_Ren]

I do have Database Connectivity Toolkit (part of the NI Developer Suite), but I know very little about SQL database. I am using LabVIEW 2009. Thanks.

[smercurio_fc]

The specified query should return all columns from the specified join. This would be the columns from the table "tdheader" and the table "tdstage". The toolkit will return a 2D array of variants, so you need to typecast to the appropriate datatype. My guess is that you want a 2D array of strings. Then you need to pick off the column that contains the serial number.

You could limit the select statement to return the column you're interested in by replacing the "*" with the column name. You'll still get a 2D array of variants, but it should only have one column.

As for a SQL tutorial, just use Google. 

[Ian_Ren]

Thanks for your help.

But first thing first, I did not find any example to access a database via a host, a port (1433, TCP/IP) and a database name? All examples I can find has a file name on a hard disk (eg, C:\NI\Demos\dB\connection.udl). Can someone share an example LabVIEW code?  Thanks.

[smercurio_fc]

You can specify the connection using a connection string, a DSN, or a UDL. The manual is pretty clear on this, and it explains how to create a UDL. For a DSN you can use the ODBC Control Panel applet to create the DSN and test the connection. If you use a connection string then you need to specify the connection string. You can ask your database administrator what this should be. You can also try to make it up yourself using the connectionstrings.com web site, which lists connection strings for all sorts of database engines and connection types.

[Hueter]

In order to connect to a database in LabVIEW you need to create a Universal Data Link (UDL) file. The UDL file is is a small file used to specify connection Information for a database. To create a UDL file follow this tutorial:

Creating a Microsoft Data Link file (UDL) for Connecting to a Database in LabVIEW

-Hunter

[smercurio_fc]

---

Hueter wrote:
In order to connect to a database in LabVIEW you need to create a Universal Data Link (UDL) file.

---

That's a somewhat misleading statement. That's one of the ways you can connect. As I indicated, and as the Database Connectivity documentation indicates, you can use a UDL, a DSN, or a connection string. 

[Ian_Ren]

I used a DSN (http://forums.ni.com/ni/board/message?board.id=170&message.id=253700&query.id=3657466#M253700

)and it worked. 

Many thanks to all. 

Ian