December 19, 2012
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.
December 21, 2012
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/
December 24, 2012
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.
December 31, 2012
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/
January 14, 2013
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!
January 14, 2013
> 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/
January 17, 2013
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/
January 23, 2013
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

January 23, 2013
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
January 23, 2013
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/