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 DatabaseDatum[] 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 ~= DatabaseDatum(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[0 .. textlen].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 */
245
246 void omg() {
247
248 enum EMPLOYEE_ID_LEN = 6 ;
249
250 SQLHENV henv = null;
251 SQLHDBC hdbc = null;
252 SQLRETURN retcode;
253 SQLHSTMT hstmt = null;
254 SQLSMALLINT sCustID;
255
256 SQLCHAR[EMPLOYEE_ID_LEN]szEmployeeID;
257 SQL_DATE_STRUCT dsOrderDate;
258 SQLINTEGER cbCustID = 0, cbOrderDate = 0, cbEmployeeID = SQL_NTS;
259
260 retcode = SQLAllocHandle(SQL_HANDLE_ENV, cast(void*) SQL_NULL_HANDLE, &henv);
261 retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, cast(SQLPOINTER*)SQL_OV_ODBC3, 0);
262
263 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
264 retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, cast(SQLPOINTER)5, 0);
265
266 retcode = SQLDriverConnect(
267 hdbc, null, cast(ubyte*)"DSN=PostgreSQL30Postgres".ptr, SQL_NTS,
268 null, 0, null,
269 SQL_DRIVER_NOPROMPT );
270
271
272 import std.stdio; writeln(retcode);
273 retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
274
275 szEmployeeID[0 .. 6] = cast(ubyte[]) "BERGS\0";
276
277 sCustID = 5;
278 dsOrderDate.year = 2006;
279 dsOrderDate.month = 3;
280 dsOrderDate.day = 17;
281
282
283 /*
284 retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, EMPLOYEE_ID_LEN, 0, szEmployeeID.ptr, 0, &cbEmployeeID);
285 import std.stdio; writeln(retcode); writeln(getSQLError(SQL_HANDLE_STMT, hstmt));
286 */
287 retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &sCustID, 0, &cbCustID);
288 import std.stdio; writeln(retcode); writeln(getSQLError(SQL_HANDLE_STMT, hstmt));
289 /*
290 retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_TIMESTAMP, dsOrderDate.sizeof, 0, &dsOrderDate, 0, &cbOrderDate);
291 import std.stdio; writeln(retcode); writeln(getSQLError(SQL_HANDLE_STMT, hstmt));
292 */
293
294 retcode = SQLPrepare(hstmt, cast(SQLCHAR*)"INSERT INTO Orders(CustomerID, EmployeeID, OrderDate) VALUES ('omg', ?, 'now')", SQL_NTS);
295
296 import std.stdio; writeln("here ", retcode); writeln(getSQLError(SQL_HANDLE_STMT, hstmt));
297
298 retcode = SQLExecute(hstmt);
299 import std.stdio; writeln(retcode); writeln(getSQLError(SQL_HANDLE_STMT, hstmt));
300 }