FrankLike 
Posted in reply to Dejan Lekic
|
> 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@8
Error 42:Symbol Undefined _SQLSetEnvAttr@16
Error 42:Symbol Undefined _SQLAllocHandle@12
Error 42:Symbol Undefined _SQLGetDiagRec@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.
|
Permalink
Reply
