MS ODBC encoding issue

[Nathan M. Swan]

On Tuesday, 4 December 2012 at 07:59:40 UTC, Sam Hu wrote:
> Greetings!
>
> Any help would be much appreicated in advance as I've really 
> struggled for quite long time!
>
> I wrote a class wrapper for MS ODBC Access database.When I try 
> to run query on an Access database file,all fields contains 
> English character are fine with the result,but for those Asian 
> characters like Chinese,the result shows blank in DFL gui form 
> and shows mess (unrecognizable under console).I think maybe the 
> problem is in the fetchAll function which I provided as below 
> together with the other main parts of the class:
> Client code (DFL form):
> [code]
> protected void onReadClick(Object sender,EventArgs e)
> {
> 		Odbc odbc=new Odbc;
> 		odbc.connect("artistdb","","");
> 		if(!odbc.isOpen)
> 		{
> 			throw new Exception("Failed to connect to ODBC");
> 			return;
>                 }
> 		auto record=odbc.fetchAll("select * from artists where 
> artistid="~txtSearch.text~";");
> 		txtID.text=to!string(record[0][0]);
> 		txtName.text=to!string(record[0][1]);
> 		odbc.close;
> 	}
> [/code]
>
> ODBC wrapper class:
> [code]
> SQLRETURN SQLExecDirectUTF8(SQLHSTMT stmt,string 
> text,SQLINTEGER tl)
> {
>        SQLRETURN retcode;
>        //uint16* utf16=UTF8toUTF16(text,null);
>        
> retcode=SQLExecDirectW(stmt,cast(SQLWCHAR*)toUTF16z(text),tl);
>
>        return retcode;
>
> }
> string[][]  fetchAll(const char* pszSql)
> {
>     string[][] v;
>
>     if(pszSql is null )
>        return null;
>     retCode=SQLExecDirectUTF8(hStmt,to!string(pszSql),SQL_NTS);
>     if((retCode != SQL_SUCCESS) && (retCode != 
> SQL_SUCCESS_WITH_INFO))
>     {
>        throw new Exception(format("Error AllocHandle with 
> retCode: %d",retCode));
>        return null;
>     }
> 	
>     retCode=SQLNumResultCols(hStmt,&col);
>     if((retCode != SQL_SUCCESS) && (retCode != 
> SQL_SUCCESS_WITH_INFO))
>     {
>        throw new Exception(format("Error AllocHandle with 
> retCode: %d",retCode));
>        return null;
>     }
>     row=0;
>     SQLINTEGER colLen = 0;
>     SQLSMALLINT buf_len = 0;
>     SQLINTEGER colType = 0;
>
>     while(true)
>     {
>        char sz_buf[256];
>        char* pszBuf;
>        SQLINTEGER  buflen;
>        string[] rowData=new string[col+1];
>
>        if(SQLFetch(hStmt)==SQL_NO_DATA)
>        {
>            break;
>        }
>        for(int i=1;i<=colCount;i++)
>        {
>            SQLColAttribute(hStmt, cast(ushort)i, SQL_DESC_NAME, 
> sz_buf.ptr, 256, &buf_len, cast(void*)0);
>            SQLColAttribute(hStmt, cast(ushort)i, SQL_DESC_TYPE, 
> cast(void*)0, 0, cast(short*)0, &colType);
>            SQLColAttribute(hStmt, cast(ushort)i, 
> SQL_DESC_LENGTH, null, 0, cast(short*)0, &colLen);
>            pszBuf=cast(char*)(new char[colLen+1]);
>            //pszBuf[0]='\0';
>            
> SQLGetData(hStmt,cast(ushort)i,SQL_C_CHAR,pszBuf,50,cast(int*)&buflen);
>            pszBuf[buflen]='\000';
>            rowData[i-1]=to!string(pszBuf);
>
>
>        }
>        v~=rowData;
>        row++;
>
>     }
>     SQLCancel(hStmt);
>     return v;
> }
> string[][] fetchAll(string sql)
> {
> 	
> 	return fetchAll(sql.ptr);
> }
> [/code]

I've never used ODBC before, but a quick scan of the MSDN docs 
suggests that you should use SQL_C_WCHAR instead, maybe using 
some D wstring functions too.

BTW, convert sql.ptr -> std.string.toStringz(sql); this is good 
practice, though I'm not sure it's your problem.

NMS