Java EE (Java Enterprise Edition) General Discussion

Is your Visual C++ application compiled in ANSI mode or in Unicode mode? It should use the Unicode mode. Make also sure that the ODBC driver in use is from Oracle and not from Microsoft.

Make sure that Visual Basic also uses Unicode, though I do not know the product so I cannot say how to achieve this.

WE8ISO8859P9 does not support 'Ə' and there is no Windows ANSI code page supporting Azeri, therefore your applications must use Unicode.


Thnx,
Sergiusz

Hi Sergiusz,

My Visual C++ application is compiled in Unicode mode. Also, I am using Oracle ODBC 10g driver.
Actually i am not sure where the exact problem is...?
Whatever data is getting inserted/retrieving through JDBC maintains its exact structure,but this not in case of ODBC..!!!
When i was using AL32UTF8 on my Oracle Client, of 7, around 4 characters where getting junked...!!!
I am reading the Oracle forum since many days...
I have found out that though Windows API are Unicode, its GUI is not Unicode...!!!
So, what apporach should i go for, as i am using Visual Studios 6.0 which does not except an unicode file as an input...!!!
So, i am in a big scoop as what should i do to support Azeri-Latin in my application......!!!!!!

Thnx 4 the reply,

Regards,
Pratik.

Windows API is both Unicode and non-Unicode. The API in use depends on both _UNICODE and UNICODE precompiler macros being defined. Oracle ODBC driver is Unicode by default since version 8.1.5.5 so it should not create problems for Azeri characters. Is your application using GUI mode or Command Prompt? Do you use some programming framework, like MFC?


-- Sergiusz

Hi,

I am using both precompiler macros _UNICODE & UNICODE and YES my application is MFC based and using GUI mode.


Thanks & Regards
Pratik

Could you paste a fragment of your program that selects some data and shows it on the screen. As the problem may be on the application side, I am trying to find out the exact calls you use.

Also, please try the following tests:

1) Execute the statement

INSERT INTO <tab> VALUES( ..., UNISTR('\018F\0259'), ... );

where <tab> is one of your application or test tables and UNISTR call corresponds to position of a textual column that you see on the screen.

2) Select the inserted value through the application and tell me what you see on the screen.

3) Modify your application to transform the selected value to hex format just after it is selected and show the hex representation on the screen to see what code is selected.
Alternatively, send the value to a binary file and use a hex editor to see at particular selected bytes.


Thnx,
Sergiusz

Hi,

Even i was thinking to share the code snippet of my application here.
But the application is so large that it's a little confusion where exactly the data is getting junked.
Also, i dont know how to debug a VC++ file not having a entry in COM+
But from the amount of debug i have done on my application i have came to an inference that while connecting app to DB itself the data might be getting junked.
Its a ODBCDLL, and it is used while building other DLL of my application.
Here we are using the conversion method MULTIBYTETOWIDECHAR. I think the codepage used in the function in making a problem.
The function is as follows,

static short PopulateSingleWCharBinds ( DBPROCESS * pDbproc)
{
int i,
lRetVal;
wchar_t l_wchar[2];
char l_errtxt[1000];
char *l_ptr;

i=0;
while ( i < pDbproc->WCharBindCtr )
{
if (UT_PARAM_INPUT != pDbproc->WCharPtrType)
{
l_ptr = (LPSTR)pDbproc->WCharBuffs[i];

RTRIM(l_ptr);

lRetVal = MultiByteToWideChar(CP_ACP,
NULL,
(LPCSTR)pDbproc->WCharBuffs[i],
MAX_UTF8_CHAR_SIZE,
l_wchar,
sizeof(l_wchar));

if (!lRetVal)
{
sprintf(l_errtxt, "Converion Failure : Db Column No %d, ErrCode = %ld", i, GetLastError());
LogExtMesg( "PopulateSingleWCharBinds", l_errtxt, __LINE__ , GetLastError());
return ( FAIL );
}

*(pDbproc->WCharPtr[i]) = l_wchar[0];
}
i++;
}

return ( SUCCEED );
}



I have tried changing CP_ACP to CP_UTF8, but it has not served the purpose of Unicode Support. Rather, doing that i am not able to login into the app thereafter. Also, i have used 1254 in place of CP_ACP but even is not serving the purpose.

Now, i have few question with the way you have mentioned for testing the application,

1) What Oracle Cleint Settings should i used, TURKEY_TURKISH.WE8ISO8859P9 OR .AL32UTF8(I prefer using .AL32UTF8)
2) I am using PL/SQL Developer, and its editor does not support Unicode fonts. So, for inserting data i am wrinting a script in notepad and saving it as .sql file
and calling it through sql. Buit, even in that case 'ə' is getting inserted as '?'.
3) Also, I have set Advanced tab of Regional Setting to "Azeri-Latin". Is it required? Only after that 6 characters are being supprted except for 'ə'
4) I understood the first two points mentioned by you, but confused with third point. Can you elaborate it little more. What modification and hex related thing...???


Thanks for keeping cool while reading this bible of mine.
Appreciating your efforts putting.

Regards,
Pratik.

Hi Sergiusz,

The issue I was fighting for long, has finally been resolved.
I am thankful to you and duly appreciate your help towards my problem.

The problem was not on the Oracle Side.

The changes that i did to support Unicode Characters in my application are as follows,
1) Changed the registry value of NLS_LANG to ".AL32UTF8"
2) In the Code Snippet I have mentioned in above post, changed CP_ACP of MultiByteToWideChar to 65001.


This changes gave me support for every Unicode Character.

