Re: Oracle ODBC / UNICODE Non Conformance

From: <jocave_at_my-deja.com>
Date: Wed, 18 Oct 2000 03:16:38 GMT
Message-ID: <8sj4mi$t0h$1@nnrp1.deja.com>

## Duplicating my reply to the OTN forum on the ODBC driver at
<http://otn.oracle.com>. Sneding follow-up questions to OTN would be appreciated, as it makes my life a lot easier to only track a discussion in one place. ##

I'll quote below a brief code snippet that demonstrates inserting UNICODE data into a UTF-8 database as either UNICODE or ASCII. The program then downloads that same data into both ASCII and UNICODE variables.

I've run this code against a UTF-8 database without any problems.

Note that I can get away with declaring the column char(8) because I know that "A" and "B" use only 1 byte in the UTF-8 encoding scheme. In order to hold an arbitrary string of 8 Unicode characters, I'd need a column 24 bytes long (8*3). I used 8 here to demonstrate that there will be no unnecessary truncation.

#include <stdio.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>
#include <tchar.h>
#include <assert.h>

void main ( void )
{

TCHAR *dropTableSQL;
TCHAR *createTableSQL;
TCHAR *populateTableSQL;
TCHAR *selectSQL;
TCHAR *inData;
TCHAR *outData;

char *singleByteOutData;
SQLINTEGER bytesReturned;

SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
RETCODE rc;

dropTableSQL = (TCHAR*) malloc( sizeof( TCHAR ) * 512 ); createTableSQL = (TCHAR*) malloc( sizeof( TCHAR ) * 512 ); populateTableSQL = (TCHAR*) malloc( sizeof( TCHAR ) * 512 ); selectSQL = (TCHAR*) malloc( sizeof( TCHAR ) * 512 ); inData = (TCHAR*) malloc( sizeof( TCHAR ) * 512 ); outData = (TCHAR*) malloc( sizeof( TCHAR ) * 512 );

singleByteOutData = (char*) malloc( sizeof( char ) * 512 );

rc = SQLAllocEnv( &hEnv );
rc = SQLAllocConnect( hEnv, &hDbc );

rc = SQLConnect( hDbc, _T(<Your DSN here>), 11,

_T("scott"), 5,
_T("tiger"), 5 );
if( rc != SQL_SUCCESS )

{
return;
}

rc = SQLAllocStmt( hDbc, &hStmt );

dropTableSQL = _T("drop table UnicodeTestTbl"); rc = SQLExecDirect( hStmt, dropTableSQL, _tcslen( dropTableSQL ) );

createTableSQL = _T("create table UnicodeTestTbl( col1 char(8) )"); rc = SQLExecDirect( hStmt, createTableSQL, _tcslen( createTableSQL ) );

populateTableSQL = _T("insert into UnicodeTestTbl values( ? )"); inData = _T("AAAAAAAA");

// Insert the data into the database as Unicode data rc = SQLPrepare( hStmt, populateTableSQL, _tcslen( populateTableSQL ) ); rc = SQLBindParameter( hStmt,
1,

SQL_PARAM_INPUT,
SQL_C_WCHAR, // C data type we have
SQL_WCHAR, // SQL type we'd like to insert

_tcslen( inData ),
0,
inData,
0,
NULL );
rc = SQLExecute( hStmt );

// Insert the data into the database as ASCII data inData = _T("BBBBBBBB");
rc = SQLPrepare( hStmt, populateTableSQL, _tcslen( populateTableSQL ) ); rc = SQLBindParameter( hStmt,
1,

SQL_PARAM_INPUT,
SQL_C_WCHAR, // C data type we have
SQL_CHAR, // SQL type we'd like to insert

_tcslen( inData ),
0,
inData,
0,
NULL );
rc = SQLExecute( hStmt );

//
// Lets select the data as UNICODE
//

selectSQL = _T("select col1 from UnicodeTestTbl");

rc = SQLExecDirect( hStmt, selectSQL, _tcslen( selectSQL ) );
rc = SQLFetch( hStmt );
rc = SQLGetData( hStmt,

1,
SQL_C_WCHAR,
outData,
9 * sizeof( TCHAR ), // 8 characters + NULL term &bytesReturned );

assert( bytesReturned = 8 * sizeof( TCHAR ) );
assert( bytesReturned = _tcslen( outData ) );
printf( "First UNICODE row - %ws\n", outData );

rc = SQLFetch( hStmt );
rc = SQLGetData( hStmt,
1,
SQL_C_WCHAR,
outData,
9 * sizeof( TCHAR ), // 8 characters + NULL term &bytesReturned );

assert( bytesReturned = 8 * sizeof( TCHAR ) );
assert( bytesReturned = _tcslen( outData ) );
printf( "Second UNICODE row - %ws\n", outData );

// Close out the first statement
SQLFreeStmt( hStmt, SQL_CLOSE );

//
// Lets select the data as ASCII
//

selectSQL = _T("select col1 from UnicodeTestTbl");

rc = SQLExecDirect( hStmt, selectSQL, _tcslen( selectSQL ) );
rc = SQLFetch( hStmt );
rc = SQLGetData( hStmt,

1,
SQL_C_CHAR,
singleByteOutData,
9 * sizeof( char ), // 8 characters + NULL term &bytesReturned );

assert( bytesReturned = 8 * sizeof( char ) );
assert( bytesReturned = strlen( singleByteOutData ) );
printf( "First ASCII row - %s\n", singleByteOutData );

rc = SQLFetch( hStmt );
rc = SQLGetData( hStmt,
1,
SQL_C_CHAR,
singleByteOutData,
9 * sizeof( char ), // 8 characters + NULL term &bytesReturned );

assert( bytesReturned = 8 * sizeof( char ) );
assert( bytesReturned = strlen( singleByteOutData ) );
printf( "Second ASCII row - %s\n", singleByteOutData );

rc = SQLFreeStmt( hStmt, SQL_COMMIT );
rc = SQLDisconnect( hDbc );

rc = SQLFreeConnect( hDbc );
rc = SQLFreeEnv( hEnv );

return;
}

--
Justin Cave - Oracle ODBC Development

Opinions expressed herein are my own and may not reflect those of
Oracle Corporation.


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Oct 17 2000 - 22:16:38 CDT