1 /** 2 * DDBC - D DataBase Connector - abstraction layer for RDBMS access, with interface similar to JDBC. 3 * 4 * Source file ddbc/drivers/mysqlddbc.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 * Currently it only includes MySQL driver. 10 * 11 * JDBC documentation can be found here: 12 * $(LINK http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html)$(BR) 13 * 14 * This module contains implementation of MySQL Driver which uses patched version of 15 * MYSQLN (native D implementation of MySQL connector, written by Steve Teale) 16 * 17 * Current version of driver implements only unidirectional readonly resultset, which with fetching full result to memory on creation. 18 * 19 * You can find usage examples in unittest{} sections. 20 * 21 * Copyright: Copyright 2013 22 * License: $(LINK www.boost.org/LICENSE_1_0.txt, Boost License 1.0). 23 * Author: Vadim Lopatin 24 */ 25 module ddbc.drivers.mysqlddbc; 26 27 import std.algorithm; 28 import std.conv : to; 29 import std.datetime; 30 import std.exception; 31 import std.stdio; 32 import std.string; 33 import std.variant; 34 import core.sync.mutex; 35 import ddbc.common; 36 import ddbc.core; 37 38 version(USE_MYSQL) { 39 40 import mysql.connection; 41 import mysql.commands;// : Command; 42 import mysql.prepared; 43 import mysql.protocol.constants; 44 import mysql.protocol.packets : FieldDescription, ParamDescription; 45 46 version(unittest) { 47 /* 48 To allow unit tests using MySQL server, 49 run mysql client using admin privileges, e.g. for MySQL server on localhost: 50 > mysql -uroot 51 52 Create test user and test DB: 53 mysql> GRANT ALL PRIVILEGES ON *.* TO testuser@'%' IDENTIFIED BY 'testpassword'; 54 mysql> GRANT ALL PRIVILEGES ON *.* TO testuser@'localhost' IDENTIFIED BY 'testpassword'; 55 mysql> CREATE DATABASE testdb; 56 */ 57 /// change to false to disable tests on real MySQL server 58 immutable bool MYSQL_TESTS_ENABLED = true; 59 /// change parameters if necessary 60 const string MYSQL_UNITTEST_HOST = "localhost"; 61 const int MYSQL_UNITTEST_PORT = 3306; 62 const string MYSQL_UNITTEST_USER = "testuser"; 63 const string MYSQL_UNITTEST_PASSWORD = "testpassword"; 64 const string MYSQL_UNITTEST_DB = "testdb"; 65 66 static if (MYSQL_TESTS_ENABLED) { 67 /// use this data source for tests 68 69 DataSource createUnitTestMySQLDataSource() { 70 string url = makeDDBCUrl("mysql", MYSQL_UNITTEST_HOST, MYSQL_UNITTEST_PORT, MYSQL_UNITTEST_DB); 71 string[string] params; 72 setUserAndPassword(params, MYSQL_UNITTEST_USER, MYSQL_UNITTEST_PASSWORD); 73 return createConnectionPool(url, params); 74 } 75 } 76 } 77 78 SqlType fromMySQLType(int t) { 79 switch(t) { 80 case SQLType.DECIMAL: 81 case SQLType.TINY: return SqlType.TINYINT; 82 case SQLType.SHORT: return SqlType.SMALLINT; 83 case SQLType.INT: return SqlType.INTEGER; 84 case SQLType.FLOAT: return SqlType.FLOAT; 85 case SQLType.DOUBLE: return SqlType.DOUBLE; 86 case SQLType.NULL: return SqlType.NULL; 87 case SQLType.TIMESTAMP: return SqlType.DATETIME; 88 case SQLType.LONGLONG: return SqlType.BIGINT; 89 case SQLType.INT24: return SqlType.INTEGER; 90 case SQLType.DATE: return SqlType.DATE; 91 case SQLType.TIME: return SqlType.TIME; 92 case SQLType.DATETIME: return SqlType.DATETIME; 93 case SQLType.YEAR: return SqlType.SMALLINT; 94 case SQLType.NEWDATE: return SqlType.DATE; 95 case SQLType.VARCHAR: return SqlType.VARCHAR; 96 case SQLType.BIT: return SqlType.BIT; 97 case SQLType.NEWDECIMAL: return SqlType.DECIMAL; 98 case SQLType.ENUM: return SqlType.OTHER; 99 case SQLType.SET: return SqlType.OTHER; 100 case SQLType.TINYBLOB: return SqlType.BLOB; 101 case SQLType.MEDIUMBLOB: return SqlType.BLOB; 102 case SQLType.LONGBLOB: return SqlType.BLOB; 103 case SQLType.BLOB: return SqlType.BLOB; 104 case SQLType.VARSTRING: return SqlType.VARCHAR; 105 case SQLType.STRING: return SqlType.VARCHAR; 106 case SQLType.GEOMETRY: return SqlType.OTHER; 107 default: return SqlType.OTHER; 108 } 109 } 110 111 class MySQLConnection : ddbc.core.Connection { 112 private: 113 string url; 114 string[string] params; 115 string dbName; 116 string username; 117 string password; 118 string hostname; 119 int port = 3306; 120 mysql.connection.Connection conn; 121 bool closed; 122 bool autocommit; 123 Mutex mutex; 124 125 126 MySQLStatement [] activeStatements; 127 128 void closeUnclosedStatements() { 129 MySQLStatement [] list = activeStatements.dup; 130 foreach(stmt; list) { 131 stmt.close(); 132 } 133 } 134 135 void checkClosed() { 136 if (closed) 137 throw new SQLException("Connection is already closed"); 138 } 139 140 public: 141 142 void lock() { 143 mutex.lock(); 144 } 145 146 void unlock() { 147 mutex.unlock(); 148 } 149 150 mysql.connection.Connection getConnection() { return conn; } 151 152 153 void onStatementClosed(MySQLStatement stmt) { 154 myRemove(activeStatements, stmt); 155 } 156 157 this(string url, string[string] params) { 158 //writeln("MySQLConnection() creating connection"); 159 mutex = new Mutex(); 160 this.url = url; 161 this.params = params; 162 try { 163 //writeln("parsing url " ~ url); 164 extractParamsFromURL(url, this.params); 165 string dbName = ""; 166 ptrdiff_t firstSlashes = std..string.indexOf(url, "//"); 167 ptrdiff_t lastSlash = std..string.lastIndexOf(url, '/'); 168 ptrdiff_t hostNameStart = firstSlashes >= 0 ? firstSlashes + 2 : 0; 169 ptrdiff_t hostNameEnd = lastSlash >=0 && lastSlash > firstSlashes + 1 ? lastSlash : url.length; 170 if (hostNameEnd < url.length - 1) { 171 dbName = url[hostNameEnd + 1 .. $]; 172 } 173 hostname = url[hostNameStart..hostNameEnd]; 174 if (hostname.length == 0) 175 hostname = "localhost"; 176 ptrdiff_t portDelimiter = std..string.indexOf(hostname, ":"); 177 if (portDelimiter >= 0) { 178 string portString = hostname[portDelimiter + 1 .. $]; 179 hostname = hostname[0 .. portDelimiter]; 180 if (portString.length > 0) 181 port = to!int(portString); 182 if (port < 1 || port > 65535) 183 port = 3306; 184 } 185 if ("user" in this.params) 186 username = this.params["user"]; 187 if ("password" in this.params) 188 password = this.params["password"]; 189 190 //writeln("host " ~ hostname ~ " : " ~ to!string(port) ~ " db=" ~ dbName ~ " user=" ~ username ~ " pass=" ~ password); 191 192 conn = new mysql.connection.Connection(hostname, username, password, dbName, cast(ushort)port); 193 closed = false; 194 setAutoCommit(true); 195 } catch (Throwable e) { 196 //writeln(e.msg); 197 throw new SQLException(e); 198 } 199 200 //writeln("MySQLConnection() connection created"); 201 } 202 override void close() { 203 checkClosed(); 204 205 lock(); 206 scope(exit) unlock(); 207 try { 208 closeUnclosedStatements(); 209 210 conn.close(); 211 closed = true; 212 } catch (Throwable e) { 213 throw new SQLException(e); 214 } 215 } 216 override void commit() { 217 checkClosed(); 218 219 lock(); 220 scope(exit) unlock(); 221 222 try { 223 Statement stmt = createStatement(); 224 scope(exit) stmt.close(); 225 stmt.executeUpdate("COMMIT"); 226 } catch (Throwable e) { 227 throw new SQLException(e); 228 } 229 } 230 override Statement createStatement() { 231 checkClosed(); 232 233 lock(); 234 scope(exit) unlock(); 235 236 try { 237 MySQLStatement stmt = new MySQLStatement(this); 238 activeStatements ~= stmt; 239 return stmt; 240 } catch (Throwable e) { 241 throw new SQLException(e); 242 } 243 } 244 245 PreparedStatement prepareStatement(string sql) { 246 checkClosed(); 247 248 lock(); 249 scope(exit) unlock(); 250 251 try { 252 MySQLPreparedStatement stmt = new MySQLPreparedStatement(this, sql); 253 activeStatements ~= stmt; 254 return stmt; 255 } catch (Throwable e) { 256 throw new SQLException(e.msg ~ " while execution of query " ~ sql); 257 } 258 } 259 260 override string getCatalog() { 261 return dbName; 262 } 263 264 /// Sets the given catalog name in order to select a subspace of this Connection object's database in which to work. 265 override void setCatalog(string catalog) { 266 checkClosed(); 267 if (dbName == catalog) 268 return; 269 270 lock(); 271 scope(exit) unlock(); 272 273 try { 274 conn.selectDB(catalog); 275 dbName = catalog; 276 } catch (Throwable e) { 277 throw new SQLException(e); 278 } 279 } 280 281 override bool isClosed() { 282 return closed; 283 } 284 285 override void rollback() { 286 checkClosed(); 287 288 lock(); 289 scope(exit) unlock(); 290 291 try { 292 Statement stmt = createStatement(); 293 scope(exit) stmt.close(); 294 stmt.executeUpdate("ROLLBACK"); 295 } catch (Throwable e) { 296 throw new SQLException(e); 297 } 298 } 299 override bool getAutoCommit() { 300 return autocommit; 301 } 302 override void setAutoCommit(bool autoCommit) { 303 checkClosed(); 304 if (this.autocommit == autoCommit) 305 return; 306 lock(); 307 scope(exit) unlock(); 308 309 try { 310 Statement stmt = createStatement(); 311 scope(exit) stmt.close(); 312 stmt.executeUpdate("SET autocommit=" ~ (autoCommit ? "1" : "0")); 313 this.autocommit = autoCommit; 314 } catch (Throwable e) { 315 throw new SQLException(e); 316 } 317 } 318 } 319 320 class MySQLStatement : Statement { 321 private: 322 MySQLConnection conn; 323 mysql.connection.ResultSet rs; // ResultSet is deprecated - should use Row[] instead 324 MySQLResultSet resultSet; 325 326 bool closed; 327 328 public: 329 void checkClosed() { 330 enforceEx!SQLException(!closed, "Statement is already closed"); 331 } 332 333 void lock() { 334 conn.lock(); 335 } 336 337 void unlock() { 338 conn.unlock(); 339 } 340 341 this(MySQLConnection conn) { 342 this.conn = conn; 343 } 344 345 ResultSetMetaData createMetadata(FieldDescription[] fields) { 346 ColumnMetadataItem[] res = new ColumnMetadataItem[fields.length]; 347 foreach(i, field; fields) { 348 ColumnMetadataItem item = new ColumnMetadataItem(); 349 item.schemaName = field.db; 350 item.name = field.originalName; 351 item.label = field.name; 352 item.precision = field.length; 353 item.scale = field.scale; 354 item.isNullable = !field.notNull; 355 item.isSigned = !field.unsigned; 356 item.type = fromMySQLType(field.type); 357 // TODO: fill more params 358 res[i] = item; 359 } 360 return new ResultSetMetaDataImpl(res); 361 } 362 ParameterMetaData createMetadata(ParamDescription[] fields) { 363 ParameterMetaDataItem[] res = new ParameterMetaDataItem[fields.length]; 364 foreach(i, field; fields) { 365 ParameterMetaDataItem item = new ParameterMetaDataItem(); 366 item.precision = field.length; 367 item.scale = field.scale; 368 item.isNullable = !field.notNull; 369 item.isSigned = !field.unsigned; 370 item.type = fromMySQLType(field.type); 371 // TODO: fill more params 372 res[i] = item; 373 } 374 return new ParameterMetaDataImpl(res); 375 } 376 public: 377 MySQLConnection getConnection() { 378 checkClosed(); 379 return conn; 380 } 381 override ddbc.core.ResultSet executeQuery(string query) { 382 checkClosed(); 383 lock(); 384 scope(exit) unlock(); 385 try { 386 rs = querySet(conn.getConnection(), query); 387 resultSet = new MySQLResultSet(this, rs, createMetadata(conn.getConnection().resultFieldDescriptions)); 388 return resultSet; 389 } catch (Throwable e) { 390 throw new SQLException(e.msg ~ " - while execution of query " ~ query); 391 } 392 } 393 override int executeUpdate(string query) { 394 checkClosed(); 395 lock(); 396 scope(exit) unlock(); 397 ulong rowsAffected = 0; 398 try { 399 rowsAffected = exec(conn.getConnection(), query); 400 return cast(int)rowsAffected; 401 } catch (Throwable e) { 402 throw new SQLException(e.msg ~ " - while execution of query " ~ query); 403 } 404 } 405 override int executeUpdate(string query, out Variant insertId) { 406 checkClosed(); 407 lock(); 408 scope(exit) unlock(); 409 try { 410 ulong rowsAffected = exec(conn.getConnection(), query); 411 insertId = Variant(conn.getConnection().lastInsertID); 412 return cast(int)rowsAffected; 413 } catch (Throwable e) { 414 throw new SQLException(e.msg ~ " - while execution of query " ~ query); 415 } 416 } 417 override void close() { 418 checkClosed(); 419 lock(); 420 scope(exit) unlock(); 421 try { 422 closeResultSet(); 423 closed = true; 424 conn.onStatementClosed(this); 425 } catch (Throwable e) { 426 throw new SQLException(e); 427 } 428 } 429 void closeResultSet() { 430 if (resultSet !is null) { 431 resultSet.onStatementClosed(); 432 resultSet = null; 433 } 434 } 435 } 436 437 class MySQLPreparedStatement : MySQLStatement, PreparedStatement { 438 439 private string query; 440 private Prepared statement; 441 private int paramCount; 442 private ResultSetMetaData metadata; 443 private ParameterMetaData paramMetadata; 444 445 this(MySQLConnection conn, string query) { 446 super(conn); 447 this.query = query; 448 try { 449 this.statement = prepare(conn.getConnection(), query); 450 this.paramCount = this.statement.numArgs; 451 } catch (Throwable e) { 452 throw new SQLException(e); 453 } 454 } 455 void checkIndex(int index) { 456 if (index < 1 || index > paramCount) 457 throw new SQLException("Parameter index " ~ to!string(index) ~ " is out of range"); 458 } 459 Variant getParam(int index) { 460 checkIndex(index); 461 return this.statement.getArg( cast(ushort)(index - 1) ); 462 } 463 public: 464 465 /// Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. 466 override ResultSetMetaData getMetaData() { 467 checkClosed(); 468 lock(); 469 scope(exit) unlock(); 470 try { 471 if (metadata is null) { 472 metadata = createMetadata(this.statement.preparedFieldDescriptions); 473 } 474 return metadata; 475 } catch (Throwable e) { 476 throw new SQLException(e); 477 } 478 } 479 480 /// Retrieves the number, types and properties of this PreparedStatement object's parameters. 481 override ParameterMetaData getParameterMetaData() { 482 checkClosed(); 483 lock(); 484 scope(exit) unlock(); 485 try { 486 if (paramMetadata is null) { 487 paramMetadata = createMetadata(this.statement.preparedParamDescriptions); 488 } 489 return paramMetadata; 490 } catch (Throwable e) { 491 throw new SQLException(e); 492 } 493 } 494 495 override int executeUpdate() { 496 checkClosed(); 497 lock(); 498 scope(exit) unlock(); 499 try { 500 ulong rowsAffected = 0; 501 rowsAffected = this.statement.exec(); 502 return cast(int)rowsAffected; 503 } catch (Throwable e) { 504 throw new SQLException(e); 505 } 506 } 507 508 override int executeUpdate(out Variant insertId) { 509 checkClosed(); 510 lock(); 511 scope(exit) unlock(); 512 try { 513 ulong rowsAffected = 0; 514 rowsAffected = this.statement.exec(); 515 insertId = conn.getConnection().lastInsertID; 516 return cast(int)rowsAffected; 517 } catch (Throwable e) { 518 throw new SQLException(e); 519 } 520 } 521 522 override ddbc.core.ResultSet executeQuery() { 523 checkClosed(); 524 lock(); 525 scope(exit) unlock(); 526 try { 527 rs = this.statement.querySet(); 528 resultSet = new MySQLResultSet(this, rs, getMetaData()); 529 return resultSet; 530 } catch (Throwable e) { 531 throw new SQLException(e); 532 } 533 } 534 535 override void clearParameters() { 536 checkClosed(); 537 lock(); 538 scope(exit) unlock(); 539 try { 540 for (int i = 1; i <= paramCount; i++) 541 setNull(i); 542 } catch (Throwable e) { 543 throw new SQLException(e); 544 } 545 } 546 547 override void setFloat(int parameterIndex, float x) { 548 checkClosed(); 549 lock(); 550 scope(exit) unlock(); 551 checkIndex(parameterIndex); 552 try { 553 this.statement.setArg(parameterIndex-1, x); 554 } catch (Throwable e) { 555 throw new SQLException(e); 556 } 557 } 558 override void setDouble(int parameterIndex, double x){ 559 checkClosed(); 560 lock(); 561 scope(exit) unlock(); 562 checkIndex(parameterIndex); 563 try { 564 this.statement.setArg(parameterIndex-1, x); 565 } catch (Throwable e) { 566 throw new SQLException(e); 567 } 568 } 569 override void setBoolean(int parameterIndex, bool x) { 570 checkClosed(); 571 lock(); 572 scope(exit) unlock(); 573 checkIndex(parameterIndex); 574 try { 575 this.statement.setArg(parameterIndex-1, x); 576 } catch (Throwable e) { 577 throw new SQLException(e); 578 } 579 } 580 override void setLong(int parameterIndex, long x) { 581 checkClosed(); 582 lock(); 583 scope(exit) unlock(); 584 checkIndex(parameterIndex); 585 try { 586 this.statement.setArg(parameterIndex-1, x); 587 } catch (Throwable e) { 588 throw new SQLException(e); 589 } 590 } 591 override void setUlong(int parameterIndex, ulong x) { 592 checkClosed(); 593 lock(); 594 scope(exit) unlock(); 595 checkIndex(parameterIndex); 596 try { 597 this.statement.setArg(parameterIndex-1, x); 598 } catch (Throwable e) { 599 throw new SQLException(e); 600 } 601 } 602 override void setInt(int parameterIndex, int x) { 603 checkClosed(); 604 lock(); 605 scope(exit) unlock(); 606 checkIndex(parameterIndex); 607 try { 608 this.statement.setArg(parameterIndex-1, x); 609 } catch (Throwable e) { 610 throw new SQLException(e); 611 } 612 } 613 override void setUint(int parameterIndex, uint x) { 614 checkClosed(); 615 lock(); 616 scope(exit) unlock(); 617 checkIndex(parameterIndex); 618 try { 619 this.statement.setArg(parameterIndex-1, x); 620 } catch (Throwable e) { 621 throw new SQLException(e); 622 } 623 } 624 override void setShort(int parameterIndex, short x) { 625 checkClosed(); 626 lock(); 627 scope(exit) unlock(); 628 checkIndex(parameterIndex); 629 try { 630 this.statement.setArg(parameterIndex-1, x); 631 } catch (Throwable e) { 632 throw new SQLException(e); 633 } 634 } 635 override void setUshort(int parameterIndex, ushort x) { 636 checkClosed(); 637 lock(); 638 scope(exit) unlock(); 639 checkIndex(parameterIndex); 640 try { 641 this.statement.setArg(parameterIndex-1, x); 642 } catch (Throwable e) { 643 throw new SQLException(e); 644 } 645 } 646 override void setByte(int parameterIndex, byte x) { 647 checkClosed(); 648 lock(); 649 scope(exit) unlock(); 650 checkIndex(parameterIndex); 651 try { 652 this.statement.setArg(parameterIndex-1, x); 653 } catch (Throwable e) { 654 throw new SQLException(e); 655 } 656 } 657 override void setUbyte(int parameterIndex, ubyte x) { 658 checkClosed(); 659 lock(); 660 scope(exit) unlock(); 661 checkIndex(parameterIndex); 662 try { 663 this.statement.setArg(parameterIndex-1, x); 664 } catch (Throwable e) { 665 throw new SQLException(e); 666 } 667 } 668 override void setBytes(int parameterIndex, byte[] x) { 669 checkClosed(); 670 lock(); 671 scope(exit) unlock(); 672 checkIndex(parameterIndex); 673 try { 674 if (x.ptr is null) { 675 setNull(parameterIndex); 676 } else { 677 this.statement.setArg(parameterIndex-1, x); 678 } 679 } catch (Throwable e) { 680 throw new SQLException(e); 681 } 682 } 683 override void setUbytes(int parameterIndex, ubyte[] x) { 684 checkClosed(); 685 lock(); 686 scope(exit) unlock(); 687 checkIndex(parameterIndex); 688 try { 689 if (x.ptr is null) { 690 setNull(parameterIndex); 691 } else { 692 this.statement.setArg(parameterIndex-1, x); 693 } 694 } catch (Throwable e) { 695 throw new SQLException(e); 696 } 697 } 698 override void setString(int parameterIndex, string x) { 699 checkClosed(); 700 lock(); 701 scope(exit) unlock(); 702 checkIndex(parameterIndex); 703 try { 704 if (x.ptr is null) { 705 setNull(parameterIndex); 706 } else { 707 this.statement.setArg(parameterIndex-1, x); 708 } 709 } catch (Throwable e) { 710 throw new SQLException(e); 711 } 712 } 713 override void setDateTime(int parameterIndex, DateTime x) { 714 checkClosed(); 715 lock(); 716 scope(exit) unlock(); 717 checkIndex(parameterIndex); 718 try { 719 this.statement.setArg(parameterIndex-1, x); 720 } catch (Throwable e) { 721 throw new SQLException(e); 722 } 723 } 724 override void setDate(int parameterIndex, Date x) { 725 checkClosed(); 726 lock(); 727 scope(exit) unlock(); 728 checkIndex(parameterIndex); 729 try { 730 this.statement.setArg(parameterIndex-1, x); 731 } catch (Throwable e) { 732 throw new SQLException(e); 733 } 734 } 735 override void setTime(int parameterIndex, TimeOfDay x) { 736 checkClosed(); 737 lock(); 738 scope(exit) unlock(); 739 checkIndex(parameterIndex); 740 try { 741 this.statement.setArg(parameterIndex-1, x); 742 } catch (Throwable e) { 743 throw new SQLException(e); 744 } 745 } 746 override void setVariant(int parameterIndex, Variant x) { 747 checkClosed(); 748 lock(); 749 scope(exit) unlock(); 750 checkIndex(parameterIndex); 751 try { 752 if (x == null) { 753 setNull(parameterIndex); 754 } else { 755 this.statement.setArg(parameterIndex-1, x); 756 } 757 } catch (Throwable e) { 758 throw new SQLException(e); 759 } 760 } 761 override void setNull(int parameterIndex) { 762 checkClosed(); 763 lock(); 764 scope(exit) unlock(); 765 checkIndex(parameterIndex); 766 try { 767 this.statement.setNullArg(parameterIndex-1); 768 } catch (Throwable e) { 769 throw new SQLException(e); 770 } 771 } 772 override void setNull(int parameterIndex, int sqlType) { 773 checkClosed(); 774 lock(); 775 scope(exit) unlock(); 776 try { 777 setNull(parameterIndex); 778 } catch (Throwable e) { 779 throw new SQLException(e); 780 } 781 } 782 override void closeResultSet() { 783 this.statement.release(); 784 } 785 } 786 787 class MySQLResultSet : ResultSetImpl { 788 private MySQLStatement stmt; 789 private mysql.connection.ResultSet rs; 790 ResultSetMetaData metadata; 791 private bool closed; 792 private int currentRowIndex; 793 private int rowCount; 794 private int[string] columnMap; 795 private bool lastIsNull; 796 private int columnCount; 797 798 Variant getValue(int columnIndex) { 799 checkClosed(); 800 enforceEx!SQLException(columnIndex >= 1 && columnIndex <= columnCount, "Column index out of bounds: " ~ to!string(columnIndex)); 801 enforceEx!SQLException(currentRowIndex >= 0 && currentRowIndex < rowCount, "No current row in result set"); 802 lastIsNull = rs[currentRowIndex].isNull(columnIndex - 1); 803 Variant res; 804 if (!lastIsNull) 805 res = rs[currentRowIndex][columnIndex - 1]; 806 return res; 807 } 808 809 void checkClosed() { 810 if (closed) 811 throw new SQLException("Result set is already closed"); 812 } 813 814 public: 815 816 void lock() { 817 stmt.lock(); 818 } 819 820 void unlock() { 821 stmt.unlock(); 822 } 823 824 this(MySQLStatement stmt, mysql.connection.ResultSet resultSet, ResultSetMetaData metadata) { 825 this.stmt = stmt; 826 this.rs = resultSet; 827 this.metadata = metadata; 828 try { 829 closed = false; 830 rowCount = cast(int)rs.length; 831 currentRowIndex = -1; 832 foreach(key, val; rs.colNameIndicies) 833 columnMap[key] = cast(int)val; 834 columnCount = cast(int)rs.colNames.length; 835 } catch (Throwable e) { 836 throw new SQLException(e); 837 } 838 } 839 840 void onStatementClosed() { 841 closed = true; 842 } 843 string decodeTextBlob(ubyte[] data) { 844 char[] res = new char[data.length]; 845 foreach (i, ch; data) { 846 res[i] = cast(char)ch; 847 } 848 return to!string(res); 849 } 850 851 // ResultSet interface implementation 852 853 //Retrieves the number, types and properties of this ResultSet object's columns 854 override ResultSetMetaData getMetaData() { 855 checkClosed(); 856 lock(); 857 scope(exit) unlock(); 858 return metadata; 859 } 860 861 override void close() { 862 checkClosed(); 863 lock(); 864 scope(exit) unlock(); 865 stmt.closeResultSet(); 866 closed = true; 867 } 868 override bool first() { 869 checkClosed(); 870 lock(); 871 scope(exit) unlock(); 872 currentRowIndex = 0; 873 return currentRowIndex >= 0 && currentRowIndex < rowCount; 874 } 875 override bool isFirst() { 876 checkClosed(); 877 lock(); 878 scope(exit) unlock(); 879 return rowCount > 0 && currentRowIndex == 0; 880 } 881 override bool isLast() { 882 checkClosed(); 883 lock(); 884 scope(exit) unlock(); 885 return rowCount > 0 && currentRowIndex == rowCount - 1; 886 } 887 override bool next() { 888 checkClosed(); 889 lock(); 890 scope(exit) unlock(); 891 if (currentRowIndex + 1 >= rowCount) 892 return false; 893 currentRowIndex++; 894 return true; 895 } 896 897 override int findColumn(string columnName) { 898 checkClosed(); 899 lock(); 900 scope(exit) unlock(); 901 int * p = (columnName in columnMap); 902 if (!p) 903 throw new SQLException("Column " ~ columnName ~ " not found"); 904 return *p + 1; 905 } 906 907 override bool getBoolean(int columnIndex) { 908 checkClosed(); 909 lock(); 910 scope(exit) unlock(); 911 Variant v = getValue(columnIndex); 912 if (lastIsNull) 913 return false; 914 if (v.convertsTo!(bool)) 915 return v.get!(bool); 916 if (v.convertsTo!(int)) 917 return v.get!(int) != 0; 918 if (v.convertsTo!(long)) 919 return v.get!(long) != 0; 920 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to boolean"); 921 } 922 override ubyte getUbyte(int columnIndex) { 923 checkClosed(); 924 lock(); 925 scope(exit) unlock(); 926 Variant v = getValue(columnIndex); 927 if (lastIsNull) 928 return 0; 929 if (v.convertsTo!(ubyte)) 930 return v.get!(ubyte); 931 if (v.convertsTo!(long)) 932 return to!ubyte(v.get!(long)); 933 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to ubyte"); 934 } 935 override byte getByte(int columnIndex) { 936 checkClosed(); 937 lock(); 938 scope(exit) unlock(); 939 Variant v = getValue(columnIndex); 940 if (lastIsNull) 941 return 0; 942 if (v.convertsTo!(byte)) 943 return v.get!(byte); 944 if (v.convertsTo!(long)) 945 return to!byte(v.get!(long)); 946 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to byte"); 947 } 948 override short getShort(int columnIndex) { 949 checkClosed(); 950 lock(); 951 scope(exit) unlock(); 952 Variant v = getValue(columnIndex); 953 if (lastIsNull) 954 return 0; 955 if (v.convertsTo!(short)) 956 return v.get!(short); 957 if (v.convertsTo!(long)) 958 return to!short(v.get!(long)); 959 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to short"); 960 } 961 override ushort getUshort(int columnIndex) { 962 checkClosed(); 963 lock(); 964 scope(exit) unlock(); 965 Variant v = getValue(columnIndex); 966 if (lastIsNull) 967 return 0; 968 if (v.convertsTo!(ushort)) 969 return v.get!(ushort); 970 if (v.convertsTo!(long)) 971 return to!ushort(v.get!(long)); 972 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to ushort"); 973 } 974 override int getInt(int columnIndex) { 975 checkClosed(); 976 lock(); 977 scope(exit) unlock(); 978 Variant v = getValue(columnIndex); 979 if (lastIsNull) 980 return 0; 981 if (v.convertsTo!(int)) 982 return v.get!(int); 983 if (v.convertsTo!(long)) 984 return to!int(v.get!(long)); 985 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to int"); 986 } 987 override uint getUint(int columnIndex) { 988 checkClosed(); 989 lock(); 990 scope(exit) unlock(); 991 Variant v = getValue(columnIndex); 992 if (lastIsNull) 993 return 0; 994 if (v.convertsTo!(uint)) 995 return v.get!(uint); 996 if (v.convertsTo!(ulong)) 997 return to!int(v.get!(ulong)); 998 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to uint"); 999 } 1000 override long getLong(int columnIndex) { 1001 checkClosed(); 1002 lock(); 1003 scope(exit) unlock(); 1004 Variant v = getValue(columnIndex); 1005 if (lastIsNull) 1006 return 0; 1007 if (v.convertsTo!(long)) 1008 return v.get!(long); 1009 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to long"); 1010 } 1011 override ulong getUlong(int columnIndex) { 1012 checkClosed(); 1013 lock(); 1014 scope(exit) unlock(); 1015 Variant v = getValue(columnIndex); 1016 if (lastIsNull) 1017 return 0; 1018 if (v.convertsTo!(ulong)) 1019 return v.get!(ulong); 1020 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to ulong"); 1021 } 1022 override double getDouble(int columnIndex) { 1023 checkClosed(); 1024 lock(); 1025 scope(exit) unlock(); 1026 Variant v = getValue(columnIndex); 1027 if (lastIsNull) 1028 return 0; 1029 if (v.convertsTo!(double)) 1030 return v.get!(double); 1031 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to double"); 1032 } 1033 override float getFloat(int columnIndex) { 1034 checkClosed(); 1035 lock(); 1036 scope(exit) unlock(); 1037 Variant v = getValue(columnIndex); 1038 if (lastIsNull) 1039 return 0; 1040 if (v.convertsTo!(float)) 1041 return v.get!(float); 1042 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to float"); 1043 } 1044 override byte[] getBytes(int columnIndex) { 1045 checkClosed(); 1046 lock(); 1047 scope(exit) unlock(); 1048 Variant v = getValue(columnIndex); 1049 if (lastIsNull) 1050 return null; 1051 if (v.convertsTo!(byte[])) { 1052 return v.get!(byte[]); 1053 } 1054 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to byte[]"); 1055 } 1056 override ubyte[] getUbytes(int columnIndex) { 1057 checkClosed(); 1058 lock(); 1059 scope(exit) unlock(); 1060 Variant v = getValue(columnIndex); 1061 if (lastIsNull) 1062 return null; 1063 if (v.convertsTo!(ubyte[])) { 1064 return v.get!(ubyte[]); 1065 } 1066 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to ubyte[]"); 1067 } 1068 override string getString(int columnIndex) { 1069 checkClosed(); 1070 lock(); 1071 scope(exit) unlock(); 1072 Variant v = getValue(columnIndex); 1073 if (lastIsNull) 1074 return null; 1075 if (v.convertsTo!(ubyte[])) { 1076 // assume blob encoding is utf-8 1077 // TODO: check field encoding 1078 return decodeTextBlob(v.get!(ubyte[])); 1079 } 1080 return v.toString(); 1081 } 1082 override std.datetime.DateTime getDateTime(int columnIndex) { 1083 checkClosed(); 1084 lock(); 1085 scope(exit) unlock(); 1086 Variant v = getValue(columnIndex); 1087 if (lastIsNull) 1088 return DateTime(); 1089 if (v.convertsTo!(DateTime)) { 1090 return v.get!DateTime(); 1091 } 1092 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to DateTime"); 1093 } 1094 override std.datetime.Date getDate(int columnIndex) { 1095 checkClosed(); 1096 lock(); 1097 scope(exit) unlock(); 1098 Variant v = getValue(columnIndex); 1099 if (lastIsNull) 1100 return Date(); 1101 if (v.convertsTo!(Date)) { 1102 return v.get!Date(); 1103 } 1104 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to Date"); 1105 } 1106 override std.datetime.TimeOfDay getTime(int columnIndex) { 1107 checkClosed(); 1108 lock(); 1109 scope(exit) unlock(); 1110 Variant v = getValue(columnIndex); 1111 if (lastIsNull) 1112 return TimeOfDay(); 1113 if (v.convertsTo!(TimeOfDay)) { 1114 return v.get!TimeOfDay(); 1115 } 1116 throw new SQLException("Cannot convert field " ~ to!string(columnIndex) ~ " to TimeOfDay"); 1117 } 1118 1119 override Variant getVariant(int columnIndex) { 1120 checkClosed(); 1121 lock(); 1122 scope(exit) unlock(); 1123 Variant v = getValue(columnIndex); 1124 if (lastIsNull) { 1125 Variant vnull = null; 1126 return vnull; 1127 } 1128 return v; 1129 } 1130 override bool wasNull() { 1131 checkClosed(); 1132 lock(); 1133 scope(exit) unlock(); 1134 return lastIsNull; 1135 } 1136 override bool isNull(int columnIndex) { 1137 checkClosed(); 1138 lock(); 1139 scope(exit) unlock(); 1140 enforceEx!SQLException(columnIndex >= 1 && columnIndex <= columnCount, "Column index out of bounds: " ~ to!string(columnIndex)); 1141 enforceEx!SQLException(currentRowIndex >= 0 && currentRowIndex < rowCount, "No current row in result set"); 1142 return rs[currentRowIndex].isNull(columnIndex - 1); 1143 } 1144 1145 //Retrieves the Statement object that produced this ResultSet object. 1146 override Statement getStatement() { 1147 checkClosed(); 1148 lock(); 1149 scope(exit) unlock(); 1150 return stmt; 1151 } 1152 1153 //Retrieves the current row number 1154 override int getRow() { 1155 checkClosed(); 1156 lock(); 1157 scope(exit) unlock(); 1158 if (currentRowIndex <0 || currentRowIndex >= rowCount) 1159 return 0; 1160 return currentRowIndex + 1; 1161 } 1162 1163 //Retrieves the fetch size for this ResultSet object. 1164 override int getFetchSize() { 1165 checkClosed(); 1166 lock(); 1167 scope(exit) unlock(); 1168 return rowCount; 1169 } 1170 } 1171 1172 // sample URL: 1173 // mysql://localhost:3306/DatabaseName 1174 class MySQLDriver : Driver { 1175 // helper function 1176 public static string generateUrl(string host, ushort port, string dbname) { 1177 return "mysql://" ~ host ~ ":" ~ to!string(port) ~ "/" ~ dbname; 1178 } 1179 public static string[string] setUserAndPassword(string username, string password) { 1180 string[string] params; 1181 params["user"] = username; 1182 params["password"] = password; 1183 return params; 1184 } 1185 override ddbc.core.Connection connect(string url, string[string] params) { 1186 //writeln("MySQLDriver.connect " ~ url); 1187 return new MySQLConnection(url, params); 1188 } 1189 } 1190 1191 unittest { 1192 static if (MYSQL_TESTS_ENABLED) { 1193 1194 DataSource ds = createUnitTestMySQLDataSource(); 1195 1196 auto conn = ds.getConnection(); 1197 scope(exit) conn.close(); 1198 auto stmt = conn.createStatement(); 1199 scope(exit) stmt.close(); 1200 1201 assert(stmt.executeUpdate("DROP TABLE IF EXISTS ddbct1") == 0); 1202 assert(stmt.executeUpdate("CREATE TABLE IF NOT EXISTS ddbct1 (id bigint not null primary key AUTO_INCREMENT, name varchar(250), comment mediumtext, ts datetime)") == 0); 1203 assert(stmt.executeUpdate("INSERT INTO ddbct1 SET id=1, name='name1', comment='comment for line 1', ts='20130202123025'") == 1); 1204 assert(stmt.executeUpdate("INSERT INTO ddbct1 SET id=2, name='name2', comment='comment for line 2 - can be very long'") == 1); 1205 assert(stmt.executeUpdate("INSERT INTO ddbct1 SET id=3, name='name3', comment='this is line 3'") == 1); 1206 assert(stmt.executeUpdate("INSERT INTO ddbct1 SET id=4, name='name4', comment=NULL") == 1); 1207 assert(stmt.executeUpdate("INSERT INTO ddbct1 SET id=5, name=NULL, comment=''") == 1); 1208 assert(stmt.executeUpdate("INSERT INTO ddbct1 SET id=6, name='', comment=NULL") == 1); 1209 assert(stmt.executeUpdate("UPDATE ddbct1 SET name=concat(name, '_x') WHERE id IN (3, 4)") == 2); 1210 1211 PreparedStatement ps = conn.prepareStatement("UPDATE ddbct1 SET name=? WHERE id=?"); 1212 ps.setString(1, null); 1213 ps.setLong(2, 3); 1214 assert(ps.executeUpdate() == 1); 1215 1216 auto rs = stmt.executeQuery("SELECT id, name name_alias, comment, ts FROM ddbct1 ORDER BY id"); 1217 1218 // testing result set meta data 1219 ResultSetMetaData meta = rs.getMetaData(); 1220 assert(meta.getColumnCount() == 4); 1221 assert(meta.getColumnName(1) == "id"); 1222 assert(meta.getColumnLabel(1) == "id"); 1223 assert(meta.isNullable(1) == false); 1224 assert(meta.isNullable(2) == true); 1225 assert(meta.isNullable(3) == true); 1226 assert(meta.getColumnName(2) == "name"); 1227 assert(meta.getColumnLabel(2) == "name_alias"); 1228 assert(meta.getColumnName(3) == "comment"); 1229 1230 int rowCount = rs.getFetchSize(); 1231 assert(rowCount == 6); 1232 int index = 1; 1233 while (rs.next()) { 1234 assert(!rs.isNull(1)); 1235 ubyte[] bytes = rs.getUbytes(3); 1236 int rowIndex = rs.getRow(); 1237 assert(rowIndex == index); 1238 long id = rs.getLong(1); 1239 assert(id == index); 1240 //writeln("field2 = '" ~ rs.getString(2) ~ "'"); 1241 //writeln("field3 = '" ~ rs.getString(3) ~ "'"); 1242 //writeln("wasNull = " ~ to!string(rs.wasNull())); 1243 if (id == 1) { 1244 DateTime ts = rs.getDateTime(4); 1245 assert(ts == DateTime(2013,02,02,12,30,25)); 1246 } 1247 if (id == 4) { 1248 assert(rs.getString(2) == "name4_x"); 1249 assert(rs.isNull(3)); 1250 } 1251 if (id == 5) { 1252 assert(rs.isNull(2)); 1253 assert(!rs.isNull(3)); 1254 } 1255 if (id == 6) { 1256 assert(!rs.isNull(2)); 1257 assert(rs.isNull(3)); 1258 } 1259 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3)) ~ "\t[" ~ to!string(bytes.length) ~ "]"); 1260 index++; 1261 } 1262 1263 PreparedStatement ps2 = conn.prepareStatement("SELECT id, name, comment FROM ddbct1 WHERE id >= ?"); 1264 scope(exit) ps2.close(); 1265 ps2.setLong(1, 3); 1266 rs = ps2.executeQuery(); 1267 while (rs.next()) { 1268 //writeln(to!string(rs.getLong(1)) ~ "\t" ~ rs.getString(2) ~ "\t" ~ strNull(rs.getString(3))); 1269 index++; 1270 } 1271 1272 // checking last insert ID for prepared statement 1273 PreparedStatement ps3 = conn.prepareStatement("INSERT INTO ddbct1 (name) values ('New String 1')"); 1274 scope(exit) ps3.close(); 1275 Variant newId; 1276 assert(ps3.executeUpdate(newId) == 1); 1277 //writeln("Generated insert id = " ~ newId.toString()); 1278 assert(newId.get!ulong > 0); 1279 1280 // checking last insert ID for normal statement 1281 Statement stmt4 = conn.createStatement(); 1282 scope(exit) stmt4.close(); 1283 Variant newId2; 1284 assert(stmt.executeUpdate("INSERT INTO ddbct1 (name) values ('New String 2')", newId2) == 1); 1285 //writeln("Generated insert id = " ~ newId2.toString()); 1286 assert(newId2.get!ulong > 0); 1287 1288 } 1289 } 1290 1291 __gshared static this() { 1292 // register MySQLDriver 1293 import ddbc.common; 1294 DriverFactory.registerDriverFactory("mysql", delegate() { return new MySQLDriver(); }); 1295 } 1296 1297 1298 } else { // version(USE_MYSQL) 1299 version(unittest) { 1300 immutable bool MYSQL_TESTS_ENABLED = false; 1301 } 1302 }