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