16.20 - Inserting LOB Data - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

ODBC Driver for Teradata
October 2018
User Guide

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  cbid = 0;
SQLUINTEGER cbimage = 0;
SQLRETURN   retcode;

retcode = SQLPrepare(hstmt,
                     (SQLCHAR *)"insert into blobtable (id, image) values (?, ?)",
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 );