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 777 return hasData; 778 } 779 780 class ColumnInfo 781 { 782 ushort nr; 783 string name; 784 short dataType; 785 short nullAble; 786 787 Variant value; 788 789 this(int nr) 790 { 791 792 this.nr = cast(short) nr; 793 794 short nameLen = 1000; 795 char[1000] nameBuff; 796 797 // BUG: SQLDescribeCol does not return the length of the of the column-name! 798 /*checkstmt!SQLDescribeCol(stmt, this.nr, null, 799 0, &nameLen, &this.dataType, null, null, &this.nullAble); 800 nameLen += 1; 801 nameBuff.length = nameLen;*/ 802 803 checkstmt!SQLDescribeCol(stmt, this.nr, nameBuff.ptr, 804 nameLen, null, &this.dataType, null, null, &this.nullAble); 805 806 this.name = fromStringz(nameBuff.ptr).idup; 807 } 808 809 void read() 810 { 811 value = readValueAsVariant(); 812 } 813 814 bool isNull() 815 { 816 return !value.hasValue(); //testNull == SQL_NULL_DATA; 817 } 818 819 Variant readValue(T)() 820 if (!isArray!(T) && !is(TypeToCIdentifier!(T) == void)) 821 { 822 T val; 823 824 int nullCheck = 0; 825 826 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), &val, 0, &nullCheck); 827 828 if (nullCheck == SQL_NULL_DATA) 829 return Variant(); 830 831 return Variant(val); 832 } 833 834 Variant readValue(T)() 835 if (isArray!(T) && !is(TypeToCIdentifier!(T) == void)) 836 { 837 T val; 838 int len = 0; 839 840 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), &val, 0, &len); 841 842 if (len == SQL_NULL_DATA) 843 return Variant(); 844 845 846 // A char-array contains a null-termination. 847 static if (is(T == char[])) 848 len += 1; 849 850 val.length = len; 851 852 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), val.ptr, len, null); 853 854 // A char-array contains a null-termination. 855 static if (is(T == char[])) 856 val = val[0 .. ($ - 1)]; 857 858 static if(is(T == char[])) 859 return Variant(val.idup); 860 else 861 return Variant(val); 862 } 863 864 Variant readValue(T)() if (is(T == SysTime)) 865 { 866 auto val = readValue!(SQL_TIMESTAMP_STRUCT); 867 868 if (val.type == typeid(SQL_TIMESTAMP_STRUCT)) 869 { 870 auto s = val.get!(SQL_TIMESTAMP_STRUCT); 871 import core.time : nsecs; 872 import std.datetime.timezone : UTC; 873 //writefln("%s-%s-%s %s:%s:%s.%s", s.year, s.month, s.day, s.hour, s.minute, s.second, s.fraction); 874 return Variant(SysTime( 875 DateTime(s.year, s.month, s.day, s.hour, s.minute, s.second), 876 nsecs(s.fraction), 877 UTC() 878 )); 879 } 880 return Variant(); 881 } 882 883 Variant readValue(T)() if (is(T == DateTime)) 884 { 885 auto val = readValue!(SQL_TIMESTAMP_STRUCT); 886 887 if (val.type == typeid(SQL_TIMESTAMP_STRUCT)) 888 { 889 auto s = val.get!(SQL_TIMESTAMP_STRUCT); 890 return Variant(DateTime(s.year, s.month, s.day, s.hour, s.minute, s.second)); 891 } 892 return Variant(); 893 } 894 895 Variant readValue(T)() if (is(T == Date)) 896 { 897 auto val = readValue!(SQL_DATE_STRUCT); 898 899 if (val.type == typeid(SQL_DATE_STRUCT)) 900 { 901 auto s = val.get!(SQL_DATE_STRUCT); 902 return Variant(Date(s.year, s.month, s.day)); 903 } 904 return Variant(); 905 } 906 907 Variant readValue(T)() if (is(T == TimeOfDay)) 908 { 909 auto val = readValue!(SQL_TIME_STRUCT); 910 911 if (val.type == typeid(SQL_TIME_STRUCT)) 912 { 913 auto s = val.get!(SQL_TIME_STRUCT); 914 return Variant(TimeOfDay(s.hour, s.minute, s.second)); 915 } 916 return Variant(); 917 } 918 919 Variant readValueAsVariant() 920 { 921 // dfmt off 922 switch (this.dataType) 923 { 924 case SQL_TINYINT: return readValue!(byte); 925 case SQL_SMALLINT: return readValue!(short); 926 case SQL_INTEGER: return readValue!(int); 927 case SQL_BIGINT: return readValue!(long); 928 929 case SQL_REAL: return readValue!(float); 930 case SQL_FLOAT: return readValue!(double); 931 case SQL_DOUBLE: return readValue!(double); 932 933 case SQL_CHAR: return readValue!(char[]); 934 case SQL_VARCHAR: return readValue!(char[]); 935 case SQL_LONGVARCHAR: return readValue!(char[]); 936 case SQL_WCHAR: return readValue!(wchar[]); 937 case SQL_WVARCHAR: return readValue!(wchar[]); 938 case SQL_WLONGVARCHAR: return readValue!(wchar[]); 939 case SQL_BINARY: return readValue!(byte[]); 940 case SQL_VARBINARY: return readValue!(byte[]); 941 case SQL_LONGVARBINARY: return readValue!(byte[]); 942 943 case SQL_NUMERIC: return readValue!(SQL_NUMERIC_STRUCT); 944 case SQL_TYPE_DATE: return readValue!(Date); 945 case SQL_TYPE_TIME: return readValue!(TimeOfDay); 946 case SQL_TYPE_TIMESTAMP: return readValue!(DateTime); 947 case -155: return readValue!(SysTime); // DATETIMEOFFSET 948 //case SQL_GUID: return Variant(readValue!(SQLGUID)); 949 950 default: 951 throw new Exception(text("TYPE ", this.dataType, " is currently not supported!")); 952 } 953 // dfmt on 954 } 955 } 956 } 957 958 class ODBCPreparedStatement : ODBCStatement, PreparedStatement 959 { 960 string query; 961 int paramCount; 962 ResultSetMetaData metadata; 963 ParameterMetaData paramMetadata; 964 965 Parameter[] params; 966 967 this(ODBCConnection conn, string query) 968 { 969 super(conn); 970 this.query = query; 971 try 972 { 973 checkstmt!SQLPrepare(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 974 SQLSMALLINT v = 0; 975 checkstmt!SQLNumParams(stmt, &v); 976 paramCount = v; 977 params.length = v; 978 } 979 catch (Throwable e) 980 { 981 throw new SQLException(e); 982 } 983 } 984 985 void checkIndex(int index) 986 { 987 if (index < 1 || index > paramCount) 988 throw new SQLException("Parameter index " ~ to!string(index) ~ " is out of range"); 989 } 990 991 public: 992 993 /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. 994 override ResultSetMetaData getMetaData() 995 { 996 return metadata; 997 } 998 999 /// Retrieves the number, types and properties of this PreparedStatement object's parameters. 1000 override ParameterMetaData getParameterMetaData() 1001 { 1002 throw new SQLException("Method not implemented"); 1003 } 1004 1005 override int executeUpdate() 1006 { 1007 checkClosed(); 1008 lock(); 1009 scope (exit) 1010 unlock(); 1011 1012 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 1013 sharedLog.trace(stmt); 1014 } 1015 1016 try 1017 { 1018 1019 checkstmt!SQLExecute(stmt); 1020 1021 int rowsAffected = 0; 1022 checkstmt!SQLRowCount(stmt, &rowsAffected); 1023 return rowsAffected; 1024 } 1025 catch (Throwable e) 1026 { 1027 throw new SQLException(e); 1028 } 1029 } 1030 1031 override int executeUpdate(out Variant insertId) 1032 { 1033 checkClosed(); 1034 lock(); 1035 scope (exit) 1036 unlock(); 1037 1038 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 1039 sharedLog.trace(stmt); 1040 } 1041 1042 try 1043 { 1044 checkstmt!SQLExecute(stmt); 1045 1046 int rowsAffected = 0; 1047 checkstmt!SQLRowCount(stmt, &rowsAffected); 1048 1049 checkstmt!SQLExecDirect(stmt, 1050 cast(SQLCHAR*) toStringz(`SELECT SCOPE_IDENTITY()`), SQL_NTS); 1051 1052 bind(); 1053 fetch(); 1054 insertId = getColumn(1).value; 1055 return rowsAffected; 1056 } 1057 catch (Throwable e) 1058 { 1059 throw new SQLException(e); 1060 } 1061 } 1062 1063 override ddbc.core.ResultSet executeQuery() 1064 { 1065 checkClosed(); 1066 lock(); 1067 scope (exit) 1068 unlock(); 1069 1070 static if(__traits(compiles, (){ import std.experimental.logger; } )) { 1071 sharedLog.trace(stmt); 1072 } 1073 1074 try 1075 { 1076 checkstmt!SQLExecute(stmt); 1077 bind(); 1078 resultSet = new ODBCResultSet(this); 1079 return resultSet; 1080 } 1081 catch (Throwable e) 1082 { 1083 throw new SQLException(e); 1084 } 1085 } 1086 1087 override void clearParameters() 1088 { 1089 checkClosed(); 1090 lock(); 1091 scope (exit) 1092 unlock(); 1093 try 1094 { 1095 for (int i = 1; i <= paramCount; i++) 1096 setNull(i); 1097 } 1098 catch (Throwable e) 1099 { 1100 throw new SQLException(e); 1101 } 1102 } 1103 1104 struct Parameter 1105 { 1106 SQLSMALLINT bindType; 1107 SQLSMALLINT dbtype; 1108 1109 void[] data; 1110 } 1111 1112 void bindParam(T)(int parameterIndex, T x) 1113 { 1114 1115 checkClosed(); 1116 lock(); 1117 scope (exit) 1118 unlock(); 1119 checkIndex(parameterIndex); 1120 1121 auto param = ¶ms[parameterIndex - 1]; 1122 1123 static if (is(T == char[])) 1124 param.data = cast(void[]) (x ~ '\0'); 1125 else static if (isArray!(T)) 1126 param.data = cast(void[]) x; 1127 else 1128 param.data = cast(void[])[x]; 1129 param.bindType = TypeToCIdentifier!(T); 1130 param.dbtype = ctypeToSQLType(TypeToCIdentifier!(T)); 1131 1132 1133 1134 SQLBindParameter(stmt, cast(ushort) parameterIndex, SQL_PARAM_INPUT, 1135 param.bindType, param.dbtype, 0, 0, param.data.ptr, 1136 cast(int) param.data.length, null); 1137 } 1138 1139 override void setFloat(int parameterIndex, float x) 1140 { 1141 bindParam(parameterIndex, x); 1142 } 1143 1144 override void setDouble(int parameterIndex, double x) 1145 { 1146 bindParam(parameterIndex, x); 1147 } 1148 1149 override void setBoolean(int parameterIndex, bool x) 1150 { 1151 bindParam(parameterIndex, x); 1152 } 1153 1154 override void setLong(int parameterIndex, long x) 1155 { 1156 bindParam(parameterIndex, x); 1157 } 1158 1159 override void setUlong(int parameterIndex, ulong x) 1160 { 1161 bindParam(parameterIndex, x); 1162 } 1163 1164 override void setInt(int parameterIndex, int x) 1165 { 1166 bindParam(parameterIndex, x); 1167 } 1168 1169 override void setUint(int parameterIndex, uint x) 1170 { 1171 bindParam(parameterIndex, x); 1172 } 1173 1174 override void setShort(int parameterIndex, short x) 1175 { 1176 bindParam(parameterIndex, x); 1177 } 1178 1179 override void setUshort(int parameterIndex, ushort x) 1180 { 1181 bindParam(parameterIndex, x); 1182 } 1183 1184 override void setByte(int parameterIndex, byte x) 1185 { 1186 bindParam(parameterIndex, x); 1187 } 1188 1189 override void setUbyte(int parameterIndex, ubyte x) 1190 { 1191 bindParam(parameterIndex, x); 1192 } 1193 1194 override void setBytes(int parameterIndex, byte[] x) 1195 { 1196 bindParam(parameterIndex, x); 1197 } 1198 1199 override void setUbytes(int parameterIndex, ubyte[] x) 1200 { 1201 bindParam(parameterIndex, cast(byte[]) cast(void[]) x); 1202 } 1203 1204 override void setString(int parameterIndex, string x) 1205 { 1206 bindParam(parameterIndex, x.dup); 1207 } 1208 1209 // todo: handle timezone 1210 override void setSysTime(int parameterIndex, SysTime x) { 1211 bindParam(parameterIndex, SQL_TIMESTAMP_STRUCT(x.year, x.month, 1212 x.day, x.hour, x.minute, x.second, to!ushort(x.fracSecs.total!"msecs"))); // msecs, usecs, or hnsecs 1213 } 1214 1215 override void setDateTime(int parameterIndex, DateTime x) 1216 { 1217 bindParam(parameterIndex, SQL_TIMESTAMP_STRUCT(x.year, x.month, 1218 x.day, x.hour, x.minute, x.second, 0)); 1219 } 1220 1221 override void setDate(int parameterIndex, Date x) 1222 { 1223 bindParam(parameterIndex, SQL_DATE_STRUCT(x.year, x.month, x.day)); 1224 } 1225 1226 override void setTime(int parameterIndex, TimeOfDay x) 1227 { 1228 bindParam(parameterIndex, SQL_TIME_STRUCT(x.hour, x.minute, x.second)); 1229 } 1230 1231 override void setVariant(int parameterIndex, Variant x) 1232 { 1233 if (x.type == typeid(float)) 1234 setFloat(parameterIndex, x.get!(float)); 1235 else if (x.type == typeid(double)) 1236 setDouble(parameterIndex, x.get!(double)); 1237 else if (x.type == typeid(bool)) 1238 setBoolean(parameterIndex, x.get!(bool)); 1239 else if (x.type == typeid(long)) 1240 setLong(parameterIndex, x.get!(long)); 1241 else if (x.type == typeid(ulong)) 1242 setUlong(parameterIndex, x.get!(ulong)); 1243 else if (x.type == typeid(int)) 1244 setInt(parameterIndex, x.get!(int)); 1245 else if (x.type == typeid(uint)) 1246 setUint(parameterIndex, x.get!(uint)); 1247 else if (x.type == typeid(short)) 1248 setShort(parameterIndex, x.get!(short)); 1249 else if (x.type == typeid(ushort)) 1250 setUshort(parameterIndex, x.get!(ushort)); 1251 else if (x.type == typeid(byte)) 1252 setByte(parameterIndex, x.get!(byte)); 1253 else if (x.type == typeid(ubyte)) 1254 setUbyte(parameterIndex, x.get!(ubyte)); 1255 else if (x.type == typeid(byte[])) 1256 setBytes(parameterIndex, x.get!(byte[])); 1257 else if (x.type == typeid(ubyte[])) 1258 setUbytes(parameterIndex, x.get!(ubyte[])); 1259 else if (x.type == typeid(string)) 1260 setString(parameterIndex, x.get!(string)); 1261 else if (x.type == typeid(DateTime)) 1262 setDateTime(parameterIndex, x.get!(DateTime)); 1263 else if (x.type == typeid(Date)) 1264 setDate(parameterIndex, x.get!(Date)); 1265 else if (x.type == typeid(TimeOfDay)) 1266 setTime(parameterIndex, x.get!(TimeOfDay)); 1267 else 1268 throw new SQLException("Type inside variant is not supported!"); 1269 1270 } 1271 1272 override void setNull(int parameterIndex) 1273 { 1274 throw new SQLException("Method not implemented"); 1275 } 1276 1277 override void setNull(int parameterIndex, int sqlType) 1278 { 1279 throw new SQLException("Method not implemented"); 1280 } 1281 1282 override string toString() { 1283 return this.query; 1284 } 1285 } 1286 1287 class ODBCResultSet : ResultSetImpl 1288 { 1289 private: 1290 ODBCStatement stmt; 1291 ResultSetMetaData metadata; 1292 bool closed; 1293 int currentRowIndex; 1294 int[string] columnMap; 1295 bool lastIsNull; 1296 1297 bool _hasRows; 1298 bool _isLastRow; 1299 1300 ODBCStatement.ColumnInfo[string] colsByName; 1301 1302 void checkClosed() 1303 { 1304 if (closed) 1305 throw new SQLException("Result set is already closed"); 1306 } 1307 1308 public: 1309 1310 void lock() 1311 { 1312 stmt.lock(); 1313 } 1314 1315 void unlock() 1316 { 1317 stmt.unlock(); 1318 } 1319 1320 this(ODBCStatement stmt) 1321 { 1322 this.stmt = stmt; 1323 1324 _hasRows = true; //stmt.fetch(); 1325 _isLastRow = false; 1326 1327 ColumnMetadataItem[] items; 1328 items.length = stmt.cols.length; 1329 1330 foreach (i, col; stmt.cols) 1331 { 1332 colsByName[col.name] = col; 1333 items[i] = new ColumnMetadataItem(); 1334 items[i].catalogName = stmt.conn.getCatalog(); 1335 items[i].name = col.name; 1336 items[i].label = col.name; 1337 items[i].type = col.dataType.fromODBCType(); 1338 items[i].typeName = (cast(SqlType) items[i].type).to!(string); 1339 items[i].isNullable = col.nullAble == SQL_NULLABLE; 1340 1341 debug sharedLog.tracef("Column meta data: catalogName='%s', name='%s', typeName='%s'", items[i].catalogName, items[i].name, items[i].typeName); 1342 } 1343 1344 metadata = new ResultSetMetaDataImpl(items); 1345 1346 } 1347 1348 void onStatementClosed() 1349 { 1350 closed = true; 1351 } 1352 1353 string decodeTextBlob(ubyte[] data) 1354 { 1355 char[] res = new char[data.length]; 1356 foreach (i, ch; data) 1357 { 1358 res[i] = cast(char) ch; 1359 } 1360 return to!string(res); 1361 } 1362 1363 // ResultSet interface implementation 1364 1365 //Retrieves the number, types and properties of this ResultSet object's columns 1366 override ResultSetMetaData getMetaData() 1367 { 1368 checkClosed(); 1369 lock(); 1370 scope (exit) 1371 unlock(); 1372 return metadata; 1373 } 1374 1375 override void close() 1376 { 1377 checkClosed(); 1378 lock(); 1379 scope (exit) 1380 unlock(); 1381 stmt.closeResultSet(); 1382 closed = true; 1383 } 1384 1385 override bool first() 1386 { 1387 /*checkClosed(); 1388 lock(); 1389 scope (exit) 1390 unlock(); 1391 currentRowIndex = 0; 1392 1393 return check(SQLFetchScroll(stmt.stmt, SQL_FETCH_FIRST, 0), stmt.stmt, SQL_HANDLE_STMT) != SQL_NO_DATA;*/ 1394 1395 throw new SQLException("Method not implemented"); 1396 1397 } 1398 1399 override bool isFirst() 1400 { 1401 checkClosed(); 1402 lock(); 1403 scope (exit) 1404 unlock(); 1405 return _hasRows && currentRowIndex == 0; 1406 } 1407 1408 override bool isLast() 1409 { 1410 checkClosed(); 1411 lock(); 1412 scope (exit) 1413 unlock(); 1414 return _hasRows && _isLastRow; 1415 } 1416 1417 override bool next() 1418 { 1419 checkClosed(); 1420 lock(); 1421 scope (exit) 1422 unlock(); 1423 1424 if (!stmt.fetch()) 1425 return false; 1426 1427 currentRowIndex++; 1428 return true; 1429 } 1430 1431 override int findColumn(string columnName) 1432 { 1433 checkClosed(); 1434 lock(); 1435 scope (exit) 1436 unlock(); 1437 auto p = (columnName in colsByName); 1438 if (!p) 1439 throw new SQLException("Column " ~ columnName ~ " not found"); 1440 return p.nr; 1441 } 1442 1443 override bool getBoolean(int columnIndex) 1444 { 1445 checkClosed(); 1446 lock(); 1447 scope (exit) 1448 unlock(); 1449 1450 return stmt.getColumn(columnIndex).value.get!(bool); 1451 } 1452 1453 override ubyte getUbyte(int columnIndex) 1454 { 1455 checkClosed(); 1456 lock(); 1457 scope (exit) 1458 unlock(); 1459 1460 return stmt.getColumn(columnIndex).value.get!(ubyte); 1461 } 1462 1463 override byte getByte(int columnIndex) 1464 { 1465 checkClosed(); 1466 lock(); 1467 scope (exit) 1468 unlock(); 1469 1470 return stmt.getColumn(columnIndex).value.get!(byte); 1471 } 1472 1473 override short getShort(int columnIndex) 1474 { 1475 checkClosed(); 1476 lock(); 1477 scope (exit) 1478 unlock(); 1479 1480 return stmt.getColumn(columnIndex).value.get!(short); 1481 } 1482 1483 override ushort getUshort(int columnIndex) 1484 { 1485 checkClosed(); 1486 lock(); 1487 scope (exit) 1488 unlock(); 1489 1490 return stmt.getColumn(columnIndex).value.get!(ushort); 1491 } 1492 1493 override int getInt(int columnIndex) 1494 { 1495 checkClosed(); 1496 lock(); 1497 scope (exit) 1498 unlock(); 1499 1500 return stmt.getColumn(columnIndex).value.get!(int); 1501 } 1502 1503 override uint getUint(int columnIndex) 1504 { 1505 checkClosed(); 1506 lock(); 1507 scope (exit) 1508 unlock(); 1509 1510 return stmt.getColumn(columnIndex).value.get!(uint); 1511 } 1512 1513 override long getLong(int columnIndex) 1514 { 1515 checkClosed(); 1516 lock(); 1517 scope (exit) 1518 unlock(); 1519 1520 return stmt.getColumn(columnIndex).value.get!(long); 1521 } 1522 1523 override ulong getUlong(int columnIndex) 1524 { 1525 checkClosed(); 1526 lock(); 1527 scope (exit) 1528 unlock(); 1529 1530 return stmt.getColumn(columnIndex).value.get!(ulong); 1531 } 1532 1533 override double getDouble(int columnIndex) 1534 { 1535 checkClosed(); 1536 lock(); 1537 scope (exit) 1538 unlock(); 1539 1540 return stmt.getColumn(columnIndex).value.get!(double); 1541 } 1542 1543 override float getFloat(int columnIndex) 1544 { 1545 checkClosed(); 1546 lock(); 1547 scope (exit) 1548 unlock(); 1549 1550 return stmt.getColumn(columnIndex).value.get!(float); 1551 } 1552 1553 private Type getArray(Type)(int columnIndex) 1554 { 1555 checkClosed(); 1556 lock(); 1557 scope (exit) 1558 unlock(); 1559 1560 auto val = stmt.getColumn(columnIndex).value; 1561 if (!val.hasValue) 1562 return cast(Type)null; 1563 else 1564 return val.get!(Type); 1565 } 1566 1567 override byte[] getBytes(int columnIndex) 1568 { 1569 return getArray!(byte[])(columnIndex); 1570 1571 //return stmt.getColumn(columnIndex).value.get!(byte[]); 1572 } 1573 1574 override ubyte[] getUbytes(int columnIndex) 1575 { 1576 return getArray!(ubyte[])(columnIndex); 1577 } 1578 1579 override string getString(int columnIndex) 1580 { 1581 return stmt.getColumn(columnIndex).value.get!(string); 1582 } 1583 1584 override SysTime getSysTime(int columnIndex) { 1585 checkClosed(); 1586 lock(); 1587 scope (exit) 1588 unlock(); 1589 1590 return stmt.getColumn(columnIndex).value.get!(SysTime); 1591 } 1592 1593 override DateTime getDateTime(int columnIndex) 1594 { 1595 checkClosed(); 1596 lock(); 1597 scope (exit) 1598 unlock(); 1599 1600 return stmt.getColumn(columnIndex).value.get!(DateTime); 1601 } 1602 1603 override Date getDate(int columnIndex) 1604 { 1605 checkClosed(); 1606 lock(); 1607 scope (exit) 1608 unlock(); 1609 1610 return stmt.getColumn(columnIndex).value.get!(Date); 1611 } 1612 1613 override TimeOfDay getTime(int columnIndex) 1614 { 1615 checkClosed(); 1616 lock(); 1617 scope (exit) 1618 unlock(); 1619 1620 return stmt.getColumn(columnIndex).value.get!(TimeOfDay); 1621 } 1622 1623 override Variant getVariant(int columnIndex) 1624 { 1625 checkClosed(); 1626 lock(); 1627 scope (exit) 1628 unlock(); 1629 1630 return stmt.getColumn(columnIndex).value; 1631 } 1632 1633 override bool wasNull() 1634 { 1635 checkClosed(); 1636 lock(); 1637 scope (exit) 1638 unlock(); 1639 return lastIsNull; 1640 } 1641 1642 override bool isNull(int columnIndex) 1643 { 1644 return stmt.getColumn(columnIndex).isNull(); 1645 } 1646 1647 //Retrieves the Statement object that produced this ResultSet object. 1648 override Statement getStatement() 1649 { 1650 checkClosed(); 1651 lock(); 1652 scope (exit) 1653 unlock(); 1654 return stmt; 1655 } 1656 1657 //Retrieves the current row number 1658 override int getRow() 1659 { 1660 checkClosed(); 1661 lock(); 1662 scope (exit) 1663 unlock(); 1664 1665 return currentRowIndex; 1666 } 1667 1668 } 1669 1670 // sample URL: 1671 // odbc://localhost:1433/DatabaseName 1672 class ODBCDriver : Driver 1673 { 1674 // returns a string on the format: 1675 // odbc://localhost,1433?user=sa,password=Ser3tP@ssw0rd,driver=FreeTDS 1676 public static string generateUrl(string host = "localhost", ushort port = 1433, string[string] params = null) 1677 { 1678 import std.array : byPair; 1679 import std.algorithm.iteration : joiner; 1680 return "odbc://" ~ host ~ "," ~ to!string(port) ~ ( (params is null)? "" : "?" ~ to!string(joiner(params.byPair.map!(p => p.key ~ "=" ~ p.value), ","))); 1681 } 1682 1683 public static string[string] setUserAndPassword(string username, string password) 1684 { 1685 string[string] params; 1686 params["user"] = username; 1687 params["password"] = password; 1688 return params; 1689 } 1690 1691 override ddbc.core.Connection connect(string url, string[string] params) 1692 { 1693 //writeln("ODBCDriver.connect " ~ url); 1694 return new ODBCConnection(url, params); 1695 } 1696 } 1697 1698 unittest 1699 { 1700 static if (ODBC_TESTS_ENABLED) 1701 { 1702 1703 import std.conv; 1704 1705 DataSource ds = createUnitTestODBCDataSource(); 1706 1707 auto conn = ds.getConnection(); 1708 scope (exit) 1709 conn.close(); 1710 auto stmt = conn.createStatement(); 1711 scope (exit) 1712 stmt.close(); 1713 1714 //assert(stmt.executeUpdate("CREATE DATABASE testdb") == -1); 1715 //assert(stmt.executeUpdate("USE testdb") == -1); 1716 1717 assert(stmt.executeUpdate( 1718 "IF OBJECT_ID('ddbct1', 'U') IS NOT NULL DROP TABLE ddbct1") == -1); 1719 1720 // Some Databases has `not null` as default. 1721 assert(stmt.executeUpdate("CREATE TABLE ddbct1 ( " ~ "id int not null primary key, " 1722 ~ "name varchar(250) null, " ~ "comment varchar(max) null, " ~ "ts datetime null)") == -1); 1723 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name, comment, ts) VALUES(1, 'name1dfgdfg', 'comment for line 1', '2017-02-03T12:30:25' )") == 1); 1724 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name, comment) VALUES" 1725 ~ "(2, 'name2', 'comment for line 2 - can be very long'), " 1726 ~ "(3, 'name3', 'this is line 3')") == 2); 1727 1728 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name) VALUES (4, 'name4')") == 1); 1729 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, comment) VALUES(5, '')") == 1); 1730 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name) VALUES(6, '')") == 1); 1731 assert(stmt.executeUpdate("UPDATE ddbct1 SET name= name + '_x' WHERE id IN (3, 4)") == 2); 1732 1733 PreparedStatement ps = conn.prepareStatement("UPDATE ddbct1 SET name=? WHERE id=?"); 1734 //ps.setString(1, null); 1735 ps.setString(1, "null"); 1736 ps.setLong(2, 3); 1737 assert(ps.executeUpdate() == 1); 1738 1739 auto rs = stmt.executeQuery("SELECT id, name name_alias, comment, ts FROM ddbct1 ORDER BY id"); 1740 1741 // testing result set meta data 1742 ResultSetMetaData meta = rs.getMetaData(); 1743 assert(meta.getColumnCount() == 4); 1744 assert(meta.getColumnName(1) == "id"); 1745 assert(meta.getColumnLabel(1) == "id"); 1746 assert(meta.isNullable(1) == false); 1747 assert(meta.isNullable(2) == true); 1748 assert(meta.isNullable(3) == true); 1749 assert(meta.getColumnName(2) == "name_alias"); 1750 assert(meta.getColumnLabel(2) == "name_alias"); 1751 assert(meta.getColumnName(3) == "comment"); 1752 1753 //writeln("type: ", meta.getColumnTypeName(1)); 1754 //writeln("type: ", meta.getColumnTypeName(2)); 1755 //writeln("type: ", meta.getColumnTypeName(3)); 1756 //writeln("type: ", meta.getColumnTypeName(4)); 1757 1758 // not supported 1759 //int rowCount = rs.getFetchSize(); 1760 //assert(rowCount == 6); 1761 int index = 1; 1762 while (rs.next()) 1763 { 1764 assert(!rs.isNull(1)); 1765 //ubyte[] bytes = rs.getUbytes(3); 1766 //int rowIndex = rs.getRow(); 1767 //writeln("row = ", rs.getRow()); 1768 //assert(rowIndex == index); 1769 1770 // BUG: the Type is defined as `BIGINT` but is read as double on some platforms insted of long! `INT` works with getLong()! 1771 // long id = rs.getLong(1); 1772 long id = rs.getDouble(1).to!long; 1773 1774 //writeln("id = ", id); 1775 1776 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1777 assert(id == index); 1778 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1779 //writeln("field3 = '" ~ rs.getString(3) ~ "'"); 1780 //writeln("wasNull = " ~ to!string(rs.wasNull())); 1781 if (id == 1) 1782 { 1783 DateTime ts = rs.getDateTime(4); 1784 assert(ts == DateTime(2017, 02, 03, 12, 30, 25)); 1785 } 1786 if (id == 4) 1787 { 1788 assert(rs.getString(2) == "name4_x"); 1789 assert(rs.isNull(3)); 1790 } 1791 if (id == 5) 1792 { 1793 assert(rs.isNull(2)); 1794 assert(!rs.isNull(3)); 1795 } 1796 if (id == 6) 1797 { 1798 assert(!rs.isNull(2)); 1799 assert(rs.isNull(3)); 1800 } 1801 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3)) ~ "\t[" ~ to!string(bytes.length) ~ "]"); 1802 index++; 1803 } 1804 1805 PreparedStatement ps2 = conn.prepareStatement( 1806 "SELECT id, name, comment FROM ddbct1 WHERE id >= ?"); 1807 scope (exit) 1808 ps2.close(); 1809 ps2.setLong(1, 3); 1810 rs = ps2.executeQuery(); 1811 while (rs.next()) 1812 { 1813 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3))); 1814 index++; 1815 } 1816 1817 // checking last insert ID for prepared statement 1818 PreparedStatement ps3 = conn.prepareStatement( 1819 "INSERT INTO ddbct1 (id, name) values (7, 'New String 1')"); 1820 scope (exit) 1821 ps3.close(); 1822 Variant newId; 1823 // does not work! 1824 //assert(ps3.executeUpdate(newId) == 1); 1825 //writeln("Generated insert id = " ~ newId.toString()); 1826 //assert(newId.get!ulong > 0); 1827 1828 // checking last insert ID for normal statement 1829 Statement stmt4 = conn.createStatement(); 1830 scope (exit) 1831 stmt4.close(); 1832 Variant newId2; 1833 // does not work! 1834 //assert(stmt.executeUpdate("INSERT INTO ddbct1 (id, name) values (8, 'New String 2')", newId2) == 1); 1835 //writeln("Generated insert id = " ~ newId2.toString()); 1836 //assert(newId2.get!ulong > 0); 1837 1838 } 1839 } 1840 1841 __gshared static this() 1842 { 1843 // register ODBCDriver 1844 import ddbc.common; 1845 1846 DriverFactory.registerDriverFactory("odbc", delegate() { 1847 return new ODBCDriver(); 1848 }); 1849 } 1850 1851 } 1852 else 1853 { // version(USE_ODBC) 1854 version (unittest) 1855 { 1856 immutable bool ODBC_TESTS_ENABLED = false; 1857 } 1858 }