Control De Inventario

Telemark University College

Department of Electrical Engineering, Information Technology and Cybernetics

Faculty of Technology, Postboks 203, Kjølnes ring 56, N-3901 Porsgrunn, Norway. Tel: +47 35 57 50 00 Fax: +47 35 57 54 01

Database Communication in LabVIEW

HANS-PETTER HALVORSEN, 2011.02.11

2

Preface

This document explains the basic concepts of a database system and how to communicate with a database from LabVIEW.

You should have some basic knowledge about LabVIEW, e.g., the “An Introduction to LabVIEW” training. This document is available for download at http://home.hit.no/~hansha/.

In addition to LabVIEW Professional Development System, you need to install the “LabVIEW Database Connectivity Toolkit”.

For more information about LabVIEW and Databases, visit my Blog: http://home.hit.no/~hansha/

Some text in this document is based on text from www.wikipedia.org and “LabVIEW Database Connectivity Toolkit User Manual”.

iii

Table of Contents

Preface ..................................................................................................................................................... 2

Table of Contents .................................................................................................................................... iii

1 Introduction to LabVIEW ................................................................................................................ 1

1.1 Dataflow programming ........................................................................................................... 1

1.2 Graphical programming ........................................................................................................... 1

1.3 Benefits .................................................................................................................................... 2

2 Database Systems ........................................................................................................................... 3

2.1 RDBMS Components ............................................................................................................... 3

2.2 Data warehouse ...................................................................................................................... 4

2.3 Relational Database ................................................................................................................. 4

2.4 Real-time databases ................................................................................................................ 4

2.5 Database Management Systems ............................................................................................. 5

2.6 MDAC ....................................................................................................................................... 5

2.6.1 ODBC ................................................................................................................................ 5

2.6.2 OLE DB ............................................................................................................................. 5

2.6.3 ADO (ActiveX Data Objects) ............................................................................................ 6

3 Relational Databases ...................................................................................................................... 7

3.1 Tables....................................................................................................................................... 7

3.2 Unique Keys and Primary Key .................................................................................................. 7

3.3 Foreign Key .............................................................................................................................. 9

3.4 Views ....................................................................................................................................... 9

3.5 Functions ............................................................................................................................... 10

3.6 Stored procedures ................................................................................................................. 10

iv Table of Contents

Tutorial: Database Communication in LabVIEW

3.7 Triggers .................................................................................................................................. 11

4 Structured Query Language (SQL) ................................................................................................ 12

4.1 Queries .................................................................................................................................. 12

4.2 Data manipulation ................................................................................................................. 13

4.3 Data definition ....................................................................................................................... 14

4.4 Data types .............................................................................................................................. 14

4.4.1 Character strings............................................................................................................ 14

4.4.2 Bit strings ....................................................................................................................... 15

4.4.3 Numbers ........................................................................................................................ 15

4.4.4 Date and Time ............................................................................................................... 15

5 Database Modelling ...................................................................................................................... 16

5.1 ER Diagram ............................................................................................................................ 16

5.2 Microsoft Visio ....................................................................................................................... 17

5.3 EXERCISES .............................................................................................................................. 18

6 Microsoft SQL Server .................................................................................................................... 20

6.1 Introduction ........................................................................................................................... 20

6.2 Requirements ........................................................................................................................ 20

6.3 SQL Server Express ................................................................................................................ 20

6.4 AdventureWorks ................................................................................................................... 21

6.5 SQL Server Management Studio ........................................................................................... 21

6.6 Create a new Database ......................................................................................................... 22

6.7 Backup/Restore ..................................................................................................................... 24

6.8 Example Database ................................................................................................................. 25

6.9 Exercises ................................................................................................................................ 27

7 Microsoft Office Access ................................................................................................................ 28

7.1 Introduction ........................................................................................................................... 28

v Table of Contents

Tutorial: Database Communication in LabVIEW

7.2 Example Database ................................................................................................................. 28

7.3 Exercises ................................................................................................................................ 30

8 ODBC ............................................................................................................................................. 32

8.1 What is ODBC?....................................................................................................................... 32

8.2 Create an ODBC Connection in “ODBC Data Source Administrator” .................................... 32

8.3 Get data into Excel using your ODBC Connection ................................................................. 34

9 LabVIEW Database Connectivity Toolkit ....................................................................................... 38

9.1 Connect to the Database ....................................................................................................... 39

9.1.1 DSN ................................................................................................................................ 40

9.1.2 UDL ................................................................................................................................ 41

9.1.3 Connection

...