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; 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 auto server = url[7 .. $].split('/').join('\\'); 381 if (server.length) 382 this.params["server"] = server; 383 void addToConnectionString(string key, string targetKey) 384 { 385 if (key in this.params) 386 { 387 connectionProps ~= [targetKey ~ "=" ~this.params[key]]; 388 } 389 } 390 391 if ("database" in this.params) 392 dbName = this.params["database"]; 393 394 addToConnectionString("dsn", "DSN"); 395 addToConnectionString("driver", "Driver"); 396 addToConnectionString("server", "Server"); 397 addToConnectionString("user", "Uid"); 398 addToConnectionString("username", "Uid"); 399 addToConnectionString("password", "Pwd"); 400 addToConnectionString("database", "Database"); 401 string connectionString = connectionProps.join(';'); 402 //writeln(connectionString); 403 404 SQLCHAR[1024] outstr; 405 SQLSMALLINT outstrlen; 406 checkdbc!SQLDriverConnect(conn, // ConnectionHandle 407 null, // WindowHandle 408 connectionString.dup.ptr, // InConnectionString 409 (connectionString.length).to!(short), // StringLength1 410 outstr.ptr, // OutConnectionString 411 outstr.length.to!(short), // BufferLength 412 &outstrlen, // StringLength2Ptr 413 cast(ushort) SQL_DRIVER_NOPROMPT // DriverCompletion 414 ); 415 416 closed = false; 417 setAutoCommit(true); 418 419 //writeln("MySQLConnection() connection created"); 420 } 421 422 override void close() 423 { 424 checkClosed(); 425 426 lock(); 427 scope (exit) 428 unlock(); 429 try 430 { 431 SQLDisconnect(conn); 432 SQLFreeHandle(SQL_HANDLE_DBC, conn); 433 conn = null; 434 SQLFreeHandle(SQL_HANDLE_ENV, henv); 435 henv = null; 436 closed = true; 437 } 438 catch (Throwable e) 439 { 440 throw new SQLException(e); 441 } 442 } 443 444 override void commit() 445 { 446 447 checkClosed(); 448 if (autocommit == false) 449 { 450 451 lock(); 452 scope (exit) 453 unlock(); 454 455 checkdbc!SQLEndTran(cast(short) SQL_HANDLE_DBC, conn, cast(short) SQL_COMMIT); 456 } 457 } 458 459 override Statement createStatement() 460 { 461 checkClosed(); 462 463 lock(); 464 scope (exit) 465 unlock(); 466 467 try 468 { 469 ODBCStatement stmt = new ODBCStatement(this); 470 activeStatements ~= stmt; 471 return stmt; 472 } 473 catch (Throwable e) 474 { 475 throw new SQLException(e); 476 } 477 } 478 479 PreparedStatement prepareStatement(string sql) 480 { 481 checkClosed(); 482 483 lock(); 484 scope (exit) 485 unlock(); 486 487 try 488 { 489 ODBCPreparedStatement stmt = new ODBCPreparedStatement(this, sql); 490 activeStatements ~= stmt; 491 return stmt; 492 } 493 catch (Throwable e) 494 { 495 throw new SQLException(e.msg ~ " while execution of query " ~ sql); 496 } 497 } 498 499 override string getCatalog() 500 { 501 return dbName; 502 } 503 504 /// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work. 505 override void setCatalog(string catalog) 506 { 507 } 508 509 override bool isClosed() 510 { 511 return closed; 512 } 513 514 override void rollback() 515 { 516 checkClosed(); 517 518 lock(); 519 scope (exit) 520 unlock(); 521 522 checkdbc!SQLEndTran(cast(short) SQL_HANDLE_DBC, conn, cast(short) SQL_ROLLBACK); 523 } 524 525 override bool getAutoCommit() 526 { 527 return autocommit; 528 } 529 530 override void setAutoCommit(bool autoCommit) 531 { 532 checkClosed(); 533 if (this.autocommit != autocommit) 534 { 535 lock(); 536 scope (exit) 537 unlock(); 538 539 uint ac = autoCommit ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF; 540 541 checkdbc!SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, &ac, SQL_IS_UINTEGER); 542 543 this.autocommit = autocommit; 544 } 545 } 546 } 547 548 class ODBCStatement : Statement 549 { 550 private: 551 ODBCConnection conn; 552 SQLHSTMT stmt; 553 ODBCResultSet resultSet; 554 ColumnInfo[] cols; 555 556 bool closed = false; 557 558 private SQLRETURN checkstmt(alias Fn, string file = __FILE__, size_t line = __LINE__)( 559 Parameters!Fn args) 560 { 561 return check!(Fn, file, line)(stmt, SQL_HANDLE_STMT, args); 562 } 563 564 public: 565 void checkClosed() 566 { 567 enforceEx!SQLException(!closed, "Statement is already closed"); 568 } 569 570 void lock() 571 { 572 conn.lock(); 573 } 574 575 void unlock() 576 { 577 conn.unlock(); 578 } 579 580 this(ODBCConnection conn) 581 { 582 this.conn = conn; 583 584 checkstmt!SQLAllocHandle(cast(short) SQL_HANDLE_STMT, this.conn.conn, &stmt); 585 } 586 587 public: 588 ODBCConnection getConnection() 589 { 590 checkClosed(); 591 return conn; 592 } 593 594 override ddbc.core.ResultSet executeQuery(string query) 595 { 596 checkClosed(); 597 lock(); 598 scope (exit) 599 unlock(); 600 try 601 { 602 checkstmt!SQLExecDirect(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 603 bind(); 604 resultSet = new ODBCResultSet(this); 605 return resultSet; 606 } 607 catch (Exception e) 608 { 609 throw new SQLException(e.msg ~ " - while execution of query " ~ query, 610 e.file, e.line); 611 } 612 } 613 614 override int executeUpdate(string query) 615 { 616 checkClosed(); 617 lock(); 618 scope (exit) 619 unlock(); 620 int rowsAffected = 0; 621 try 622 { 623 checkstmt!SQLExecDirect(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 624 625 checkstmt!SQLRowCount(stmt, &rowsAffected); 626 627 return rowsAffected; 628 } 629 catch (Exception e) 630 { 631 throw new SQLException(e.msg ~ " - while execution of query " ~ query, 632 e.file, e.line); 633 } 634 } 635 636 override int executeUpdate(string query, out Variant insertId) 637 { 638 checkClosed(); 639 lock(); 640 scope (exit) 641 unlock(); 642 try 643 { 644 int rowsAffected = executeUpdate(query); 645 646 checkstmt!SQLExecDirect(stmt, 647 cast(SQLCHAR*) toStringz(`SELECT SCOPE_IDENTITY()`), SQL_NTS); 648 649 bind(); 650 fetch(); 651 insertId = getColumn(1).readValueAsVariant(); 652 653 return rowsAffected; 654 } 655 catch (Throwable e) 656 { 657 throw new SQLException(e.msg ~ " - while execution of query " ~ query); 658 } 659 } 660 661 override void close() 662 { 663 checkClosed(); 664 lock(); 665 scope (exit) 666 unlock(); 667 try 668 { 669 closeResultSet(); 670 671 SQLFreeHandle(SQL_HANDLE_STMT, stmt); 672 stmt = null; 673 closed = true; 674 conn.onStatementClosed(this); 675 676 } 677 catch (Throwable e) 678 { 679 throw new SQLException(e); 680 } 681 } 682 683 private void closeResultSet() 684 { 685 if (resultSet !is null) 686 { 687 resultSet.onStatementClosed(); 688 resultSet = null; 689 } 690 } 691 692 private: 693 694 void bind() 695 { 696 697 SQLSMALLINT num = 0; 698 checkstmt!SQLNumResultCols(stmt, &num); 699 700 cols.length = num; 701 702 foreach (i; 0 .. num) 703 { 704 cols[i] = new ColumnInfo(i + 1); 705 //check(SQLBindCol(stmt, cast(ushort)(i + 1), sqlTypeToCType(cols[i].dataType), null, 0, null), stmt, SQL_HANDLE_STMT); 706 } 707 } 708 709 int getColumnCount() 710 { 711 return cast(int) cols.length; 712 } 713 714 ColumnInfo getColumn(int nr) 715 { 716 return cols[nr - 1]; 717 } 718 719 bool fetch() 720 { 721 bool hasData = checkstmt!SQLFetch(stmt) != SQL_NO_DATA; 722 723 if (hasData) 724 { 725 this.cols.each!(c => c.read()); 726 } 727 728 return hasData; 729 } 730 731 class ColumnInfo 732 { 733 ushort nr; 734 string name; 735 short dataType; 736 short nullAble; 737 738 Variant value; 739 740 this(int nr) 741 { 742 743 this.nr = cast(short) nr; 744 745 short nameLen = 1000; 746 char[1000] nameBuff; 747 748 // BUG: SQLDescribeCol does not return the length of the of the column-name! 749 /*checkstmt!SQLDescribeCol(stmt, this.nr, null, 750 0, &nameLen, &this.dataType, null, null, &this.nullAble); 751 nameLen += 1; 752 nameBuff.length = nameLen;*/ 753 754 checkstmt!SQLDescribeCol(stmt, this.nr, nameBuff.ptr, 755 nameLen, null, &this.dataType, null, null, &this.nullAble); 756 757 this.name = fromStringz(nameBuff.ptr).idup; 758 } 759 760 void read() 761 { 762 value = readValueAsVariant(); 763 } 764 765 bool isNull() 766 { 767 return !value.hasValue(); //testNull == SQL_NULL_DATA; 768 } 769 770 Variant readValue(T)() 771 if (!isArray!(T) && !is(TypeToCIdentifier!(T) == void)) 772 { 773 T val; 774 775 int nullCheck = 0; 776 777 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), &val, 0, &nullCheck); 778 779 if (nullCheck == SQL_NULL_DATA) 780 return Variant(); 781 782 return Variant(val); 783 } 784 785 Variant readValue(T)() 786 if (isArray!(T) && !is(TypeToCIdentifier!(T) == void)) 787 { 788 T val; 789 int len = 0; 790 791 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), null, 0, &len); 792 793 if (len == SQL_NULL_DATA) 794 return Variant(); 795 796 797 // A char-array contains a null-termination. 798 static if (is(T == char[])) 799 len += 1; 800 801 val.length = len; 802 803 checkstmt!SQLGetData(stmt, this.nr, TypeToCIdentifier!(T), val.ptr, len, null); 804 805 // A char-array contains a null-termination. 806 static if (is(T == char[])) 807 val = val[0 .. ($ - 1)]; 808 809 static if(is(T == char[])) 810 return Variant(val.idup); 811 else 812 return Variant(val); 813 } 814 815 Variant readValue(T)() if (is(T == DateTime)) 816 { 817 auto val = readValue!(SQL_TIMESTAMP_STRUCT); 818 819 if (val.type == typeid(SQL_TIMESTAMP_STRUCT)) 820 { 821 auto s = val.get!(SQL_TIMESTAMP_STRUCT); 822 return Variant(DateTime(s.year, s.month, s.day, s.hour, s.minute, s.second)); 823 } 824 return Variant(); 825 826 } 827 828 Variant readValue(T)() if (is(T == Date)) 829 { 830 auto val = readValue!(SQL_DATE_STRUCT); 831 832 if (val.type == typeid(SQL_DATE_STRUCT)) 833 { 834 auto s = val.get!(SQL_DATE_STRUCT); 835 return Variant(Date(s.year, s.month, s.day)); 836 } 837 return Variant(); 838 } 839 840 Variant readValue(T)() if (is(T == TimeOfDay)) 841 { 842 auto val = readValue!(SQL_TIME_STRUCT); 843 844 if (val.type == typeid(SQL_TIME_STRUCT)) 845 { 846 auto s = val.get!(SQL_TIME_STRUCT); 847 return Variant(TimeOfDay(s.hour, s.minute, s.second)); 848 } 849 return Variant(); 850 } 851 852 Variant readValueAsVariant() 853 { 854 // dfmt off 855 switch (this.dataType) 856 { 857 case SQL_TINYINT: return readValue!(byte); 858 case SQL_SMALLINT: return readValue!(short); 859 case SQL_INTEGER: return readValue!(int); 860 case SQL_BIGINT: return readValue!(long); 861 862 case SQL_REAL: return readValue!(float); 863 case SQL_FLOAT: return readValue!(double); 864 case SQL_DOUBLE: return readValue!(double); 865 866 case SQL_CHAR: return readValue!(char[]); 867 case SQL_VARCHAR: return readValue!(char[]); 868 case SQL_LONGVARCHAR: return readValue!(char[]); 869 case SQL_WCHAR: return readValue!(wchar[]); 870 case SQL_WVARCHAR: return readValue!(wchar[]); 871 case SQL_WLONGVARCHAR: return readValue!(wchar[]); 872 case SQL_BINARY: return readValue!(byte[]); 873 case SQL_VARBINARY: return readValue!(byte[]); 874 case SQL_LONGVARBINARY: return readValue!(byte[]); 875 876 case SQL_NUMERIC: return readValue!(SQL_NUMERIC_STRUCT); 877 case SQL_TYPE_DATE: return readValue!(Date); 878 case SQL_TYPE_TIME: return readValue!(TimeOfDay); 879 case SQL_TYPE_TIMESTAMP: return readValue!(DateTime); 880 //case SQL_GUID: return Variant(readValue!(SQLGUID)); 881 882 default: 883 throw new Exception(text("TYPE ", this.dataType, " is currently not supported!")); 884 } 885 // dfmt on 886 } 887 } 888 } 889 890 class ODBCPreparedStatement : ODBCStatement, PreparedStatement 891 { 892 string query; 893 int paramCount; 894 ResultSetMetaData metadata; 895 ParameterMetaData paramMetadata; 896 897 Parameter[] params; 898 899 this(ODBCConnection conn, string query) 900 { 901 super(conn); 902 this.query = query; 903 try 904 { 905 checkstmt!SQLPrepare(stmt, cast(SQLCHAR*) toStringz(query), SQL_NTS); 906 SQLSMALLINT v = 0; 907 checkstmt!SQLNumParams(stmt, &v); 908 paramCount = v; 909 params.length = v; 910 } 911 catch (Throwable e) 912 { 913 throw new SQLException(e); 914 } 915 } 916 917 void checkIndex(int index) 918 { 919 if (index < 1 || index > paramCount) 920 throw new SQLException("Parameter index " ~ to!string(index) ~ " is out of range"); 921 } 922 923 public: 924 925 /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. 926 override ResultSetMetaData getMetaData() 927 { 928 return metadata; 929 } 930 931 /// Retrieves the number, types and properties of this PreparedStatement object's parameters. 932 override ParameterMetaData getParameterMetaData() 933 { 934 throw new SQLException("Method not implemented"); 935 } 936 937 override int executeUpdate() 938 { 939 checkClosed(); 940 lock(); 941 scope (exit) 942 unlock(); 943 try 944 { 945 946 checkstmt!SQLExecute(stmt); 947 948 int rowsAffected = 0; 949 checkstmt!SQLRowCount(stmt, &rowsAffected); 950 return rowsAffected; 951 } 952 catch (Throwable e) 953 { 954 throw new SQLException(e); 955 } 956 } 957 958 override int executeUpdate(out Variant insertId) 959 { 960 checkClosed(); 961 lock(); 962 scope (exit) 963 unlock(); 964 try 965 { 966 checkstmt!SQLExecute(stmt); 967 968 int rowsAffected = 0; 969 checkstmt!SQLRowCount(stmt, &rowsAffected); 970 971 checkstmt!SQLExecDirect(stmt, 972 cast(SQLCHAR*) toStringz(`SELECT SCOPE_IDENTITY()`), SQL_NTS); 973 974 bind(); 975 fetch(); 976 insertId = getColumn(1).value; 977 return rowsAffected; 978 } 979 catch (Throwable e) 980 { 981 throw new SQLException(e); 982 } 983 } 984 985 override ddbc.core.ResultSet executeQuery() 986 { 987 checkClosed(); 988 lock(); 989 scope (exit) 990 unlock(); 991 try 992 { 993 checkstmt!SQLExecute(stmt); 994 bind(); 995 resultSet = new ODBCResultSet(this); 996 return resultSet; 997 } 998 catch (Throwable e) 999 { 1000 throw new SQLException(e); 1001 } 1002 } 1003 1004 override void clearParameters() 1005 { 1006 checkClosed(); 1007 lock(); 1008 scope (exit) 1009 unlock(); 1010 try 1011 { 1012 for (int i = 1; i <= paramCount; i++) 1013 setNull(i); 1014 } 1015 catch (Throwable e) 1016 { 1017 throw new SQLException(e); 1018 } 1019 } 1020 1021 struct Parameter 1022 { 1023 SQLSMALLINT bindType; 1024 SQLSMALLINT dbtype; 1025 1026 void[] data; 1027 } 1028 1029 void bindParam(T)(int parameterIndex, T x) 1030 { 1031 1032 checkClosed(); 1033 lock(); 1034 scope (exit) 1035 unlock(); 1036 checkIndex(parameterIndex); 1037 1038 auto param = ¶ms[parameterIndex - 1]; 1039 1040 static if (is(T == char[])) 1041 param.data = cast(void[]) (x ~ '\0'); 1042 else static if (isArray!(T)) 1043 param.data = cast(void[]) x; 1044 else 1045 param.data = cast(void[])[x]; 1046 param.bindType = TypeToCIdentifier!(T); 1047 param.dbtype = ctypeToSQLType(TypeToCIdentifier!(T)); 1048 1049 1050 1051 SQLBindParameter(stmt, cast(ushort) parameterIndex, SQL_PARAM_INPUT, 1052 param.bindType, param.dbtype, 0, 0, param.data.ptr, 1053 cast(int) param.data.length, null); 1054 } 1055 1056 override void setFloat(int parameterIndex, float x) 1057 { 1058 bindParam(parameterIndex, x); 1059 } 1060 1061 override void setDouble(int parameterIndex, double x) 1062 { 1063 bindParam(parameterIndex, x); 1064 } 1065 1066 override void setBoolean(int parameterIndex, bool x) 1067 { 1068 bindParam(parameterIndex, x); 1069 } 1070 1071 override void setLong(int parameterIndex, long x) 1072 { 1073 bindParam(parameterIndex, x); 1074 } 1075 1076 override void setUlong(int parameterIndex, ulong x) 1077 { 1078 bindParam(parameterIndex, x); 1079 } 1080 1081 override void setInt(int parameterIndex, int x) 1082 { 1083 bindParam(parameterIndex, x); 1084 } 1085 1086 override void setUint(int parameterIndex, uint x) 1087 { 1088 bindParam(parameterIndex, x); 1089 } 1090 1091 override void setShort(int parameterIndex, short x) 1092 { 1093 bindParam(parameterIndex, x); 1094 } 1095 1096 override void setUshort(int parameterIndex, ushort x) 1097 { 1098 bindParam(parameterIndex, x); 1099 } 1100 1101 override void setByte(int parameterIndex, byte x) 1102 { 1103 bindParam(parameterIndex, x); 1104 } 1105 1106 override void setUbyte(int parameterIndex, ubyte x) 1107 { 1108 bindParam(parameterIndex, x); 1109 } 1110 1111 override void setBytes(int parameterIndex, byte[] x) 1112 { 1113 bindParam(parameterIndex, x); 1114 } 1115 1116 override void setUbytes(int parameterIndex, ubyte[] x) 1117 { 1118 bindParam(parameterIndex, cast(byte[]) cast(void[]) x); 1119 } 1120 1121 override void setString(int parameterIndex, string x) 1122 { 1123 bindParam(parameterIndex, x.dup); 1124 } 1125 1126 override void setDateTime(int parameterIndex, DateTime x) 1127 { 1128 bindParam(parameterIndex, SQL_TIMESTAMP_STRUCT(x.year, x.month, 1129 x.day, x.hour, x.minute, x.second, 0)); 1130 } 1131 1132 override void setDate(int parameterIndex, Date x) 1133 { 1134 bindParam(parameterIndex, SQL_DATE_STRUCT(x.year, x.month, x.day)); 1135 } 1136 1137 override void setTime(int parameterIndex, TimeOfDay x) 1138 { 1139 bindParam(parameterIndex, SQL_TIME_STRUCT(x.hour, x.minute, x.second)); 1140 } 1141 1142 override void setVariant(int parameterIndex, Variant x) 1143 { 1144 if (x.type == typeid(float)) 1145 setFloat(parameterIndex, x.get!(float)); 1146 else if (x.type == typeid(double)) 1147 setDouble(parameterIndex, x.get!(double)); 1148 else if (x.type == typeid(bool)) 1149 setBoolean(parameterIndex, x.get!(bool)); 1150 else if (x.type == typeid(long)) 1151 setLong(parameterIndex, x.get!(long)); 1152 else if (x.type == typeid(ulong)) 1153 setUlong(parameterIndex, x.get!(ulong)); 1154 else if (x.type == typeid(int)) 1155 setInt(parameterIndex, x.get!(int)); 1156 else if (x.type == typeid(uint)) 1157 setUint(parameterIndex, x.get!(uint)); 1158 else if (x.type == typeid(short)) 1159 setShort(parameterIndex, x.get!(short)); 1160 else if (x.type == typeid(ushort)) 1161 setUshort(parameterIndex, x.get!(ushort)); 1162 else if (x.type == typeid(byte)) 1163 setByte(parameterIndex, x.get!(byte)); 1164 else if (x.type == typeid(ubyte)) 1165 setUbyte(parameterIndex, x.get!(ubyte)); 1166 else if (x.type == typeid(byte[])) 1167 setBytes(parameterIndex, x.get!(byte[])); 1168 else if (x.type == typeid(ubyte[])) 1169 setUbytes(parameterIndex, x.get!(ubyte[])); 1170 else if (x.type == typeid(string)) 1171 setString(parameterIndex, x.get!(string)); 1172 else if (x.type == typeid(DateTime)) 1173 setDateTime(parameterIndex, x.get!(DateTime)); 1174 else if (x.type == typeid(Date)) 1175 setDate(parameterIndex, x.get!(Date)); 1176 else if (x.type == typeid(TimeOfDay)) 1177 setTime(parameterIndex, x.get!(TimeOfDay)); 1178 else 1179 throw new SQLException("Type inside variant is not supported!"); 1180 1181 } 1182 1183 override void setNull(int parameterIndex) 1184 { 1185 throw new SQLException("Method not implemented"); 1186 } 1187 1188 override void setNull(int parameterIndex, int sqlType) 1189 { 1190 throw new SQLException("Method not implemented"); 1191 } 1192 1193 } 1194 1195 class ODBCResultSet : ResultSetImpl 1196 { 1197 private: 1198 ODBCStatement stmt; 1199 ResultSetMetaData metadata; 1200 bool closed; 1201 int currentRowIndex; 1202 int[string] columnMap; 1203 bool lastIsNull; 1204 1205 bool _hasRows; 1206 bool _isLastRow; 1207 1208 ODBCStatement.ColumnInfo[string] colsByName; 1209 1210 void checkClosed() 1211 { 1212 if (closed) 1213 throw new SQLException("Result set is already closed"); 1214 } 1215 1216 public: 1217 1218 void lock() 1219 { 1220 stmt.lock(); 1221 } 1222 1223 void unlock() 1224 { 1225 stmt.unlock(); 1226 } 1227 1228 this(ODBCStatement stmt) 1229 { 1230 this.stmt = stmt; 1231 1232 _hasRows = true; //stmt.fetch(); 1233 _isLastRow = false; 1234 1235 ColumnMetadataItem[] items; 1236 items.length = stmt.cols.length; 1237 1238 foreach (i, col; stmt.cols) 1239 { 1240 colsByName[col.name] = col; 1241 items[i] = new ColumnMetadataItem(); 1242 items[i].catalogName = stmt.conn.getCatalog(); 1243 items[i].name = col.name; 1244 items[i].label = col.name; 1245 items[i].type = col.dataType.fromODBCType(); 1246 items[i].typeName = (cast(SqlType) items[i].type).to!(string); 1247 items[i].isNullable = col.nullAble == SQL_NULLABLE; 1248 1249 debug writeln("ColumnMetadataItem: ", items[i].catalogName, "; ", items[i].name, "; ", items[i].typeName); 1250 } 1251 1252 metadata = new ResultSetMetaDataImpl(items); 1253 1254 } 1255 1256 void onStatementClosed() 1257 { 1258 closed = true; 1259 } 1260 1261 string decodeTextBlob(ubyte[] data) 1262 { 1263 char[] res = new char[data.length]; 1264 foreach (i, ch; data) 1265 { 1266 res[i] = cast(char) ch; 1267 } 1268 return to!string(res); 1269 } 1270 1271 // ResultSet interface implementation 1272 1273 //Retrieves the number, types and properties of this ResultSet object's columns 1274 override ResultSetMetaData getMetaData() 1275 { 1276 checkClosed(); 1277 lock(); 1278 scope (exit) 1279 unlock(); 1280 return metadata; 1281 } 1282 1283 override void close() 1284 { 1285 checkClosed(); 1286 lock(); 1287 scope (exit) 1288 unlock(); 1289 stmt.closeResultSet(); 1290 closed = true; 1291 } 1292 1293 override bool first() 1294 { 1295 /*checkClosed(); 1296 lock(); 1297 scope (exit) 1298 unlock(); 1299 currentRowIndex = 0; 1300 1301 return check(SQLFetchScroll(stmt.stmt, SQL_FETCH_FIRST, 0), stmt.stmt, SQL_HANDLE_STMT) != SQL_NO_DATA;*/ 1302 1303 throw new SQLException("Method not implemented"); 1304 1305 } 1306 1307 override bool isFirst() 1308 { 1309 checkClosed(); 1310 lock(); 1311 scope (exit) 1312 unlock(); 1313 return _hasRows && currentRowIndex == 0; 1314 } 1315 1316 override bool isLast() 1317 { 1318 checkClosed(); 1319 lock(); 1320 scope (exit) 1321 unlock(); 1322 return _hasRows && _isLastRow; 1323 } 1324 1325 override bool next() 1326 { 1327 checkClosed(); 1328 lock(); 1329 scope (exit) 1330 unlock(); 1331 1332 if (!stmt.fetch()) 1333 return false; 1334 1335 currentRowIndex++; 1336 return true; 1337 } 1338 1339 override int findColumn(string columnName) 1340 { 1341 checkClosed(); 1342 lock(); 1343 scope (exit) 1344 unlock(); 1345 auto p = (columnName in colsByName); 1346 if (!p) 1347 throw new SQLException("Column " ~ columnName ~ " not found"); 1348 return p.nr; 1349 } 1350 1351 override bool getBoolean(int columnIndex) 1352 { 1353 checkClosed(); 1354 lock(); 1355 scope (exit) 1356 unlock(); 1357 1358 return stmt.getColumn(columnIndex).value.get!(bool); 1359 } 1360 1361 override ubyte getUbyte(int columnIndex) 1362 { 1363 checkClosed(); 1364 lock(); 1365 scope (exit) 1366 unlock(); 1367 1368 return stmt.getColumn(columnIndex).value.get!(ubyte); 1369 } 1370 1371 override byte getByte(int columnIndex) 1372 { 1373 checkClosed(); 1374 lock(); 1375 scope (exit) 1376 unlock(); 1377 1378 return stmt.getColumn(columnIndex).value.get!(byte); 1379 } 1380 1381 override short getShort(int columnIndex) 1382 { 1383 checkClosed(); 1384 lock(); 1385 scope (exit) 1386 unlock(); 1387 1388 return stmt.getColumn(columnIndex).value.get!(short); 1389 } 1390 1391 override ushort getUshort(int columnIndex) 1392 { 1393 checkClosed(); 1394 lock(); 1395 scope (exit) 1396 unlock(); 1397 1398 return stmt.getColumn(columnIndex).value.get!(ushort); 1399 } 1400 1401 override int getInt(int columnIndex) 1402 { 1403 checkClosed(); 1404 lock(); 1405 scope (exit) 1406 unlock(); 1407 1408 return stmt.getColumn(columnIndex).value.get!(int); 1409 } 1410 1411 override uint getUint(int columnIndex) 1412 { 1413 checkClosed(); 1414 lock(); 1415 scope (exit) 1416 unlock(); 1417 1418 return stmt.getColumn(columnIndex).value.get!(uint); 1419 } 1420 1421 override long getLong(int columnIndex) 1422 { 1423 checkClosed(); 1424 lock(); 1425 scope (exit) 1426 unlock(); 1427 1428 return stmt.getColumn(columnIndex).value.get!(long); 1429 } 1430 1431 override ulong getUlong(int columnIndex) 1432 { 1433 checkClosed(); 1434 lock(); 1435 scope (exit) 1436 unlock(); 1437 1438 return stmt.getColumn(columnIndex).value.get!(ulong); 1439 } 1440 1441 override double getDouble(int columnIndex) 1442 { 1443 checkClosed(); 1444 lock(); 1445 scope (exit) 1446 unlock(); 1447 1448 return stmt.getColumn(columnIndex).value.get!(double); 1449 } 1450 1451 override float getFloat(int columnIndex) 1452 { 1453 checkClosed(); 1454 lock(); 1455 scope (exit) 1456 unlock(); 1457 1458 return stmt.getColumn(columnIndex).value.get!(float); 1459 } 1460 1461 private Type getArray(Type)(int columnIndex) 1462 { 1463 checkClosed(); 1464 lock(); 1465 scope (exit) 1466 unlock(); 1467 1468 auto val = stmt.getColumn(columnIndex).value; 1469 if (!val.hasValue) 1470 return cast(Type)null; 1471 else 1472 return val.get!(Type); 1473 } 1474 1475 override byte[] getBytes(int columnIndex) 1476 { 1477 return getArray!(byte[])(columnIndex); 1478 1479 //return stmt.getColumn(columnIndex).value.get!(byte[]); 1480 } 1481 1482 override ubyte[] getUbytes(int columnIndex) 1483 { 1484 return getArray!(ubyte[])(columnIndex); 1485 } 1486 1487 override string getString(int columnIndex) 1488 { 1489 return stmt.getColumn(columnIndex).value.get!(string); 1490 } 1491 1492 override std.datetime.DateTime getDateTime(int columnIndex) 1493 { 1494 checkClosed(); 1495 lock(); 1496 scope (exit) 1497 unlock(); 1498 1499 return stmt.getColumn(columnIndex).value.get!(DateTime); 1500 } 1501 1502 override std.datetime.Date getDate(int columnIndex) 1503 { 1504 checkClosed(); 1505 lock(); 1506 scope (exit) 1507 unlock(); 1508 1509 return stmt.getColumn(columnIndex).value.get!(Date); 1510 } 1511 1512 override std.datetime.TimeOfDay getTime(int columnIndex) 1513 { 1514 checkClosed(); 1515 lock(); 1516 scope (exit) 1517 unlock(); 1518 1519 return stmt.getColumn(columnIndex).value.get!(TimeOfDay); 1520 } 1521 1522 override Variant getVariant(int columnIndex) 1523 { 1524 checkClosed(); 1525 lock(); 1526 scope (exit) 1527 unlock(); 1528 1529 return stmt.getColumn(columnIndex).value; 1530 } 1531 1532 override bool wasNull() 1533 { 1534 checkClosed(); 1535 lock(); 1536 scope (exit) 1537 unlock(); 1538 return lastIsNull; 1539 } 1540 1541 override bool isNull(int columnIndex) 1542 { 1543 return stmt.getColumn(columnIndex).isNull(); 1544 } 1545 1546 //Retrieves the Statement object that produced this ResultSet object. 1547 override Statement getStatement() 1548 { 1549 checkClosed(); 1550 lock(); 1551 scope (exit) 1552 unlock(); 1553 return stmt; 1554 } 1555 1556 //Retrieves the current row number 1557 override int getRow() 1558 { 1559 checkClosed(); 1560 lock(); 1561 scope (exit) 1562 unlock(); 1563 1564 return currentRowIndex; 1565 } 1566 1567 } 1568 1569 // sample URL: 1570 // mysql://localhost:3306/DatabaseName 1571 class ODBCDriver : Driver 1572 { 1573 // helper function 1574 public static string generateUrl(string host, ushort port, string dbname) 1575 { 1576 return "odbc://" ~ host ~ ":" ~ to!string(port) ~ "/" ~ dbname; 1577 } 1578 1579 public static string[string] setUserAndPassword(string username, string password) 1580 { 1581 string[string] params; 1582 params["user"] = username; 1583 params["password"] = password; 1584 return params; 1585 } 1586 1587 override ddbc.core.Connection connect(string url, string[string] params) 1588 { 1589 //writeln("MySQLDriver.connect " ~ url); 1590 return new ODBCConnection(url, params); 1591 } 1592 } 1593 1594 unittest 1595 { 1596 static if (ODBC_TESTS_ENABLED) 1597 { 1598 1599 import std.conv; 1600 1601 DataSource ds = createUnitTestODBCDataSource(); 1602 1603 auto conn = ds.getConnection(); 1604 scope (exit) 1605 conn.close(); 1606 auto stmt = conn.createStatement(); 1607 scope (exit) 1608 stmt.close(); 1609 1610 assert(stmt.executeUpdate( 1611 "IF OBJECT_ID('ddbct1', 'U') IS NOT NULL DROP TABLE ddbct1") == -1); 1612 1613 // Some Databases has `not null` as default. 1614 assert(stmt.executeUpdate("CREATE TABLE ddbct1 ( " ~ "id int not null primary key, " 1615 ~ "name varchar(250) null, " ~ "comment varchar(max) null, " ~ "ts datetime null)") == -1); 1616 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name, comment, ts) VALUES(1, 'name1dfgdfg', 'comment for line 1', '2017-02-03T12:30:25' )") == 1); 1617 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name, comment) VALUES" 1618 ~ "(2, 'name2', 'comment for line 2 - can be very long'), " 1619 ~ "(3, 'name3', 'this is line 3')") == 2); 1620 1621 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name) VALUES (4, 'name4')") == 1); 1622 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, comment) VALUES(5, '')") == 1); 1623 assert(stmt.executeUpdate("INSERT INTO ddbct1(id, name) VALUES(6, '')") == 1); 1624 assert(stmt.executeUpdate("UPDATE ddbct1 SET name= name + '_x' WHERE id IN (3, 4)") == 2); 1625 1626 PreparedStatement ps = conn.prepareStatement("UPDATE ddbct1 SET name=? WHERE id=?"); 1627 //ps.setString(1, null); 1628 ps.setString(1, "null"); 1629 ps.setLong(2, 3); 1630 assert(ps.executeUpdate() == 1); 1631 1632 auto rs = stmt.executeQuery( 1633 "SELECT id, name name_alias, comment, ts FROM ddbct1 ORDER BY id"); 1634 1635 // testing result set meta data 1636 ResultSetMetaData meta = rs.getMetaData(); 1637 assert(meta.getColumnCount() == 4); 1638 assert(meta.getColumnName(1) == "id"); 1639 assert(meta.getColumnLabel(1) == "id"); 1640 assert(meta.isNullable(1) == false); 1641 assert(meta.isNullable(2) == true); 1642 assert(meta.isNullable(3) == true); 1643 assert(meta.getColumnName(2) == "name_alias"); 1644 assert(meta.getColumnLabel(2) == "name_alias"); 1645 assert(meta.getColumnName(3) == "comment"); 1646 1647 //writeln("type: ", meta.getColumnTypeName(1)); 1648 //writeln("type: ", meta.getColumnTypeName(2)); 1649 //writeln("type: ", meta.getColumnTypeName(3)); 1650 //writeln("type: ", meta.getColumnTypeName(4)); 1651 1652 // not supported 1653 //int rowCount = rs.getFetchSize(); 1654 //assert(rowCount == 6); 1655 int index = 1; 1656 while (rs.next()) 1657 { 1658 assert(!rs.isNull(1)); 1659 //ubyte[] bytes = rs.getUbytes(3); 1660 //int rowIndex = rs.getRow(); 1661 //writeln("row = ", rs.getRow()); 1662 //assert(rowIndex == index); 1663 1664 // BUG: the Type is defined as `BIGINT` but is read as double on some platforms insted of long! `INT` works with getLong()! 1665 // long id = rs.getLong(1); 1666 long id = rs.getDouble(1).to!long; 1667 1668 //writeln("id = ", id); 1669 1670 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1671 assert(id == index); 1672 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1673 //writeln("field3 = '" ~ rs.getString(3) ~ "'"); 1674 //writeln("wasNull = " ~ to!string(rs.wasNull())); 1675 if (id == 1) 1676 { 1677 DateTime ts = rs.getDateTime(4); 1678 assert(ts == DateTime(2017, 02, 03, 12, 30, 25)); 1679 } 1680 if (id == 4) 1681 { 1682 assert(rs.getString(2) == "name4_x"); 1683 assert(rs.isNull(3)); 1684 } 1685 if (id == 5) 1686 { 1687 assert(rs.isNull(2)); 1688 assert(!rs.isNull(3)); 1689 } 1690 if (id == 6) 1691 { 1692 assert(!rs.isNull(2)); 1693 assert(rs.isNull(3)); 1694 } 1695 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3)) ~ "\t[" ~ to!string(bytes.length) ~ "]"); 1696 index++; 1697 } 1698 1699 PreparedStatement ps2 = conn.prepareStatement( 1700 "SELECT id, name, comment FROM ddbct1 WHERE id >= ?"); 1701 scope (exit) 1702 ps2.close(); 1703 ps2.setLong(1, 3); 1704 rs = ps2.executeQuery(); 1705 while (rs.next()) 1706 { 1707 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3))); 1708 index++; 1709 } 1710 1711 // checking last insert ID for prepared statement 1712 PreparedStatement ps3 = conn.prepareStatement( 1713 "INSERT INTO ddbct1 (id, name) values (7, 'New String 1')"); 1714 scope (exit) 1715 ps3.close(); 1716 Variant newId; 1717 // does not work! 1718 //assert(ps3.executeUpdate(newId) == 1); 1719 //writeln("Generated insert id = " ~ newId.toString()); 1720 //assert(newId.get!ulong > 0); 1721 1722 // checking last insert ID for normal statement 1723 Statement stmt4 = conn.createStatement(); 1724 scope (exit) 1725 stmt4.close(); 1726 Variant newId2; 1727 // does not work! 1728 //assert(stmt.executeUpdate("INSERT INTO ddbct1 (id, name) values (8, 'New String 2')", newId2) == 1); 1729 //writeln("Generated insert id = " ~ newId2.toString()); 1730 //assert(newId2.get!ulong > 0); 1731 1732 } 1733 } 1734 1735 __gshared static this() 1736 { 1737 // register MySQLDriver 1738 import ddbc.common; 1739 1740 DriverFactory.registerDriverFactory("odbc", delegate() { 1741 return new ODBCDriver(); 1742 }); 1743 } 1744 1745 } 1746 else 1747 { // version(USE_ODBC) 1748 version (unittest) 1749 { 1750 immutable bool ODBC_TESTS_ENABLED = false; 1751 } 1752 }