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 }