ODBC Learning Examples - Tier 2 is a suite of applications designed to provide an informative guide to developing ODBC applications as well as providing simple building blocks for ODBC applications.  The samples are split into eleven distinct modules. Within each module there is a range of applications usually with a unifying lesson in mind. This document contains instructions for the use and the descriptions behind each module and the sample applications contained within that module.

The code within these applications is thoroughly, but selectively, documented. This means that the earlier modules, and applications that appear earlier in the module, are more completely documented than the later modules and applications. The primary intention of these applications is to provide a tool for learning ODBC applications, If some of the logic appears cryptic, looking at earlier examples may turn out to be beneficial to understanding the application’s intent.

The learning examples are written in ANSI-C using the ODBC API, and the code is compatible  with 32-bit  and 64-bit platforms. Every example is a single, self-contained file which means that many lines of code are repeated. At the core of each main() function, after a full-connect, and before a full-disconnect, the code specific to any given example will be found clearly highlighted with comments.

Contents

Download

The learning examples are available as a .zip file for Windows, and a .tar file for the UNIX variants. Download and extract the appropriate version for your platform.

Installation

Untar or unzip the package.  It will include a directory tree with all the learning examples (each in their own folder),  and a system of Makefiles to build the examples in Unix (see secion 2.1 Unix).  The WINDOWS builds will be left up to the user to setup based on the instructions below.

 
Note: The data source has to be set up before running the examples.

Setting up data source on Windows

  1. Click Start Menu->Settings->Control Panel->Administrative Tools->ODBC
  2. Double click on the ODBC icon. It should default to the User DSN tab across the top of the dialog box. iIf not, click on that tab.
  3. Click on the Add... button
  4. Find the Teradata ODBC Driver,  double click on it, or highlight it and click Finish.
  5. Enter Name: tdat
  6. Enter Description : (anything)
  7. Enter TOR Server Info: (the IP address of the Teradata database)
  8. Click OK (leaving the User Information fields empty)
  9. Click OK again.

Setting up data source on UNIX

Below is a sample odbc.ini file for the 64-bit ODBC Driver with the tdat DSN added. The DBCName field needs to be set to the Teradata Database the user wants to connect to.

[ODBC]
InstallDir=/opt/teradata/client/ODBC_64
Trace=0
TraceDll=/opt/teradata/client/ODBC_64/lib/odbctrac.so
TraceFile=/usr/joe/odbcusr/trace.log
TraceAutoStop=0

[ODBC Data Sources]
default=tdata.so
testdsn=tdata.so
tdat=tdata.so

[testdsn]
Driver=/opt/teradata/client/ODBC_64/lib/tdata.so
Description=Teradata running Teradata V1R5.2
DBCName=208.199.59.208
LastUser=
Username=
Password=
Database=
DefaultDatabase=

[tdat]
Driver=/opt/teradata/client/ODBC_64/lib/tdata.so
Description=Teradata running Teradata V1R5.2
DBCName=208.199.59.208
LastUser=
Username=
Password=
Database=
DefaultDatabase=

[default]
Driver=/opt/teradata/client/ODBC_64/lib/tdata.so
Description=Default DSN is Teradata 5100
DBCName=208.199.59.208
LastUser=
Username=
Password=
Database=
DefaultDatabase=

Windows usage

Rather than include project files with the learning examples, a set of steps have been provided below to describe what default values need to be changed in the Property Pages for the learning example project. This information is based on Visual Studio 2005.

Steps

 
  1. Start Visual Studio 2005.
  2. Select File->New->Project.
    • In Project Types, highlight Win32 under the Visual C++ folder.
    • Fill in the Name field and the Solution Name field will be automatically filled in too.
    • In Templates, select Win32 Console Application.
    • Select OK.
    • Select Next.
    • Check Empty Project.
    • Select Finish.
  3. Solution Explorer - Add learning Example Source to Project.
    1. Right-click on Source Files.
    2. Select Add->Existing Item.
    3. Using the Look In field, navigate to one of the learning example source files and select OK.
    4. The source file should show up under Source Files.
  4. Solution Explorer - Set Project Properties.
    • Right-click on Project Name and select Properties.
    • Under Configuration Properties, select General and set "Character Set" to "Not Set"
    • Under Configuration Properties, select Preprocessor under C/C++ and add "_TRACE_ON; CRT_SECURE_NO_WARNINGS" to the Preprocessor Definitions.
    • Under Configuration Properties, select Language under C/C++ and set "Treat wchar_t as Built-in Type" to "No (/Zc:wchar_t-)".
    • Select OK.
  5. Select Build->Rebuild Solution.
  6. Select Debug->Start Debugging.
    • Note: set break point at the end of main() to see output.
  7. Repeat steps 1 - 6 for each learning example.

