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
parent reply "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
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 parent reply "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
parent reply "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
parent reply "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
parent reply "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
next sibling parent reply "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
parent reply "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
parent reply "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
parent reply "Sam Hu" <samhudotsamhu gmail.com> writes:
On Monday, 10 December 2012 at 14:43:08 UTC, Regan Heath wrote:

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.

R

I have such extra lib files to link successfully but I don't find any way to upload here.Sorry.
Dec 18 2012
parent reply "Regan Heath" <regan netmail.co.nz> writes:
On Wed, 19 Dec 2012 06:33:16 -0000, Sam Hu <samhudotsamhu gmail.com> wrote:

 On Monday, 10 December 2012 at 14:43:08 UTC, Regan Heath wrote:

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.

R

I have such extra lib files to link successfully but I don't find any way to upload here.Sorry.

NP, I am up and running. I can run the test app and see my existing rows, plus insert new ones. I got it working with a connection string directly to an access database file on disk without needing a DSN, not sure why you were having trouble with that. So, what sort of data do I need to add, which will cause the issues you were originally having :) R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Dec 21 2012
parent reply "Sam Hu" <samhudotsamhu gmail.com> writes:
On Friday, 21 December 2012 at 15:20:39 UTC, Regan Heath wrote:
 On Wed, 19 Dec 2012 06:33:16 -0000, Sam Hu 
 <samhudotsamhu gmail.com> wrote:

 On Monday, 10 December 2012 at 14:43:08 UTC, Regan Heath wrote:

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.

R

I have such extra lib files to link successfully but I don't find any way to upload here.Sorry.

NP, I am up and running. I can run the test app and see my existing rows, plus insert new ones. I got it working with a connection string directly to an access database file on disk without needing a DSN, not sure why you were having trouble with that. So, what sort of data do I need to add, which will cause the issues you were originally having :) R

Really excited to hear that! As I said,field contains Chinese characters will produce error during read from and write back to database file; and can't work out a DSNless connection function. Would be much appreciated if you would like to share your complete code here.
Dec 23 2012
parent reply "Regan Heath" <regan netmail.co.nz> writes:
On Mon, 24 Dec 2012 07:18:51 -0000, Sam Hu <samhudotsamhu gmail.com> wrote:

 On Friday, 21 December 2012 at 15:20:39 UTC, Regan Heath wrote:
 On Wed, 19 Dec 2012 06:33:16 -0000, Sam Hu <samhudotsamhu gmail.com>  
 wrote:

 On Monday, 10 December 2012 at 14:43:08 UTC, Regan Heath wrote:

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.

R

I have such extra lib files to link successfully but I don't find any way to upload here.Sorry.

NP, I am up and running. I can run the test app and see my existing rows, plus insert new ones. I got it working with a connection string directly to an access database file on disk without needing a DSN, not sure why you were having trouble with that. So, what sort of data do I need to add, which will cause the issues you were originally having :) R

Really excited to hear that! As I said,field contains Chinese characters will produce error during read from and write back to database file;

Can you save some of these characters in a text file, as UTF-8, and zip that and upload/attach it here for me to try. I want to make sure I'm testing the exact same data as you are. I suspect the issue you're having is related to the encoding of the strings. D expects/uses UTF-8, but the database will be configured with a specific locale/encoding for the columns in question. So, can you export/describe your database table to me, columns, and locales/encodings etc so I can compare them to mine.
 and can't work out a DSNless connection function.

 Would be much appreciated if you would like to share your complete code  
 here.

At present I'm simply using your code, with only very minor changes to the odbcutiltest.d file, like the connection string for example: odbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Development\D\src\odbcutiltest\test.accdb;"); And the table name "artists" (IIRC) plus I have 3 columns, so.. write("Please enter artist ID:"); string id=chomp(readln); write("Please enter artist Name:"); string name=chomp(readln); write("Please enter artist Age:"); string age=chomp(readln); string sql="insert into artists values("~id~",'"~name~"',"~age~");"; int changed=odbc.executeNonQuery(sql); writefln("%d row affected.",changed); R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Dec 31 2012
parent reply "Sam Hu" <samhudotsamhu gmail.com> writes:
On Monday, 31 December 2012 at 11:01:17 UTC, Regan Heath wrote:
 On Mon, 24 Dec 2012 07:18:51 -0000, Sam Hu 
 <samhudotsamhu gmail.com> wrote:

 On Friday, 21 December 2012 at 15:20:39 UTC, Regan Heath wrote:
 On Wed, 19 Dec 2012 06:33:16 -0000, Sam Hu 
 <samhudotsamhu gmail.com> wrote:

 On Monday, 10 December 2012 at 14:43:08 UTC, Regan Heath 
 wrote:

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.

R

I have such extra lib files to link successfully but I don't find any way to upload here.Sorry.

NP, I am up and running. I can run the test app and see my existing rows, plus insert new ones. I got it working with a connection string directly to an access database file on disk without needing a DSN, not sure why you were having trouble with that. So, what sort of data do I need to add, which will cause the issues you were originally having :) R

Really excited to hear that! As I said,field contains Chinese characters will produce error during read from and write back to database file;

Can you save some of these characters in a text file, as UTF-8, and zip that and upload/attach it here for me to try. I want to make sure I'm testing the exact same data as you are. I suspect the issue you're having is related to the encoding of the strings. D expects/uses UTF-8, but the database will be configured with a specific locale/encoding for the columns in question. So, can you export/describe your database table to me, columns, and locales/encodings etc so I can compare them to mine.
 and can't work out a DSNless connection function.

 Would be much appreciated if you would like to share your 
 complete code here.