Thanks for the Knowledge Transfer,

Regards,
Pratik.

You are welcome. But note that your approach is not "canonical". An application compiled in Unicode mode working with the Oracle ODBC 10g driver should not need to do this explicit conversion at all. The conversion should be done by the ODBC driver making your application immune to NLS_LANG character set value. NLS_LANG should remain set to one of the ANSI code pages, such as WE8MSWIN1252 or TR8MSWIN1254

I am guessing that the issue is caused by the fact that you bind columns/parameters using the value type SQL_C_CHAR (unsigned char *) instead of the value type SQL_C_WCHAR (wchar_t *).


Thnx,
Sergiusz

Hi Sergiusz,

Thnks 4 the reply. The cause you mentioned that can arise the problem for unicode charcters,
was already been implemented in the application code.
So, i can not see any other reason for non-recognition of Unicode Characters.
But, now i am facing a new problem. I hope you can help.

Problem Description,
As mentioned earlier, the Advanced Tab of Regional Setting was changed to Azeri-Latin,
modifyiing the Nls in registry to 1254(Actually meant for Turkish, not supporting 'Ə')
Thus, in Notepad in ANSI encoding all Azeri-Latin Characters get supported except for 'Ə'.
Thus, same goes for xml file.
Now what my issue is, the application parse the data using XML(DLL's in VC++).
So, while data entry is done the XML that is generated does not support 'Ə' and the same variable gets inserted into database.
So, now the problem is not on Oracle side as it is retreiving data properly if it is proper in database.
but now insertion is problem, due to no supported Code Page by Windows.

So, how can provide Unicode Encoding to XML so that it can preserve the Unicode data till it is handed over to Oracle?


Thanks & Regards,
Pratik.

The Solution for the previous posted question got answered.
Now, a new problem got arised,
Thus re-posting the problem.

Hi Sergiusz,

Thnks 4 the reply. The cause you mentioned that can arise the problem for unicode charcters,
was already been implemented in the application code.
So, i can not see any other reason for non-recognition of Unicode Characters.
But, now i am facing a new problem. I hope you can help.

Problem Description,
As mentioned earlier, the Advanced Tab of Regional Setting was changed to Azeri-Latin,
modifyiing the Nls in registry to 1254(Actually meant for Turkish, not supporting 'Ə')
Thus, in Notepad in ANSI encoding all Azeri-Latin Characters get supported except for 'Ə'.
Thus, same goes for xml file.
Now what my issue is, the application parse the data using XML(DLL's in VC++).
So, while data entry is done the XML that is generated does not support 'Ə' and the same variable gets inserted into database.
So, now the problem is not on Oracle side as it is retreiving data properly if it is proper in database.
but now insertion is problem, due to no supported Code Page by Windows.

So, how can provide Unicode Encoding to XML so that it can preserve the Unicode data till it is handed over to Oracle?


Thanks & Regards,
Pratik.

Hi,

I would also like to share few more doubts that i have came across while striving to resolve the Globalization issues,

The application DLL that i was mentioning in my initial post, consist of .rc(resource file).
The structure of .rc file is as shown,


#if !defined(AFX_RESOURCE_DLL) || defined(AFX_TARG_ENU)
#ifdef _WIN32
LANGUAGE LANG_ENGLISH, SUBLANG_ENGLISH_US
#pragma code_page(1252)
#endif //_WIN32

#ifdef APSTUDIO_INVOKED
/////////////////////////////////////////////////////////////////////////////
//
// TEXTINCLUDE
//

1 TEXTINCLUDE DISCARDABLE
BEGIN
"resource.h\0"
END

2 TEXTINCLUDE DISCARDABLE
BEGIN
"#include ""winres.h""\r\n"
"\0"
END

3 TEXTINCLUDE DISCARDABLE
BEGIN
"1 TYPELIB ""FinAppTxn.tlb""\r\n"
"\0"
END

#endif // APSTUDIO_INVOKED


#ifndef _MAC
/////////////////////////////////////////////////////////////////////////////
//
// Version
//

VS_VERSION_INFO VERSIONINFO
FILEVERSION 1,0,0,1
PRODUCTVERSION 1,0,0,1
FILEFLAGSMASK 0x3fL
#ifdef _DEBUG
FILEFLAGS 0x1L
#else
FILEFLAGS 0x0L
#endif
FILEOS 0x4L
FILETYPE 0x2L
FILESUBTYPE 0x0L
BEGIN
BLOCK "StringFileInfo"
BEGIN
BLOCK "040904B0"
BEGIN
VALUE "CompanyName", "\0"
VALUE "FileDescription", "FinAppTxn Module\0"
VALUE "FileVersion", "1, 0, 0, 1\0"
VALUE "InternalName", "FinAppTxn\0"
VALUE "LegalCopyright", "Copyright 2000\0"
VALUE "OriginalFilename", "FinAppTxn.DLL\0"
VALUE "ProductName", "FinAppTxn Module\0"
VALUE "ProductVersion", "1, 0, 0, 1\0"
VALUE "OLESelfRegister", "\0"
END
END
BLOCK "VarFileInfo"
BEGIN
VALUE "Translation", 0x409, 1200
END
END

#endif // !_MAC



Till my knowledge, we tend to change AFX_TARG_ENU, pragma code_page, etc. for Globalization support, but
that is only in case for messages written in .rc file itself, and not for data that gets parsed through the DLL, while XML gen and Message Build.

So, can changing .rc file make some kind of difference for Globalization Support.
And what all are the Globalization Specific Changes...???


Regards,
Pratik.

You start asking Microsoft-specific questions on an Oracle-specific forum ;-)

You should only use XML files encoded in UTF-8. This is the standard XML encoding. If no encoding information is presented to XML parser, the parser is expected to read the file as UTF-8 per XML standard. If this is not the case, you have to provide much more information about how you read the XML file, preferably with code examples.

Compilation macros like AFX_TARG_ENU are not expected to influence the globalization support at runtime. Certainly, they have no influence on Oracle globalization support. They are meant to help compile the right localized version of your resources for the given language version of your Windows application.



-- Sergiusz

Hi,

I was to comment that i am asking Microsoft Specific question on Oracle Forum,
but then didnt has you were sound enough to answer my both sides of question and were aware of my problem.
Later, in any forum i had to explain my problem in detail again.

Now, coming to problem while going through the flow of data, i have found that almost every where,
encoding = 'UTF-8' is used. But even with it, the support is only for characters as per code page.
Also for azeri the codepage that gets set is 1254(that of Turkish, thus tends not to support 'ə')

Though, you did cleared one of my doubt that i had with .rc file, and that is surely going to help me fighting this problem.
Currently, i am studying the application code myself, and get back to you with the code sample, where there is a scope of modification.

I would like to share few more things regarding my application.
1) As i mentioned earlier, the input data is mapped with XML tags and is parsed to DB through an DLL build using VC++
2) While storing input data in DB, it is stored in XML format itself in the DB table with data type BLOB (can you confirm if BLOB will support Unicode Characters)
3) The XML in DB itself shows the incorrect data. So, its obvious before storing in DB itself, the data gets corrupted.
3) Later, other tables are updated using the XML stored in DB table.
4) So, as insertion is incorrect, retrieval has to be incorrect...!!!
5) But, for correct data in DB the retreival works perfectly fine with the settings that i mentioned in few of my earlier posts.


