1 // NOTE: I haven't even tried to use this for a test yet! 2 // It's probably godawful, if it works at all. 3 /++ 4 Implementation of [arsd.database.Database] interface for 5 Microsoft SQL Server, via ODBC. 6 +/ 7 module arsd.mssql; 8 9 version(Windows): 10 11 pragma(lib, "odbc32"); 12 13 public import arsd.database; 14 15 import std.string; 16 import std.exception; 17 18 import core.sys.windows.sql; 19 import core.sys.windows.sqlext; 20 21 /// 22 class MsSql : Database { 23 /// auto db = new MsSql("Driver={SQL Server Native Client 10.0};Server=<host>[\\<optional-instance-name>];Database=dbtest;Trusted_Connection=Yes") 24 this(string connectionString) { 25 SQLAllocHandle(SQL_HANDLE_ENV, cast(void*)SQL_NULL_HANDLE, &env); 26 enforce(env !is null); 27 scope(failure) 28 SQLFreeHandle(SQL_HANDLE_ENV, env); 29 SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, cast(void *) SQL_OV_ODBC3, 0); 30 SQLAllocHandle(SQL_HANDLE_DBC, env, &conn); 31 scope(failure) 32 SQLFreeHandle(SQL_HANDLE_DBC, conn); 33 enforce(conn !is null); 34 35 auto ret = SQLDriverConnect( 36 conn, null, cast(ubyte*)connectionString.ptr, SQL_NTS, 37 null, 0, null, 38 SQL_DRIVER_NOPROMPT ); 39 40 if ((ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_SUCCESS)) 41 throw new DatabaseException("Unable to connect to ODBC object: " ~ getSQLError(SQL_HANDLE_DBC, conn)); // FIXME: print error 42 43 //query("SET NAMES 'utf8'"); // D does everything with utf8 44 } 45 46 ~this() { 47 SQLDisconnect(conn); 48 SQLFreeHandle(SQL_HANDLE_DBC, conn); 49 SQLFreeHandle(SQL_HANDLE_ENV, env); 50 } 51 52 override void startTransaction() { 53 query("START TRANSACTION"); 54 } 55 56 // possible fixme, idk if this is right 57 override string sysTimeToValue(SysTime s) { 58 return "'" ~ escape(s.toISOExtString()) ~ "'"; 59 } 60 61 ResultSet queryImpl(string sql, Variant[] args...) { 62 sql = escapedVariants(this, sql, args); 63 64 // this is passed to MsSqlResult to control 65 SQLHSTMT statement; 66 auto returned = SQLAllocHandle(SQL_HANDLE_STMT, conn, &statement); 67 68 enforce(returned == SQL_SUCCESS); 69 70 returned = SQLExecDirect(statement, cast(ubyte*)sql.ptr, cast(SQLINTEGER) sql.length); 71 if(returned != SQL_SUCCESS) 72 throw new DatabaseException(getSQLError(SQL_HANDLE_STMT, statement)); 73 74 return new MsSqlResult(statement); 75 } 76 77 string escape(string sqlData) { // FIXME 78 return ""; //FIX ME 79 //return ret.replace("'", "''"); 80 } 81 82 83 string error() { 84 return null; // FIXME 85 } 86 87 private: 88 SQLHENV env; 89 SQLHDBC conn; 90 } 91 92 class MsSqlResult : ResultSet { 93 // name for associative array to result index 94 int getFieldIndex(string field) { 95 if(mapping is null) 96 makeFieldMapping(); 97 if (field !in mapping) 98 return -1; 99 return mapping[field]; 100 } 101 102 103 string[] fieldNames() { 104 if(mapping is null) 105 makeFieldMapping(); 106 return columnNames; 107 } 108 109 // this is a range that can offer other ranges to access it 110 bool empty() { 111 return isEmpty; 112 } 113 114 Row front() { 115 return row; 116 } 117 118 void popFront() { 119 if(!isEmpty) 120 fetchNext; 121 } 122 123 override size_t length() 124 { 125 return 1; //FIX ME 126 } 127 128 this(SQLHSTMT statement) { 129 this.statement = statement; 130 131 SQLSMALLINT info; 132 SQLNumResultCols(statement, &info); 133 numFields = info; 134 135 fetchNext(); 136 } 137 138 ~this() { 139 SQLFreeHandle(SQL_HANDLE_STMT, statement); 140 } 141 142 private: 143 SQLHSTMT statement; 144 int[string] mapping; 145 string[] columnNames; 146 int numFields; 147 148 bool isEmpty; 149 150 Row row; 151 152 void fetchNext() { 153 if(isEmpty) 154 return; 155 156 if(SQLFetch(statement) == SQL_SUCCESS) { 157 Row r; 158 r.resultSet = this; 159 string[] row; 160 161 for(int i = 0; i < numFields; i++) { 162 string a; 163 164 SQLLEN ptr; 165 166 more: 167 SQLCHAR[1024] buf; 168 if(SQLGetData(statement, cast(ushort)(i+1), SQL_CHAR, buf.ptr, 1024, &ptr) != SQL_SUCCESS) 169 throw new DatabaseException("get data: " ~ getSQLError(SQL_HANDLE_STMT, statement)); 170 171 assert(ptr != SQL_NO_TOTAL); 172 if(ptr == SQL_NULL_DATA) 173 a = null; 174 else { 175 a ~= cast(string) buf[0 .. ptr > 1024 ? 1024 : ptr].idup; 176 ptr -= ptr > 1024 ? 1024 : ptr; 177 if(ptr) 178 goto more; 179 } 180 row ~= a; 181 } 182 183 r.row = row; 184 this.row = r; 185 } else { 186 isEmpty = true; 187 } 188 } 189 190 void makeFieldMapping() { 191 for(int i = 0; i < numFields; i++) { 192 SQLSMALLINT len; 193 SQLCHAR[1024] buf; 194 auto ret = SQLDescribeCol(statement, 195 cast(ushort)(i+1), 196 cast(ubyte*)buf.ptr, 197 1024, 198 &len, 199 null, null, null, null); 200 if (ret != SQL_SUCCESS) 201 throw new DatabaseException("Field mapping error: " ~ getSQLError(SQL_HANDLE_STMT, statement)); 202 203 string a = cast(string) buf[0 .. len].idup; 204 205 columnNames ~= a; 206 mapping[a] = i; 207 } 208 209 } 210 } 211 212 private string getSQLError(short handletype, SQLHANDLE handle) 213 { 214 char[32] sqlstate; 215 char[256] message; 216 SQLINTEGER nativeerror=0; 217 SQLSMALLINT textlen=0; 218 auto ret = SQLGetDiagRec(handletype, handle, 1, 219 cast(ubyte*)sqlstate.ptr, 220 cast(int*)&nativeerror, 221 cast(ubyte*)message.ptr, 222 256, 223 &textlen); 224 225 return message.idup; 226 } 227 228 /* 229 import std.stdio; 230 void main() { 231 //auto db = new MsSql("Driver={SQL Server};Server=<host>[\\<optional-instance-name>]>;Database=dbtest;Trusted_Connection=Yes"); 232 auto db = new MsSql("Driver={SQL Server Native Client 10.0};Server=<host>[\\<optional-instance-name>];Database=dbtest;Trusted_Connection=Yes") 233 234 db.query("INSERT INTO users (id, name) values (30, 'hello mang')"); 235 236 foreach(line; db.query("SELECT * FROM users")) { 237 writeln(line[0], line["name"]); 238 } 239 } 240 */