2 Replies Latest reply: Mar 3, 2009 3:12 AM by 665706 RSS

    Direct Execution of query having Unicode Characters

      Direct Execution of query having Unicode Characters

      Hi All,

      In my application I am firing a Select Query having Unicode characters in Where Clause under condition like '%%'
      to Oracle 10g DB from a Interface written in VC6.0...
      Application funcationality is working fine for ANSI characters and getting the result of Select properly.
      But in case of Unicode Characters in VC it says 'No Data Found'.
      I know where the exact problem is in my code. But not getting the exact solution for resolving my issue...
      Here with I am adding my code snippet with the comments of what i understand and what i want to understand...

      DBPROCESS Structure used in the functions,_

      typedef struct
      HENV hEnv;
      HDBC hDbc;
      HSTMT hStmt;

      char CmdBuff[[8192]];
      char RpcParamName[[255]];
      SQLINTEGER SpRetVal;
      SQLINTEGER ColIndPtr[[255]];
      SQLINTEGER ParamIndPtr[[255]];
      SQLPOINTER pOutputParam;
      SQLUSMALLINT CurrentParamNo;
      SQLUSMALLINT OutputParamNo;
      SQLUSMALLINT InputParamCtr;
      SQLINTEGER BatchStmtNo;

      SQLINTEGER CmdBuffLen;

      short CurrentStmtType;
      SQLRETURN LastStmtRetcode;
      SQLCHAR SqlState[[10]];
      int ShowDebug;

      SQLCHAR* ParameterValuePtr;
      int ColumnSize;
      DBTYPE DatabaseType;
      DRVTYPE OdbcDriverType;
      BLOCKBIND *ptrBlockBind;
      } DBPROCESS;


      BOOL CDynamicPickList::GetResultSet(DBPROCESS *pDBProc, bstrt& pQuery, short pNumOdbcBindParams, COdbcBindParameter pOdbcBindParams[], CQueryResultSet& pQueryResultSet)
           int               lRetVal,
           bstrt               lResultSet;
           wchar_t               lColName[[256]];     
           SQLUINTEGER          lColSize;
           SQLSMALLINT          lColNameLen,
           wchar_t               lResultRow[[32]][[256]];
      OdbcCmdW(pDBProc, (wchar_t *)pQuery); *//Query is perfectly fine till this point all the Unicode Characters are preserved...*

           if ( OdbcSqlExec(pDBProc) != SUCCEED )
                LogAppError(L"Error In Executing Query %s", (wchar_t *)pQuery);          
                return FALSE;



      Function OdbcCmdW_
      //From this point have no idea what is exactly happening to the Unicode Characters...
      //Actually i have try printing the query that gets stored in CmdBuff... it show junk for Unicode Characters...
      //CmdBuff is the Char type Variable and hence must be showing junk for Unicode data
      //I have also try printing the HexaDecimal of the query... I m not getting the proper output... But till i Understand, I think the HexaDecimal Value is perfect & preserved
      //After the execution of this function the call goes to OdbcSqlExec where actual execution of qurey takes place on DB

      SQLRETURN OdbcCmdW( DBPROCESS p_ptr_dbproc, WCHAR      p_sql_command )
           char *p_sql_commandMBCS;
           int l_ret_val;
           int l_size = wcslen(p_sql_command);
           int l_org_length,
      p_sql_commandMBCS = (char *)calloc(sizeof(char) * MAX_CMD_BUFF,1);

      l_ret_val = WideCharToMultiByte(
                          NULL,                         // performance and mapping flags
                          p_sql_command,          // wide-character string
                          -1,                         // number of chars in string
                          (LPSTR)p_sql_commandMBCS,// buffer for new string
                          MAX_CMD_BUFF,                    // size of buffer
                          NULL, // default for unmappable chars
                          NULL // set when default char used

      l_org_length = p_ptr_dbproc->CmdBuffLen;

      l_newcmd_length = strlen(p_sql_commandMBCS);

      p_ptr_dbproc->CmdBuff[[l_org_length]] = '\0';

      if( l_org_length )

      if( (l_org_length + l_newcmd_length) >= MAX_CMD_BUFF )

      if( l_org_length == 0 )
      OdbcReuseStmtHandle( p_ptr_dbproc );
      strcat(p_ptr_dbproc->CmdBuff, " ");
           l_org_length +=2;

      strcat(p_ptr_dbproc->CmdBuff, p_sql_commandMBCS);
      p_ptr_dbproc->CmdBuffLen = l_org_length + l_newcmd_length;
      if (p_sql_commandMBCS != NULL)
      return( SUCCEED );


      Function OdbcSqlExec_
      //SQLExecDirect Requires data of Unsigned Char type. Thus the above process is valid...
      //But i am not getting what is the exact problem...

      SQLRETURN OdbcSqlExec( DBPROCESS *p_ptr_dbproc )
      SQLRETURN l_ret_val;
      SQLINTEGER l_db_error_code=0;
           int     i,l_occur = 1;
           char     *token_list[[50]][[2]] =
      {     /*"to_date(","convert(datetime,",
                                         "'yyyy-mm-dd hh24:mi:ss'","1",*/
                                         "nvl","isnull" ,
                                         "sysdate",     "getdate()",
                                         "format_date", "dbo.format_date",
                                         "format_amount", "dbo.format_amount",
                                         "to_date", "dbo.to_date",

      char          *l_qry_lwr;  
      l_qry_lwr = (char *)calloc(sizeof(char) * (MAX_CMD_BUFF), 1);

      l_ret_val = SQLExecDirect( p_ptr_dbproc->hStmt,
      (SQLCHAR *)p_ptr_dbproc->CmdBuff,
      SQL_NTS );

      switch( l_ret_val )
      case SQL_SUCCESS :
      case SQL_NO_DATA :

      ClearCmdBuff( p_ptr_dbproc );
      p_ptr_dbproc->LastStmtRetcode = l_ret_val;

      if (l_qry_lwr != NULL)
      return( SUCCEED );

      case SQL_NEED_DATA :

      case SQL_ERROR :


      I do not see much issue in the code... The process flow is quite valid...
      But now i am not getting whether,
      1) storing the string in CmdBuff is creating issue
      2) SQLExecDirect si creating an issue(and some other function can be used here)...
      3) Odbc Driver creating an issue and want some Client Setting to be done(though i have tried doing some permutation combination)...


      Any kind of help would be appreciated,

      Thanks & Regards,

      Edited by: prats on Feb 27, 2009 12:57 PM