So, for now my insertion is problem and not retreival.
I am working on my application code, and will get back soon as i mentioned.
But, would appreciate if you can find out by the kind of description i have given, where the problem can exactly be...???


Thanks and Regards,
Pratik.

Do you store data in CLOB or in BLOB?

BLOB stands for Binary Large OBject. Binary means that DB treats the contents as bunch of bytes. Whatever your application inserts into a BLOB on the client side gets stored in the DB and can be later retrieved. If you store Unicode characters, the BLOB will contain Unicode characters, if you store a JPG picture, the BLOB will contain the picture. It is like a file in a Unix file system -- it is up to applications to interpret the contents.

The contents of BLOBs is usually not interpreted by the server. The exception is Oracle Text, which allows indexing documents stored in BLOBs.

CLOB stands for Character Large OBject. It is expected to contain text in the database character set. When data to be stored in CLOB is transported from a client to the DB, it undergoes standard character set conversion between the declared client character set and the declared DB character set.

## 3) Later, other tables are updated using the XML stored in DB table.

As I do not see any BLOB-based API in Oracle XML DB, I wonder how you do this.



-- Sergiusz

Hi,

The DB tables are not directly updated using BLOB contents.
The API is written using VC++ and not in Oracle XML DB.
The Contents of BLOB are read by VC file and later it updates the respective DB tables.
This what i was trying to mean. But, to cut short the description mentioned it that way.
But, though my point of concern for now is the data getting stored in BLOB data type in DB.
Later part will check later.
First to correct the starting point, then will progressively check the intermediate steps is the approach i am looking towards right now.


Regards,
Pratik.

Hi All,
&
Hi Sergiusz,

So I am posting after long time... Might be possible that you must have lost the track of the issue was regarding...

Sorry to ask VC related questions, here at Oracle forums... But this is the only place where i can hope to get it resolved...

Below mentioning the code snippets in which the data flows,
There are multiple files in the application. I've consolidated the code as per the flow...
Also, highlighting the code that are of use, rest all would be the bussiness logic...

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Call to Database through VC

