Inserting LOB Data - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

Product
ODBC Driver for Teradata
Release Number
16.20
Published
August 2020
Language
English (United States)
Last Update
2020-08-25
dita:mapPath
fxv1527114222338.ditamap
dita:ditavalPath
Audience_PDF_product_legacy_odbc_include.ditaval
dita:id
B035-2526
lifecycle
previous
Product Category
Teradata Tools and Utilities

This section contains sample source code that inserts LOB data.

The first step is to prepare a parameterized insert statement. The first parameter is a numeric key used to identify this row. The second parameter is the BLOB data.

The second parameter is specified as a SQL_LONGVARBINARY data type.

The length of the LOB data is specified in the SQL_LEN_DATA_AT_EXEC macro call.

SQLINTEGER	 id;
SQLCHAR     image[IMAGEPART_LEN ];
SQLINTEGER  cbid = 0;
SQLUINTEGER cbimage = 0;
SQLRETURN   retcode;

retcode = SQLPrepare(hstmt,
                    (SQLCHAR *)"insert into blobtable (id, image) values (?, ?)",
                    SQL_NTS);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
          // Bind the parameters.
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
                               SQL_C_SLONG, SQL_INTEGER, 10, 0, &id, 0, &cbid);
    retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                               SQL_LONGVARBINARY, LOBSIZE, 0, (SQLPOINTER) 2, 0,
                               &cbimage);
    //Set the 1. parameter (id)
	 id = 1001;
    //Set the 2. parameter (image)
    cbimage = SQL_LEN_DATA_AT_EXEC(0);
Setting the length to 0 as in SQL_LEN_DATA_AT_EXEC(0) means that the length is unknown.

The next step is to insert the data. The LOB data may be very large and the data are therefore inserted in chunks.

     // Execute the request
     retcode = SQLExecute(hstmt);

     // For the data-at-execution parameter (2. parameter)
     // call SQLParamData and SQLPutData repeatedly
     // to insert the BLOB data in parts.
     while (retcode == SQL_NEED_DATA) {
         retcode = SQLParamData(hstmt, NULL);
         while (GetUserData(image, IMAGEPART_LEN )) {
             SQLPutData(hstmt, image, IMAGEPART_LEN );
         }
     }
}