MS ODBC for DMD 2.053

东拼西凑，终于在dmd2.053下成功连接上了ODBC 数据库（ACCESS)。代码还很不完善，慢慢再补。参数化查询函数（如bind)还没有，通过连接字符串和变量来组成查询语句进行查询是可以的，中文以及字段值为转义字符嵌套的字符串也支持。

所需的sql头文件来自D bindings--win32包。
已知问题：不建立DSN直接用字符串连接还不行，不知道是什么原因，个人XP下和公司WIN7下都试过，看来是非权限问题，不知API调用上有什么问题。有人知道的话，麻烦告诉俺，谢谢了。
类封装只有一个文件odbcutil.d,先看一段客户代码：
odbcutiltest.d
[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;");
//dsn :odbcartist;table :artists.mdb;user id="",passwd=""
odbc.connect("odbcartist","","");
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]
odbcutil.d
[code]
//Orignial c++ implementation DBUtil.h
//By 小E QQ592646022

//ported by Sam Hu
//at Donguan,2011-5-20 15:04
module odbcutil;

import std.stdio;
import std.string;
import std.conv;
import std.c.string;
import std.array;

import win32.sql;
import win32.sqlext;
import win32.sqltypes;


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




}
~this()
{
close();
}

bool connect(string dsn,string username,string passwd)
{
if(bState==false)
{
retCode=SQLConnect(hDbc,cast(SQLCHAR*)dsn.ptr,SQL_NTS,cast(SQLCHAR*) username.ptr,SQL_NTS,cast(SQLCHAR*)passwd.ptr,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*)connectionString.ptr, 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=SQLExecDirect(hStmt,cast(SQLCHAR*)pszSql,SQL_NTS);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))
{

throw new Exception(format("Erro AllocHandle with retCode: %d",retCode));
return -1;
}

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

throw new Exception(format("Erro 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(sql.ptr);
}
int executeNonQuery(const char* pszSql)
{
row=0;
if(pszSql is null )
return 0;

retCode=SQLExecDirect(hStmt,cast(SQLCHAR*)pszSql,SQL_NTS);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))
{

throw new Exception(format("Erro AllocHandle with retCode: %d",retCode));
return -1;
}
retCode=SQLRowCount(hStmt,&row);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))
{
throw new Exception(format("Erro AllocHandle with retCode: %d",retCode));
return -1;
}
retCode=SQLNumResultCols(hStmt,&col);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))
{
throw new Exception(format("Erro AllocHandle with retCode: %d",retCode));
return -1;
}

SQLCancel(hStmt);
return row;
}
int executeNonQuery(string sql)
{
return executeNonQuery(sql.ptr);
}
string[][] fetchAll(const char* pszSql)
{
string[][] v;
if(pszSql is null )
return null;
retCode=SQLExecDirect(hStmt,cast(SQLCHAR*)pszSql,SQL_NTS);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))
{
throw new Exception(format("Erro AllocHandle with retCode: %d",retCode));
return null;
}

retCode=SQLNumResultCols(hStmt,&col);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))
{
throw new Exception(format("Erro 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=(new char[colLen+1]).ptr;
pszBuf[0]='\0';
SQLGetData(hStmt,cast(ushort)i,SQL_C_CHAR,pszBuf,50,cast(int*)&buflen);
rowData[i-1]=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]