BOOL CAuthWrap::insert_txn_log()
{
int l_user_no,
l_id_tran_seq,
l_id_tran_seq_old,
l_tran_cat,
l_cod_org_brn,
l_cod_usr_brn,
l_cod_lo,
l_auth_no,
l_cod_tcy,
l_cod_acy,
l_cod_lcy,
l_txn_mode,
l_reply_code,
l_txn_xml_len,
l_flg_compress,
l_flg_asg_seq,
l_retcode;
wchar_t l_dat_post[64],
l_dat_value[64],
l_tim_sys[64],
l_dat_sys[64],
l_dattim_sys[128],
l_id_acct[64],
l_id_from_acct [64],
l_id_to_acct [64],
l_user_id[64],
l_cod_tran_stat,
*l_wptr_txn_xml,
l_cod_tran[MAX_COD_TRAN_LEN + 1];

double l_amt_tcy,
l_amt_acy,
l_amt_lcy,
l_rat_tclcy,
l_rat_aclcy;
char *l_ptr_txn_xml;

DBPROCESS *l_ptr_ho_dbproc;


wchar_t l_txt_comment1[255 + 1] ,
l_txt_comment2[255 + 1] ,
l_auth_id[12+1];
int l_auth_stat,
l_ctr_batch_no,
l_cod_txn_mnemonic;
long l_num_txn;
wchar_t l_dat_post_odbc [32+1];
int l_cod_issuer = 0;

memset(l_txt_comment1,0,sizeof(l_txt_comment1)) ;
memset(l_txt_comment2,0,sizeof(l_txt_comment2)) ;

memset(l_tim_sys, 0, sizeof(l_tim_sys));
memset(l_dat_sys, 0, sizeof(l_dat_sys));

if ((m_dom.get_int_value("UserInfo.UserNo", &l_user_no) != FLDOK) ||
(m_dom.get_string_value(l_cod_tran,"TXNDEFN.CodTran") != FLDOK) ||
(m_dom.get_int_value("TXNDATA.TxnStan", &l_id_tran_seq) != FLDOK) ||
(m_dom.get_int_value("TXNDATA.OrgTxnStan", &l_id_tran_seq_old) != FLDOK) ||
(m_dom.get_int_value("TXNDEFN.TxnCat", &l_tran_cat) != FLDOK) ||
(m_dom.get_int_value("BranchInfo.BranchCode", &l_cod_org_brn) != FLDOK) ||
(m_dom.get_int_value("TXN.Mode", &l_txn_mode) != FLDOK) ||
(m_dom.get_int_value("TXN.ReplyCode", &l_reply_code) != FLDOK) ||
(m_dom.get_string_value(l_user_id, "UserInfo.UserID") != FLDOK) ||
(m_dom.get_string_value(l_dat_post, "TxnDates.Posting") != FLDOK) ||
(m_dom.get_string_value(l_dat_value, "TxnDates.Value") != FLDOK) ||
(m_dom.get_string_value(l_tim_sys, "TxnDates.TxnTime") != FLDOK) ||
(m_dom.get_string_value(l_dat_sys, "TxnDates.Txn") != FLDOK))

{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Error in Getting Data From XML","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG523;
return FALSE;
}

swprintf(l_dattim_sys, L"%s%s", l_dat_sys, l_tim_sys);

m_dom.get_string_value(l_id_acct, "AccountNo");

if (!wcscmp(l_id_acct, L""))
m_dom.get_string_value(l_id_acct, "GLAccountNo");

if (MONETORY_TXN == l_tran_cat || REVERSAL_TXN == l_tran_cat)
{
if ((m_dom.get_int_value("cod_tran_cur", &l_cod_tcy) != FLDOK) ||
(m_dom.get_int_value("cod_acct_cur", &l_cod_acy) != FLDOK) ||
(m_dom.get_int_value("STS_COD_LCL_CUR", &l_cod_lcy) != FLDOK) ||
(m_dom.get_double_val("amt_tran_tcye", &l_amt_tcy) != FLDOK) ||
(m_dom.get_double_val("amt_tran_acye", &l_amt_acy) != FLDOK) ||
(m_dom.get_double_val("amt_tran_lcye", &l_amt_lcy) != FLDOK) ||
(m_dom.get_double_val("rat_tran_lcl_cur", &l_rat_tclcy) != FLDOK) ||
(m_dom.get_double_val("rat_acct_lcl_cur", &l_rat_aclcy) != FLDOK))
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Error in Getting Data From XML","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG523;
return FALSE;
}
}
else
{
l_cod_tcy = 0;
l_cod_acy = 0;
l_cod_lcy = 0;
l_amt_tcy = 0.0;
l_amt_acy = 0.0;
l_amt_lcy = 0.0;
l_rat_tclcy = 0.0;
l_rat_aclcy = 0.0;
}

CTranStat l_tran_stat(l_txn_mode, l_reply_code);

l_tran_stat.incomplete();

( m_auth_status == AUTH_GRANTED ) ? l_tran_stat.supauth() : ( m_auth_status == AUTH_DENIED ) ? l_tran_stat.cancel() : 0;

l_cod_tran_stat = l_tran_stat.getvalue();

l_wptr_txn_xml = (wchar_t *)calloc(m_dom.get_xml_len() * sizeof(wchar_t), 1);

if (!l_wptr_txn_xml)
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Memory Allocation Failure","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG505;
return FALSE;
}

l_flg_compress = 0;
if (is_mow_txn())
l_flg_compress = 1;

if (m_dom.get_modified_xml(l_wptr_txn_xml, l_flg_compress) != FLDOK)
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Failed Accessing XML","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG523;
free(l_wptr_txn_xml);
return FALSE;
}

l_txn_xml_len = WideCharToMultiByte(65001, 0, l_wptr_txn_xml, -1, NULL, 0, NULL, NULL);

if (!l_txn_xml_len)
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Failed Conversion","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG523;
free(l_wptr_txn_xml);
return FALSE;
}

l_ptr_txn_xml = (char *)calloc(l_txn_xml_len, 1);

if (!l_ptr_txn_xml)
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Memory Allocation Failure","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG505;
free(l_wptr_txn_xml);
return FALSE;
}

l_txn_xml_len = WideCharToMultiByte(65001, 0, l_wptr_txn_xml, -1, l_ptr_txn_xml, l_txn_xml_len, NULL, NULL);

if (!l_txn_xml_len)
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Failed Conversion","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG523;
free(l_wptr_txn_xml);
free(l_ptr_txn_xml);
return FALSE;
}

if(m_auth_type == CENTRAL_AUTH)
{
switch (m_auth_status)
{
case AUTH_GRANTED:
l_auth_stat = AUTH_STAT_GRANTED;
break;
case AUTH_DENIED:
l_auth_stat = AUTH_STAT_DENIED;
if(!wcscmp(l_user_id,m_auth_id))
l_auth_stat = AUTH_STAT_CANCELLED ;
break;
case AUTH_SUBMIT:
case AUTH_REDIRECT:
l_auth_stat = AUTH_STAT_PENDING;
break;
}

if((l_auth_stat == AUTH_STAT_GRANTED) || (l_auth_stat == AUTH_STAT_DENIED))
{
if (m_dom.get_int_value("AuthInfo.AuthBrn", &l_cod_usr_brn) != FLDOK)
{
LOG_MSG( NULL,"CAuthWrap::insert_txn_log","",0,"Error in Getting Data From XML","","",FATAL_ERROR_MSG );
m_err_code = TP_MSG523;
free(l_wptr_txn_xml);
free(l_ptr_txn_xml);
return FALSE;
}
}
}

