MS ODBC for DMD 2.053

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

所需的sql头文件来自D bindings--win32包。
已知问题：不建立DSN直接用字符串连接还不行，不知道是什么原因，个人XP下和公司WIN7下都试过，看来是非权限问题，不知API调用上有什么问题。有人知道的话，麻烦告诉俺，谢谢了。
类封装只有一个文件odbcutil.d,先看一段客户代码：
odbcutiltest.d

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

odbcutil.d

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

评论

2 楼 flythink 2011-05-21  

oledb是不是更容易弄一些? 纯猜测

1 楼 hqs7636 2011-05-20  

8错，继续完善一下。。。