Re: MS ODBC encoding issue

2014-04-12 Thread FrankLike 



Sam


you should put th ODBC code to the github.com ,

let all world to see  your hard work!


Frank

Re: MS ODBC encoding issue

2013-01-23 Thread Regan Heath 

On Wed, 23 Jan 2013 04:09:01 -, Sam Hu samhudotsa...@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/suzymdzjeifnfirtb...@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/

Re: MS ODBC encoding issue

2013-01-23 Thread Regan Heath 

On Wed, 23 Jan 2013 04:41:11 -, Sam Hu samhudotsa...@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/

Re: MS ODBC encoding issue

2013-01-22 Thread Sam Hu 

On Thursday, 17 January 2013 at 18:36:16 UTC, Regan Heath wrote:
On Mon, 14 Jan 2013 10:02:04 -, Regan Heath 
re...@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/suzymdzjeifnfirtb...@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

Re: MS ODBC encoding issue

2013-01-22 Thread Sam Hu 

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

Re: MS ODBC encoding issue

2013-01-17 Thread Regan Heath 
On Mon, 14 Jan 2013 10:02:04 -, Regan Heath re...@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(rDriver={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/

Re: MS ODBC encoding issue

2013-01-14 Thread Regan Heath 
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/

Re: MS ODBC encoding issue

2013-01-13 Thread Sam Hu 

On Monday, 31 December 2012 at 11:01:17 UTC, Regan Heath wrote:
On Mon, 24 Dec 2012 07:18:51 -, Sam Hu 
samhudotsa...@gmail.com wrote:



On Friday, 21 December 2012 at 15:20:39 UTC, Regan Heath wrote:
On Wed, 19 Dec 2012 06:33:16 -, Sam Hu 
samhudotsa...@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(rDriver={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!

Re: MS ODBC encoding issue

2012-12-31 Thread Regan Heath 

On Mon, 24 Dec 2012 07:18:51 -, Sam Hu samhudotsa...@gmail.com wrote:


On Friday, 21 December 2012 at 15:20:39 UTC, Regan Heath wrote:
On Wed, 19 Dec 2012 06:33:16 -, Sam Hu samhudotsa...@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(rDriver={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/

Re: MS ODBC encoding issue

2012-12-23 Thread Sam Hu 

On Friday, 21 December 2012 at 15:20:39 UTC, Regan Heath wrote:
On Wed, 19 Dec 2012 06:33:16 -, Sam Hu 
samhudotsa...@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.

Re: MS ODBC encoding issue

2012-12-21 Thread Regan Heath 

On Wed, 19 Dec 2012 06:33:16 -, Sam Hu samhudotsa...@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/

Re: MS ODBC encoding issue

2012-12-18 Thread Sam Hu 

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.

Re: MS ODBC encoding issue

2012-12-10 Thread Regan Heath 

On Fri, 07 Dec 2012 00:27:57 -, Sam Hu samhudotsa...@gmail.com wrote:


On Thursday, 6 December 2012 at 16:44:01 UTC, Regan Heath wrote:
On Thu, 06 Dec 2012 01:26:32 -, Sam Hu samhudotsa...@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/

Re: MS ODBC encoding issue

2012-12-06 Thread Regan Heath 

On Thu, 06 Dec 2012 01:26:32 -, Sam Hu samhudotsa...@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/

Re: MS ODBC encoding issue

2012-12-06 Thread Sam Hu 

On Thursday, 6 December 2012 at 16:44:01 UTC, Regan Heath wrote:
On Thu, 06 Dec 2012 01:26:32 -, Sam Hu 
samhudotsa...@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

Re: MS ODBC encoding issue

2012-12-05 Thread Regan Heath 

On Wed, 05 Dec 2012 03:29:50 -, Sam Hu samhudotsa...@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/

Re: MS ODBC encoding issue

2012-12-05 Thread Sam Hu 

On Wednesday, 5 December 2012 at 11:33:16 UTC, Regan Heath wrote:
On Wed, 05 Dec 2012 03:29:50 -, Sam Hu 
samhudotsa...@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));

Re: MS ODBC encoding issue

2012-12-05 Thread Sam Hu 

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

MS ODBC encoding issue

2012-12-04 Thread Sam Hu 

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]

Re: MS ODBC encoding issue

2012-12-04 Thread Nathan M. Swan 

On Tuesday, 4 December 2012 at 07:59:40 UTC, Sam Hu wrote:

Greetings!

Any help would be much appreicated in advance as I've really 
struggled for quite long time!


I wrote a class wrapper for MS ODBC Access database.When I try 
to run query on an Access database file,all fields contains 
English character are fine with the result,but for those Asian 
characters like Chinese,the result shows blank in DFL gui form 
and shows mess (unrecognizable under console).I think maybe the 
problem is in the fetchAll function which I provided as below 
together with the other main parts of the class:

Client code (DFL form):
[code]
protected void onReadClick(Object sender,EventArgs e)
{
Odbc odbc=new Odbc;
odbc.connect(artistdb,,);
if(!odbc.isOpen)
{
throw new Exception(Failed to connect to ODBC);
return;
}
		auto record=odbc.fetchAll(select * from artists where 
artistid=~txtSearch.text~;);

txtID.text=to!string(record[0][0]);
txtName.text=to!string(record[0][1]);
odbc.close;
}
[/code]

ODBC wrapper class:
[code]
SQLRETURN SQLExecDirectUTF8(SQLHSTMT stmt,string 
text,SQLINTEGER tl)

{
   SQLRETURN retcode;
   //uint16* utf16=UTF8toUTF16(text,null);
   
retcode=SQLExecDirectW(stmt,cast(SQLWCHAR*)toUTF16z(text),tl);


   return retcode;

}
string[][]  fetchAll(const char* pszSql)
{
string[][] v;

if(pszSql is null )
   return null;
retCode=SQLExecDirectUTF8(hStmt,to!string(pszSql),SQL_NTS);
if((retCode != SQL_SUCCESS)  (retCode != 
SQL_SUCCESS_WITH_INFO))

{
   throw new Exception(format(Error AllocHandle with 
retCode: %d,retCode));

   return null;
}

retCode=SQLNumResultCols(hStmt,col);
if((retCode != SQL_SUCCESS)  (retCode != 
SQL_SUCCESS_WITH_INFO))

{
   throw new Exception(format(Error AllocHandle with 
retCode: %d,retCode));

   return null;
}
row=0;
SQLINTEGER colLen = 0;
SQLSMALLINT buf_len = 0;
SQLINTEGER colType = 0;

while(true)
{
   char sz_buf[256];
   char* pszBuf;
   SQLINTEGER  buflen;
   string[] rowData=new string[col+1];

   if(SQLFetch(hStmt)==SQL_NO_DATA)
   {
   break;
   }
   for(int i=1;i=colCount;i++)
   {
   SQLColAttribute(hStmt, cast(ushort)i, SQL_DESC_NAME, 
sz_buf.ptr, 256, buf_len, cast(void*)0);
   SQLColAttribute(hStmt, cast(ushort)i, SQL_DESC_TYPE, 
cast(void*)0, 0, cast(short*)0, colType);
   SQLColAttribute(hStmt, cast(ushort)i, 
SQL_DESC_LENGTH, null, 0, cast(short*)0, colLen);

   pszBuf=cast(char*)(new char[colLen+1]);
   //pszBuf[0]='\0';
   
SQLGetData(hStmt,cast(ushort)i,SQL_C_CHAR,pszBuf,50,cast(int*)buflen);

   pszBuf[buflen]='\000';
   rowData[i-1]=to!string(pszBuf);


   }
   v~=rowData;
   row++;

}
SQLCancel(hStmt);
return v;
}
string[][] fetchAll(string sql)
{

return fetchAll(sql.ptr);
}
[/code]


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


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


NMS

Re: MS ODBC encoding issue

2012-12-04 Thread Sam Hu 

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.

Re: MS ODBC encoding issue

2012-12-04 Thread Sam Hu 

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.