At present I'm simply using your code, with only very minor changes to the odbcutiltest.d file, like the connection string for example: odbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Development\D\src\odbcutiltest\test.accdb;"); And the table name "artists" (IIRC) plus I have 3 columns, so.. write("Please enter artist ID:"); string id=chomp(readln); write("Please enter artist Name:"); string name=chomp(readln); write("Please enter artist Age:"); string age=chomp(readln); string sql="insert into artists values("~id~",'"~name~"',"~age~");"; int changed=odbc.executeNonQuery(sql); writefln("%d row affected.",changed); R

I'm more than happy to upload the database file here,but I can't find how to.May I have your mail address?Appreciated for all the help!
Jan 13 2013
parent reply "Regan Heath" <regan netmail.co.nz> writes:
 I'm more than happy to upload the database file here,but I can't find  
 how to.May I have your mail address?Appreciated for all the help!

My email address in the from is valid: regan at netmail dot co dot nz -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Jan 14 2013
parent reply "Regan Heath" <regan netmail.co.nz> writes:
On Mon, 14 Jan 2013 10:02:04 -0000, Regan Heath <regan netmail.co.nz>  
wrote:

 I'm more than happy to upload the database file here,but I can't find  
 how to.May I have your mail address?Appreciated for all the help!

My email address in the from is valid: regan at netmail dot co dot nz

Ok, solved the issue I think. 1. I can connect to your database file with a connection string like.. odbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=<PATHGOESHERE>\db1.mdb;"); Just replace <PATHGOESHERE> with the real path to the file, using single \ characters for path separators. 2. In fetchAll, I have made the following changes: while(true) { char sz_buf[256]; -> wchar[] pszBuf; SQLINTEGER buflen; string[] rowData; -> uint uintVal; 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); -> switch(colType) -> { -> case SQL_INTEGER: -> SQLGetData(hStmt, cast(ushort)i, SQL_C_ULONG, &uintVal, uintVal.sizeof, cast(int*)&buflen); -> rowData ~= to!string(uintVal); -> break; -> case SQL_VARCHAR: -> pszBuf = new wchar[colLen]; -> SQLGetData(hStmt, cast(ushort)i, SQL_C_WCHAR, pszBuf.ptr, pszBuf.length, cast(int*)&buflen); -> pszBuf.length = buflen; -> rowData ~= toUTF8(pszBuf); -> break; -> default: -> break; -> } } v~=rowData; row++; } The key here is that when we ask for the VARCHAR data, we ask for it as WCHAR (meaning UTF-16 or more likely UCS-2 a subset of UTF-16). We then have to convert it to UTF-8 using std.utf.toUTF8() Your original code was asking for it as SQL_C_CHAR, and the odbc layer knows it cannot represent the Chinese characters in ASCII, so it was returning a '?' for each of them. Now I can see (in Visual-D debugger) the Chinese characters in the rowData, but I can't get it to display correctly on my windows console.. can someone remind me how to do that? R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Jan 17 2013
parent reply "Sam Hu" <samhudotsamhu gmail.com> writes:
On Thursday, 17 January 2013 at 18:36:16 UTC, Regan Heath wrote:
 On Mon, 14 Jan 2013 10:02:04 -0000, Regan Heath 
 <regan netmail.co.nz> wrote:
 Ok, solved the issue I think.

Appreciatd all the help and am very excited that it finaly works,WOW!
 Now I can see (in Visual-D debugger) the Chinese characters in 
 the rowData, but I can't get it to display correctly on my 
 windows console..  can someone remind me how to do that?

Before I am deep into the code later sometime as end of the Chinese New Year I am crazy busy on my job,for windows console show Chinese character issue,I've solved and posted on the forum before.Please refer to below link: http://forum.dlang.org/thread/suzymdzjeifnfirtbnrc dfeed.kimsufi.thecybershadow.net#post-suzymdzjeifnfirtbnrc:40dfeed.kimsufi.thecybershadow.net You can see when ppl asked Chinese character (with Windows Console) such questions in the forum,rarely ppl will answer,strange ^_^ Regards, Sam
Jan 22 2013
next sibling parent reply "Sam Hu" <samhudotsamhu gmail.com> writes:
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.

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;
		
	}

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.

Any tips would be appreciated.

Regards,
Sam
Jan 22 2013
parent "Regan Heath" <regan netmail.co.nz> writes:
On Wed, 23 Jan 2013 04:41:11 -0000, Sam Hu <samhudotsamhu 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/
Jan 23 2013
prev sibling parent "Regan Heath" <regan netmail.co.nz> writes:
On Wed, 23 Jan 2013 04:09:01 -0000, Sam Hu <samhudotsamhu gmail.com> wrote:

 for windows console show Chinese character issue,I've solved and posted  
 on the forum before.Please refer to below link:
 http://forum.dlang.org/thread/suzymdzjeifnfirtbnrc dfeed.kimsufi.thecybershadow.net#post-suzymdzjeifnfirtbnrc:40dfeed.kimsufi.thecybershadow.net

I tried all that, still doesn't work for me. I suspect I don't have the Chinese Language pack installed, and I don't seem to be able to install it (I have Win 7 Pro here at work). R -- Using Opera's revolutionary email client: http://www.opera.com/mail/
Jan 23 2013
prev sibling parent " FrankLike" <1150015857 qq.com> writes:
 Sam

you should put th ODBC code to the github.com , let all world to see your hard work! Frank
Apr 12 2014