Problem to insert characters into NCLOB

From: faisal <faisalhisham_at_hotmail.com>
Date: 1 Nov 2001 06:16:14 -0800
Message-ID: <501840a9.0111010616.23ae3d98_at_posting.google.com>

Hi all,

I've a problem to insert characters into NCLOB datatype column using ODBC driver.I'm using Oracle ODBC Driver 8.01.6 and the database server 8.1.6 You'll find my sample program to show the problem. Infos :

1. T_NCLOB has one NCLOB column
2. T_CLOB  has one CLOB column
3. Sql statement for NCLOB insertion is different from the statement

for CLOB. I've run both statemente in SQL*Plus, replacing ? with string and they work fine.
4. I've a mytxt.txt file to get easily my string, you can hard coded the string buffer if you want.

Running this program to insert into CLOB is OK but when using the sql statement for NCLOB, SQLExecute returns this error message : "ORA-00932: inconsistent datatypes". I thought it must be the problem when executing SQLBindParameter - we should use the correct sql type and C type. but I've tried many types offered in sqlext.h but unsuccesful.

If you've any comments, I'd love to hear.

thanks.
faisal

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

// Include The Appropriate Header Files
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <io.h>
#include <fcntl.h>

#define BUFFER_SIZE 2000

#define OCI_EXIT_FAILURE 1 /* exit flags */
#define OCI_EXIT_SUCCESS 0

SQLCHAR    DSNName[14]	 = "MyDSNOracle";
SQLCHAR    DBName[]	 = "MyDBName";

//SQLCHAR SQLStmt[] = "INSERT INTO T_NCLOB VALUES (TRANSLATE (? USING NCHAR_CS))";
SQLCHAR SQLStmt[] = "INSERT INTO T_CLOB VALUES (?)";

SQLHANDLE EnvHandle;
SQLHANDLE ConHandle;
SQLHANDLE StmtHandle;
SQLRETURN rc;

void GetError( SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt) {

	SQLSMALLINT cberrormsg ;
	SQLINTEGER SqlDbCode ;
	SQLCHAR strDbMessage[2000];
	SQLCHAR strState[100 + 1];
	RETCODE rc;

	
	rc = SQLError ( 
					henv,
					hdbc,
					hstmt,
					strState,
					&SqlDbCode,
					strDbMessage,
					1000 - 1 ,
					&cberrormsg );

}

void LOGON()
{

        // Initialize The Return Code Variable
    rc = SQL_SUCCESS;

        // Allocate An Environment Handle
    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle);

    // Set The ODBC Application Version To 3.x     if (rc == SQL_SUCCESS)

        rc = SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION, 
                 (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);

    // Allocate A Connection Handle
    if (rc == SQL_SUCCESS)

        rc = SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &ConHandle);

	rc = SQLConnect(ConHandle, (SQLCHAR *) DSNName, sizeof(DSNName), 
	      		(SQLCHAR *) DBName, sizeof(DBName), (SQLCHAR *) DBName,
sizeof(DBName));
	if (rc != 0)
		GetError(EnvHandle, ConHandle,NULL);
	

}

void LOGOF()
{

        // Disconnect From Database
    rc = SQLDisconnect(ConHandle);

        // Free The Connection Handle
    if (ConHandle != NULL)

        SQLFreeHandle(SQL_HANDLE_DBC, ConHandle);

    // Free The Environment Handle
    if (EnvHandle != NULL)

        SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle); }

/* Function INSERT(): 
/*		Loads  raw data	into a memory buffer from a source file		*/
/*		Bind the column with SQLBindParameter						*/
/*		Execute the statement and perform the piecewise operation	*/

void      INSERT()

{
  int fd;
  FILE *fp;
  char filename[10] = "mytxt.txt";   

  printf("Opening source file\n");
  if (!(fd = open((char *)filename, O_RDONLY)))     exit(1);

  fp = fopen((const char *)filename, (const char *) "r");   

  unsigned char	bufp[255];
  unsigned char	oneb[1];

  int RC = 1;
  int i=0;

  while (RC > 0)
  {

		RC = fread((void *)oneb, sizeof(unsigned char), 1, fp);
		if (RC > 0)
			bufp[i++] = oneb[0];
		memset(oneb, '\0', 1);	

  }

  bufp[i] = '\0';

  SQLINTEGER B[1];
  B[0] = SQL_DATA_AT_EXEC;
  rc = SQLBindParameter(

			StmtHandle, 
			1, 
			SQL_PARAM_INPUT, 
			SQL_C_CHAR, 
			SQL_LONGVARCHAR, 
			BUFFER_SIZE,//i,
            0,  bufp, sizeof(bufp), B);

  if (rc != 0)
	GetError(EnvHandle, ConHandle, StmtHandle);

  // Execute The SQL Statement
  rc = SQLExecute(StmtHandle);
  if (rc != 0 && rc != 99)

                GetError(EnvHandle, ConHandle, StmtHandle);

  SQLPOINTER pParamToken;
  RETCODE rc2;
  while ( rc == SQL_NEED_DATA )
  {

      rc = SQLParamData( StmtHandle, &pParamToken );
      if ( rc == SQL_NEED_DATA ) 
	  {
		 if (i < 2000)
		 {
            rc2 = SQLPutData( StmtHandle, (SQLPOINTER)bufp, i );
            if ( rc2 != SQL_SUCCESS ) 
			{
				GetError(EnvHandle, ConHandle, StmtHandle);
            }
         }
      }

  }   

  rc= SQLTransact(EnvHandle, ConHandle, SQL_COMMIT);   if (rc != 0)

        GetError(EnvHandle, ConHandle, StmtHandle);

  if (close(fd))                           /*        close file */

    exit(OCI_EXIT_FAILURE);   

}

void main(void)
{
  /* log on to Oracle */
  LOGON();   // Allocate An SQL Statement Handle
  rc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &StmtHandle);

  // Prepare The SQL Statement
  rc = SQLPrepare(StmtHandle, SQLStmt, sizeof(SQLStmt));

  if (rc != 0)

                GetError(EnvHandle, ConHandle, NULL);

  // Process the Insertion
  INSERT();   // Free The SQL Statement Handle
  if (StmtHandle != NULL)

        SQLFreeHandle(SQL_HANDLE_STMT, StmtHandle);   

  /*log off from the server */
  LOGOF();
}

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

Received on Thu Nov 01 2001 - 15:16:14 CET