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 string escapeBinaryString(const(ubyte)[] data) { // FIXME 83 return "'" ~ escape(cast(string) data) ~ "'"; 84 } 85 86 87 string error() { 88 return null; // FIXME 89 } 90 91 private: 92 SQLHENV env; 93 SQLHDBC conn; 94 } 95 96 class MsSqlResult : ResultSet { 97 // name for associative array to result index 98 int getFieldIndex(string field) { 99 if(mapping is null) 100 makeFieldMapping(); 101 if (field !in mapping) 102 return -1; 103 return mapping[field]; 104 } 105 106 107 string[] fieldNames() { 108 if(mapping is null) 109 makeFieldMapping(); 110 return columnNames; 111 } 112 113 // this is a range that can offer other ranges to access it 114 bool empty() { 115 return isEmpty; 116 } 117 118 Row front() { 119 return row; 120 } 121 122 void popFront() { 123 if(!isEmpty) 124 fetchNext; 125 } 126 127 override size_t length() 128 { 129 return 1; //FIX ME 130 } 131 132 this(SQLHSTMT statement) { 133 this.statement = statement; 134 135 SQLSMALLINT info; 136 SQLNumResultCols(statement, &info); 137 numFields = info; 138 139 fetchNext(); 140 } 141 142 ~this() { 143 SQLFreeHandle(SQL_HANDLE_STMT, statement); 144 } 145 146 private: 147 SQLHSTMT statement; 148 int[string] mapping; 149 string[] columnNames; 150 int numFields; 151 152 bool isEmpty; 153 154 Row row; 155 156 void fetchNext() { 157 if(isEmpty) 158 return; 159 160 if(SQLFetch(statement) == SQL_SUCCESS) { 161 Row r; 162 r.resultSet = this; 163 string[] row; 164 165 for(int i = 0; i < numFields; i++) { 166 string a; 167 168 SQLLEN ptr; 169 170 more: 171 SQLCHAR[1024] buf; 172 if(SQLGetData(statement, cast(ushort)(i+1), SQL_CHAR, buf.ptr, 1024, &ptr) != SQL_SUCCESS) 173 throw new DatabaseException("get data: " ~ getSQLError(SQL_HANDLE_STMT, statement)); 174 175 assert(ptr != SQL_NO_TOTAL); 176 if(ptr == SQL_NULL_DATA) 177 a = null; 178 else { 179 a ~= cast(string) buf[0 .. ptr > 1024 ? 1024 : ptr].idup; 180 ptr -= ptr > 1024 ? 1024 : ptr; 181 if(ptr) 182 goto more; 183 } 184 row ~= a; 185 } 186 187 r.row = row; 188 this.row = r; 189 } else { 190 isEmpty = true; 191 } 192 } 193 194 void makeFieldMapping() { 195 for(int i = 0; i < numFields; i++) { 196 SQLSMALLINT len; 197 SQLCHAR[1024] buf; 198 auto ret = SQLDescribeCol(statement, 199 cast(ushort)(i+1), 200 cast(ubyte*)buf.ptr, 201 1024, 202 &len, 203 null, null, null, null); 204 if (ret != SQL_SUCCESS) 205 throw new DatabaseException("Field mapping error: " ~ getSQLError(SQL_HANDLE_STMT, statement)); 206 207 string a = cast(string) buf[0 .. len].idup; 208 209 columnNames ~= a; 210 mapping[a] = i; 211 } 212 213 } 214 } 215 216 private string getSQLError(short handletype, SQLHANDLE handle) 217 { 218 char[32] sqlstate; 219 char[256] message; 220 SQLINTEGER nativeerror=0; 221 SQLSMALLINT textlen=0; 222 auto ret = SQLGetDiagRec(handletype, handle, 1, 223 cast(ubyte*)sqlstate.ptr, 224 cast(int*)&nativeerror, 225 cast(ubyte*)message.ptr, 226 256, 227 &textlen); 228 229 return message.idup; 230 } 231 232 /* 233 import std.stdio; 234 void main() { 235 //auto db = new MsSql("Driver={SQL Server};Server=<host>[\\<optional-instance-name>]>;Database=dbtest;Trusted_Connection=Yes"); 236 auto db = new MsSql("Driver={SQL Server Native Client 10.0};Server=<host>[\\<optional-instance-name>];Database=dbtest;Trusted_Connection=Yes") 237 238 db.query("INSERT INTO users (id, name) values (30, 'hello mang')"); 239 240 foreach(line; db.query("SELECT * FROM users")) { 241 writeln(line[0], line["name"]); 242 } 243 } 244 */