m_dom.get_int_value("IssuerCode", &l_cod_issuer);

if ((m_auth_type != CENTRAL_AUTH) ||
((m_auth_type == CENTRAL_AUTH) && (l_cod_org_brn == l_cod_usr_brn)) ||
((m_auth_type == CENTRAL_AUTH) && (l_auth_stat != AUTH_STAT_GRANTED && l_auth_stat != AUTH_STAT_DENIED))
)
{
OdbcProcedureCmd(m_dbproc, L"ap_upd_ins_incomplete_txn", 22, 0);
OdbcBindParameter(m_dbproc, 1, UT_PARAM_INPUT, UTINTBIND, -1, &l_user_no);
OdbcBindParameter(m_dbproc, 2, UT_PARAM_INPUT, UTSTRINGBIND, sizeof(l_cod_tran), l_cod_tran);
OdbcBindParameter(m_dbproc, 3, UT_PARAM_INPUT, UTINTBIND, -1, &l_id_tran_seq);
OdbcBindParameter(m_dbproc, 4, UT_PARAM_INPUT, UTINTBIND, -1, &l_id_tran_seq_old);
OdbcBindParameter(m_dbproc, 5, UT_PARAM_INPUT, UTINTBIND, -1, &l_tran_cat);
OdbcBindParameter(m_dbproc, 6, UT_PARAM_INPUT, UTINTBIND, -1, &l_cod_org_brn);
OdbcBindParameter(m_dbproc, 7, UT_PARAM_INPUT, UTSMALLBIND, -1, &l_cod_tran_stat);
OdbcBindParameter(m_dbproc, 8, UT_PARAM_INPUT, UTSTRINGBIND, sizeof(l_user_id), l_user_id);
OdbcBindParameter(m_dbproc, 9, UT_PARAM_INPUT, UTSTRINGBIND, sizeof(l_dat_post), l_dat_post);
OdbcBindParameter(m_dbproc,10, UT_PARAM_INPUT, UTSTRINGBIND, sizeof(l_dat_value), l_dat_value);
OdbcBindParameter(m_dbproc,11, UT_PARAM_INPUT, UTSTRINGBIND, sizeof(l_dattim_sys), l_dattim_sys);
OdbcBindParameter(m_dbproc,12, UT_PARAM_INPUT, UTSTRINGBIND, sizeof(l_id_acct), l_id_acct);
OdbcBindParameter(m_dbproc,13, UT_PARAM_INPUT, UTINTBIND, -1, &l_cod_tcy);
OdbcBindParameter(m_dbproc,14, UT_PARAM_INPUT, UTINTBIND, -1, &l_cod_acy);
OdbcBindParameter(m_dbproc,15, UT_PARAM_INPUT, UTINTBIND, -1, &l_cod_lcy);
OdbcBindParameter(m_dbproc,16, UT_PARAM_INPUT, UTDOUBLEBIND, -1, &l_amt_tcy);
OdbcBindParameter(m_dbproc,17, UT_PARAM_INPUT, UTDOUBLEBIND, -1, &l_amt_acy);
OdbcBindParameter(m_dbproc,18, UT_PARAM_INPUT, UTDOUBLEBIND, -1, &l_amt_lcy);
OdbcBindParameter(m_dbproc,19, UT_PARAM_INPUT, UTDOUBLEBIND, -1, &l_rat_tclcy);
OdbcBindParameter(m_dbproc,20, UT_PARAM_INPUT, UTDOUBLEBIND, -1, &l_rat_aclcy);
OdbcBindParameter(m_dbproc,21, UT_PARAM_INPUT, UTINTBIND, -1, &l_cod_issuer);
OdbcBindParameter(m_dbproc,22, UT_PARAM_INPUT, UTBLOBBIND, l_txn_xml_len, l_ptr_txn_xml);
...
...
...
}



-------------------------------------------------------------------------------------------------------------------------------------------------------------

OdbcBindParameterW definition,


#define OdbcBindParameter OdbcBindParameterW


ODBCLIBAPI SQLRETURN OdbcBindParameterW( DBPROCESS * pDbproc,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT InputOutputType,
SQLSMALLINT ParameterType,
SQLINTEGER ColumnSize,
SQLPOINTER ParameterValuePtr);


-------------------------------------------------------------------------------------------------------------------------------------------------------------

