LabWindows/CVI User Group Documents

cancel
Showing results for 
Search instead for 
Did you mean: 

LabWindows/CVI Tip: Using the SQL Toolkit

Contents

  1. Features of the LabWindows/CVI SQL Toolkit
  2. Breif Introduction to SQL Programming
  3. Using the LabWindows/CVI Toolkit
  4. Third Party ODBC Databases
  5. Download and Evaluate the SQL Toolkit

Features of the LabWindows/CVI SQL Toolkit

As of LabWindows/CVI 2013 SP2, the SQL Control Toolkit is now included with LabWindows/CVI Full and is fully licensed with the same serial number used to activate LabWindows/CVI development environement.


The LabWindows/CVI SQL Toolkit is an add-on package for accessing databases. The toolkit contains a set of high-level functions for performing the most common database tasks and advanced functions for customized tasks.

  • Works with any provider that adheres to the Active Data Object (ADO) standard.
  • Works with any database driver that complies with Open Database Connectivity (ODBC).
  • Maintains a high level of portability. In many cases, you can port your application to another database by changing the connection string you pass to the DBConnect function.
  • Converts database column values from native data types to standard LabWindows/CVI SQL Toolkit data types, which further enhances portability.
  • The default ADO ODBC provider permits the use of SQL statements with all supported database systems, even non-SQL systems.
  • Includes functions to retrieve the name and data type of a column returned by a SELECT statement.
  • Creates tables and selects, inserts, updates, and deletes records without using SQL statements.

Breif Introduciton to Structured Query Language (SQL) Programming

A database consists of an organized collection of data. While the underlying details can vary, most modern Database Management Systems (DBMS) store data in tables. The tables are organized into records, also called rows, and fields, also called columns. Every table in a database must have a unique name. Similarly, every field within a table must have a unique name.

Database Sessions

Database interactions occur within a database session. A simple session consists of the following steps:

  1. Step 1. Connecting to the Database
    Before you can execute SQL statements, you must establish a connection to a database. The LabWindows/CVI SQL Toolkit supports multiple, simultaneous connections to a single database or to multiple databases.

  2. Step 2. Activating SQL Statements
    With the LabWindows/CVI SQL Toolkit, you can use several methods for activating statements.

    Automatic SQL constructs the statement for you. Automatic SQL can construct only simple SELECT and CREATE TABLE statements.
    Explicit SQL must have the statement passed into the function. Use explicit SQL for more complex SELECT statements or other types of statements.
    Advanced functions give you more control over statement execution.

  3. Step 3. Processing SQL Statements
    In general, only SQL SELECT statements require further processing. Use SELECT statements for the following database operations.

  • Retrieving rows from a table.
  • Updating rows in a table.
  • Creating new rows in a table.

To use a SELECT statement, you must bind the selected columns to variables in your program. You then can use the fetch functions to retrieve the selected rows. Each time you call a fetch function, the LabWindows/CVI SQL Toolkit copies the column values into the bound variables. You also use the bound variables when updating a row or creating a new row. That is, when updating or creating a row, you copy the new values into bound variables and then call the appropriate function. For more details on variable binding, refer to Using the SQL Toolkit Library in the LabWindows/CVI SQL Toolkit Help.

You also can get information about an active SELECT statement, such as the number of columns selected, the name and data type of a given column, and the number of rows selected. This information is useful when selecting all columns (SELECT * ...) in an unfamiliar table or when creating a program, such as a database browser, that must access a variety of tables.

     4. Step 4. Deactivating SQL Statements

After you finish using a statement, deactivate the statement to free system resources. Deactivation is especially important when fetching in any direction; the LabWindows/CVI SQL Toolkit properly closes and deletes temporary log files.

    5. Step 5. Disconnecting from the Database

At the end of a database session, disconnect from the database to free system resources.

SQL Commands

Structured Query Language (SQL) consists of a widely supported standard for database access. You can use SQL commands to manipulate the rows and columns in database tables. The following list describes some of the most useful SQL commands.

  • CREATE TABLE—Creates a new table specifying the name and data type for each column.
  • SELECT—Retrieves all rows in a table that match specified conditions.
  • INSERT—Adds a new record to the table. You can then assign values for the columns.
  • UPDATE—Changes values in specified columns for all rows that match specified conditions.
  • DELETE—Deletes all rows that match specified conditions.

Using the LabWindows/CVI SQL Toolkit

The following figure shows how the major SQL Library functions relate to each other. Funciton references can be found in the LabWindows/CVI SQL Toolkit Help.

CVI_SQL_Function_Summary.png

Third-Party ODBC Database Drivers

The LabWindows/CVI SQL Toolkit supplies a few ODBC database drivers. Database system vendors and third-party developers offer more powerful versions of these drivers. They also offer drivers for other database systems. The LabWindows/CVI SQL Toolkit complies with the ODBC standard, so you can use it with any ODBC-compliant database drivers. Refer to your vendor documentation for information about registering your specific database drivers with the ODBC Data Source Administrator.

Download or Purchase the SQL Toolkit

>> Download and evaluate the LabWindows/CVI SQL Toolkit for free

>> Purchase the LabWindows/CVI SQL Toolkit

Did you find this tip useful? Rate this document or add a comment below.

If you give this a try, share your experience! Add a comment below.


National Instruments
Contributors