9 .1 .1 Connect to the Database Connect to the Database
Before you can access data in a table or execute SQL statements, you must establish a connection to Before you can access data in a table or execute SQL statements, you must establish a connection to a database. You may use different methods in order to connect to the database:
a database. You may use different methods in order to connect to the database:
ODBC Data Source Name (DSN)ODBC Data Source Name (DSN)
Universal Data Link (UDL)Universal Data Link (UDL)
Connection StringConnection String
These different methods are explained below.
These different methods are explained below.
For all of these methods, you will use the same VI:
For all of these methods, you will use the same VI:
Connecting to a database is w
Connecting to a database is where most errors occur because each database management systemhere most errors occur because each database management system (DBMS) uses different parameters for the connection and different levels of security. The different (DBMS) uses different parameters for the connection and different levels of security. The different standards also use different methods of connecting to databases. For example, ODBC uses Data standards also use different methods of connecting to databases. For example, ODBC uses Data Source Names (DSN) for the connection, whereas the Microsoft ActiveX Data Object (ADO) standard Source Names (DSN) for the connection, whereas the Microsoft ActiveX Data Object (ADO) standard uses Universal Data Links (UDL) for the
uses Universal Data Links (UDL) for the connection. The “DB Tools Open Connection.vi” VI connection. The “DB Tools Open Connection.vi” VI supportssupports all these methods for connecting to a database.
all these methods for connecting to a database.
When you are finished with reading from the database and writing to the database, you should When you are finished with reading from the database and writing to the database, you should always
always close the Connection. Use the “DB Tools Close Connection.vi”.close the Connection. Use the “DB Tools Close Connection.vi”.
40
40 LabVIEW LabVIEW Database Database Connectivity Connectivity ToolkitToolkit
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
9.1.1
9.1.1 DSN DSN
A
ADSN (ODBC Data Source Name (DSN))DSN (ODBC Data Source Name (DSN)) is the name of the data source, or database, to which you is the name of the data source, or database, to which you are connecting. The DSN also contains information about the ODBC driver and other connection are connecting. The DSN also contains information about the ODBC driver and other connection attributes including paths, security information, and
attributes including paths, security information, and read-only status of the database. Two mainread-only status of the database. Two main types of DSNs exist: machine DSNs and file DSNs. Machine DSNs are in
types of DSNs exist: machine DSNs and file DSNs. Machine DSNs are in the system registry and applythe system registry and apply to all users of the computer system or to a single user. DSNs that apply to all users of a computer to all users of the computer system or to a single user. DSNs that apply to all users of a computer system are system DSNs. DSNs that apply to single users are user DSNs. A file DSN is a text file with a system are system DSNs. DSNs that apply to single users are user DSNs. A file DSN is a text file with a .dsn extension and is accessible to anyone with proper permissions. File DSNs are not restricted to a .dsn extension and is accessible to anyone with proper permissions. File DSNs are not restricted to a single user or computer system. Use the ODBC Data Source Administrator to create and configure single user or computer system. Use the ODBC Data Source Administrator to create and configure DSNs.
DSNs.
In the Control Panel, Administrative Tools, you find the ODBC Data Source Administrator tool.
In the Control Panel, Administrative Tools, you find the ODBC Data Source Administrator tool.
41
41 LabVIEW LabVIEW Database Database Connectivity Connectivity ToolkitToolkit
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
Example: DSN Example: DSN
This Example specifies a DSN called MS Access to open a connection to that specific
This Example specifies a DSN called MS Access to open a connection to that specific database.database.
Example: DSN from File Example: DSN from File
You can use a path to specify a file DSN. This example specifies a path to
You can use a path to specify a file DSN. This example specifies a path to a file DSN calleda file DSN called
“access.dsn” to open a connection to the database.
“access.dsn” to open a connection to the database.
Example: DSN with UserId and Password Example: DSN with UserId and Password Most Database systems (DBMS
Most Database systems (DBMS – – Database Management Systems) also require a Database Management Systems) also require a UserId and aUserId and a Password.
Password.
9.1.2
9.1.2 UDL UDL
Whereas you must create a DSN to connect to a database using ODBC, you use
Whereas you must create a DSN to connect to a database using ODBC, you use UDL (Universal DataUDL (Universal Data Link)
Link) to connect to databases that use ADO and OLE DB. to connect to databases that use ADO and OLE DB.
A UDL is similar to a DSN in that it describes more than just the data source. A UDL specifies what A UDL is similar to a DSN in that it describes more than just the data source. A UDL specifies what OLE DB provider is used, server information, the user ID and password, the default database, and OLE DB provider is used, server information, the user ID and password, the default database, and other related information.
other related information.
In order to create a new UDL file, create an empty text file and c
In order to create a new UDL file, create an empty text file and c hange the file extension of thishange the file extension of this document from .txt to .udl. You then can double-click the UDL fi
document from .txt to .udl. You then can double-click the UDL fi le to display the Data Link Propertiesle to display the Data Link Properties dialog box.
dialog box.
42
42 LabVIEW LabVIEW Database Database Connectivity Connectivity ToolkitToolkit
Tutorial: Database Communication in LabVIEW Tutorial: Database Communication in LabVIEW
Example: UDL Example: UDL
Connect to a Database using UDL:
Connect to a Database using UDL:
9.1.3
9.1.3 Connection String Connection String
Rather than including an existing UDL in an application, you also can use an ODBC connection string Rather than including an existing UDL in an application, you also can use an ODBC connection string with the Microsoft ActiveX Data Object (ADO)
with the Microsoft ActiveX Data Object (ADO) standard.standard.
A connection string is written like this:
A connection string is written like this:
PROVIDER
PROVIDER=SQLOLEDB;=SQLOLEDB;DATADATA SOURCE
SOURCE=server_name;=server_name;UIDUID=user_name;=user_name;PWDPWD=password;=password;DATABASEDATABASE=database_name;=database_name;
You could use more parameters, but
You could use more parameters, but the parameters used above are the most common othe parameters used above are the most common o nes.nes.