mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs

   1 //
   2 // Mono.Data.SqliteClient.SqliteCommand.cs
   3 //
   4 // Represents a Transact-SQL statement or stored procedure to execute against
   5 // a Sqlite database file.
   6 //
   7 // Author(s):   Vladimir Vukicevic  <vladimir@pobox.com>
   8 //              Everaldo Canuto  <everaldo_canuto@yahoo.com.br>
   9 //              Chris Turchin <chris@turchin.net>
  10 //              Jeroen Zwartepoorte <jeroen@xs4all.nl>
  11 //              Thomas Zoechling <thomas.zoechling@gmx.at>
  12 //              Joshua Tauberer <tauberer@for.net>
  13 //
  14 // Copyright (C) 2002  Vladimir Vukicevic
  15 //
  16 // Permission is hereby granted, free of charge, to any person obtaining
  17 // a copy of this software and associated documentation files (the
  18 // "Software"), to deal in the Software without restriction, including
  19 // without limitation the rights to use, copy, modify, merge, publish,
  20 // distribute, sublicense, and/or sell copies of the Software, and to
  21 // permit persons to whom the Software is furnished to do so, subject to
  22 // the following conditions:
  23 //
  24 // The above copyright notice and this permission notice shall be
  25 // included in all copies or substantial portions of the Software.
  26 //
  27 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  28 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  29 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  30 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  31 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  32 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  33 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  34 //
  35
  36 using System;
  37 using System.Collections;
  38 using System.Text;
  39 using System.Runtime.InteropServices;
  40 using System.Text.RegularExpressions;
  41 using System.Data;
  42 #if NET_2_0
  43 using System.Data.Common;
  44 #endif
  45 using System.Diagnostics;
  46 using Group = System.Text.RegularExpressions.Group;
  47
  48 namespace Mono.Data.SqliteClient
  49 {
  50 #if NET_2_0
  51         public class SqliteCommand : DbCommand, ICloneable
  52 #else
  53         public class SqliteCommand : IDbCommand, ICloneable
  54 #endif
  55         {
  56                 #region Fields
  57
  58                 private SqliteConnection parent_conn;
  59                 //private SqliteTransaction transaction;
  60                 private IDbTransaction transaction;
  61                 private string sql;
  62                 private int timeout;
  63                 private CommandType type;
  64                 private UpdateRowSource upd_row_source;
  65                 private SqliteParameterCollection sql_params;
  66                 private bool prepared = false;
  67                 private bool _designTimeVisible = true;
  68
  69                 #endregion
  70
  71                 #region Constructors and destructors
  72
  73                 public SqliteCommand ()
  74                 {
  75                         sql = "";
  76                 }
  77
  78                 public SqliteCommand (string sqlText)
  79                 {
  80                         sql = sqlText;
  81                 }
  82
  83                 public SqliteCommand (string sqlText, SqliteConnection dbConn)
  84                 {
  85                         sql = sqlText;
  86                         parent_conn = dbConn;
  87                 }
  88
  89                 public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
  90                 {
  91                         sql = sqlText;
  92                         parent_conn = dbConn;
  93                         transaction = trans;
  94                 }
  95
  96 #if !NET_2_0
  97                 public void Dispose ()
  98                 {
  99                 }
 100 #endif
 101
 102                 #endregion
 103
 104                 #region Properties
 105
 106 #if NET_2_0
 107                 override
 108 #endif
 109                 public string CommandText
 110                 {
 111                         get { return sql; }
 112                         set { sql = value; prepared = false; }
 113                 }
 114
 115 #if NET_2_0
 116                 override
 117 #endif
 118                 public int CommandTimeout
 119                 {
 120                         get { return timeout; }
 121                         set { timeout = value; }
 122                 }
 123
 124 #if NET_2_0
 125                 override
 126 #endif
 127                 public CommandType CommandType
 128                 {
 129                         get { return type; }
 130                         set { type = value; }
 131                 }
 132
 133 #if NET_2_0
 134                 protected override DbConnection DbConnection
 135                 {
 136                         get { return parent_conn; }
 137                         set { parent_conn = (SqliteConnection)value; }
 138                 }
 139 #endif
 140                 public SqliteConnection Connection
 141                 {
 142                         get { return parent_conn; }
 143                         set { parent_conn = (SqliteConnection)value; }
 144                 }
 145
 146 #if !NET_2_0
 147                 IDbConnection IDbCommand.Connection
 148                 {
 149                         get
 150                         {
 151                                 return parent_conn;
 152                         }
 153                         set
 154                         {
 155                                 if (!(value is SqliteConnection))
 156                                 {
 157                                         throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
 158                                 }
 159                                 parent_conn = (SqliteConnection) value;
 160                         }
 161                 }
 162 #endif
 163
 164                 public
 165 #if NET_2_0
 166                 new
 167 #endif
 168                 SqliteParameterCollection Parameters
 169
 170                 {
 171                         get
 172                         {
 173                                 if (sql_params == null)
 174                                         sql_params = new SqliteParameterCollection();
 175                                 return sql_params;
 176                         }
 177                 }
 178
 179 #if NET_2_0
 180                 protected override DbParameterCollection DbParameterCollection
 181                 {
 182                         get { return (DbParameterCollection) Parameters; }
 183                 }
 184 #endif
 185
 186 #if !NET_2_0
 187                 IDataParameterCollection IDbCommand.Parameters
 188                 {
 189                         get { return Parameters; }
 190                 }
 191
 192 #endif
 193
 194 #if NET_2_0
 195                 protected override DbTransaction DbTransaction
 196 #else
 197                 public IDbTransaction Transaction
 198 #endif
 199                 {
 200                         get {
 201 #if NET_2_0
 202                                 return (DbTransaction)transaction;
 203 #else
 204                                 return transaction;
 205 #endif
 206                         }
 207                         set { transaction = value; }
 208                 }
 209
 210 #if NET_2_0
 211                 public override bool DesignTimeVisible
 212                 {
 213                         get { return _designTimeVisible; }
 214                         set { _designTimeVisible = value; }
 215                 }
 216 #endif
 217
 218 #if NET_2_0
 219                 override
 220 #endif
 221                 public UpdateRowSource UpdatedRowSource
 222                 {
 223                         get { return upd_row_source; }
 224                         set { upd_row_source = value; }
 225                 }
 226
 227                 #endregion
 228
 229                 #region Internal Methods
 230
 231                 internal int NumChanges ()
 232                 {
 233                         if (parent_conn.Version == 3)
 234                                 return Sqlite.sqlite3_changes(parent_conn.Handle);
 235                         else
 236                                 return Sqlite.sqlite_changes(parent_conn.Handle);
 237                 }
 238
 239                 private void BindParameters3 (IntPtr pStmt)
 240                 {
 241                         if (sql_params == null) return;
 242                         if (sql_params.Count == 0) return;
 243
 244                         int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
 245
 246                         for (int i = 1; i <= pcount; i++)
 247                         {
 248                                 String name = Sqlite.HeapToString (Sqlite.sqlite3_bind_parameter_name (pStmt, i), Encoding.UTF8);
 249
 250                                 SqliteParameter param = null;
 251                                 if (name != null)
 252                                         param = sql_params[name] as SqliteParameter;
 253                                 else
 254                                         param = sql_params[i-1] as SqliteParameter;
 255
 256                                 if (param.Value == null) {
 257                                         Sqlite.sqlite3_bind_null (pStmt, i);
 258                                         continue;
 259                                 }
 260
 261                                 Type ptype = param.Value.GetType ();
 262                                 if (ptype.IsEnum)
 263                                         ptype = Enum.GetUnderlyingType (ptype);
 264
 265                                 SqliteError err;
 266
 267                                 if (ptype.Equals (typeof (String)))
 268                                 {
 269                                         String s = (String)param.Value;
 270                                         err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, -1, (IntPtr)(-1));
 271                                 }
 272                                 else if (ptype.Equals (typeof (DBNull)))
 273                                 {
 274                                         err = Sqlite.sqlite3_bind_null (pStmt, i);
 275                                 }
 276                                 else if (ptype.Equals (typeof (Boolean)))
 277                                 {
 278                                         bool b = (bool)param.Value;
 279                                         err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
 280                                 } else if (ptype.Equals (typeof (Byte)))
 281                                 {
 282                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
 283                                 }
 284                                 else if (ptype.Equals (typeof (Char)))
 285                                 {
 286                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
 287                                 }
 288                                 else if (ptype.IsEnum)
 289                                 {
 290                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 291                                 }
 292                                 else if (ptype.Equals (typeof (Int16)))
 293                                 {
 294                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
 295                                 }
 296                                 else if (ptype.Equals (typeof (Int32)))
 297                                 {
 298                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 299                                 }
 300                                 else if (ptype.Equals (typeof (SByte)))
 301                                 {
 302                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
 303                                 }
 304                                 else if (ptype.Equals (typeof (UInt16)))
 305                                 {
 306                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
 307                                 }
 308                                 else if (ptype.Equals (typeof (DateTime)))
 309                                 {
 310                                         DateTime dt = (DateTime)param.Value;
 311                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
 312                                 }
 313                                 else if (ptype.Equals (typeof (Double)))
 314                                 {
 315                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
 316                                 }
 317                                 else if (ptype.Equals (typeof (Single)))
 318                                 {
 319                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
 320                                 }
 321                                 else if (ptype.Equals (typeof (UInt32)))
 322                                 {
 323                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
 324                                 }
 325                                 else if (ptype.Equals (typeof (Int64)))
 326                                 {
 327                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
 328                                 }
 329                                 else if (ptype.Equals (typeof (Byte[])))
 330                                 {
 331                                         err = Sqlite.sqlite3_bind_blob (pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1));
 332                                 }
 333                                 else
 334                                 {
 335                                         throw new ApplicationException("Unkown Parameter Type");
 336                                 }
 337                                 if (err != SqliteError.OK)
 338                                 {
 339                                         throw new ApplicationException ("Sqlite error in bind " + err);
 340                                 }
 341                         }
 342                 }
 343
 344                 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
 345                 {
 346                         if (parent_conn.Version == 3)
 347                         {
 348                                 SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
 349                                 if (err != SqliteError.OK)
 350                                         throw new SqliteSyntaxException (GetError3());
 351                         }
 352                         else
 353                         {
 354                                 IntPtr errMsg;
 355                                 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
 356
 357                                 if (err != SqliteError.OK)
 358                                 {
 359                                         string msg = "unknown error";
 360                                         if (errMsg != IntPtr.Zero)
 361                                         {
 362                                                 msg = Marshal.PtrToStringAnsi (errMsg);
 363                                                 Sqlite.sqliteFree (errMsg);
 364                                         }
 365                                         throw new SqliteSyntaxException (msg);
 366                                 }
 367                         }
 368                 }
 369
 370                 // Executes a statement and ignores its result.
 371                 private void ExecuteStatement (IntPtr pStmt) {
 372                         int cols;
 373                         IntPtr pazValue, pazColName;
 374                         ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
 375                 }
 376
 377                 // Executes a statement and returns whether there is more data available.
 378                 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
 379                         SqliteError err;
 380
 381                         if (parent_conn.Version == 3)
 382                         {
 383                                 err = Sqlite.sqlite3_step (pStmt);
 384                                 if (err == SqliteError.ERROR)
 385                                         throw new SqliteExecutionException (GetError3());
 386                                 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
 387                                 cols = Sqlite.sqlite3_column_count (pStmt);
 388                         }
 389                         else
 390                         {
 391                                 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
 392                                 if (err == SqliteError.ERROR)
 393                                         throw new SqliteExecutionException ();
 394                         }
 395
 396                         if (err == SqliteError.BUSY)
 397                                 throw new SqliteBusyException();
 398
 399                         if (err == SqliteError.MISUSE)
 400                                 throw new SqliteExecutionException();
 401
 402                         // err is either ROW or DONE.
 403                         return err == SqliteError.ROW;
 404                 }
 405
 406                 #endregion
 407
 408                 #region Public Methods
 409
 410                 object ICloneable.Clone ()
 411                 {
 412                         return new SqliteCommand (sql, parent_conn, transaction);
 413                 }
 414 #if NET_2_0
 415                 override
 416 #endif
 417                 public void Cancel ()
 418                 {
 419                 }
 420
 421                 public string BindParameters2()
 422                 {
 423                         string text = sql;
 424
 425                         // There used to be a crazy regular expression here, but it caused Mono
 426                         // to go into an infinite loop of some sort when there were no parameters
 427                         // in the SQL string.  That was too complicated anyway.
 428
 429                         // Here we search for substrings of the form [:?]wwwww where w is a letter or digit
 430                         // (not sure what a legitimate Sqlite3 identifier is), except those within quotes.
 431
 432                         char inquote = (char)0;
 433                         int counter = 0;
 434                         for (int i = 0; i < text.Length; i++) {
 435                                 char c = text[i];
 436                                 if (c == inquote) {
 437                                         inquote = (char)0;
 438                                 } else if (inquote == (char)0 && (c == '\'' || c == '"')) {
 439                                         inquote = c;
 440                                 } else if (inquote == (char)0 && (c == ':' || c == '?')) {
 441                                         int start = i;
 442                                         while (++i < text.Length && char.IsLetterOrDigit(text[i])) { } // scan to end
 443                                         string name = text.Substring(start, i-start);
 444                                         SqliteParameter p;
 445                                         if (name.Length > 1)
 446                                                 p = Parameters[name] as SqliteParameter;
 447                                         else
 448                                                 p = Parameters[counter] as SqliteParameter;
 449                                         string value = "'" + Convert.ToString(p.Value).Replace("'", "''") + "'";
 450                                         text = text.Remove(start, name.Length).Insert(start, value);
 451                                         i += value.Length - name.Length - 1;
 452                                         counter++;
 453                                 }
 454                         }
 455
 456                         return text;
 457                 }
 458
 459 #if NET_2_0
 460                 override
 461 #endif
 462                 public void Prepare ()
 463                 {
 464                         // There isn't much we can do here.  If a table schema
 465                         // changes after preparing a statement, Sqlite bails,
 466                         // so we can only compile statements right before we
 467                         // want to run them.
 468
 469                         if (prepared) return;
 470
 471                         if (Parameters.Count > 0 && parent_conn.Version == 2)
 472                         {
 473                                 sql = BindParameters2();
 474                         }
 475
 476                         prepared = true;
 477                 }
 478
 479 #if !NET_2_0
 480                 IDbDataParameter IDbCommand.CreateParameter()
 481                 {
 482                         return CreateParameter ();
 483                 }
 484 #endif
 485
 486 #if NET_2_0
 487                 protected override DbParameter CreateDbParameter ()
 488 #else
 489                 public SqliteParameter CreateParameter ()
 490 #endif
 491                 {
 492                         return new SqliteParameter ();
 493                 }
 494
 495 #if NET_2_0
 496                 override
 497 #endif
 498                 public int ExecuteNonQuery ()
 499                 {
 500                         int rows_affected;
 501                         ExecuteReader (CommandBehavior.Default, false, out rows_affected);
 502                         return rows_affected;
 503                 }
 504
 505 #if NET_2_0
 506                 override
 507 #endif
 508                 public object ExecuteScalar ()
 509                 {
 510                         SqliteDataReader r = (SqliteDataReader)ExecuteReader ();
 511                         if (r == null || !r.Read ()) {
 512                                 return null;
 513                         }
 514                         object o = r[0];
 515                         r.Close ();
 516                         return o;
 517                 }
 518
 519 #if !NET_2_0
 520                 IDataReader IDbCommand.ExecuteReader ()
 521                 {
 522                         return ExecuteReader ();
 523                 }
 524
 525                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 526                 {
 527                         return ExecuteReader (behavior);
 528                 }
 529
 530                 public SqliteDataReader ExecuteReader ()
 531                 {
 532                         return ExecuteReader (CommandBehavior.Default);
 533                 }
 534 #endif
 535
 536                 public new SqliteDataReader ExecuteReader (CommandBehavior behavior)
 537                 {
 538                         int r;
 539                         return ExecuteReader (behavior, true, out r);
 540                 }
 541
 542 #if NET_2_0
 543                 public new SqliteDataReader ExecuteReader ()
 544                 {
 545                         return ExecuteReader (CommandBehavior.Default);
 546                 }
 547
 548                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 549                 {
 550                         return (DbDataReader) ExecuteReader (behavior);
 551                 }
 552 #endif
 553
 554                 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
 555                 {
 556                         Prepare ();
 557
 558                         // The SQL string may contain multiple sql commands, so the main
 559                         // thing to do is have Sqlite iterate through the commands.
 560                         // If want_results, only the last command is returned as a
 561                         // DataReader.  Otherwise, no command is returned as a
 562                         // DataReader.
 563
 564                         IntPtr psql; // pointer to SQL command
 565
 566                         // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
 567                         // character (iso8859).  But if you give the --enable-utf8 option to the configure script, then the
 568                         // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
 569                         // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
 570                         // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
 571                         // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
 572                         //
 573                         // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
 574                         // default.  Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
 575                         // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
 576                         // of what Sqlite is treating them as,
 577
 578                         // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
 579
 580                         if (parent_conn.Version == 2)
 581                                 psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
 582                         else
 583                                 psql = Marshal.StringToHGlobalUni (sql.Trim());
 584
 585                         IntPtr pzTail = psql;
 586                         IntPtr errMsgPtr;
 587
 588                         parent_conn.StartExec ();
 589
 590                         rows_affected = 0;
 591
 592                         try {
 593                                 while (true) {
 594                                         IntPtr pStmt;
 595
 596                                         GetNextStatement(pzTail, out pzTail, out pStmt);
 597
 598                                         if (pStmt == IntPtr.Zero)
 599                                                 throw new Exception();
 600
 601                                         // pzTail is positioned after the last byte in the
 602                                         // statement, which will be the NULL character if
 603                                         // this was the last statement.
 604                                         bool last = Marshal.ReadByte(pzTail) == 0;
 605
 606                                         try {
 607                                                 if (parent_conn.Version == 3)
 608                                                         BindParameters3 (pStmt);
 609
 610                                                 if (last && want_results)
 611                                                         return new SqliteDataReader (this, pStmt, parent_conn.Version);
 612
 613                                                 ExecuteStatement(pStmt);
 614
 615                                                 if (last) // rows_affected is only used if !want_results
 616                                                         rows_affected = NumChanges ();
 617
 618                                         } finally {
 619                                                 if (parent_conn.Version == 3)
 620                                                         Sqlite.sqlite3_finalize (pStmt);
 621                                                 else
 622                                                         Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
 623                                         }
 624
 625                                         if (last) break;
 626                                 }
 627
 628                                 return null;
 629                         } finally {
 630                                 parent_conn.EndExec ();
 631                                 Marshal.FreeHGlobal (psql);
 632                         }
 633                 }
 634
 635                 public int LastInsertRowID ()
 636                 {
 637                         return parent_conn.LastInsertRowId;
 638                 }
 639
 640                 private string GetError3() {
 641                         return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));
 642                 }
 643         #endregion
 644         }
 645 }