Direct file input and output for LOB handling in CLI applications

Database queries, updates, and inserts might involve a transfer of individual LOB column values to and from files. In CLI applications, if you require the entire LOB column instead of a single column value, you can request a direct file input and output for LOBs.

The two CLI LOB file access functions are:

SQLBindFileToCol()

Binds (associates) a LOB column in a result set with a file name.

Example:

    SQLUINTEGER     fileOption = SQL_FILE_OVERWRITE;
    SQLINTEGER      fileInd = 0;
    SQLSMALLINT     fileNameLength = 14;
    /* ... */
    SQLCHAR         fileName[14] = "";
    
    /* ... */
    rc = SQLBindFileToCol(hstmt, 1, fileName, &fileNameLength,
                          &fileOption, 14, NULL, &fileInd);

SQLBindFileToParam()

Binds (associates) a LOB parameter marker with a file name.

Example:

    SQLUINTEGER     fileOption = SQL_FILE_OVERWRITE;
    SQLINTEGER      fileInd = 0;
    SQLSMALLINT     fileNameLength = 14;
    /* ... */
    SQLCHAR         fileName[14] = "";
    
    /* ... */

    rc = SQLBindFileToParam(hstmt, 3, SQL_BLOB, fileName,
            &fileNameLength, &fileOption, 14, &fileInd);

The file name is either the complete path name of the file (which is recommended), or a relative file name. If a relative file name is provided, it is appended to the current path (of the operating environment) of the client process. On execute or fetch, data transfer to and from the file would take place, in a similar way to that of bound application variables. A file options argument associated with these 2 functions indicates how the files are to be handled at time of transfer.

Use of SQLBindFileToParam() is more efficient than the sequential input of data segments using SQLPutData(), since SQLPutData() essentially puts the input segments into a temporary file and then uses the SQLBindFileToParam() technique to send the LOB data value to the server. Applications should take advantage of SQLBindFileToParam() instead of using SQLPutData().

Note: CLI uses a temporary file when inserting LOB data in pieces. If the data originates in a file, the use of a temporary file can be avoided by using SQLBindFileToParam(). Call SQLGetFunctions() to query if support is provided for SQLBindFileToParam(), since SQLBindFileToParam() is not supported against servers that do not support LOBs.