Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQLExecuteDirect corrupting language string [ODBC138] #135

Closed
firebird-automations opened this issue Feb 11, 2012 · 17 comments
Closed

SQLExecuteDirect corrupting language string [ODBC138] #135

firebird-automations opened this issue Feb 11, 2012 · 17 comments

Comments

@firebird-automations
Copy link

Submitted by: Eric Martin (ericm)

Attachments:
Surfliibrary.zip
Untitled.png
Alex.png
Alex2.png

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?

@firebird-automations
Copy link
Author

Modified by: Eric Martin (ericm)

Attachment: Surfliibrary.zip [ 12098 ]

description: 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?

I have a file I can send and access file I can send and pictures but your JIRA doesn't allow for me to add those???

=>

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?

@firebird-automations
Copy link
Author

Commented by: Eric Martin (ericm)

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...

@firebird-automations
Copy link
Author

Modified by: Eric Martin (ericm)

Attachment: Untitled.png [ 12099 ]

@firebird-automations
Copy link
Author

Commented by: @alexpotapchenko

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.

@firebird-automations
Copy link
Author

Modified by: @alexpotapchenko

priority: Critical [ 2 ] => Major [ 3 ]

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Author

Modified by: Eric Martin (ericm)

Attachment: Alex.png [ 12101 ]

@firebird-automations
Copy link
Author

Commented by: Eric Martin (ericm)

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...

@firebird-automations
Copy link
Author

Commented by: @alexpotapchenko

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?

@firebird-automations
Copy link
Author

Commented by: Eric Martin (ericm)

>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... )

@firebird-automations
Copy link
Author

Commented by: Eric Martin (ericm)

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

@firebird-automations
Copy link
Author

Commented by: @alexpotapchenko

>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.

@firebird-automations
Copy link
Author

Commented by: Eric Martin (ericm)

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! )

@firebird-automations
Copy link
Author

Modified by: Eric Martin (ericm)

Attachment: Alex2.png [ 12102 ]

@firebird-automations
Copy link
Author

Commented by: @alexpotapchenko

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.

@firebird-automations
Copy link
Author

Commented by: Eric Martin (ericm)

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...!?

@firebird-automations
Copy link
Author

Modified by: @alexpotapchenko

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

Fix Version: 2.0 [ 10044 ]

@firebird-automations
Copy link
Author

Modified by: @alexpotapchenko

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants