aX ODBC Features

1
aX ODBC Features


OpenDataBaseConnectivity
Understand what ODBC is all about and how it is
used. Do the Software-Setup on your operating
system and aX. Know which ODBC functions are
available in aX. Solve problems caused by
connection failures.

• Training aX Basics

2
aX-ODBC Overview

• Introduction to ODBC
• ODBC Client/Server Architecture
• Structured Query Language (SQL)
• ODBC configuration on Windows NT
• ODBC configuration on automationX
• ODBC functions Conn, Read, Add, Update, Delete
• Exercise Read a data set from a specific table
• Troubleshooting

3
Introduction To ODBC

• ODBC is an acronym for Open Data Base
  Connectivity.
• It is a standardized set of application
  programming interface (API) that can be used to
  access data stored in database management systems
  (DBMS).
• ODBC uses a client / server model which can be
  used on one host or across the network.
• To use ODBC, you need to create a Data Source
  Name (DSN).

4
ODBC Client/Server Architecture
For a connection to a DBMS, you need
5
Structured Query Language

• SQL stands for Structured Query Language.
• SQL is an invention of IBM.
• The official standard is called ANSI SQL92.
• SQL is text based and easy to use.
• SQL is implemented in the most DBMS systems.
• SQL is the data protocol between ODBC and the
  DBMS.

6
Structured Query Language

• SQL stands for Structured Query Language
• SQL is an invention of IBM
• The official standard is called ANSI SQL92
• SQL is text based and easy to use
• SQL is implemented in the most DBMS systems
• SQL is the data protocol between ODBC and the DBMS

7
ODBC Configuration on Windows NT

• First we have to create a DSN
• Invoke the ODBC Data Source Administrator on the
  Control Panel by double clicking the 32bit ODBC
  icon.
• Next, select the System DSN tab.

8
ODBC Configuration on Windows NT

• First we have to create a DSN
• Invoke the ODBC Data Source Administrator on the
  Control Panel by double clicking the 32bit ODBC
  icon

9
ODBC Configuration on Windows NT

• Click on the Add Button and select the
  corresponding driver to your database (e.g.
  Microsoft Access Driver )
• In the ODBC Configurator window (e.g. ODBC
  Microsoft Access Setup, depending on the DBMS
  used) enter a DSN (e.g. AXODBCSample)

10
ODBC Configuration on aXStep 1
When creating a new ODBC variable the ODBC Wizard
appears

• Select the DSN created before (AXODBC Sample).
• Login
• From the underlying tables, select the table,
  procedure or view you want to use.
• Click Next to open the second window of the
  ODBC Wizard.

11
ODBC Configuration on aXStep 2

• Enter the desired
• table row count.

Click Finish
12
ODBC Configuration on aXStep 3

• Enter a variable name

Enter all variable flags and parameters as
you need
13
ODBC functions Conn, Read, Add, Update, Delete

• automationX provides the following ODBC function
  blocks
• ODBCConn to establish a single line connection to
  DBMS.
• ODBCRead to query data from DBMS.
• ODBCAdd to append data to DBMS.
• ODBCUpdate to change existing data sets.
• ODBCDelete to delete specific data sets.

14
Exercise read a data set from a specific table

• Insert a ODBCRead from automationX library

Number of read datasets
If an error is pending, err is set to 1
active is set during an DBMS read
Time in ms spent in the DBMS operation

• Connect the ODBC variable to the ODBCTable
  Input.
• Create an SQL Query if needed, otherwise leave it
  open.
• If trigger is set to 1, one read operation will
  be performed.
• If logger is set to 1, all errors will be
  logged in the Journal under ODBC.
• If dbg is set to 1, all errors will be
  reported in an online debug window.

15
aX-ODBC Troubleshooting

• The ODBC function block remains activated for an
  infinite time
• Make sure the axodbcserver is enabled in
  UserProcesses

• The ODBC function block reports an error
• Make sure the logger input is set to 1.
• If the logger input is set, all errors are
  reported in the ODBC journal.
• For detailed information use the NT/2000 ODBC
  logger.

• The ODBC driver reports that no more
  connections are valid
• Use the ODBCConn function block to create a
  single connection to the DBMS.

16
Reference Material

• Reference material can be found on our web-site
  www.automationX.com
• in automationX System Manual, chapters
• Function Block Classes
• Tools and Extended Functions
• in Support gt Technical FAQs
• The practical SQL Handbook, Addison - Wesley
• ODBC 3.5 Developers Guide, McGraw-Hill

End
End