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.



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.



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,  

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