DevOps, CI/CD and Automation

Hi,

Im having problem retrieving data from NCLOB columns via ODBC. After SQLExecdirect on my SELECT statement call to SQLFetch gives me following error:

ORA-24806 LOB form mismatch

I tried following two ways of data retrieval:

SQLExecDirect(hstmt,(SQLTCHAR*)szSQLStatement,SQL_NTS);
SQLBindCol(hstmt,3,SQL_C_TCHAR,chBuffer,sizeof(chBuffer),&iDataLen);
SQLFetch(hstmt);

OR

SQLExecDirect(hstmt,(SQLTCHAR*)szSQLStatement,SQL_NTS);
SQLFetch(hstmt);
SQLGetData(hstmt,3,SQL_C_TCHAR,chBuffer,sizeof(chBuffer),&iDataLen);

Both times call to SQLFetch gives me ORA-24806 LOB form mismatch error.

INSERT and UPDATE for this table works just fine.
If I change NCLOB type to CLOB works just fine, but I will be storing Unicode data, so using CLOB cant be a solution

Any ideas appreciated.

Thanks in advance,
Vlad

//*********************************************************
Server:
Oracle 9.2 database on Windows 2000 Server
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CHARACTERSET = WE8MSWIN1252
NLS_NCHAR_CHARACTERSET = AL16UTF16


Client:
Unicode C++ application, connects via Oracle 9.2 ODBC driver on Windows XP
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252

//*********************************************************
CREATE TABLE my_nclob_table (
id number (10,0) NOT NULL ,
name nvarchar2 (255) NOT NULL ,
description nvarchar2 (255) NULL ,
definition nclob NOT NULL ,
creation date NOT NULL ,
lastmodified date NOT NULL ,
CONSTRAINT PK_cat_pricelist PRIMARY KEY
(
id
)
) ;

//**********************************************************
#define nil NULL
#define null NULL

#define SQL_ERR(__sqlerr__) (((__sqlerr__) != SQL_SUCCESS) && ((__sqlerr__) != SQL_SUCCESS_WITH_INFO))


#define STOP_IF_SQL_ERR(__sqlerr__) \
do \
{ \
if (SQL_ERR(__sqlerr__)) goto stop_; \
} while (false)
//****************************************************************************
void eqTestOracleLOBSelect(void)
{
SQLHENV henv = null;
SQLHDBC hdbc = null;
SQLHSTMT hstmt = null;
SQLRETURN retcode = SQL_SUCCESS;
TCHAR szSQLStatement[] = T("select id, name, definition from mynclob_table");
int iFetchedRows = 0;
TCHAR szMsg[512];
TCHAR chBuffer[2049];
SQLLEN iDataLen = 0;

try
{
retcode = eqOpenConnectionODBC(_T("myTestDsn"),_T("user"),_T("pwd"),henv,hdbc);
STOP_IF_SQL_ERR(retcode);

retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
STOP_IF_SQL_ERR(retcode);

retcode = SQLExecDirect(hstmt,(SQLTCHAR*)szSQLStatement,SQL_NTS);
STOP_IF_SQL_ERR(retcode);

retcode = SQLBindCol(hstmt,3,SQL_C_TCHAR,chBuffer,sizeof(chBuffer),&iDataLen);
STOP_IF_SQL_ERR(retcode);

{

while (retcode == SQL_SUCCESS)
{
retcode = SQLFetch(hstmt);
if (retcode == SQL_NO_DATA)
{
retcode = SQL_SUCCESS;
break;
}
STOP_IF_SQL_ERR(retcode);

// retcode = SQLGetData(hstmt,3,SQL_C_TCHAR,chBuffer,sizeof(chBuffer),&iDataLen);
// STOP_IF_SQL_ERR(retcode);

++iFetchedRows;
}
}

stop_:

if (SQL_ERR(retcode))
{
MessageBox(null,_T("SQL statement execution error."),_T("Error"),MB_OK);
eqShowStatementError(SQL_HANDLE_STMT,hstmt,retcode);
} else {
_stprintf(szMsg,_T("SQL execution success. Fetched rows: %ld"),iFetchedRows);

MessageBox(null,szMsg,_T("Success"),MB_OK);
}

}
catch(...)
{
MessageBox(null,_T("Unknown exception in eqTestOracleLOBSelect"),_T("Unknown exception"),MB_OK);
}

if (hstmt != null)
{
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
hstmt = null;
}

if (hdbc != nil)
{
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
hdbc = nil;
}

if (henv != nil)
{
SQLFreeHandle(SQL_HANDLE_ENV, henv);
henv = nil;
}
}


//********************************************************************
SQLRETURN eqOpenConnectionODBC(TCHAR *szDSN,
TCHAR *szUser,
TCHAR *szPassword,
SQLHENV &henv,
SQLHDBC &hdbc)
{
SQLRETURN retcode;

henv = nil;
hdbc = nil;

//Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
STOP_IF_SQL_ERR(retcode);

//Set the ODBC version environment attribute
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
STOP_IF_SQL_ERR(retcode);

// Allocate connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
STOP_IF_SQL_ERR(retcode);

// Set login timeout to 5 seconds.
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)5, 0);
STOP_IF_SQL_ERR(retcode);

retcode = SQLConnect(hdbc, (SQLTCHAR*)szDSN, SQL_NTS,
(SQLTCHAR*)szUser, SQL_NTS,
(SQLTCHAR*)szPassword, SQL_NTS);


stop_:
if (SQL_ERR(retcode))
{
MessageBox(null,_T("Connection Error."),_T("Error"),MB_OK);

if (hdbc != nil)
{
eqShowStatementError(SQL_HANDLE_DBC, hdbc,retcode);

SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
hdbc = nil;
}

if (henv != nil)
{
eqShowStatementError(SQL_HANDLE_ENV, henv,retcode);

SQLFreeHandle(SQL_HANDLE_ENV, henv);
henv = nil;
}
} else MessageBox(null,_T("Connected."),_T("Success"),MB_OK);

return retcode;
}

//****************************************************************************

void eqShowStatementError(SQLSMALLINT chHandleType, SQLHANDLE hHandle,long lErrOrig)
{
SQLRETURN nErr = SQL_SUCCESS;
int nRecNum = 1;
SQLTCHAR szSqlState[6];
SQLTCHAR szMsg[SQL_MAX_MESSAGE_LENGTH];
SQLSMALLINT nMsgLen;
SQLINTEGER nNativeError;

while ((nErr = SQLGetDiagRec(chHandleType,hHandle,nRecNum,
szSqlState, &nNativeError,
szMsg, sizeof(szMsg), &nMsgLen)) != SQL_NO_DATA)
{
szSqlState[5] = 0;
MessageBox(NULL,(const TCHAR *)szMsg,_T("ODBC Error"),MB_OK);
++nRecNum;
}

}