MS ODBC encoding issue

Regan Heath regan at netmail.co.nz
Wed Jan 23 03:31:56 PST 2013


On Wed, 23 Jan 2013 04:41:11 -0000, Sam Hu <samhudotsamhu at gmail.com> wrote:

> I've tested and the Chinese character issue really fixed!
>
> But I have two more issues here.
> 1.for connect with DSNless string function provided by my original code  
> as below,I can not make it to connect successfully with really database  
> file.Don't now why yours works.

Does the connection function throw?  What is the retCode?

Note; your connect function throws, and the cleanup happens /after/ that..  
which means it doesn't happen at all.  You need to use "scope(failure)" or  
finally or something like that to ensure cleanup happens.

> 2.Inserting new record from windows console faile on Chinese characters  
> but success on English characters.If I enter a Chinese character to feed  
> the new record,the program closed (crashed I think) immedialtey and none  
> row affected in the database.

#1 First thing to check is that your are reading your input in the correct  
encoding.  Are the characters you are reading from the console encoded as  
UTF-8, or perhaps in the native OS encoding/codepage.  If they're native,  
and you've stored them in a "string" then phobos will at some stage  
attempt to validate/use that string and it will throw (like when you  
convert it in SQLExecDirectUTF8 for example).

What you want is to read the input and ensure it is encoded as UTF-8, so  
you can pass it around in a string without issues and convert if/when  
required.  To test if it's correct try converting your input string from  
UTF-8 to UTF-16 and UTF-32 and if it throws, check the exception text for  
clues.


#2 Next thing to check (assuming you've got your input as UTF-8) is that  
you're passing it to SQL as the correct type in the correct encoding.

I see that you are currently using SQLExecDirectW, and converting the  
entire SQL statement from UTF-8 to UTF-16.  Assuming the input was UTF-8,  
this should probably work (suggesting to me that #1 above is the issue  
you're currently having).

However, if you're sure your input is correctly UTF-8 encoded and it's  
still not working then another idea to try is to use SQLBindParameter to  
bind parameters to the statement instead of having their values in the  
statement itself.

For example:

<hStmt opened>

int id;                             <- populate from console
wchar[] name = toUTF16(inputName);  <- inputName is from console

int i = 1;

SQLBindParameter(hStmt, i++, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0,  
0, &id, 0, 0);
SQLBindParameter(hStmt, i++, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR,  
colWidth, 0, value.ptr, 0, 0);

The statement to execute then becomes "insert into artists values(?,?)"  
where each value is replaced by a parameter placeholder '?'.  Note, you do  
not need to enclude strings or dates in '' in the statement, SQL knows the  
type of the parameter because you're telling it in the bind, so additional  
'' are not required.

Using bind for parameters is a good idea because it makes 2 statements  
with different parameter values look the same, and this is good because  
many SQL servers have a statement execution plan cache and for each  
different statement they need to calculate the execution plan, which takes  
time, if they find a matching plan and re-use it, it is faster.

R

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/


More information about the Digitalmars-d-learn mailing list