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