Issue Details (XML | Word | Printable)

Key: ODBC-138
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Alexander Potapchenko
Reporter: Eric Martin
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
ODBC Driver

SQLExecuteDirect corrupting language string

Created: 10/Feb/12 11:06 PM   Updated: 31/May/12 03:26 PM
Component/s: None
Affects Version/s: 2.0
Fix Version/s: 2.0

File Attachments: 1. Zip Archive Surfliibrary.zip (681 kB)

Image Attachments:

1. Alex.png
(46 kB)

2. Alex2.png
(79 kB)

3. Untitled.png
(204 kB)
Environment:
Win 7 pro 64 Using Microsfot VC2008 with Firebird... Converting Access file over to firebird
Using the ODBC firebird driver...


 Description  « Hide
I have firebird working mostly just fine for converting our access databases over to firebird..

Using a MFC based application that opens up access and firebird and populates Firebird...

I can create tables, insert data, delete data, just fine.

when I try to convert a string over from Italian and use SQLExecDirect(hstmt, SQL, SQL_NTS); It changes all the characters from the italian database ( access ) to "?" the numbers come through fine. I converted everything from a Multibyte character set to UNICODE and it still does it??

I'm not sure if I did something wrong but Like I said it seems to work fine for other languages I have tried?



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Eric Martin added a comment - 10/Feb/12 11:23 PM - edited
I added the access mdb I'm going from and the Firebird database I'm converting too.. Also added a picture of the access and Firebird result with Flame Robin...

Alexander Potapchenko added a comment - 11/Feb/12 07:59 AM
I think it is not bug in the driver, this is problem of your application but you did not attach its code.
You can't insert UNICODE sting into Firebird database with NONE charset at least.
There are russian characters in your MS Access database (for example - 0.1093 D - 4 заходн - Концевая фреза).
In this case your Firebird database can be created with WIN1251 charset and you can insert stings from MS Access into Firebird database without any converting.

Eric Martin added a comment - 11/Feb/12 03:16 PM - edited
Thanks Alexander for such fast response... VERY appreciated!

I have three questions:

1. How do you create the Firebird database with WIN1251 ?
( I have many languages that this database will be used)

2. Does this mean I can go back to Multibyte character set instead of UNICODE? ( do you suggest this )

3. The code I use is below to execute direct SQL calls, see picture Alex.png I put a break point at that point to see that string right before it enters the SQLExecDirect call... it does have the characters in it correctly..


bool CSQLUtilityDlg::executeSQL (SQLWCHAR *SQL,SQLHANDLE hOdbcEnv,SQLHANDLE hDbConn)
{
SQLRETURN sr; //Return code for your ODBC calls
SQLHSTMT hstmt;
bool m_bSuccess=1;
// Allocate new Statement Handle based on previous connection
sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {
char message[4096];
sprintf (message, "Error Allocating Handle: %d\n", sr);
CString astr;
astr.Format(_T("%S"),message);
AfxMessageBox(astr);
m_bSuccess=0;

                                      }

sr = SQLExecDirectW(hstmt, SQL, SQL_NTS);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {
char message[4096];
sprintf (message, "Error in SQLExecDirect. SQL was:\n\n%S\n\n", SQL);
displayODBCError( sr, message, hDbConn);
// OdbcCheckCode(sr,hDbConn,message,SQL_HANDLE_DESC);
m_bSuccess=0;
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

return m_bSuccess;
}

Thanks again...

Alexander Potapchenko added a comment - 11/Feb/12 04:24 PM
1) you can read it for example - http://www.destructor.de/firebird/charsets.htm
2/3) you use unicode version of ODBC function SQLExecDirect[W], but in your first description was SQLExecDirect.
In the function SQLExecDirectW ODBC Driver converts [SQLWCHAR SQL] to a string with multibyte characters and executes SQL.
You can try to use SQLExecDirect in your program too.
Can you give me simple test case with sources for VS for reproducing problem in SQLExecDirectW?

Eric Martin added a comment - 12/Feb/12 06:37 PM
>Give you a simple test case
Sure, let me figure out that charsets and see if that helps and I will also get you a simple case...

Also I used the ODBC record sets to connect to Access not sure if that has any affect on firebird .. it seems not to...

It's odd that there is Russian characters in the Italian database... :o) ( but since I'm russian challenged I wouldn't have known that... )

Eric Martin added a comment - 12/Feb/12 06:54 PM
Another question:

Is there a universal Charset that will accept these languages:

Chinese(Simplifed, traditional ), Finnish, French, German, Italian, Japanese, Korean, Polish, Russian, Spanish, Sedish, English

Alexander Potapchenko added a comment - 12/Feb/12 07:16 PM
>Is there a universal Charset that will accept these languages
UTF8
CHARSET must be UTF8 in the ODBC connection settings also.

>Sure, let me figure out that charsets and see if that helps and I will also get you a simple case...
I have not understood you. If you didn't handle your problem then I want to get a simple project for reproducing a error with corrupting strings.

Eric Martin added a comment - 13/Feb/12 12:58 AM
GOOD NEWS Alexander,

I figured it out with your help...!! THANKS! ( ERROR in Creation of ODBC ) see picture...?

1. I re-created database with UTF8 and tried many ways ... all fail?? ( maybe I did it wrong? )

swprintf(create_db, _T("CREATE DATABASE '%s' user 'SYSDBA' password 'masterkey' DEFAULT CHARACTER SET UTF8"), new_dbname);

BUT registry doesn't set the CHARACTER SET ->UTF8 ( I type in utf8 into registry ...it works)


( See picture! )

Alexander Potapchenko added a comment - 13/Feb/12 06:49 AM
This registry key stores ODBC datasorce settings and it is edited using odbcad32.exe utility.
The real database parameters (CHARSET and others) and ODBC datasorce settings are unrelated directly.

Eric Martin added a comment - 13/Feb/12 06:50 PM
OK,

>This registry key stores ODBC datasorce settings and it is edited using odbcad32.exe utility.
then a few questions:

1. IF so why is it created then when the isc_dsql_execute_immediate is called?

2. When I manually type in UTF8 in that character set it works...!?