A lot of people want to use D,but they only know MS SQL Server,what will help them to Learn D?

FrankLike 1150015857 at qq.com
Mon Apr 14 10:13:55 PDT 2014


> My advice - use ODBC, it is the fastest way you may connect to 
> the SQL server, and you already have everything you need for 
> that. :)
>
> Regards

I have test the d\dmd2\windows\lib\odbc32.lib,the size is 4.5kb,
I test it by test.d(build :dmd test.d)
but find the error:
Error 42:Symbol Undefined _SQLFreeHandle at 8
Error 42:Symbol Undefined _SQLSetEnvAttr at 16
Error 42:Symbol Undefined _SQLAllocHandle at 12
Error 42:Symbol Undefined _SQLGetDiagRec at 32
-- errorlevel 4

build mssql.lib :dmd -lib mssql.d database.d -Itrunk/win32/sql.d 
-Itrunk/win32/sqlext.d -IODBC32.lib -Iwin32.lib
build test.exe  :dmd test.d
-----------------------------module test.d--------------------
module test;
import std.stdio;
import arsd.mssql;
pragma(lib,"mssql");
void main() {
	try{
	//auto db = new MsSql("Driver={SQL 
Server};Server=<host>[\\<optional-instance-name>]>;Database=dbtest;Trusted_Connection=Yes");
	auto db = new MsSql("Driver={SQL Server Native Client 
10.0};Server=127.0.0.1;Database=test;Trusted_Connection=Yes");

	//db.query("INSERT INTO users (id, name) values (30, 'hello 
mang')");
        db.query("SELECT top 10 * FROM testa");
	
	}
	catch(Exception e){
		auto f = new File("err.text","w");
		scope(exit)f.close();
		f.writeln(e.info);
		
	}
}
--------------------------------------------module 
mssql.d-----------

// NOTE: I haven't even tried to use this for a test yet!
// It's probably godawful, if it works at all.

module arsd.mssql;
pragma(lib, "odbc32");

public import arsd.database;

import std.string;
import std.exception;

import win32.sql;
import win32.sqlext;

class MsSql : Database {
	// dbname = name  is probably the most common connection string
	this(string connectionString) {
		SQLAllocHandle(SQL_HANDLE_ENV, cast(void*)SQL_NULL_HANDLE, 
&env);
		enforce(env !is null);
		scope(failure)
			SQLFreeHandle(SQL_HANDLE_ENV, env);
		SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, cast(void *) 
SQL_OV_ODBC3, 0);
		SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
		scope(failure)
			SQLFreeHandle(SQL_HANDLE_DBC, conn);
		enforce(conn !is null);

		auto ret = SQLDriverConnect(
			conn, null, cast(ubyte*)connectionString.ptr, SQL_NTS,
			null, 0, null,
			SQL_DRIVER_NOPROMPT );

		if ((ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_SUCCESS))
			throw new DatabaseException("Unable to connect to ODBC object: 
" ~ getSQLError(SQL_HANDLE_DBC, conn)); // FIXME: print error

		//query("SET NAMES 'utf8'"); // D does everything with utf8
	}

	~this() {
		SQLDisconnect(conn);
		SQLFreeHandle(SQL_HANDLE_DBC, conn);
		SQLFreeHandle(SQL_HANDLE_ENV, env);
	}

	override void startTransaction() {
		query("START TRANSACTION");
	}

	ResultSet queryImpl(string sql, Variant[] args...) {
		sql = escapedVariants(this, sql, args);

		// this is passed to MsSqlResult to control
		SQLHSTMT statement;
		auto returned = SQLAllocHandle(SQL_HANDLE_STMT, conn, 
&statement);

		enforce(returned == SQL_SUCCESS);

		returned = SQLExecDirect(statement, cast(ubyte*)sql.ptr, 
SQL_NTS);
		if(returned != SQL_SUCCESS)
			throw new DatabaseException(error());

		return new MsSqlResult(statement);
	}

	string escape(string sqlData) { // FIXME
		return ""; //FIX ME
		//return ret.replace("'", "''");
	}


	string error() {
		return null; // FIXME
	}

	private:
		SQLHENV env;
		SQLHDBC conn;
}

class MsSqlResult : ResultSet {
	// name for associative array to result index
	int getFieldIndex(string field) {
		if(mapping is null)
			makeFieldMapping();
		if (field !in mapping)
			return -1;
		return mapping[field];
	}


	string[] fieldNames() {
		if(mapping is null)
			makeFieldMapping();
		return columnNames;
	}

	// this is a range that can offer other ranges to access it
	bool empty() {
		return isEmpty;
	}

	Row front() {
		return row;
	}

	void popFront() {
		if(!isEmpty)
			fetchNext;
	}

	int length()
	{
		return 1; //FIX ME
	}
	
	this(SQLHSTMT statement) {
		this.statement = statement;

		SQLSMALLINT info;
		SQLNumResultCols(statement, &info);
		numFields = info;

		fetchNext();
	}

	~this() {
		SQLFreeHandle(SQL_HANDLE_STMT, statement);
	}

	private:
		SQLHSTMT statement;
		int[string] mapping;
		string[] columnNames;
		int numFields;

		bool isEmpty;

		Row row;

