www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - MS ODBC encoding issue

reply "Sam Hu" <samhudotsamhu gmail.com> writes:
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]
Dec 03 2012
next sibling parent "Nathan M. Swan" <nathanmswan gmail.com> writes:
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
Dec 04 2012
prev sibling next sibling parent "Sam Hu" <samhudotsamhu gmail.com> writes:
On Tuesday, 4 December 2012 at 10:05:16 UTC, Nathan M. Swan wrote:
 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

Appreciated the prompt help! Unfortunately I've still not fixed this yet.Changing to SQL_C_WCHAR and using wchar[] to contain the result value turned out still mess.
Dec 04 2012
prev sibling next sibling parent "Sam Hu" <samhudotsamhu gmail.com> writes:
On Tuesday, 4 December 2012 at 10:05:16 UTC, Nathan M. Swan wrote:
 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

Appreciated the prompt help!Unfortunately I've not fixed the issue yet.Changing to SQL_C_WCHAR and contained the result value by wchar* does not help much.
Dec 04 2012
prev sibling next sibling parent "Regan Heath" <regan netmail.co.nz> writes:
On Wed, 05 Dec 2012 03:29:50 -0000, Sam Hu <samhudotsamhu gmail.com> wrote:

 On Tuesday, 4 December 2012 at 10:05:16 UTC, Nathan M. Swan wrote:
 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

Appreciated the prompt help!Unfortunately I've not fixed the issue yet.Changing to SQL_C_WCHAR and contained the result value by wchar* does not help much.

If you make a complete working (but for the problem) code sample available I'll download it and try it here. I have some experience with ODBC and a working example in C/C++ to compare things with so I should be able to track it down. No promises tho, I am supposed to be working :p R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Dec 05 2012
prev sibling next sibling parent "Sam Hu" <samhudotsamhu gmail.com> writes:
On Wednesday, 5 December 2012 at 11:33:16 UTC, Regan Heath wrote:
 On Wed, 05 Dec 2012 03:29:50 -0000, Sam Hu 
 <samhudotsamhu gmail.com> wrote:

 On Tuesday, 4 December 2012 at 10:05:16 UTC, Nathan M. Swan 
 wrote:
 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

Appreciated the prompt help!Unfortunately I've not fixed the issue yet.Changing to SQL_C_WCHAR and contained the result value by wchar* does not help much.

If you make a complete working (but for the problem) code sample available I'll download it and try it here. I have some experience with ODBC and a working example in C/C++ to compare things with so I should be able to track it down. No promises tho, I am supposed to be working :p R

