Build a Teradata PT Database Connection Object
In order to establish a connection with the Teradata Database, users must create an object that will manage all of that connection’s important attributes. In Teradata PT, this object is called a Connection object.
There are two steps involved in building a Connection object:
1 Create a Connection Object
The Connection object is defined as a C++ class. A Connection object can be created by calling the Connection class constructor.
using namespace teradata::client::API;
Connection* conn = new Connection();
2 Add Parameters
The Connection object is used to house parameters for a database connection. There are three types of parameters:
c DML Groups
There are various types of options that can be set using attributes. Each driver has a set of required attributes and optional attributes. Depending on the attribute, there can be multiple values for that attribute. These are called array attributes. The following is an example of adding attributes and array attributes:
conn->AddArrayAttribute(TD_WORK_TABLE, 2,"testa_wt","testb_wt", NULL);
Set the value(s) for an attribute in a single call to either the AddAttribute function or the AddArrayAttribute function. Once an attribute has been set, the value(s) of the attribute cannot be changed and additional values cannot be added to it.
Set all attributes prior to calling the Initiate function. After a Connection object has been initiated, no additional attributes may be set.
A schema object defines the format of the data being used in the load and export tasks. A schema consists of one or more columns, and the format of each column’s data. The schema lets the driver know what data to expect.
Unlike an attribute, a schema is defined as an external C++ class. When creating a schema object using the class constructor, the schema must be designated as an input or output schema. This means the task will involve either loading, updating data, or exporting data. The following is an example of creating a schema object:
Schema * schema = new Schema("input");
Columns are added to the schema object one at a time using the TD_DataType constants to designate the data type of the column. The following are examples of adding a column to a schema object:
Figure 3 depicts the coding lines for a two‑table select statement and how the data is brought together in the answer set.Figure 3: Schema Definition for a Multiple Table Select Statement
When all columns have been added to the schema object, the schema object is then passed to the Connection class. The following is an example of adding a schema object to a Connection object:
Working with Schemas
When working with schemas, note the following:
Examples of Schema Definitions
If the Export driver is being used to export data from Table 1 which has columns A, B, and C, and the SELECT statement is:
SELECT A FROM T able 1
then the output schema is defined as one column with a data type matching column A’s definition in Table 1. If column A is defined as a variable character with a length of 50, then the column in the output schema is defined as TD_VARCHAR with a length of 50.
Schema* schema = new Schema("output");
schema->AddColumn("A", TD_VARCHAR, 50);
Here’s another example using the same table. If the select statement is changed to:
SELECT A(CHAR(100)) FROM Table 1
then the column in the output schema is defined as TD_CHAR with a length of 100.
Schema* schema = new Schema("output");
schema->AddColumn("A", TD_CHAR, 100);
DMLGroup objects are used to store sets of DML statements and DML options which can later be applied when loading or updating data. A DMLGroup is defined as an external C++ class.
The following is an example of creating a DMLGroup object:
DMLGroup * dmlGr = new DMLGroup();
Each DML statement is stored as a character string within the DMLGroup object. DML statements can be added one at a time to the DMLGroup object using the AddStatement function. More than one DML statements can be added to the same DMLGroup object but each DML statement must be added in a separate call to the AddStatement function. The following is an example of adding a statement to a DMLGroup object:
dmlGr->AddStatement("INSERT INTO test1( :Associate_Id, :Salary );");
Along with DML statements, a series of DML options can be set for a DMLGroup object using the TD_DMLOption parameters. The following is an example of setting DML options for a DMLGroup object:
A DMLgroup is added to the Connection class by using the AddDMLGroup function. The AddDMLGroup function takes two parameters:
The following is an example of adding a DML group to the Connection class:
TD_StatusCode returnValue = conn->AddDMLGroup(dmlGr, &index);