Function OdbcBindParameterW(



SQLRETURN OdbcBindParameterW( DBPROCESS *pDbproc,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT InputOutputType,
SQLSMALLINT ParameterType,
SQLINTEGER ColumnSize,
SQLPOINTER ParameterValuePtr )
{
SQLRETURN lRetcode;
SQLINTEGER* lpStrLen_or_IndPtr;
SQLSMALLINT lInputOutputType,
lBoundVarType,
lDatabaseColumnType;
SQLINTEGER lColSize,
lBufferLength,
lRetSize;
SQLPOINTER lParameterValuePtr;
char l_err_txt[1000];

if (pDbproc->CurrentStmtType == PROCEDURE_STMT)
{
lpStrLen_or_IndPtr = (SQLINTEGER *)&(pDbproc->ParamIndPtr[ParameterNumber]);
}
else
{
lpStrLen_or_IndPtr = (SQLINTEGER *)&(pDbproc->ParamIndPtr[ParameterNumber + 1]);
}


switch( ParameterType )

{

...
...
...
case UTSTRINGBIND :
case UTVARYSTRINGBIND :

lBoundVarType = SQL_C_CHAR;

if (IS_SYBASE_DB(pDbproc))
lDatabaseColumnType = SQL_UNICODE_VARCHAR;
else
lDatabaseColumnType = SQL_VARCHAR;

lBufferLength = MAX_WCHAR_SIZE * UTF_FACTOR_SIZE + 1;

pDbproc->WStringPtr[pDbproc->WStringBindCtr] = (WCHAR *)ParameterValuePtr;
pDbproc->WStringPtrSize[pDbproc->WStringBindCtr] = ColumnSize;
pDbproc->WStringPtrType[pDbproc->WStringBindCtr] = InputOutputType;
pDbproc->WStringPtrParamNo[pDbproc->WStringBindCtr] = &pDbproc->ParamIndPtr[ParameterNumber + 1];
lParameterValuePtr = pDbproc->WStringBuffs[pDbproc->WStringBindCtr];

switch (InputOutputType)
{
case UT_PARAM_OUTPUT:

if (ParameterValuePtr == NULL)
{
LogMesg( "OdbcBindParameter", "Cannot Bind NULL Ptr For Output", __LINE__ );
return FAIL;
}

lColSize = (ColumnSize/sizeof(wchar_t) ) * UTF_FACTOR_SIZE - 1; _ASSERT(lColSize > 0);

if (lColSize <= 0)
{
LogMesg("OdbcBindParameter", "Cannot bind, insufficient application buffer", __LINE__);
return FAIL;
}
break;

case UT_PARAM_INPUT_OUTPUT:

if (ParameterValuePtr == NULL)
{
LogMesg( "OdbcBindParameter", "Cannot Bind NULL Ptr For Output", __LINE__ );
return FAIL;
}

lColSize = (ColumnSize/sizeof(wchar_t) ) * UTF_FACTOR_SIZE - 1; _ASSERT(lColSize > 0);

if (lColSize <= 0)
{
LogMesg("OdbcBindParameter", "Cannot bind, insufficient application buffer", __LINE__);
return FAIL;
}

if (wcslen((WCHAR *)ParameterValuePtr))
{
WCHAR *l_ptr;

l_ptr = (WCHAR *)ParameterValuePtr;

RTRIMW(l_ptr);

lRetSize = WideCharToMultiByte( CP_ACP,
NULL,
(LPCWSTR)ParameterValuePtr,
-1,
(LPSTR)lParameterValuePtr,
lBufferLength,
NULL,
NULL);

if (!lRetSize)
{
sprintf(l_err_txt, "Failed In Converting From UCS2 To DB type, Column No %d, Data %S : Error Code %d, Text:", pDbproc->WCharBindCtr, ParameterValuePtr, GetLastError());
LogExtMesg( "OdbcBindParameter", l_err_txt, __LINE__, GetLastError());
return FAIL;
}

DumpData(ParameterNumber, (LPCWSTR)ParameterValuePtr, (LPCSTR)lParameterValuePtr);
}
else
{
strcpy((LPSTR)lParameterValuePtr, "");
}

*lpStrLen_or_IndPtr = SQL_NTS;

break;

case UT_PARAM_INPUT:

if (ParameterValuePtr && wcslen((WCHAR *)ParameterValuePtr))
{
WCHAR *l_ptr;

l_ptr = (WCHAR *)ParameterValuePtr;

RTRIMW(l_ptr);

lRetSize = WideCharToMultiByte( 65001,
NULL,
(LPCWSTR)ParameterValuePtr,
-1,
(LPSTR)lParameterValuePtr,
lBufferLength,
NULL,
NULL);

if (!lRetSize)
{
sprintf(l_err_txt, "Failed In Converting From UCS2 To DB type, Column No %d, Data %S : Error Code %d, Text:", pDbproc->WCharBindCtr, ParameterValuePtr, GetLastError());
LogExtMesg( "OdbcBindParameter", l_err_txt, __LINE__, GetLastError());
return FAIL;
}

DumpData(ParameterNumber, (LPCWSTR)ParameterValuePtr, (LPSTR)lParameterValuePtr);

lColSize = lRetSize;

*lpStrLen_or_IndPtr = SQL_NTS;
}
else
{

lColSize = 1;
*lpStrLen_or_IndPtr = SQL_NULL_DATA;
lParameterValuePtr = SQL_NULL_HANDLE;
}
}

pDbproc->WStringBindCtr++;

if( ParameterValuePtr )
{
switch(InputOutputType)
{
case UT_PARAM_INPUT :
LogDebug(pDbproc,"I/P %d is: '%S'", ParameterNumber, ParameterValuePtr);
break;
case UT_PARAM_INPUT_OUTPUT :
LogDebug(pDbproc,"I/O %d is: '%S'", ParameterNumber, ParameterValuePtr);
break;
default :
break;
}
}

break;


...
...
...

case UTBLOBBIND :

lBoundVarType = SQL_C_BINARY;

if ( ParameterType == UTIMAGEBIND )//For IMAGE/BLOB
lDatabaseColumnType = SQL_LONGVARBINARY;
else//For TEXT/LONG RAW
{
if (IS_SYBASE_DB(pDbproc))//For TEXT
lDatabaseColumnType = SQL_LONGVARCHAR;
else//For LONG RAW
lDatabaseColumnType = SQL_LONGVARBINARY;
}

lColSize = ColumnSize;
lBufferLength = ColumnSize;

pDbproc->ParameterValuePtr = (SQLCHAR *)ParameterValuePtr;
pDbproc->ColumnSize = ColumnSize;

if (ParameterValuePtr)
{
lParameterValuePtr = (SQLPOINTER)ParameterValuePtr;
*lpStrLen_or_IndPtr = SQL_LEN_DATA_AT_EXEC(ColumnSize);
}
else
{
lParameterValuePtr = SQL_NULL_HANDLE;
*lpStrLen_or_IndPtr = SQL_NULL_DATA;
}

if( ParameterValuePtr )
{
switch(InputOutputType)
{
case UT_PARAM_INPUT :
LogDebug(pDbproc,"I/P %d is: BLOB/TEXT", ParameterNumber);
break;
case UT_PARAM_INPUT_OUTPUT :
LogDebug(pDbproc,"I/O %d is: BLOB", ParameterNumber);
break;
default :
break;
}
}

break;

default :
LogMesg("OdbcBindParameter", "Incorrect Bind Type passed", __LINE__);
return( FAIL );
}


-------------------------------------------------------------------------------------------------------------------------------------------------------------

sqlbinding with DB


if ( InputOutputType == UT_PARAM_INPUT )
lInputOutputType = SQL_PARAM_INPUT;
else if ( InputOutputType == UT_PARAM_OUTPUT )
lInputOutputType = SQL_PARAM_OUTPUT;
else
lInputOutputType = SQL_PARAM_INPUT_OUTPUT;

if (pDbproc->CurrentStmtType == PROCEDURE_STMT)
{
lRetcode = SQLBindParameter(pDbproc->hStmt,
ParameterNumber,
lInputOutputType,
lBoundVarType,
lDatabaseColumnType,
lColSize,
0,
lParameterValuePtr,
lBufferLength,
lpStrLen_or_IndPtr);
}
else
{
lRetcode = SQLBindParameter(pDbproc->hStmt,
ParameterNumber + 1,
lInputOutputType,
lBoundVarType,
lDatabaseColumnType,
lColSize,
0,
lParameterValuePtr,
lBufferLength,
lpStrLen_or_IndPtr);
}



-------------------------------------------------------------------------------------------------------------------------------------------------------------


Declaration at DB function


CREATE OR REPLACE FUNCTION ap_upd_ins_incomplete_txn
(
var_pi_user_no NUMBER,
var_pi_cod_tran VARCHAR2,
var_pi_id_tran_seq NUMBER,
var_pi_id_tran_seq_old NUMBER,
var_pi_cod_tran_cat CHAR,
var_pi_cod_org_brn NUMBER,
var_pi_cod_tran_stat NUMBER,
var_pi_user_id VARCHAR2,
var_pi_dat_post CHAR,
var_pi_dat_value CHAR,
var_pi_dattim_sys CHAR,
var_pi_id_acct CHAR,
var_pi_cod_tcy NUMBER,
var_pi_cod_acy NUMBER,
var_pi_cod_lcy NUMBER,
var_pi_amt_tcy NUMBER,
var_pi_amt_acy NUMBER,
var_pi_amt_lcy NUMBER,
var_pi_rat_tclcy NUMBER,
var_pi_rat_aclcy NUMBER,
var_pi_cod_issuer NUMBER,
var_pi_txn_xml BLOB
)



-------------------------------------------------------------------------------------------------------------------------------------------------------------




There are 3 tables that gets updated from the XML,
1) In 2 tables the columns are mapped with corresponding tag in XML. Those 2 DB tables are bind through UTSTRINGBING parameter type. In this case the Unicode data retains its properties without any loss in it.
2) Whereas, in 3rd table the whole XML is stored for viewing, printing & similar purpose, in BLOB datatype, and the column of that DB table is bind through UTBLOBBIND parameter type. In this case, only the data supported by Windows Codepage is preserved, rest others are garbaged or transform or unrecognized.