		void fetchNext() {
			if(isEmpty)
				return;

			if(SQLFetch(statement) == SQL_SUCCESS) {
				Row r;
				r.resultSet = this;
				string[] row;

				SQLLEN ptr;

				for(int i = 0; i < numFields; i++) {
					string a;

					more:
				        SQLCHAR buf[255];
					if(SQLGetData(statement, cast(ushort)(i+1), SQL_CHAR, 
buf.ptr, 255, &ptr) != SQL_SUCCESS)
						throw new DatabaseException("get data: " ~ 
getSQLError(SQL_HANDLE_STMT, statement));

					assert(ptr != SQL_NO_TOTAL);
					if(ptr == SQL_NULL_DATA)
						a = null;
					else {
						a ~= cast(string) buf[0 .. ptr > 255 ? 255 : ptr].idup;
						ptr -= ptr > 255 ? 255 : ptr;
						if(ptr)
							goto more;
					}
					row ~= a;
				}

				r.row = row;
				this.row = r;
			} else {
				isEmpty = true;
			}
		}

		void makeFieldMapping() {
			for(int i = 0; i < numFields; i++) {
				SQLSMALLINT len;
				SQLCHAR[255] buf;
				auto ret = SQLDescribeCol(statement,
					cast(ushort)(i+1),
					cast(ubyte*)buf.ptr,
					255,
					&len,
					null, null, null, null);
				if (ret != SQL_SUCCESS)
					throw new DatabaseException("Field mapping error: " ~ 
getSQLError(SQL_HANDLE_STMT, statement));
				
				string a = cast(string) buf[0 .. len].idup;

				columnNames ~= a;
				mapping[a] = i;
			}

		}
}

private string getSQLError(short handletype, SQLHANDLE handle)
{
	char sqlstate[32];
	char message[256];
	SQLINTEGER nativeerror=0;
	SQLSMALLINT textlen=0;			
	auto ret = SQLGetDiagRec(handletype, handle, 1,
			cast(ubyte*)sqlstate.ptr,
			cast(int*)&nativeerror,
			cast(ubyte*)message.ptr,
			256,
			&textlen);

	return message.idup;
}

/*
import std.stdio;
void main() {
	//auto db = new MsSql("Driver={SQL 
Server};Server=<host>[\\<optional-instance-name>]>;Database=dbtest;Trusted_Connection=Yes");
	auto db = new MsSql("Driver={SQL Server Native Client 
10.0};Server=<host>[\\<optional-instance-name>];Database=dbtest;Trusted_Connection=Yes")

	db.query("INSERT INTO users (id, name) values (30, 'hello 
mang')");

	foreach(line; db.query("SELECT * FROM users")) {
		writeln(line[0], line["name"]);
	}
}
*/

---------------------------------------------module database.d's 
part-------------------------------
module arsd.database;

public import std.variant;
import std.string;

import core.vararg;

interface Database {
	/// Actually implements the query for the database. The query() 
method
	/// below might be easier to use.
	ResultSet queryImpl(string sql, Variant[] args...);

	/// Escapes data for inclusion into an sql string literal
	string escape(string sqlData);

	/// query to start a transaction, only here because sqlite is 
apparently different in syntax...
	void startTransaction();

	// FIXME: this would be better as a template, but can't because 
it is an interface

	/// Just executes a query. It supports placeholders for 
parameters
	/// by using ? in the sql string. NOTE: it only accepts string, 
int, long, and null types.
	/// Others will fail runtime asserts.
	final ResultSet query(T...)(string sql, T t) {
		Variant[] args;
		foreach(arg; t) {
			Variant a;
			static if(__traits(compiles, a = arg))
				a = arg;
			else
				a = to!string(t);
			args ~= a;
		}
		return queryImpl(sql, args);
	}
	version(none)
	final ResultSet query(string sql, ...) {
		Variant[] args;
		foreach(arg; _arguments) {
			string a;
			if(arg == typeid(string) || arg == typeid(immutable(string)) 
|| arg == typeid(const(string)))
				a = va_arg!string(_argptr);
			else if (arg == typeid(int) || arg == typeid(immutable(int)) 
|| arg == typeid(const(int))) {
				auto e = va_arg!int(_argptr);
				a = to!string(e);
			} else if (arg == typeid(uint) || arg == 
typeid(immutable(uint)) || arg == typeid(const(uint))) {
				auto e = va_arg!uint(_argptr);
				a = to!string(e);
			} else if (arg == typeid(immutable(char))) {
				auto e = va_arg!char(_argptr);
				a = to!string(e);
			} else if (arg == typeid(long) || arg == typeid(const(long)) 
|| arg == typeid(immutable(long))) {
				auto e = va_arg!long(_argptr);
				a = to!string(e);
			} else if (arg == typeid(ulong) || arg == typeid(const(ulong)) 
|| arg == typeid(immutable(ulong))) {
				auto e = va_arg!ulong(_argptr);
				a = to!string(e);
			} else if (arg == typeid(null)) {
				a = null;
			} else assert(0, "invalid type " ~ arg.toString() );

			args ~= Variant(a);
		}

		return queryImpl(sql, args);
	}
}
import std.stdio;

Ret queryOneColumn(Ret, string file = __FILE__, size_t line = 
__LINE__, T...)(Database db, string sql, T t) {
	auto res = db.query(sql, t);
	if(res.empty)
		throw new Exception("no row in result", file, line);
	auto row = res.front;
	return to!Ret(row[0]);
----------------------------------------not end--------


If you have some time,please help me,Thank you.


More information about the Digitalmars-d-learn mailing list