Home » Developer & Programmer » Precompilers, OCI & OCCI » How to Fetch SQL Unicode characters?  () 1 Vote
icon5.gif  How to Fetch SQL Unicode characters? [message #239076] Mon, 21 May 2007 07:33 Go to next message
ketu0001
Messages: 7
Registered: April 2007
Junior Member
Hi All,

I am getting problem in fetching data using C++ program.
How to Fetch SQL Unicode characters?
I am using ODBC driver calls in C++ to fetch SQL data.
It is working fine with character data.
But for unicode data it displays ???? instead of data.
Can anybody please help?

Regards
Ketaki
Re: How to Fetch SQL Unicode characters? [message #239194 is a reply to message #239076] Mon, 21 May 2007 13:08 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Ketaki

You'll need to check the ODBC docs.

You'll need to know what character sets Oracle is using. For VARCHAR etc. datatypes you are intersted in NLS_CHARACTERSET, for NVARCHAR you are interested in NLS_NCHAR_CHARACTERSET.

select parameter, value
  from v$nls_parameters
  where parameter like '%CHARACTERSET%';


Also - it's unlikely that your Windows characterset can display all Unicode characters - so rather look at the byte values retrieved and compare to what's stored in the table.

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
NLS_CHARACTERSET	AL32UTF8

create table utf8_tst(col1 varchar2(1 char));

-- Euro is U+20AC
insert into utf8_tst values (unistr('\20AC'));
-- Small Greek Gamma U+03B3
insert into utf8_tst values (unistr('\03B3'));
insert into utf8_tst values (unistr('A'));

select col1, vsize(col1), dump(col1, 1010) Decimal_bytes, dump(col1, 1016) Hex_Bytes from utf8_tst;
¬ 	3	Typ=1 Len=3 CharacterSet=AL32UTF8: 226,130,172	Typ=1 Len=3 CharacterSet=AL32UTF8: e2,82,ac
¿	2	Typ=1 Len=2 CharacterSet=AL32UTF8: 206,179	Typ=1 Len=2 CharacterSet=AL32UTF8: ce,b3
A	1	Typ=1 Len=1 CharacterSet=AL32UTF8: 65	Typ=1 Len=1 CharacterSet=AL32UTF8: 41

Re: How to Fetch SQL Unicode characters? [message #239297 is a reply to message #239076] Tue, 22 May 2007 00:12 Go to previous message
ketu0001
Messages: 7
Registered: April 2007
Junior Member
Following is C++ code I am using for fetching SQL data:
#include <windows.h>

#include <sqlext.h>
#include<stdio.h>
#include <iostream.h>
#include <string.h>


int main(void)

{

HENV hEnv = NULL; // Env Handle from SQLAllocEnv()

HDBC hDBC = NULL; // Connection handle

HSTMT hStmt = NULL;// Statement handle

UCHAR szDSN[SQL_MAX_DSN_LENGTH] = "myDataSource";// Data Source Name buffer

UCHAR szUID[10] = "sa";// User ID buffer

UCHAR szPasswd[10] = "bmcAdm1n";// Password buffer

// UCHAR szModel[128];// Model buffer

// SDWORD cbModel;// Model buffer bytes recieved

char buff[9] = "Testing";


// UCHAR szSqlStr[128]= "INSERT into Quali (Colname) Values ('Testing')" ;

UCHAR szSqlStr[150]= "select name ,default_database_name,default_language_name from sys.sql_logins" ;


RETCODE retcode;

//sprintf((char*)szSqlStr,"INSERT into <Tablename> (Colname) Values ('%s')",buff);
sprintf((char*)szSqlStr,"select name ,default_database_name,default_language_name from sys.sql_logins",buff);



// Allocate memory for ODBC Environment handle
cout<<"hEnv before SQLAllocEnv = "<<hEnv<<endl;
retcode= SQLAllocEnv (&hEnv);
cout<<"retcode= "<<retcode<<endl;
cout<<"hEnv after SQLAllocEnv = "<<hEnv<<endl;


cout<<"\n***********************\n\n";

// Allocate memory for the connection handle
cout<<"hDBC before SQLAllocConnect = "<<hDBC<<endl;
retcode= SQLAllocConnect (hEnv, &hDBC);
cout<<"retcode= "<<retcode<<endl;
cout<<"hDBC after SQLAllocConnect = "<<hDBC<<endl;

cout<<"\n***********************\n\n";
// Connect to the data source "test" using userid and password.



retcode = SQLConnect (hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd, SQL_NTS);
cout<<"retcode= "<<retcode<<endl;


if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{
cout<<"Connection established....!\n";


// Allocate memory for the statement handle
cout<<"hStmt before SQLAllocStmt = "<<hStmt<<endl;
retcode = SQLAllocStmt (hDBC, &hStmt);
cout<<"retcode= "<<retcode<<endl;
cout<<"hStmt after SQLAllocStmt = "<<hStmt<<endl;
cout<<"\n***********************\n\n";


/*
// Prepare the SQL statement by assigning it to the statement handle
retcode = SQLPrepare (hStmt, szSqlStr, sizeof (szSqlStr));

cout<<"\nStatement szSqlStr: "<<szSqlStr<<endl;
cout<<"\nStatement hStmt: "<<hStmt<<endl;
// Execute the SQL statement handle

retcode = SQLExecute (hStmt);
*/

retcode = SQLExecDirect(hStmt,(unsigned char *)szSqlStr, SQL_NTS);

UCHAR name[4000],defDBnm[4000],defLanguage[4000];
SDWORD retcode2, cbname, cbdefDBnm, cbdefLanguage;

retcode2=NULL;

if (retcode2 == SQL_SUCCESS)
{
retcode2 = SQLBindCol(hStmt, 1, SQL_C_CHAR, name, 4000, &cbname);
retcode2 = SQLBindCol(hStmt, 2, SQL_C_CHAR, defDBnm, 4000, &cbdefDBnm);
retcode2 = SQLBindCol(hStmt, 3, SQL_C_CHAR, defLanguage, 4000, &cbdefLanguage);
}

UCHAR sqlState[6]; /* buffer to store SQLSTATE */
SDWORD retcode3=NULL; /* return code */
SDWORD nativeErr; /* native error code */
UCHAR errMsg[256]; /* buffer to store error message */
SWORD realMsgLen; /* real length of error message */
// SWORD count;





if (retcode2 != SQL_SUCCESS) /* warning or error returned */
{
retcode3 = SQLError(SQL_NULL_HDBC, hDBC, SQL_NULL_HSTMT, sqlState,&nativeErr, errMsg, 256, &realMsgLen);
//print_err(sqlState, nativeErr, errMsg, realMsgLen);
cout<<sqlState<<endl<<nativeErr<<endl<<errMsg<<endl<<realMsgLen;
}




cout <<"\n****************************************\n";
while (TRUE)
{
retcode2 = SQLFetch(hStmt);

if (retcode2 == SQL_SUCCESS || retcode2 == SQL_SUCCESS_WITH_INFO)
{
if (cbname == SQL_NULL_DATA) /* check null data */
printf("\nname: NULL\n");
else
printf("\nName: %s\n", name);

if (cbdefDBnm == SQL_NULL_DATA)
printf("defDBnm name: NULL\n");
else
printf("defDBnm name: %s\n", defDBnm);

if (cbdefLanguage == SQL_NULL_DATA)
printf("defLanguage: NULL\n");
else
printf("defLanguage : %s\n\n", defLanguage);
}

else if (retcode2 == SQL_ERROR ) /* warning or error returned */
{
retcode3 = SQLError(SQL_NULL_HDBC, hDBC, SQL_NULL_HSTMT, sqlState,&nativeErr, errMsg, 256, &realMsgLen);
//print_err(sqlState, nativeErr, errMsg, realMsgLen);
cout<<sqlState<<endl<<nativeErr<<endl<<errMsg<<endl<<realMsgLen;
}

else /* if no more data or errors returned */
break;
}


// Project only column 1 which is the models
// SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);


// Get row of data from the result set defined above in the statement

// retcode = SQLFetch (hStmt);


// Free the allocated statement handle
SQLFreeStmt (hStmt, SQL_DROP);

// Disconnect from datasource
SQLDisconnect (hDBC);
}

// Free the allocated connection handle
SQLFreeConnect (hDBC);

// Free the allocated ODBC environment handle
SQLFreeEnv (hEnv);

return 0;
}





Previous Topic: Free Download Pro*C for SUN
Next Topic: Issue with OCI JDBC using cwallet.sso
Goto Forum:
  


Current Time: Thu Mar 28 18:46:35 CDT 2024