So much thanks in advance!!! My apologizes for the poor code but I am really expecting somebody help me on the issue. Sorry I did not find proper way to upload attachment here so I paste all the source code below. main code: [code] module odbcutiltest; import std.stdio; import std.string; import std.conv; import odbcutil; int main() { Odbc odbc=new Odbc; //connect ODBC without setting up a DSN does not work at current. //odbc.connect("Driver= {Microsoft Access Driver(*.mdb)};DBQ=C:/Personnal/language/DLang/dbi_7zfromWeb/dbiallsamples/db1.mdb;"); odbc.connect("artistDB","",""); if(!odbc.isOpen) throw new Exception("ODBC connection failed.exit."); auto table=odbc.fetchAll("select * from artists"); foreach(row;table) { foreach(column;row) { writef("%s\t",column); } write("\n"); } writeln("Read table successfully."); writeln("Insert a new record..."); write("Please enter artist ID:"); string id=chomp(readln); write("Please enter artist Name:"); string name=chomp(readln); string sql="insert into artists values("~id~",'"~name~"');"; int changed=odbc.executeNonQuery(sql); writefln("%d row affected.",changed); writeln("Done"); readln; return 0; } [/code] ODBC wrapper: [code] module odbcutil; import std.stdio; import std.string; import std.conv; import std.c.string; import std.array; import std.utf; import win32.sql; import win32.sqlext; import win32.sqltypes; import win32.sqlucode; extern(Windows){SQLRETURN SQLExecDirectW( SQLHSTMT StatementHandle, SQLWCHAR* StatementText, SQLINTEGER TextLength); } class Odbc { private: SQLHANDLE hEnv; SQLHANDLE hDbc; SQLHANDLE hStmt; SQLRETURN retCode; SQLINTEGER retErro; SQLINTEGER row; SQLSMALLINT col; bool bState; char* pszUName; char* pszUPassword; char* pszDSN; 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; } public: this() { bState=false; //row=col=0; retCode=SQLAllocHandle(SQL_HANDLE_ENV,cast(SQLHANDLE)null,&hEnv); if((retCode!=SQL_SUCCESS)&& (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); return; } retCode=SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,cast(SQLPOINTER) SQL_OV_ODBC3,SQL_IS_INTEGER); if((retCode!=SQL_SUCCESS)&& (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); SQLFreeHandle( SQL_HANDLE_DBC, hEnv ); return; } retCode=SQLAllocHandle(SQL_HANDLE_DBC,hEnv,&hDbc); if((retCode!=SQL_SUCCESS)&& (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); SQLFreeHandle( SQL_HANDLE_DBC, hEnv ); return; } //pszDSN=cast(char*)"odbcartists".ptr; } ~this() { close(); } bool connect(string dsn,string username,string passwd) { if(bState==false) { retCode=SQLConnect(hDbc,cast(SQLCHAR*)toStringz(dsn),SQL_NTS,cast(SQLCHAR*) toStringz(username),SQL_NTS,cast(SQLCHAR*)toStringz(passwd),SQL_NTS); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); SQLFreeHandle( SQL_HANDLE_DBC, hDbc ); return false; } retCode=SQLAllocHandle(SQL_HANDLE_STMT,hDbc,&hStmt); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); SQLDisconnect( hDbc ); SQLFreeHandle( SQL_HANDLE_DBC, hDbc); return false; } } bState=true; return true; } // bug:connect ODBC without DSN failed ,but I don't know why.If anybody know about it, // kindly let me know with thanks!!!! bool connect(string connectionString) { SQLCHAR connStrOut[256]; SQLSMALLINT connStrOutLen; if(bState==false) { retCode=SQLDriverConnect(hDbc, null, cast(SQLCHAR*)toStringz(connectionString), SQL_NTS, cast(ubyte*)connStrOut, connStrOut.length, &connStrOutLen, SQL_DRIVER_COMPLETE); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); SQLFreeHandle( SQL_HANDLE_DBC, hDbc ); return false; } retCode=SQLAllocHandle(SQL_HANDLE_STMT,hDbc,&hStmt); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Erro AllocHandle with retCode: %d",retCode)); SQLDisconnect( hDbc ); SQLFreeHandle( SQL_HANDLE_DBC, hDbc); return false; } } bState=true; return true; } /* string escape (string str) { char[] result; size_t count = 0; result.length = str.length * 2; for (size_t i = 0; i < str.length; i++) { switch (str[i]) { case '"': case '\'': case '\\': result[count++] = '\\'; break; default: break; } result[count++] = str[i]; } result.length = count; return std.conv.to!string(result); } */ int executeQuery(const char* pszSql) { if(pszSql is null ) return 0; writefln("hStmt=%s",cast(int)hStmt); 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 -1; } retCode=SQLNumResultCols(hStmt,&col); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Error AllocHandle with retCode: %d",retCode)); return -1; } row=0; while(SQL_NO_DATA!=SQLFetch(hStmt)) { row++; } SQLCancel(hStmt); return rowCount; } int executeQuery(string sql) { return executeQuery(std.utf.toUTFz!(const(char)*)(sql)); //return executeQuery(sql.ptr); } int executeNonQuery(const char* pszSql) { row=0; if(pszSql is null ) return 0; 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 -1; } retCode=SQLRowCount(hStmt,&row); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Error AllocHandle with retCode: %d",retCode)); return -1; } retCode=SQLNumResultCols(hStmt,&col); if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) { throw new Exception(format("Error AllocHandle with retCode: %d",retCode)); return -1; } SQLCancel(hStmt); return row; } int executeNonQuery(string sql) { return executeNonQuery(std.utf.toUTFz!(const(char)*)(sql)); //return executeNonQuery(sql.ptr); } 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]; //dchar* pszBuf; //wchar[] pszBuf; char* pszBuf; SQLINTEGER buflen; //string[] rowData=new string[col+1]; string[] rowData; if(SQLFetch(hStmt)==SQL_NO_DATA) { break; } for(int i=1;i<=col;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]='\000'; SQLGetData(hStmt,cast(ushort)i,SQL_C_CHAR,pszBuf,50,cast(int*)&buflen); //pszBuf[buflen]='\0'; rowData~=to!string(pszBuf); } v~=rowData; row++; } SQLCancel(hStmt); return v; } string[][] fetchAll(string sql) { return fetchAll(sql.ptr); } bool close() { if(bState) { SQLDisconnect(hDbc); SQLFreeHandle(SQL_HANDLE_DBC,hDbc); SQLFreeHandle(SQL_HANDLE_ENV,hEnv); bState=false; } return true; } bool isOpen() { return bState; } int rowCount() { return row; } int colCount() { return col; } } [/code]
Dec 05 2012
prev sibling next sibling parent "Sam Hu" <samhudotsamhu gmail.com> writes:
Known issues:

Under console reading Access table recordsets works fine 
,inserting a new record which contains only English characters 
works fine as well,but inserting new record which contains 
Chinese character will crash;

If calling the fetchAll in a DFL gui form and try to display the 
result value in a Text control,English values works fine but the 
Text control will display nothing when any record (row/field) 
contains Chinese character.

Regards,
Sam
Dec 05 2012
prev sibling next sibling parent "Regan Heath" <regan netmail.co.nz> writes:
On Thu, 06 Dec 2012 01:26:32 -0000, Sam Hu <samhudotsamhu gmail.com> wrote:
 Known issues:

 Under console reading Access table recordsets works fine ,inserting a  
 new record which contains only English characters works fine as well,but  
 inserting new record which contains Chinese character will crash;

 If calling the fetchAll in a DFL gui form and try to display the result  
 value in a Text control,English values works fine but the Text control  
 will display nothing when any record (row/field) contains Chinese  
 character.

Where do the win32.sql etc modules come from? R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Dec 06 2012
prev sibling next sibling parent "Sam Hu" <samhudotsamhu gmail.com> writes:
On Thursday, 6 December 2012 at 16:44:01 UTC, Regan Heath wrote:
 On Thu, 06 Dec 2012 01:26:32 -0000, Sam Hu 
 <samhudotsamhu gmail.com> wrote:
 Known issues:

 Under console reading Access table recordsets works fine 
 ,inserting a new record which contains only English characters 
 works fine as well,but inserting new record which contains 
 Chinese character will crash;

 If calling the fetchAll in a DFL gui form and try to display 
 the result value in a Text control,English values works fine 
 but the Text control will display nothing when any record 
 (row/field) contains Chinese character.

Where do the win32.sql etc modules come from? R

You can find it from below links: http://www.dsource.org/projects/bindings/wiki/WindowsApi https://github.com/AndrejMitrovic/WindowsAPI Regards, Sam
Dec 06 2012
prev sibling parent "Regan Heath" <regan netmail.co.nz> writes:
On Fri, 07 Dec 2012 00:27:57 -0000, Sam Hu <samhudotsamhu gmail.com> wrote:

 On Thursday, 6 December 2012 at 16:44:01 UTC, Regan Heath wrote:
 On Thu, 06 Dec 2012 01:26:32 -0000, Sam Hu <samhudotsamhu gmail.com>  
 wrote:
 Known issues:

 Under console reading Access table recordsets works fine ,inserting a  
 new record which contains only English characters works fine as  
 well,but inserting new record which contains Chinese character will  
 crash;

 If calling the fetchAll in a DFL gui form and try to display the  
 result value in a Text control,English values works fine but the Text  
 control will display nothing when any record (row/field) contains  
 Chinese character.

Where do the win32.sql etc modules come from? R

You can find it from below links: http://www.dsource.org/projects/bindings/wiki/WindowsApi https://github.com/AndrejMitrovic/WindowsAPI

Ahh, of course. Now I'm having linking issues :p I'm using VisualD and I've added odbc32.lib to the right place, but some symbols are still missing - specifically the W versions. I've dumped the symbols in the DMC odbc32.lib and it's missing those symbols. I am guessing I should be using converted M$ libs and I'll do this next, when I get a spare moment (work has been busy lately). R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Dec 10 2012