MS ODBC encoding issue
Sam Hu
samhudotsamhu at gmail.com
Wed Dec 5 17:20:32 PST 2012
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 at 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]
More information about the Digitalmars-d-learn
mailing list