www.natinst.com

1
SQL Toolkit For LabVIEW
www.natinst.com
2
Goals

• Database Terminology
• What is SQL?
• What is ODBC?
• SQL Commands
• Easy SQL VIs
• Advanced SQL VIs
• Dynamic SQL
• Misc VIs for SQL
• Troubleshooting Tips

3
Database Terminology

• Table Database models are based on data
  structures called tables. Each table consists of
  rows and columns. Tables are uniquely identified
  by their names that can be addressed in SQL
  command.
• Record In simple words a record is a row in
  table.
• Field It is the column of the table. Columns
  contain the column name, data type, and any other
  attributes for the column.
• Primary Key Its a field in the table which
  uniquely defines each record in the table. e.g
  Social Security can be a primary key in the
  employees database to uniquely define a
  particular employee. Keys are used to related two
  or more tables

4
Database Terminology (cont)

• RDBM (Relational Database Management System)
• It is the set of software package thats
  accesses the data which must use the information
  in the tables to put together related data.
• e.g MSAccess 97,SQL Server, Oracle etc.
• DSN (Data Source Name)
• DSN is a unique name used to refer to a specific
  database. ODBC driver uses this name to
  communicate to the physical database (local or
  remote).
• Recordset Collection of data produced that meets
  your search criteria.

5
Table
6
What is SQL? (Structured Query Language)

• Developed by IBM in the late-1970s.
• ANSI SQL SQL-86/-89/-92
• It is the standard language for relational
  database management systems (RDBMs).
• SQL statements are used to log on to, create,
  store, modify, retrieve, sort, and manage data in
  databases.

7
What is ODBC? (Open DataBase Connectivity)

• The Open Database Connectivity (ODBC) Standard
  was developed by Microsoft Corporation.
• This standard provides a common way of accessing
  databases from a variety of other
• Databases
• Applications
• This standard consists of a driver packaging
  standard, a multi-level application programming
  interface (API) definition, a common SQL
  implementation based on ANSI SQL, and a means for
  defining and maintaining DSNs

8
What is ODBC?
LabVIEW SQL VIs
ODBC Drivers (DLLs)
ODBC
DSN (Data Source Name)
Databases
Dbase, FoxPro, MSAccess etc
9
ODBC Administrator
SQL Toolkit DSN
DSN
10
SQL Commands

• SELECT Initiates the search for all the rows in
  a table whose column data satisfy specific
  combination of conditions. e.g
• SELECT col names FROM Table Name WHERE
  condition
• SELECT FirstName,LastName FROM Employees
  WHERE FirstNamejohn
• SELECT FROM Employees
• INSERT Adds a new data row to the table,allowing
  values to be specified for each column. e.g
• INSERT INTO Table Name Col Names VALUES
  Col values
• INSERT INTO Employees (FirstName,LastName)
  VALUES (Abdullah,Rafiq)
• Can be other clauses e.g GROUP BY,ORDER BY,JOIN
  BY etc.

11
Easy SQL VI

• One VI is complete SQL session
• Easy to use
• Not very flexible
• Not good for repeated
• write/reads

12
Easy SQL VI (cont)
Last Name
Empolyees Table
First Name
13
Advanced SQL VIs

• Just like File I/O VIs, You open the session,
  execute the query,fetch the results and then
  close the session in the end.
• Connection Reference is the reference to the ODBC
  table that you are connected to.
• Recordset Reference is the reference to the
  recordset of the recent SQL command.

14
Advanced SQL VIs (cont)

• Its flexible and fast because you can run multipe
  commands while having one session open. e.g I can
  open the session, run couple of insert commands
  and in the end read the latest content of
  database by a SELECT command.
• Close the recordset reference after each SQL
  command.

15
Advanced SQL VIs (cont)
Empolyees Table
Inserted record
16
Dynamic SQL

• Dynamic SQL is beneficial when a statement needs
  to be executed more than once with only the data
  varying in the statement. e.g inserting more than
  one record.
• Inserting records using dynamic SQL can be
  several times faster than executing a new SQL
  statement for each record insertion.
• The placeholder character ? indicates where the
  values are assigned.
• The parameter number for placehonlder? starts
  with 1 and not with 0.

17
Mics VIs in SQL

• Start Transaction Starts the database
  transaction series on a specified database
  connection. Changes are placed in the temporary
  buffer.
• Commit Transaction Causes the database to enter
  all changes permanently that have been made to
  the content since the last call to Start
  Transaction VI.
• Rollback Transaction Causes the database to
  discard all changes permanently that have been
  made to the content since the last call to Start
  Transaction VI.

18
Use of Transaction VIs

• Temperature values that are above 100 are
  actually written to the database,
  others are erased from the temporary buffer.

19
Mics Vis in SQL (cont)

• Get ISO Level Determines the current isolation
  level for the connection.
• Get Supported ISO Levels Returns the isolation
  levels supported by that database.
• Set ISO Level Sets the isolation level for that
  specified connection.
• SQL Information VIs These Vis return information
  about DSNs,Tables, column names,column types,
  active connections etc.

20
More Information

• Run the SQL Examples in Labview on DBV DEMO
  DBASE.
• Useful troubleshooting tool as well as good
  start at SQL.
• (..\Labview\Examples\SQL\)
• SQL Toolkit For G Reference Manual other
  literature.