Skip to content
≡

Administrator

Developer

Tools

External Tools

SQLPutLob

SQLPutLob#

All operations of this function are performed as internal updates. Insertion is the operation of updating existing LOB data of 0 length value into another value. Depending on the argument value, update either overwrites existing data with another value, starting at a specified position, or appends another value at the end of existing data.

Syntax#

SQLRETURN SQLPutLob(
    SQLHSTMT        stmt,
    SQLSMALLINT     locatorCType,
    SQLUBIGINT      targetLocator,
    SQLUINTEGER     fromPosition,
    SQLUINTEGER     forLength,
    SQLSMALLINT     sourceCType,
    SQLPOINTER      value,
    SQLUINTEGER     valueLength);

Arguments#

Data Type Argument In/Output Description
SQLHSTMT stmt Input Handle for the found results.
SQLSMALLINT locatorCType Input The C data type of a target LOB locator.
- SQL_C_BLOB_LOCATOR
- SQL_C_CLOB_LOCATOR
SQLUBIGINT targetLocator Input Target LOB Locator
SQLUINTEGER fromPosition Input The start point to update data in LOB (byte). It begins at 1.
SQLUINTEGER forLength Input Not used.
SQLSMALLINT sourceCType Input The C data type identifier of the value buffer.
- SQL_C_BINARY (for BLOB)
- SQL_C_CHAR (for CLOB)
SQLPOINTER value Input The pointer that points to the buffer storing input data.
SQLUINTEGER valueLength Input The length of data input in the value buffer (unit: bytes). A value larger than 0 must be set; SQL_NULL_DATA is not accepted.

Return Values#

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Description#

This function inserts or updates data stored in the application data buffer into the LOB which the target LOB locator points to.

When this function is operated, the server overwrites data of the target LOB, starting at the position of fromPosition, for the length of valueLength of the value buffer. If valueLength is larger than (LOBSize - fromPosition), the length of the target LOB of the database is extended; if fromPosition points to the end position of the target LOB value, data of the length of valueLength of the value buffer is appended to the end of the existing value.

If the LOB locator is not one which is opened in the current session, it is not accepted as an argument for this function, since the LOB locator becomes invalid when the transaction terminates. If the target LOB locator is invalid, the SQLPutLob() function returns SQL_ERROR.

If the target LOB locator points to a LOB with the value of NULL, the SQLPutLob() function operates equivalent to the LOB locator pointing to a LOB with the length of 0.

The fromPosition argument must not be larger than the length of the target LOB at the time point of calling. If the value of the fromPosition argument is larger than the length of the target LOB, the SQLPutLob() function returns SQL_ERROR.

Diagnosis#

SQLSTATE Description Comments
08S01 Communication link fault (Data transmission failure) Communication link failed before function processing is complete between Altibase CLI driver and DB.
HY000 General error 
SQLGetLobLength
SQLGetLob

Examples#

It is assumed that a table has been created with the following DDL.

CREATE TABLE T1 (i1 INTEGER PRIMARY KEY, i2 CLOB);

After inserting a record with the CLOB column value being 'Ver.Beta', replace 'Beta' with 'Gamma'#

SQLCHAR buf[5];
SQLUBIGINT lobLoc;
.
strcpy(query, "INSERT INTO T1 VALUES (1, 'Ver.Beta')");
if (SQLExecDirect(stmt, query, SQL_NTS) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLExecDirect : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

.
strcpy(query, "SELECT i2 FROM T1 WHERE i1=1 FOR UPDATE");
if (SQLExecDirect(stmt, query, SQL_NTS) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLExecDirect : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLBindCol(stmt, 1, SQL_C_CLOB_LOCATOR, &lobLoc, 0, NULL) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLBindCol : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLFetch(stmt) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLFetch : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

memcpy(buf, "Gamma", 5);
if (SQLPutLob(stmt, SQL_C_CLOB_LOCATOR, lobLoc, 4, 4, SQL_C_CHAR, buf, 5) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLPutLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLFreeLob(stmt, lobLoc) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLFreeLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

Insert a record with the CLOB column value being 'Ver.0.9a'#

SQLCHAR buf[8];
SQLINTEGER lobInd;
SQLUBIGINT lobLoc;
.
.
.
strcpy(query, "INSERT INTO T1 VALUES (5, ?)");
if (SQLPrepare(stmt, query, SQL_NTS) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLPrepare : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLBindParameter(stmt, 1, SQL_PARAM_OUTPUT, SQL_C_CLOB_LOCATOR, SQL_CLOB_LOCATOR, 0, 0, &lobLoc, 0, &lobInd) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLBindParameter : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLExecute(stmt) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLExecute : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

memcpy(buf, "Ver.0.9a", 8);
if (SQLPutLob(stmt, SQL_C_CLOB_LOCATOR, lobLoc, 0, 0, SQL_C_CHAR, buf, 7) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLPutLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

/*  In 'Ver.0.9a', replace '0.9' with '1'. */
memcpy(buf, "1", 1);
if (SQLPutLob(stmt, SQL_C_CLOB_LOCATOR, lobLoc, 4, 3, SQL_C_CHAR, buf, 1) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLPutLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLFreeLob(stmt, lobLoc) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLFreeLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

Change the CLOB of multiple records to 'Retail' at once#

SQLCHAR buf[6];
SQLINTEGER lobInd;
SQLUBIGINT lobLoc;
.
.
.
strcpy(query, "UPDATE T1 SET i2=? WHERE i1>=1 AND i1<=100");
if (SQLPrepare(stmt, query, SQL_NTS) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLPrepare : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

/*  If an UPDATE query is executed after a LOB locator parameter is being outbound, LOB columns to be updated will be truncated to null automatically.  */
if (SQLBindParameter(stmt, 1, SQL_PARAM_OUTPUT, SQL_C_CLOB_LOCATOR, SQL_CLOB_LOCATOR, 0, 0, &lobLoc, 0, &lobInd) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLBindParameter : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLExecute(stmt) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLExecute : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

memcpy(buf, "Retail", 6);
if (SQLPutLob(stmt, SQL_C_CLOB_LOCATOR, lobLoc, 0, 0, SQL_C_CHAR, buf, 6) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLPutLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}

if (SQLFreeLob(stmt, lobLoc) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLFreeLob : ");
    SQLFreeStmt(stmt, SQL_DROP);
    return SQL_ERROR;
}