LOB and LOB locator example

An application can use LOB values in the application address space, and LOB locators can help you identify and manipulate these values at the database server.

The following example shows an application that extracts the 'Interests' section from the RESUME CLOB column of the EMP_RESUME table. This application transfers only a substring into memory.

Figure 1. An application that uses LOB locators
/* ... */
    SQLCHAR         stmt2[] =
                    "SELECT resume FROM emp_resume "
                      "WHERE empno = ?  AND resume_format = 'ascii'";
/* ... */
/******************************************************************
**  Get CLOB locator to selected Resume  **
*******************************************************************/
    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 7, 
                           0, Empno.s, sizeof(Empno.s), &Empno.ind);
    printf("\n>Enter an employee number:\n");
    gets(Empno.s);
    rc = SQLExecDirect(hstmt, stmt2, SQL_NTS);
    rc = SQLBindCol(hstmt, 1, SQL_C_CLOB_LOCATOR, &ClobLoc1, 0,
                     &pcbValue);
    rc = SQLFetch(hstmt);
/******************************************************************
    Search CLOB locator to find "Interests"
    Get substring of resume (from position of interests to end)
*******************************************************************/
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &lhstmt);
    /* Get total length */
    rc = SQLGetLength(lhstmt, SQL_C_CLOB_LOCATOR, ClobLoc1, &SLength, &Ind);
    /* Get starting position */
    rc = SQLGetPosition(lhstmt, SQL_C_CLOB_LOCATOR, ClobLoc1, 0,
                    "Interests", 9, 1, &Pos1, &Ind);
    buffer = (SQLCHAR *)malloc(SLength - Pos1 + 1);
    /* Get just the "Interests" section of the Resume CLOB */
    /* (From Pos1 to end of CLOB) */
    rc = SQLGetSubString(lhstmt, SQL_C_CLOB_LOCATOR, ClobLoc1, Pos1,
         SLength - Pos1, SQL_C_CHAR, buffer, SLength - Pos1 +1,
         &OutLength, &Ind);
    /* Print Interest section of Employee's resume */
    printf("\nEmployee #: %s\n %s\n", Empno.s, buffer); 
/* ... */