Home » Developer & Programmer » Precompilers, OCI & OCCI » How to Fetch SQL Unicode characters?
( ) 1 Vote
How to Fetch SQL Unicode characters? [message #239076] |
Mon, 21 May 2007 07:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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;
}
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:27:14 CDT 2024
|