UNIX usage

The learning examples on UNIX are provided with a corresponding Makefile and common.includes file that will be used to build the examples located in their individual directories.  The Makefiles are very basic and the user should have no troubles in executing them or modifying them for any custom needs.

The user should be able to go to the desired directory and execute make on the command line with no changes, however there could be some instances where the paths specified in the common.includes file will need adjusting based on how the compiler was installed.

Modules

Module 0 - Establish the basis for the other ODBC application samples

T20000OD - Create the sample user, database, and table to be used by subsequent samples. The item table contains 2 columns. The first column is named TESTED and is of type SQL_INTEGER. The second column is named NAME and is of type SQL_CHAR with a maximum length of 30 characters. The third column is named DESCRIPTION and is of type SQL_LONG_VARCHAR. The integer values for the test columns will be the number of the test with the "OD" written as 13. For example, T20100OD will write T2010013 into the integer column.

Module 1 - Insertion of data into a table

T20100OD - Inserts a row into the item table using a SQL INSERT statement.

T20101OD – Inserts a row into the item table using a parameterized SQL INSERT statement. The name and description column values are passed as parameters.

T20102OD – Inserts a row into the item table using a parameterized SQL INSERT statement. The description column value is passed as a parameter at execution time.

Module 2 - Retrieval of data from a table

T20200OD - Demonstrates how to use SQLBind and SQLFetch to retrieve data from a specific row in a table using a SELECT statement with a WHERE clause.

T20201OD - Demonstrates how to use SQLFetch and SQLGetData to retrieve data from a specific row in a table using a SELECT statement with a WHERE clause.

T20202OD - Retrieves data from a specific row from the items table using a SELECT statement with a parameterized WHERE clause. The parameters are passed at execution time.

Module 3 - Editing the data that has already been entered into the table

Editing is done with a update command that changes the values in the rows that were entered in Module 1.

T20300OD - Change the description of all items in the items table where TestID is equal to a specific value using an UPDATE statement.

T20301OD - Change the description of all items in the items table where TestID  is equal to a specific value using a parameterized UPDATE statement.

Module 4 - Retrieval of descriptions of databases, tables and their components

T20400OD - Display the names of all the databases on the system.

T20401OD - Display a list of databases that match a value that contains wildcard characters.

T20402OD - Display a specific database and a table.

T20403OD - Display a list of all table types.

T20404OD - Output a list of tables that match a specific table type.

Module 5 - Data type information

T20500OD - Display information about the SQL_VARCHAR data-type.

T20501OD - Display a list of supported data-types.

Module 6 - Column information

T20600OD – Display information about the name column in the t2samples table.

T20601OD – "Wildcard" search for columns.

Module 7 - Uses "%" and  "_" in database and table names

T20700OD - Create database/tables/columns/indexes that use "_" and  "%" as part of their name, then verify that the index was created using SQLStatistics.

T20701OD - Create database / tables / columns / indexes that use "_" and  "%" as part of their name and then get the list of columns using the search pattern escape character as part of a search string to find them.

Module 8 - Retrieves privileges

T20800OD - Retrieves the privileges for tables matching a wild card search specification.

Module 9 – Gets information about the Teradata ODBC driver

T20900OD - Get information about the Teradata ODBC driver that is stored as a null terminated string.

T20901OD - Get information about the Teradata ODBC driver that is stored as a 16-bit integer value.

T20902OD - Get information about the Teradata ODBC driver that is stored as bit in a 32-bit bitmap.

T20903OD - Get information about the Teradata ODBC driver that is stored in a 32-bit binary value.

T20904OD - Get information about the Teradata ODBC driver that is stored as a 16-bit integer value.

Module 10 - Deletion of data

T21000OD - Deletes rows from the item table using a DELETE statement with a WHERE clause. Display the number of rows that were deleted.

T21001OD - Deletes rows from the item table using a DELETE statement with a parameterized WHERE clause. Display the number of rows that were deleted.

Module 11 - Removes everything that was created by previous modules

T21100OD - Drop all tables owned by the sample user.