1 /** 2 * DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC. 3 * 4 * Source file ddbc/drivers/pgsqlddbc.d. 5 * 6 * DDBC library attempts to provide implementation independent interface to different databases. 7 * 8 * Set of supported RDBMSs can be extended by writing Drivers for particular DBs. 9 * 10 * JDBC documentation can be found here: 11 * $(LINK http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html)$(BR) 12 * 13 * This module contains implementation of SQLite Driver 14 * 15 * You can find usage examples in unittest{} sections. 16 * 17 * Copyright: Copyright 2013 18 * License: $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0). 19 * Author: Raphael Ungricht 20 */ 21 22 module ddbc.drivers.odbcddbc; 23 24 import std.algorithm; 25 import std.conv; 26 import std.datetime : Date, DateTime, TimeOfDay; 27 import std.datetime.date; 28 import std.datetime.systime; 29 import std.exception; 30 31 // For backwards compatibily 32 // 'enforceEx' will be removed with 2.089 33 static if(__VERSION__ < 2080) { 34 alias enforceHelper = enforceEx; 35 } else { 36 alias enforceHelper = enforce; 37 } 38 39 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 40 import std.experimental.logger; 41 } 42 import std.stdio; 43 import std..string; 44 import std.variant; 45 import core.sync.mutex; 46 import std.traits; 47 import ddbc.common; 48 import ddbc.core; 49 50 version (USE_ODBC) 51 { 52 53 version (unittest) 54 { 55 56 /// change to false to disable tests on real ODBC server 57 immutable bool ODBC_TESTS_ENABLED = false; 58 59 static if (ODBC_TESTS_ENABLED) 60 { 61 62 /// use this data source for tests 63 64 DataSource createUnitTestODBCDataSource() 65 { 66 import std.file; 67 68 string url = "ddbc:odbc://localhost,1433?user=SA,password=bbk4k77JKH88g54,driver=FreeTDS";//cast(string) read("test_connection.txt"); 69 70 return createConnectionPool(url); 71 } 72 } 73 } 74 75 import etc.c.odbc.sql; 76 import etc.c.odbc.sqlext; 77 import etc.c.odbc.sqltypes; 78 79 /*private SQLRETURN check(lazy SQLRETURN fn, SQLHANDLE h, SQLSMALLINT t, 80 string file = __FILE__, size_t line = __LINE__) 81 { 82 SQLRETURN e = fn(); 83 if (e != SQL_SUCCESS && e != SQL_SUCCESS_WITH_INFO && e != SQL_NO_DATA) 84 { 85 extractError(fn.stringof, h, t, file, line); 86 } 87 return e; 88 }*/ 89 90 private SQLRETURN check(alias fn, string file = __FILE__, size_t line = __LINE__)( 91 SQLHANDLE h, SQLSMALLINT t, Parameters!fn args) 92 { 93 import std.typecons; 94 95 enum RetVals 96 { 97 SQL_SUCCESS = 0, 98 SQL_SUCCESS_WITH_INFO = 1, 99 SQL_NO_DATA = 100, 100 SQL_ERROR = (-1), 101 SQL_INVALID_HANDLE = (-2), 102 SQL_STILL_EXECUTING = 2, 103 SQL_NEED_DATA = 99 104 } 105 106 SQLRETURN retval = fn(args); 107 108 debug 109 { 110 if(retval < 0) { 111 sharedLog.errorf("%s(%s) : %s", fullyQualifiedName!fn, format("%(%s%|, %)", tuple(args)), cast(RetVals) retval); 112 } else { 113 //sharedLog.tracef("%s(%s) : %s", fullyQualifiedName!fn, format("%(%s%|, %)", tuple(args)), cast(RetVals) retval); 114 } 115 } 116 117 118 if (retval != SQL_SUCCESS && retval != SQL_SUCCESS_WITH_INFO && retval != SQL_NO_DATA) 119 { 120 extractError(fullyQualifiedName!fn, h, t, file, line); 121 } 122 return retval; 123 } 124 125 import std.functional : partial; 126 127 alias checkenv = partial!(check, SQL_HANDLE_ENV); 128 129 private void extractError(string fn, SQLHANDLE handle, SQLSMALLINT type, string file, size_t line) 130 { 131 short i = 0; 132 SQLINTEGER errorCode; 133 SQLCHAR[7] state; 134 SQLCHAR[1024] msg; 135 SQLSMALLINT textLen; 136 SQLRETURN ret; 137 138 string message; 139 do 140 { 141 ret = SQLGetDiagRec(type, handle, ++i, state.ptr, &errorCode, 142 msg.ptr, msg.length.to!short, &textLen); 143 if (SQL_SUCCEEDED(ret)) 144 { 145 import std.format; 146 message ~= format("\n\t%s:%d:%d\t%s", fromStringz(state.ptr), 147 cast(int) i, errorCode, fromStringz(msg.ptr)).idup; 148 } 149 } 150 while (ret == SQL_SUCCESS); 151 //debug stderr.writefln("%s:%s:%s %s", file, line, fn, message); 152 throw new Exception(message, file, line); 153 } 154 155 enum Namedd 156 { 157 SQL_C_BINARY = SQL_BINARY, 158 SQL_C_BIT = SQL_BIT, 159 SQL_C_SBIGINT = (SQL_BIGINT + SQL_SIGNED_OFFSET), /* SIGNED BIGINT */ 160 SQL_C_UBIGINT = (SQL_BIGINT + SQL_UNSIGNED_OFFSET), /* UNSIGNED BIGINT */ 161 SQL_C_TINYINT = SQL_TINYINT, 162 SQL_C_SLONG = (SQL_C_LONG + SQL_SIGNED_OFFSET), /* SIGNED INTEGER */ 163 SQL_C_SSHORT = (SQL_C_SHORT + SQL_SIGNED_OFFSET), /* SIGNED SMALLINT */ 164 SQL_C_STINYINT = (SQL_TINYINT + SQL_SIGNED_OFFSET), /* SIGNED TINYINT */ 165 SQL_C_ULONG = (SQL_C_LONG + SQL_UNSIGNED_OFFSET), /* UNSIGNED INTEGER */ 166 SQL_C_USHORT = (SQL_C_SHORT + SQL_UNSIGNED_OFFSET), /* UNSIGNED SMALLINT */ 167 SQL_C_UTINYINT = (SQL_TINYINT + SQL_UNSIGNED_OFFSET), /* UNSIGNED TINYINT */ 168 SQL_C_BOOKMARK = SQL_C_ULONG, /* BOOKMARK */ 169 SQL_C_VARBOOKMARK = SQL_C_BINARY, 170 171 // ODBCVER >= 0x0350 172 SQL_C_GUID = SQL_GUID /* GUID */ 173 } 174 175 template TypeToCIdentifier(T) 176 { 177 178 static if (is(T == byte)) 179 alias TypeToCIdentifier = SQL_C_STINYINT; 180 else static if (is(T == ubyte)) 181 alias TypeToCIdentifier = SQL_C_UTINYINT; 182 else static if (is(T == short)) 183 alias TypeToCIdentifier = SQL_C_SSHORT; 184 else static if (is(T == ushort)) 185 alias TypeToCIdentifier = SQL_C_USHORT; 186 else static if (is(T == int)) 187 alias TypeToCIdentifier = SQL_C_SLONG; 188 else static if (is(T == uint)) 189 alias TypeToCIdentifier = SQL_C_ULONG; 190 else static if (is(T == long)) 191 alias TypeToCIdentifier = SQL_C_SBIGINT; 192 else static if (is(T == ulong)) 193 alias TypeToCIdentifier = SQL_C_UBIGINT; 194 else static if (is(T == float)) 195 alias TypeToCIdentifier = SQL_C_FLOAT; 196 else static if (is(T == double)) 197 alias TypeToCIdentifier = SQL_C_DOUBLE; 198 else static if (is(T == bool)) 199 alias TypeToCIdentifier = SQL_C_BIT; 200 else static if (is(T == char[])) 201 alias TypeToCIdentifier = SQL_C_CHAR; 202 else static if (is(T == wchar[])) 203 alias TypeToCIdentifier = SQL_C_WCHAR; 204 else static if (is(T == byte[])) 205 alias TypeToCIdentifier = SQL_C_BINARY; 206 else static if (is(T == SQL_DATE_STRUCT)) 207 alias TypeToCIdentifier = SQL_C_TYPE_DATE; 208 else static if (is(T == SQL_TIME_STRUCT)) 209 alias TypeToCIdentifier = SQL_C_TYPE_TIME; 210 else static if (is(T == SQL_TIMESTAMP_STRUCT)) 211 alias TypeToCIdentifier = SQL_C_TYPE_TIMESTAMP; 212 else static if (is(T == SQL_NUMERIC_STRUCT)) 213 alias TypeToCIdentifier = SQL_C_NUMERIC; 214 else static if (is(T == SQLGUID)) 215 alias TypeToCIdentifier = SQL_C_GUID; 216 else static if (true) 217 alias TypeToCIdentifier = void; 218 219 } 220 221 short ctypeToSQLType(short ctype) 222 { 223 // dfmt off 224 const short[short] mymap = [ 225 SQL_C_STINYINT: SQL_TINYINT, 226 SQL_C_UTINYINT: SQL_TINYINT, 227 SQL_C_SSHORT: SQL_SMALLINT, 228 SQL_C_USHORT: SQL_SMALLINT, 229 SQL_C_SLONG: SQL_INTEGER, 230 SQL_C_ULONG: SQL_INTEGER, 231 SQL_C_SBIGINT: SQL_BIGINT, 232 SQL_C_UBIGINT: SQL_BIGINT, 233 SQL_C_FLOAT: SQL_REAL, 234 SQL_C_DOUBLE: SQL_DOUBLE, 235 SQL_C_BIT: SQL_BIT, 236 SQL_C_CHAR: SQL_VARCHAR, 237 SQL_C_WCHAR: SQL_WVARCHAR, 238 SQL_C_BINARY: SQL_BINARY, 239 SQL_C_TYPE_DATE: SQL_TYPE_DATE, 240 SQL_C_TYPE_TIME: SQL_TYPE_TIME, 241 SQL_C_TYPE_TIMESTAMP: SQL_TYPE_TIMESTAMP, 242 ]; 243 // dfmt on 244 return mymap[ctype]; 245 } 246 247 short sqlTypeToCType(short sqltype) 248 { 249 // dfmt off 250 const short[short] mymap = [ 251 SQL_TINYINT: SQL_C_STINYINT, 252 SQL_SMALLINT: SQL_C_SSHORT, 253 SQL_INTEGER: SQL_C_SLONG, 254 SQL_BIGINT: SQL_C_SBIGINT, 255 SQL_REAL: SQL_C_FLOAT, 256 SQL_DOUBLE: SQL_C_DOUBLE, 257 SQL_BIT: SQL_C_BIT, 258 SQL_VARCHAR: SQL_C_CHAR, 259 SQL_WVARCHAR: SQL_C_WCHAR, 260 SQL_BINARY: SQL_C_BINARY, 261 SQL_TYPE_DATE: SQL_C_TYPE_DATE, 262 SQL_TYPE_TIME: SQL_C_TYPE_TIME, 263 SQL_TYPE_TIMESTAMP: SQL_C_TYPE_TIMESTAMP, 264 ]; 265 // dfmt on 266 return mymap[sqltype]; 267 } 268 269 SqlType fromODBCType(int t) 270 { 271 switch (t) 272 { 273 case SQL_TINYINT: 274 return SqlType.TINYINT; 275 case SQL_SMALLINT: 276 return SqlType.SMALLINT; 277 case SQL_INTEGER: 278 return SqlType.INTEGER; 279 case SQL_REAL: 280 return SqlType.FLOAT; 281 case SQL_DOUBLE: 282 return SqlType.DOUBLE; 283 284 case SQL_DECIMAL: 285 case SQL_NUMERIC: 286 return SqlType.DECIMAL; 287 288 case SQL_TYPE_TIMESTAMP: 289 return SqlType.DATETIME; 290 291 case SQL_BIGINT: 292 return SqlType.BIGINT; 293 294 case SQL_TYPE_DATE: 295 return SqlType.DATE; 296 case SQL_TYPE_TIME: 297 return SqlType.TIME; 298 299 case SQL_CHAR: 300 return SqlType.CHAR; 301 302 case SQL_WLONGVARCHAR: 303 case SQL_WVARCHAR: 304 case SQL_VARCHAR: 305 return SqlType.VARCHAR; 306 case SQL_BIT: 307 return SqlType.BIT; 308 case SQL_BINARY: 309 return SqlType.BLOB; 310 default: 311 return SqlType.OTHER; 312 } 313 } 314 315 class ODBCConnection : ddbc.core.Connection 316 { 317 private: 318 string url; 319 string[string] params; 320 string dbName; 321 322 SQLHENV henv = SQL_NULL_HENV; 323 SQLHDBC conn = SQL_NULL_HDBC; 324 325 bool closed; 326 bool autocommit = true; 327 Mutex mutex; 328 329 ODBCStatement[] activeStatements; 330 331 void closeUnclosedStatements() 332 { 333 ODBCStatement[] list = activeStatements.dup; 334 foreach (stmt; list) 335 { 336 stmt.close(); 337 } 338 } 339 340 void checkClosed() 341 { 342 if (closed) 343 throw new SQLException("Connection is already closed"); 344 } 345 346 public: 347 348 void lock() 349 { 350 mutex.lock(); 351 } 352 353 void unlock() 354 { 355 mutex.unlock(); 356 } 357 358 SQLHDBC getConnection() 359 { 360 return conn; 361 } 362 363 void onStatementClosed(ODBCStatement stmt) 364 { 365 myRemove(activeStatements, stmt); 366 } 367 368 private SQLRETURN checkenv(alias Fn, string file = __FILE__, size_t line = __LINE__)( 369 Parameters!Fn args) 370 { 371 return check!(Fn, file, line)(henv, cast(ushort) SQL_HANDLE_ENV, args); 372 } 373 374 private SQLRETURN checkdbc(alias Fn, string file = __FILE__, size_t line = __LINE__)( 375 Parameters!Fn args) 376 { 377 return check!(Fn, file, line)(conn, cast(ushort) SQL_HANDLE_DBC, args); 378 } 379 380 this(string url, string[string] params) 381 { 382 //writeln("ODBCConnection() creating connection"); 383 mutex = new Mutex(); 384 this.url = url; 385 this.params = params; 386 387 //writeln("parsing url " ~ url); 388 extractParamsFromURL(url, this.params); 389 //writeln(url); 390 391 // Allocate environment handle 392 checkenv!SQLAllocHandle(cast(ushort) SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); 393 394 // Set the ODBC version environment attribute 395 checkenv!SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, cast(SQLPOINTER*) SQL_OV_ODBC3, 0); 396 397 // Allocate connection handle 398 checkdbc!SQLAllocHandle(cast(ushort) SQL_HANDLE_DBC, henv, &conn); 399 400 // Set login timeout to 5 seconds 401 checkdbc!SQLSetConnectAttr(conn, SQL_LOGIN_TIMEOUT, cast(SQLPOINTER) 5, 0); 402 403 string[] connectionProps; 404 405 auto server = url[7 .. $].split('/').join('\\'); 406 if (server.length) 407 this.params["server"] = server; 408 void addToConnectionString(string key, string targetKey) 409 { 410 if (key in this.params) 411 { 412 connectionProps ~= [targetKey ~ "=" ~this.params[key]]; 413 } 414 } 415 416 if ("database" in this.params) 417 dbName = this.params["database"]; 418 419 addToConnectionString("dsn", "DSN"); 420 addToConnectionString("driver", "Driver"); 421 addToConnectionString("server", "Server"); 422 addToConnectionString("user", "Uid"); 423 addToConnectionString("username", "Uid"); 424 addToConnectionString("password", "Pwd"); 425 addToConnectionString("database", "Database"); 426 string connectionString = connectionProps.join(';'); 427 428 sharedLog.info(connectionString); 429 430 SQLCHAR[1024] outstr; 431 SQLSMALLINT outstrlen; 432 checkdbc!SQLDriverConnect(conn, // ConnectionHandle 433 null, // WindowHandle 434 connectionString.dup.ptr, // InConnectionString 435 (connectionString.length).to!(short), // StringLength1 436 outstr.ptr, // OutConnectionString 437 outstr.length.to!(short), // BufferLength 438 &outstrlen, // StringLength2Ptr 439 cast(ushort) SQL_DRIVER_NOPROMPT // DriverCompletion 440 ); 441 442 closed = false; 443 setAutoCommit(true); 444 445 //writeln("MySQLConnection() connection created"); 446 } 447 448 override void close() 449 { 450 checkClosed(); 451 452 lock(); 453 scope (exit) 454 unlock(); 455 try 456 { 457 SQLDisconnect(conn); 458 SQLFreeHandle(SQL_HANDLE_DBC, conn); 459 conn = null; 460 SQLFreeHandle(SQL_HANDLE_ENV, henv); 461 henv = null; 462 closed = true; 463 } 464 catch (Throwable e) 465 { 466 throw new SQLException(e); 467 } 468 } 469 470 override void commit() 471 { 472 473 checkClosed(); 474 if (autocommit == false) 475 { 476 477 lock(); 478 scope (exit) 479 unlock(); 480 481 checkdbc!SQLEndTran(cast(short) SQL_HANDLE_DBC, conn, cast(short) SQL_COMMIT); 482 } 483 } 484 485 override Statement createStatement() 486 { 487 checkClosed(); 488 489 lock(); 490 scope (exit) 491 unlock(); 492 493 try 494 { 495 ODBCStatement stmt = new ODBCStatement(this); 496 activeStatements ~= stmt; 497 return stmt; 498 } 499 catch (Throwable e) 500 { 501 throw new SQLException(e); 502 } 503 } 504 505 PreparedStatement prepareStatement(string sql) 506 { 507 checkClosed(); 508 509 lock(); 510 scope (exit) 511 unlock(); 512 513 try 514 { 515 ODBCPreparedStatement stmt = new ODBCPreparedStatement(this, sql); 516 activeStatements ~= stmt; 517 return stmt; 518 } 519 catch (Throwable e) 520 { 521 throw new SQLException(e.msg ~ " while execution of query " ~ sql); 522 } 523 } 524 525 override string getCatalog() 526 { 527 return dbName; 528 } 529 530 /// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work. 531 override void setCatalog(string catalog) 532 { 533 } 534 535 override bool isClosed() 536 { 537 return closed; 538 } 539 540 override void rollback() 541 { 542 checkClosed(); 543 544 lock(); 545 scope (exit) 546 unlock(); 547 548 checkdbc!SQLEndTran(cast(short) SQL_HANDLE_DBC, conn, cast(short) SQL_ROLLBACK); 549 } 550 551 override bool getAutoCommit() 552 { 553 return autocommit; 554 } 555 556 override void setAutoCommit(bool autoCommit) 557 { 558 checkClosed(); 559 if (this.autocommit != autocommit) 560 { 561 lock(); 562 scope (exit) 563 unlock(); 564 565 uint ac = autoCommit ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF; 566 567 checkdbc!SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, &ac, SQL_IS_UINTEGER); 568 569 this.autocommit = autocommit; 570 } 571 } 572 } 573 574 class ODBCStatement : Statement 575 { 576 private: 577 ODBCConnection conn; 578 SQLHSTMT stmt; 579 ODBCResultSet resultSet; 580 ColumnInfo[] cols; 581 582 bool closed = false; 583 584 private SQLRETURN checkstmt(alias Fn, string file = __FILE__, size_t line = __LINE__)( 585 Parameters!Fn args) 586 { 587 return check!(Fn, file, line)(stmt, SQL_HANDLE_STMT, args); 588 } 589 590 public: 591 void checkClosed() 592 { 593 enforceHelper!SQLException(!closed, "Statement is already closed"); 594 } 595 596 void lock() 597 { 598 conn.lock(); 599 } 600 601 void unlock() 602 { 603 conn.unlock(); 604 } 605 606 this(ODBCConnection conn) 607 { 608 this.conn = conn; 609 610 checkstmt!SQLAllocHandle(cast(short) SQL_HANDLE_STMT, this.conn.conn, &stmt); 611 } 612 613 public: 614 ODBCConnection getConnection() 615 { 616 checkClosed(); 617 return conn; 618 } 619 620 override ddbc.core.ResultSet executeQuery(string query) 621 { 622 checkClosed(); 623 lock(); 624 scope (exit) 625 unlock(); 626 627 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 628 sharedLog.trace(query); 629 } 630 631 try 632 { 633 // the 3rd arg is length of the query string or SQL_NTS if the string is null terminated 634 // will return 1 of: 635 // 636 // SQL_SUCCESS 637 // SQL_SUCCESS_WITH_INFO 638 // SQL_ERROR 639 // SQL_INVALID_HANDLE 640 // SQL_NEED_DATA 641 // SQL_NO_DATA_FOUND 642 checkstmt!SQLExecDirect(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 643 bind(); 644 resultSet = new ODBCResultSet(this); 645 return resultSet; 646 } 647 catch (Exception e) 648 { 649 throw new SQLException(e.msg ~ " - while execution of query " ~ query, 650 e.file, e.line); 651 } 652 } 653 654 override int executeUpdate(string query) 655 { 656 checkClosed(); 657 lock(); 658 scope (exit) 659 unlock(); 660 int rowsAffected = 0; 661 662 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 663 sharedLog.trace(query); 664 } 665 666 try 667 { 668 checkstmt!SQLExecDirect(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 669 670 checkstmt!SQLRowCount(stmt, &rowsAffected); 671 672 return rowsAffected; 673 } 674 catch (Exception e) 675 { 676 throw new SQLException(e.msg ~ " While executing query: '" ~ query ~ "'", e.file, e.line); 677 } 678 } 679 680 override int executeUpdate(string query, out Variant insertId) 681 { 682 checkClosed(); 683 lock(); 684 scope (exit) 685 unlock(); 686 687 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 688 sharedLog.trace(query); 689 } 690 691 try 692 { 693 int rowsAffected = executeUpdate(query); 694 695 checkstmt!SQLExecDirect(stmt, 696 cast(SQLCHAR*) toStringz(`SELECT SCOPE_IDENTITY()`), SQL_NTS); 697 698 bind(); 699 fetch(); 700 insertId = getColumn(1).readValueAsVariant(); 701 702 return rowsAffected; 703 } 704 catch (Throwable e) 705 { 706 throw new SQLException(e.msg ~ " - while execution of query " ~ query); 707 } 708 } 709 710 override void close() 711 { 712 checkClosed(); 713 lock(); 714 scope (exit) 715 unlock(); 716 try 717 { 718 closeResultSet(); 719 720 SQLFreeHandle(SQL_HANDLE_STMT, stmt); 721 stmt = null; 722 closed = true; 723 conn.onStatementClosed(this); 724 725 } 726 catch (Throwable e) 727 { 728 throw new SQLException(e); 729 } 730 } 731 732 private void closeResultSet() 733 { 734 if (resultSet !is null) 735 { 736 resultSet.onStatementClosed(); 737 resultSet = null; 738 } 739 } 740 741 private: 742 743 void bind() 744 { 745 746 SQLSMALLINT num = 0; 747 checkstmt!SQLNumResultCols(stmt, &num); 748 749 cols.length = num; 750 751 foreach (i; 0 .. num) 752 { 753 cols[i] = new ColumnInfo(i + 1); 754 //check(SQLBindCol(stmt, cast(ushort)(i + 1), sqlTypeToCType(cols[i].dataType), null, 0, null), stmt, SQL_HANDLE_STMT); 755 } 756 } 757 758 int getColumnCount() 759 { 760 return cast(int) cols.length; 761 } 762 763 ColumnInfo getColumn(int nr) 764 { 765 return cols[nr - 1]; 766 } 767 768 bool fetch() 769 { 770 bool hasData = checkstmt!SQLFetch(stmt) != SQL_NO_DATA; 771 772 if (hasData) 773 { 774 this.cols.each!(c => c.read()); 775 } 776 else 777 { 778 SQLFreeStmt(stmt, SQL_CLOSE); 779 } 780 781 return hasData; 782 } 783 784 class ColumnInfo 785 { 786 ushort nr; 787 string name; 788 short dataType; 789 short nullAble; 790 791 Variant value; 792 793 this(int nr) 794 { 795 796 this.nr = cast(short) nr; 797 798 short nameLen = 1000; 799 char[1000] nameBuff; 800 801 // BUG: SQLDescribeCol does not return the length of the of the column-name! 802 /*checkstmt!SQLDescribeCol(stmt, this.nr, null, 803 0, &nameLen, &this.dataType, null, null, &this.nullAble); 804 nameLen += 1; 805 nameBuff.length = nameLen;*/ 806 807 checkstmt!SQLDescribeCol(stmt, this.nr, nameBuff.ptr, 808 nameLen, null, &this.dataType, null, null, &this.nullAble); 809 810 this.name = fromStringz(nameBuff.ptr).idup; 811 } 812 813 void read() 814 { 815 value = readValueAsVariant(); 816 } 817 818 bool isNull() 819 { 820 return !value.hasValue(); //testNull == SQL_NULL_DATA; 821 } 822 823 Variant readValue(T)() 824 if (!isArray!(T) && !is(TypeToCIdentifier!(T) == void)) 825 { 826 T val; 827 828 int nullCheck = 0; 829 830 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), &val, 0, &nullCheck); 831 832 if (nullCheck == SQL_NULL_DATA) 833 return Variant(); 834 835 return Variant(val); 836 } 837 838 Variant readValue(T)() 839 if (isArray!(T) && !is(TypeToCIdentifier!(T) == void)) 840 { 841 T val; 842 int len = 0; 843 844 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), &val, 0, &len); 845 846 if (len == SQL_NULL_DATA) 847 return Variant(); 848 849 850 // A char-array contains a null-termination. 851 static if (is(T == char[])) 852 len += 1; 853 854 val.length = len; 855 856 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), val.ptr, len, null); 857 858 // A char-array contains a null-termination. 859 static if (is(T == char[])) 860 val = val[0 .. ($ - 1)]; 861 862 static if(is(T == char[])) 863 return Variant(val.idup); 864 else 865 return Variant(val); 866 } 867 868 Variant readValue(T)() if (is(T == SysTime)) 869 { 870 auto val = readValue!(SQL_TIMESTAMP_STRUCT); 871 872 if (val.type == typeid(SQL_TIMESTAMP_STRUCT)) 873 { 874 auto s = val.get!(SQL_TIMESTAMP_STRUCT); 875 import core.time : nsecs; 876 import std.datetime.timezone : UTC; 877 //writefln("%s-%s-%s %s:%s:%s.%s", s.year, s.month, s.day, s.hour, s.minute, s.second, s.fraction); 878 return Variant(SysTime( 879 DateTime(s.year, s.month, s.day, s.hour, s.minute, s.second), 880 nsecs(s.fraction), 881 UTC() 882 )); 883 } 884 return Variant(); 885 } 886 887 Variant readValue(T)() if (is(T == DateTime)) 888 { 889 auto val = readValue!(SQL_TIMESTAMP_STRUCT); 890 891 if (val.type == typeid(SQL_TIMESTAMP_STRUCT)) 892 { 893 auto s = val.get!(SQL_TIMESTAMP_STRUCT); 894 return Variant(DateTime(s.year, s.month, s.day, s.hour, s.minute, s.second)); 895 } 896 return Variant(); 897 } 898 899 Variant readValue(T)() if (is(T == Date)) 900 { 901 auto val = readValue!(SQL_DATE_STRUCT); 902 903 if (val.type == typeid(SQL_DATE_STRUCT)) 904 { 905 auto s = val.get!(SQL_DATE_STRUCT); 906 return Variant(Date(s.year, s.month, s.day)); 907 } 908 return Variant(); 909 } 910 911 Variant readValue(T)() if (is(T == TimeOfDay)) 912 { 913 auto val = readValue!(SQL_TIME_STRUCT); 914 915 if (val.type == typeid(SQL_TIME_STRUCT)) 916 { 917 auto s = val.get!(SQL_TIME_STRUCT); 918 return Variant(TimeOfDay(s.hour, s.minute, s.second)); 919 } 920 return Variant(); 921 } 922 923 Variant readValueAsVariant() 924 { 925 // dfmt off 926 switch (this.dataType) 927 { 928 case SQL_TINYINT: return readValue!(byte); 929 case SQL_SMALLINT: return readValue!(short); 930 case SQL_INTEGER: return readValue!(int); 931 case SQL_BIGINT: return readValue!(long); 932 933 case SQL_REAL: return readValue!(float); 934 case SQL_FLOAT: return readValue!(double); 935 case SQL_DOUBLE: return readValue!(double); 936 937 case SQL_CHAR: return readValue!(char[]); 938 case SQL_VARCHAR: return readValue!(char[]); 939 case SQL_LONGVARCHAR: return readValue!(char[]); 940 case SQL_WCHAR: return readValue!(wchar[]); 941 case SQL_WVARCHAR: return readValue!(wchar[]); 942 case SQL_WLONGVARCHAR: return readValue!(wchar[]); 943 case SQL_BINARY: return readValue!(byte[]); 944 case SQL_VARBINARY: return readValue!(byte[]); 945 case SQL_LONGVARBINARY: return readValue!(byte[]); 946 947 case SQL_NUMERIC: return readValue!(SQL_NUMERIC_STRUCT); 948 case SQL_TYPE_DATE: return readValue!(Date); 949 case SQL_TYPE_TIME: return readValue!(TimeOfDay); 950 case SQL_TYPE_TIMESTAMP: return readValue!(DateTime); 951 case -155: return readValue!(SysTime); // DATETIMEOFFSET 952 //case SQL_GUID: return Variant(readValue!(SQLGUID)); 953 954 default: 955 throw new Exception(text("TYPE ", this.dataType, " is currently not supported!")); 956 } 957 // dfmt on 958 } 959 } 960 } 961 962 class ODBCPreparedStatement : ODBCStatement, PreparedStatement 963 { 964 string query; 965 int paramCount; 966 ResultSetMetaData metadata; 967 ParameterMetaData paramMetadata; 968 969 Parameter[] params; 970 971 this(ODBCConnection conn, string query) 972 { 973 super(conn); 974 this.query = query; 975 try 976 { 977 checkstmt!SQLPrepare(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 978 SQLSMALLINT v = 0; 979 checkstmt!SQLNumParams(stmt, &v); 980 paramCount = v; 981 params.length = v; 982 } 983 catch (Throwable e) 984 { 985 throw new SQLException(e); 986 } 987 } 988 989 void checkIndex(int index) 990 { 991 if (index < 1 || index > paramCount) 992 throw new SQLException("Parameter index " ~ to!string(index) ~ " is out of range"); 993 } 994 995 public: 996 997 /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. 998 override ResultSetMetaData getMetaData() 999 { 1000 return metadata; 1001 } 1002 1003 /// Retrieves the number, types and properties of this PreparedStatement object's parameters. 1004 override ParameterMetaData getParameterMetaData() 1005 { 1006 throw new SQLException("Method not implemented"); 1007 } 1008 1009 override int executeUpdate() 1010 { 1011 checkClosed(); 1012 lock(); 1013 scope (exit) 1014 unlock(); 1015 1016 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 1017 sharedLog.trace(stmt); 1018 } 1019 1020 try 1021 { 1022 1023 checkstmt!SQLExecute(stmt); 1024 1025 int rowsAffected = 0; 1026 checkstmt!SQLRowCount(stmt, &rowsAffected); 1027 return rowsAffected; 1028 } 1029 catch (Throwable e) 1030 { 1031 throw new SQLException(e); 1032 } 1033 } 1034 1035 override int executeUpdate(out Variant insertId) 1036 { 1037 checkClosed(); 1038 lock(); 1039 scope (exit) 1040 unlock(); 1041 1042 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 1043 sharedLog.trace(stmt); 1044 } 1045 1046 try 1047 { 1048 checkstmt!SQLExecute(stmt); 1049 1050 int rowsAffected = 0; 1051 checkstmt!SQLRowCount(stmt, &rowsAffected); 1052 1053 checkstmt!SQLExecDirect(stmt, 1054 cast(SQLCHAR*) toStringz(`SELECT SCOPE_IDENTITY()`), SQL_NTS); 1055 1056 bind(); 1057 fetch(); 1058 insertId = getColumn(1).value; 1059 return rowsAffected; 1060 } 1061 catch (Throwable e) 1062 { 1063 throw new SQLException(e); 1064 } 1065 } 1066 1067 override ddbc.core.ResultSet executeQuery() 1068 { 1069 checkClosed(); 1070 lock(); 1071 scope (exit) 1072 unlock(); 1073 1074 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 1075 sharedLog.trace(stmt); 1076 } 1077 1078 try 1079 { 1080 checkstmt!SQLExecute(stmt); 1081 bind(); 1082 resultSet = new ODBCResultSet(this); 1083 return resultSet; 1084 } 1085 catch (Throwable e) 1086 { 1087 throw new SQLException(e); 1088 } 1089 } 1090 1091 override void clearParameters() 1092 { 1093 checkClosed(); 1094 lock(); 1095 scope (exit) 1096 unlock(); 1097 try 1098 { 1099 for (int i = 1; i <= paramCount; i++) 1100 setNull(i); 1101 } 1102 catch (Throwable e) 1103 { 1104 throw new SQLException(e); 1105 } 1106 } 1107 1108 struct Parameter 1109 { 1110 SQLSMALLINT bindType; 1111 SQLSMALLINT dbtype; 1112 1113 void[] data; 1114 } 1115 1116 void bindParam(T)(int parameterIndex, T x) 1117 { 1118 1119 checkClosed(); 1120 lock(); 1121 scope (exit) 1122 unlock(); 1123 checkIndex(parameterIndex); 1124 1125 auto param = ¶ms[parameterIndex - 1]; 1126 1127 static if (is(T == char[])) 1128 param.data = cast(void[]) (x ~ '\0'); 1129 else static if (isArray!(T)) 1130 param.data = cast(void[]) x; 1131 else 1132 param.data = cast(void[])[x]; 1133 param.bindType = TypeToCIdentifier!(T); 1134 param.dbtype = ctypeToSQLType(TypeToCIdentifier!(T)); 1135 1136 1137 1138 SQLBindParameter(stmt, cast(ushort) parameterIndex, SQL_PARAM_INPUT, 1139 param.bindType, param.dbtype, 0, 0, param.data.ptr, 1140 cast(int) param.data.length, null); 1141 } 1142 1143 override void setFloat(int parameterIndex, float x) 1144 { 1145 bindParam(parameterIndex, x); 1146 } 1147 1148 override void setDouble(int parameterIndex, double x) 1149 { 1150 bindParam(parameterIndex, x); 1151 } 1152 1153 override void setBoolean(int parameterIndex, bool x) 1154 { 1155 bindParam(parameterIndex, x); 1156 } 1157 1158 override void setLong(int parameterIndex, long x) 1159 { 1160 bindParam(parameterIndex, x); 1161 } 1162 1163 override void setUlong(int parameterIndex, ulong x) 1164 { 1165 bindParam(parameterIndex, x); 1166 } 1167 1168 override void setInt(int parameterIndex, int x) 1169 { 1170 bindParam(parameterIndex, x); 1171 } 1172 1173 override void setUint(int parameterIndex, uint x) 1174 { 1175 bindParam(parameterIndex, x); 1176 } 1177 1178 override void setShort(int parameterIndex, short x) 1179 { 1180 bindParam(parameterIndex, x); 1181 } 1182 1183 override void setUshort(int parameterIndex, ushort x) 1184 { 1185 bindParam(parameterIndex, x); 1186 } 1187 1188 override void setByte(int parameterIndex, byte x) 1189 { 1190 bindParam(parameterIndex, x); 1191 } 1192 1193 override void setUbyte(int parameterIndex, ubyte x) 1194 { 1195 bindParam(parameterIndex, x); 1196 } 1197 1198 override void setBytes(int parameterIndex, byte[] x) 1199 { 1200 bindParam(parameterIndex, x); 1201 } 1202 1203 override void setUbytes(int parameterIndex, ubyte[] x) 1204 { 1205 bindParam(parameterIndex, cast(byte[]) cast(void[]) x); 1206 } 1207 1208 override void setString(int parameterIndex, string x) 1209 { 1210 bindParam(parameterIndex, x.dup); 1211 } 1212 1213 // todo: handle timezone 1214 override void setSysTime(int parameterIndex, SysTime x) { 1215 bindParam(parameterIndex, SQL_TIMESTAMP_STRUCT(x.year, x.month, 1216 x.day, x.hour, x.minute, x.second, to!ushort(x.fracSecs.total!"msecs"))); // msecs, usecs, or hnsecs 1217 } 1218 1219 override void setDateTime(int parameterIndex, DateTime x) 1220 { 1221 bindParam(parameterIndex, SQL_TIMESTAMP_STRUCT(x.year, x.month, 1222 x.day, x.hour, x.minute, x.second, 0)); 1223 } 1224 1225 override void setDate(int parameterIndex, Date x) 1226 { 1227 bindParam(parameterIndex, SQL_DATE_STRUCT(x.year, x.month, x.day)); 1228 } 1229 1230 override void setTime(int parameterIndex, TimeOfDay x) 1231 { 1232 bindParam(parameterIndex, SQL_TIME_STRUCT(x.hour, x.minute, x.second)); 1233 } 1234 1235 override void setVariant(int parameterIndex, Variant x) 1236 { 1237 if (x.type == typeid(float)) 1238 setFloat(parameterIndex, x.get!(float)); 1239 else if (x.type == typeid(double)) 1240 setDouble(parameterIndex, x.get!(double)); 1241 else if (x.type == typeid(bool)) 1242 setBoolean(parameterIndex, x.get!(bool)); 1243 else if (x.type == typeid(long)) 1244 setLong(parameterIndex, x.get!(long)); 1245 else if (x.type == typeid(ulong)) 1246 setUlong(parameterIndex, x.get!(ulong)); 1247 else if (x.type == typeid(int)) 1248 setInt(parameterIndex, x.get!(int)); 1249 else if (x.type == typeid(uint)) 1250 setUint(parameterIndex, x.get!(uint)); 1251 else if (x.type == typeid(short)) 1252 setShort(parameterIndex, x.get!(short)); 1253 else if (x.type == typeid(ushort)) 1254 setUshort(parameterIndex, x.get!(ushort)); 1255 else if (x.type == typeid(byte)) 1256 setByte(parameterIndex, x.get!(byte)); 1257 else if (x.type == typeid(ubyte)) 1258 setUbyte(parameterIndex, x.get!(ubyte)); 1259 else if (x.type == typeid(byte[])) 1260 setBytes(parameterIndex, x.get!(byte[])); 1261 else if (x.type == typeid(ubyte[])) 1262 setUbytes(parameterIndex, x.get!(ubyte[])); 1263 else if (x.type == typeid(string)) 1264 setString(parameterIndex, x.get!(string)); 1265 else if (x.type == typeid(DateTime)) 1266 setDateTime(parameterIndex, x.get!(DateTime)); 1267 else if (x.type == typeid(Date)) 1268 setDate(parameterIndex, x.get!(Date)); 1269 else if (x.type == typeid(TimeOfDay)) 1270 setTime(parameterIndex, x.get!(TimeOfDay)); 1271 else 1272 throw new SQLException("Type inside variant is not supported!"); 1273 1274 } 1275 1276 override void setNull(int parameterIndex) 1277 { 1278 throw new SQLException("Method not implemented"); 1279 } 1280 1281 override void setNull(int parameterIndex, int sqlType) 1282 { 1283 throw new SQLException("Method not implemented"); 1284 } 1285 1286 override string toString() { 1287 return this.query; 1288 } 1289 } 1290 1291 class ODBCResultSet : ResultSetImpl 1292 { 1293 private: 1294 ODBCStatement stmt; 1295 ResultSetMetaData metadata; 1296 bool closed; 1297 int currentRowIndex; 1298 int[string] columnMap; 1299 bool lastIsNull; 1300 1301 bool _hasRows; 1302 bool _isLastRow; 1303 1304 ODBCStatement.ColumnInfo[string] colsByName; 1305 1306 void checkClosed() 1307 { 1308 if (closed) 1309 throw new SQLException("Result set is already closed"); 1310 } 1311 1312 public: 1313 1314 void lock() 1315 { 1316 stmt.lock(); 1317 } 1318 1319 void unlock() 1320 { 1321 stmt.unlock(); 1322 } 1323 1324 this(ODBCStatement stmt) 1325 { 1326 this.stmt = stmt; 1327 1328 _hasRows = true; //stmt.fetch(); 1329 _isLastRow = false; 1330 1331 ColumnMetadataItem[] items; 1332 items.length = stmt.cols.length; 1333 1334 foreach (i, col; stmt.cols) 1335 { 1336 colsByName[col.name] = col; 1337 items[i] = new ColumnMetadataItem(); 1338 items[i].catalogName = stmt.conn.getCatalog(); 1339 items[i].name = col.name; 1340 items[i].label = col.name; 1341 items[i].type = col.dataType.fromODBCType(); 1342 items[i].typeName = (cast(SqlType) items[i].type).to!(string); 1343 items[i].isNullable = col.nullAble == SQL_NULLABLE; 1344 1345 debug sharedLog.tracef("Column meta data: catalogName='%s', name='%s', typeName='%s'", items[i].catalogName, items[i].name, items[i].typeName); 1346 } 1347 1348 metadata = new ResultSetMetaDataImpl(items); 1349 1350 } 1351 1352 void onStatementClosed() 1353 { 1354 closed = true; 1355 } 1356 1357 string decodeTextBlob(ubyte[] data) 1358 { 1359 char[] res = new char[data.length]; 1360 foreach (i, ch; data) 1361 { 1362 res[i] = cast(char) ch; 1363 } 1364 return to!string(res); 1365 } 1366 1367 // ResultSet interface implementation 1368 1369 //Retrieves the number, types and properties of this ResultSet object's columns 1370 override ResultSetMetaData getMetaData() 1371 { 1372 checkClosed(); 1373 lock(); 1374 scope (exit) 1375 unlock(); 1376 return metadata; 1377 } 1378 1379 override void close() 1380 { 1381 checkClosed(); 1382 lock(); 1383 scope (exit) 1384 unlock(); 1385 stmt.closeResultSet(); 1386 closed = true; 1387 } 1388 1389 override bool first() 1390 { 1391 /*checkClosed(); 1392 lock(); 1393 scope (exit) 1394 unlock(); 1395 currentRowIndex = 0; 1396 1397 return check(SQLFetchScroll(stmt.stmt, SQL_FETCH_FIRST, 0), stmt.stmt, SQL_HANDLE_STMT) != SQL_NO_DATA;*/ 1398 1399 throw new SQLException("Method not implemented"); 1400 1401 } 1402 1403 override bool isFirst() 1404 { 1405 checkClosed(); 1406 lock(); 1407 scope (exit) 1408 unlock(); 1409 return _hasRows && currentRowIndex == 0; 1410 } 1411 1412 override bool isLast() 1413 { 1414 checkClosed(); 1415 lock(); 1416 scope (exit) 1417 unlock(); 1418 return _hasRows && _isLastRow; 1419 } 1420 1421 override bool next() 1422 { 1423 checkClosed(); 1424 lock(); 1425 scope (exit) 1426 unlock(); 1427 1428 if (!stmt.fetch()) 1429 return false; 1430 1431 currentRowIndex++; 1432 return true; 1433 } 1434 1435 override int findColumn(string columnName) 1436 { 1437 checkClosed(); 1438 lock(); 1439 scope (exit) 1440 unlock(); 1441 auto p = (columnName in colsByName); 1442 if (!p) 1443 throw new SQLException("Column " ~ columnName ~ " not found"); 1444 return p.nr; 1445 } 1446 1447 override bool getBoolean(int columnIndex) 1448 { 1449 checkClosed(); 1450 lock(); 1451 scope (exit) 1452 unlock(); 1453 1454 return stmt.getColumn(columnIndex).value.get!(bool); 1455 } 1456 1457 override ubyte getUbyte(int columnIndex) 1458 { 1459 checkClosed(); 1460 lock(); 1461 scope (exit) 1462 unlock(); 1463 1464 return stmt.getColumn(columnIndex).value.get!(ubyte); 1465 } 1466 1467 override byte getByte(int columnIndex) 1468 { 1469 checkClosed(); 1470 lock(); 1471 scope (exit) 1472 unlock(); 1473 1474 return stmt.getColumn(columnIndex).value.get!(byte); 1475 } 1476 1477 override short getShort(int columnIndex) 1478 { 1479 checkClosed(); 1480 lock(); 1481 scope (exit) 1482 unlock(); 1483 1484 return stmt.getColumn(columnIndex).value.get!(short); 1485 } 1486 1487 override ushort getUshort(int columnIndex) 1488 { 1489 checkClosed(); 1490 lock(); 1491 scope (exit) 1492 unlock(); 1493 1494 return stmt.getColumn(columnIndex).value.get!(ushort); 1495 } 1496 1497 override int getInt(int columnIndex) 1498 { 1499 checkClosed(); 1500 lock(); 1501 scope (exit) 1502 unlock(); 1503 1504 return stmt.getColumn(columnIndex).value.get!(int); 1505 } 1506 1507 override uint getUint(int columnIndex) 1508 { 1509 checkClosed(); 1510 lock(); 1511 scope (exit) 1512 unlock(); 1513 1514 return stmt.getColumn(columnIndex).value.get!(uint); 1515 } 1516 1517 override long getLong(int columnIndex) 1518 { 1519 checkClosed(); 1520 lock(); 1521 scope (exit) 1522 unlock(); 1523 1524 return stmt.getColumn(columnIndex).value.get!(long); 1525 } 1526 1527 override ulong getUlong(int columnIndex) 1528 { 1529 checkClosed(); 1530 lock(); 1531 scope (exit) 1532 unlock(); 1533 1534 return stmt.getColumn(columnIndex).value.get!(ulong); 1535 } 1536 1537 override double getDouble(int columnIndex) 1538 { 1539 checkClosed(); 1540 lock(); 1541 scope (exit) 1542 unlock(); 1543 1544 return stmt.getColumn(columnIndex).value.get!(double); 1545 } 1546 1547 override float getFloat(int columnIndex) 1548 { 1549 checkClosed(); 1550 lock(); 1551 scope (exit) 1552 unlock(); 1553 1554 return stmt.getColumn(columnIndex).value.get!(float); 1555 } 1556 1557 private Type getArray(Type)(int columnIndex) 1558 { 1559 checkClosed(); 1560 lock(); 1561 scope (exit) 1562 unlock(); 1563 1564 auto val = stmt.getColumn(columnIndex).value; 1565 if (!val.hasValue) 1566 return cast(Type)null; 1567 else 1568 return val.get!(Type); 1569 } 1570 1571 override byte[] getBytes(int columnIndex) 1572 { 1573 return getArray!(byte[])(columnIndex); 1574 1575 //return stmt.getColumn(columnIndex).value.get!(byte[]); 1576 } 1577 1578 override ubyte[] getUbytes(int columnIndex) 1579 { 1580 return getArray!(ubyte[])(columnIndex); 1581 } 1582 1583 override string getString(int columnIndex) 1584 { 1585 return stmt.getColumn(columnIndex).value.get!(string); 1586 } 1587 1588 override SysTime getSysTime(int columnIndex) 1589 { 1590 Variant v = stmt.getColumn(columnIndex).value; 1591 if (v.peek!(SysTime) is null) { 1592 return Clock.currTime(); 1593 } 1594 1595 if (v.convertsTo!(SysTime)) { 1596 return v.get!(SysTime); 1597 } 1598 throw new SQLException("Cannot convert '" ~ v.toString() ~ "' to SysTime"); 1599 } 1600 1601 override DateTime getDateTime(int columnIndex) 1602 { 1603 Variant v = stmt.getColumn(columnIndex).value; 1604 if (v.peek!(DateTime) is null) { 1605 return cast(DateTime) Clock.currTime(); 1606 } 1607 1608 if (v.convertsTo!(DateTime)) { 1609 return v.get!(DateTime); 1610 } 1611 throw new SQLException("Cannot convert '" ~ v.toString() ~ "' to DateTime"); 1612 } 1613 1614 override Date getDate(int columnIndex) 1615 { 1616 checkClosed(); 1617 lock(); 1618 scope (exit) 1619 unlock(); 1620 1621 return stmt.getColumn(columnIndex).value.get!(Date); 1622 } 1623 1624 override TimeOfDay getTime(int columnIndex) 1625 { 1626 checkClosed(); 1627 lock(); 1628 scope (exit) 1629 unlock(); 1630 1631 return stmt.getColumn(columnIndex).value.get!(TimeOfDay); 1632 } 1633 1634 override Variant getVariant(int columnIndex) 1635 { 1636 checkClosed(); 1637 lock(); 1638 scope (exit) 1639 unlock(); 1640 1641 return stmt.getColumn(columnIndex).value; 1642 } 1643 1644 override bool wasNull() 1645 { 1646 checkClosed(); 1647 lock(); 1648 scope (exit) 1649 unlock(); 1650 return lastIsNull; 1651 } 1652 1653 override bool isNull(int columnIndex) 1654 { 1655 return stmt.getColumn(columnIndex).isNull(); 1656 } 1657 1658 //Retrieves the Statement object that produced this ResultSet object. 1659 override Statement getStatement() 1660 { 1661 checkClosed(); 1662 lock(); 1663 scope (exit) 1664 unlock(); 1665 return stmt; 1666 } 1667 1668 //Retrieves the current row number 1669 override int getRow() 1670 { 1671 checkClosed(); 1672 lock(); 1673 scope (exit) unlock(); 1674 1675 return this.currentRowIndex; 1676 } 1677 1678 } 1679 1680 // sample URL: 1681 // odbc://localhost:1433/DatabaseName 1682 class ODBCDriver : Driver 1683 { 1684 // returns a string on the format: 1685 // odbc://localhost,1433?user=sa,password=Ser3tP@ssw0rd,driver=FreeTDS 1686 public static string generateUrl(string host = "localhost", ushort port = 1433, string[string] params = null) 1687 { 1688 import std.array : byPair; 1689 import std.algorithm.iteration : joiner; 1690 return "odbc://" ~ host ~ "," ~ to!string(port) ~ ( (params is null)? "" : "?" ~ to!string(joiner(params.byPair.map!(p => p.key ~ "=" ~ p.value), ","))); 1691 } 1692 1693 public static string[string] setUserAndPassword(string username, string password) 1694 { 1695 string[string] params; 1696 params["user"] = username; 1697 params["password"] = password; 1698 return params; 1699 } 1700 1701 override ddbc.core.Connection connect(string url, string[string] params) 1702 { 1703 //writeln("ODBCDriver.connect " ~ url); 1704 return new ODBCConnection(url, params); 1705 } 1706 } 1707 1708 unittest 1709 { 1710 static if (ODBC_TESTS_ENABLED) 1711 { 1712 1713 import std.conv; 1714 1715 DataSource ds = createUnitTestODBCDataSource(); 1716 1717 auto conn = ds.getConnection(); 1718 scope (exit) 1719 conn.close(); 1720 auto stmt = conn.createStatement(); 1721 scope (exit) 1722 stmt.close(); 1723 1724 //assert(stmt.executeUpdate("CREATE DATABASE testdb") == -1); 1725 //assert(stmt.executeUpdate("USE testdb") == -1); 1726 1727 assert(stmt.executeUpdate( 1728 "IF OBJECT_ID('ddbct1', 'U') IS NOT NULL DROP TABLE ddbct1") == -1); 1729 1730 // Some Databases has `not null` as default. 1731 assert(stmt.executeUpdate("CREATE TABLE ddbct1 ( " ~ "id int not null primary key, " 1732 ~ "name varchar(250) null, " ~ "comment varchar(max) null, " ~ "ts datetime null)") == -1); 1733 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name, comment, ts) VALUES(1, 'name1dfgdfg', 'comment for line 1', '2017-02-03T12:30:25' )") == 1); 1734 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name, comment) VALUES" 1735 ~ "(2, 'name2', 'comment for line 2 - can be very long'), " 1736 ~ "(3, 'name3', 'this is line 3')") == 2); 1737 1738 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name) VALUES (4, 'name4')") == 1); 1739 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, comment) VALUES(5, '')") == 1); 1740 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name) VALUES(6, '')") == 1); 1741 assert(stmt.executeUpdate("UPDATE ddbct1 SET name= name + '_x' WHERE id IN (3, 4)") == 2); 1742 1743 PreparedStatement ps = conn.prepareStatement("UPDATE ddbct1 SET name=? WHERE id=?"); 1744 //ps.setString(1, null); 1745 ps.setString(1, "null"); 1746 ps.setLong(2, 3); 1747 assert(ps.executeUpdate() == 1); 1748 1749 auto rs = stmt.executeQuery("SELECT id, name name_alias, comment, ts FROM ddbct1 ORDER BY id"); 1750 1751 // testing result set meta data 1752 ResultSetMetaData meta = rs.getMetaData(); 1753 assert(meta.getColumnCount() == 4); 1754 assert(meta.getColumnName(1) == "id"); 1755 assert(meta.getColumnLabel(1) == "id"); 1756 assert(meta.isNullable(1) == false); 1757 assert(meta.isNullable(2) == true); 1758 assert(meta.isNullable(3) == true); 1759 assert(meta.getColumnName(2) == "name_alias"); 1760 assert(meta.getColumnLabel(2) == "name_alias"); 1761 assert(meta.getColumnName(3) == "comment"); 1762 1763 //writeln("type: ", meta.getColumnTypeName(1)); 1764 //writeln("type: ", meta.getColumnTypeName(2)); 1765 //writeln("type: ", meta.getColumnTypeName(3)); 1766 //writeln("type: ", meta.getColumnTypeName(4)); 1767 1768 // not supported 1769 //int rowCount = rs.getFetchSize(); 1770 //assert(rowCount == 6); 1771 int index = 1; 1772 while (rs.next()) 1773 { 1774 assert(!rs.isNull(1)); 1775 //ubyte[] bytes = rs.getUbytes(3); 1776 //int rowIndex = rs.getRow(); 1777 //writeln("row = ", rs.getRow()); 1778 //assert(rowIndex == index); 1779 1780 // BUG: the Type is defined as `BIGINT` but is read as double on some platforms insted of long! `INT` works with getLong()! 1781 // long id = rs.getLong(1); 1782 long id = rs.getDouble(1).to!long; 1783 1784 //writeln("id = ", id); 1785 1786 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1787 assert(id == index); 1788 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1789 //writeln("field3 = '" ~ rs.getString(3) ~ "'"); 1790 //writeln("wasNull = " ~ to!string(rs.wasNull())); 1791 if (id == 1) 1792 { 1793 DateTime ts = rs.getDateTime(4); 1794 assert(ts == DateTime(2017, 02, 03, 12, 30, 25)); 1795 } 1796 if (id == 4) 1797 { 1798 assert(rs.getString(2) == "name4_x"); 1799 assert(rs.isNull(3)); 1800 } 1801 if (id == 5) 1802 { 1803 assert(rs.isNull(2)); 1804 assert(!rs.isNull(3)); 1805 } 1806 if (id == 6) 1807 { 1808 assert(!rs.isNull(2)); 1809 assert(rs.isNull(3)); 1810 } 1811 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3)) ~ "\t[" ~ to!string(bytes.length) ~ "]"); 1812 index++; 1813 } 1814 1815 PreparedStatement ps2 = conn.prepareStatement( 1816 "SELECT id, name, comment FROM ddbct1 WHERE id >= ?"); 1817 scope (exit) 1818 ps2.close(); 1819 ps2.setLong(1, 3); 1820 rs = ps2.executeQuery(); 1821 while (rs.next()) 1822 { 1823 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3))); 1824 index++; 1825 } 1826 1827 // checking last insert ID for prepared statement 1828 PreparedStatement ps3 = conn.prepareStatement( 1829 "INSERT INTO ddbct1 (id, name) values (7, 'New String 1')"); 1830 scope (exit) 1831 ps3.close(); 1832 Variant newId; 1833 // does not work! 1834 //assert(ps3.executeUpdate(newId) == 1); 1835 //writeln("Generated insert id = " ~ newId.toString()); 1836 //assert(newId.get!ulong > 0); 1837 1838 // checking last insert ID for normal statement 1839 Statement stmt4 = conn.createStatement(); 1840 scope (exit) 1841 stmt4.close(); 1842 Variant newId2; 1843 // does not work! 1844 //assert(stmt.executeUpdate("INSERT INTO ddbct1 (id, name) values (8, 'New String 2')", newId2) == 1); 1845 //writeln("Generated insert id = " ~ newId2.toString()); 1846 //assert(newId2.get!ulong > 0); 1847 1848 } 1849 } 1850 1851 __gshared static this() 1852 { 1853 // register ODBCDriver 1854 import ddbc.common; 1855 1856 DriverFactory.registerDriverFactory("odbc", delegate() { 1857 return new ODBCDriver(); 1858 }); 1859 } 1860 1861 } 1862 else 1863 { // version(USE_ODBC) 1864 version (unittest) 1865 { 1866 immutable bool ODBC_TESTS_ENABLED = false; 1867 } 1868 }