

ODBC Learning Examples
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
- Installation
- Windows usage
- UNIX usage
-
Modules
- Module 0 - Establish the basis for the other ODBC application samples
-
Module 1 - Insertion of data into a table
-
Module 2 - Retrieval of data from a table
-
Module 3 - Editing the data that has already been entered into the table
-
Module 4 - Retrieval of descriptions of databases, tables and their components
-
Module 5 - Data type information
-
Module 6 - Column information
-
Module 7 - Uses "%" and "_" in database and table names
-
Module 8 - Retrieves privileges
-
Module 9 – Gets information about the Teradata ODBC driver
-
Module 10 - Deletion of data
-
Module 11 - Removes everything that was created by previous modules
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
-
Click Start Menu->Settings->Control Panel->Administrative Tools->ODBC
-
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.
-
Click on the Add... button
-
Find the Teradata ODBC Driver, double click on it, or highlight it and click Finish.
-
Enter Name:
tdat
-
Enter Description : (anything)
-
Enter TOR Server Info: (the IP address of the Teradata database)
-
Click OK (leaving the User Information fields empty)
-
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
- Start Visual Studio 2005.
-
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.
-
Solution Explorer - Add learning Example Source to Project.
- Right-click on Source Files.
- Select Add->Existing Item.
- Using the Look In field, navigate to one of the learning example source files and select OK.
- The source file should show up under Source Files.
-
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.
- Select Build->Rebuild Solution.
-
Select Debug->Start Debugging.
-
Note: set break point at the end of
main()
to see output.
-
Note: set break point at the end of
- 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.
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.