Can you help me find, which part of code is causing the loss of Unicode data.

Thanks & Regards,
Pratik.

My first question would be to verify what this call does:

m_dom.get_modified_xml(l_wptr_txn_xml, l_flg_compress)

Add a simple loop over characters (wchar_t) in l_wptr_txn_xml and write these characters to a temporary file using "%04x". Check with any hex editor, if the code for reversed e is there. Then, after this call:

l_txn_xml_len = WideCharToMultiByte(65001, 0, l_wptr_txn_xml, -1, l_ptr_txn_xml, l_txn_xml_len, NULL, NULL);


add another loop over bytes (char) in l_ptr_txn_xml and write these bytes to a temporary file using "%02x". Check, if the UTF-8 code for reversed e is still there.


-- Sergiusz

Hi,

the code line,

l_txn_xml_len = WideCharToMultiByte(65001, 0, l_wptr_txn_xml, -1, l_ptr_txn_xml, l_txn_xml_len, NULL, NULL);

has been incorporated by me.
I was just checking if it is working fine with UNICODE Codepage reference, as it worked when done with STRINGBIND.
Actual code is,

l_txn_xml_len = WideCharToMultiByte(CP_ACP, 0, l_wptr_txn_xml, -1, l_ptr_txn_xml, l_txn_xml_len, NULL, NULL);

And about the adding of loop over characters and writing it to a temporary file,
its like can u elaborate what kind temporary file and hex editor...???
i mean i have tried printing the XML on a windows temporary location.
But when i open it, the point from where the Unicode characters starts there is no XML in later part.
But the whole XML is stored in BLOB with unicode characters either transformed or unrecognized...!!!


Thanks & Regards,
Pratik.

Any reference to CP_ACP is certainly wrong in your architecture. You use UTF-16 or UTF-8, never the ACP.

Based on the IS_SYBASE macro, I conclude that your application may need to be portable, but from Oracle perspective the current NLS architecture is incorrect. You should not convert from WCHAR to CHAR using WideCharToMultiByte. You should bind directly using SQL_W_CHAR.

Anyway, by the loop I meant simply:

for (i=0;i < l_txn_xml_len, ++i) fprintf(f,"%02x ",(unsigned int)l_ptr_txn_xml\[i\]);

(analogous for wchar_t and l_wptr_txn_xml, using %04x) where f has been previously opened with fopen() in write binary mode and points to a new temporary file on your client workstation. Close the file with fclose() just after the loop.

By a hex editor, I understand any editor that allows showing a file in binary mode, i.e. byte by byte and not character by character, for example WinVi from Raphael Molle.

Looking at the file where reversed e is expected will tell you if the value is correct before it goes to Oracle.


-- Sergiusz

Hi Sergiusz,

Thnx for the detailed explanation. Its just that i am working for the first time on VC front and on top of all first time on Globalization Support.
I completely agree to your point that there is no requirement of conversion WideCharToMultiByte. But, it is not possible to change the architecture.
So, have to manipulate the data in such a way that even architecture remains the same and also finds the unicode support...!!!

Now, i tried to check whether the code for reversed e is there, using the method that u have informed.
In the process i came across a very strange thing... and this is the reason i am posting...

As i stated in the above post, in the whole flow the end point is the XML stored in BLOB datatype,

now when i m checking the BLOB datatype after the completion of transaction, the result seen is as follows,

...><CustShortName>ĞIƏŞ</CustShortName><... ...><AcctTitle>ĞIƏŞ</AcctTitle><... ...><Narrative>ĞIƏŞ</Narrative><...

now when i saving this XML file to windows text(.txt) file, the result is as follows,

...><CustShortName>ĞIƏŞ</CustShortName><... ...><AcctTitle>ĞIƏŞ</AcctTitle><... ...><Narrative>ĞIƏŞ</Narrative><...

which is actually correct...!!!

i even the checked the Hex value of XML in DB and the one i retrieved by saving it(to .txt format)... in both the case code for reversed e was there...!!!

now what inference can we make out of it...!!!
till i understand, i could infer that the hex value of the result is intact in BLOB datatype, the issue is of displaying the UNICODE characters properly...!!!


Thanks & Regards,
Pratik.

## when i m checking the BLOB datatype after the completion of transaction


How do you do this?

From looking at what you pasted into your posting (note, this operation itself may introduce additional conversion/display problem ;-)), I suspect that when looking at BLOB you try to display it contents as ANSI text (while it is UTF-8). When you save the text into a .txt file, Notepad can recognize that the file is UTF-8 and displays the contents correctly. So the issue may be with displaying UTF-8 contents from the BLOB.

Any textual/graphical presentation of results may introduce additional conversion, font problems, 8th-bit stripping and so on. Therefore, I always insist on looking at binary codes of source values to be able to see what values are really there. If the binary contents is expected but the visual appearance on the screen is wrong, we know that the problem is in the display.


-- Sergiusz

i am using PL/SQL Developer tool for all DB related operations...
it can show BLOB datatype in various forms(viz. .txt, .rtf, .hex, etc)...
similarly, i used HEX editor on the Output XML and compared the HEX values, to find them identical...

now that we infer that the issue is (may be) with displaying UTF-8 contents from the BLOB, there comes some more...
issue is not limited to displaying the BLOB content, but also the display at front end...
i mean when accessing (enquiring) the data stored in BLOB from front end, it displays the same form of characters as it shows in BLOB,
though we know (with ref. 2 HEX value) that data has retained its property...

so can u suggest, what can we do to rectify the display issue of BLOB content...
if resolved at one place, will definitely get resolved at other...


Thanks & Regards,
Pratik.

PL/SQL Developer seems to always interpret the contents of BLOBs in the ACP (system code page). It is its right to do so, as BLOB is essentially just a bunch of bytes. Only the Hex format is formally valid for a general BLOB viewer. Any other contents should be interpreted by the dedicated application associated with this data. Of course, PL/SQL Developer could let the user configure the encoding for text/HTML display but this would be an enhancement request for PL/SQL Developer folks. Currently, if you want to see the contents as interpreted in UTF-8, save the file and open it in Notepad, selecting UTF-8 for "Encoding". (You can try the "External" display tab but then you must rely on Notepad to automatically recognize UTF-8, while Notepad is not always able to do this.)

As far as your application is concerned, this is another issue. If what you see on the screen is the same as in PL/SQL Developer, then either:

1) You select BLOB data, pass it directly for display, and the application uses Win32 ANSI API (e.g. it is not compiled with the UNICODE define), or
2) You select BLOB data, convert it using MultiByteToWideChar specifying CP_ACP, instead of 65001, pass it for display, and the application uses Win32 Unicode API.


You should use option 2) after changing CP_ACP to 65001.


-- Sergiusz

Hi,

Thanks for putting in lots of time and efforts in helping me resolve this issue.
The issue has been resolved by doing following changes,

CodePage = CP_UTF8 or 65001
And now i am not using BLOB datatype.
Changing BLOB datatype to NCLOB resolved the issue, with bind type as SQL_C_CHAR

Thanks & Regards,
Pratik.