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

   1 //
   2 // Mono.Data.Sqlite.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 #if !NET_2_0
  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 using System.Diagnostics;
  43 using Group = System.Text.RegularExpressions.Group;
  44
  45 namespace Mono.Data.Sqlite
  46 {
  47         public class SqliteCommand : IDbCommand, ICloneable
  48         {
  49                 #region Fields
  50
  51                 private SqliteConnection parent_conn;
  52                 //private SqliteTransaction transaction;
  53                 private IDbTransaction transaction;
  54                 private string sql;
  55                 private int timeout;
  56                 private CommandType type;
  57                 private UpdateRowSource upd_row_source;
  58                 private SqliteParameterCollection sql_params;
  59                 private bool prepared = false;
  60                 private bool _designTimeVisible = true;
  61
  62                 #endregion
  63
  64                 #region Constructors and destructors
  65
  66                 public SqliteCommand ()
  67                 {
  68                         sql = "";
  69                 }
  70
  71                 public SqliteCommand (string sqlText)
  72                 {
  73                         sql = sqlText;
  74                 }
  75
  76                 public SqliteCommand (string sqlText, SqliteConnection dbConn)
  77                 {
  78                         sql = sqlText;
  79                         parent_conn = dbConn;
  80                 }
  81
  82                 public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
  83                 {
  84                         sql = sqlText;
  85                         parent_conn = dbConn;
  86                         transaction = trans;
  87                 }
  88
  89                 public void Dispose ()
  90                 {
  91                 }
  92
  93                 #endregion
  94
  95                 #region Properties
  96                 public string CommandText
  97                 {
  98                         get { return sql; }
  99                         set { sql = value; prepared = false; }
 100                 }
 101
 102                 public int CommandTimeout
 103                 {
 104                         get { return timeout; }
 105                         set { timeout = value; }
 106                 }
 107
 108                 public CommandType CommandType
 109                 {
 110                         get { return type; }
 111                         set { type = value; }
 112                 }
 113
 114                 public SqliteConnection Connection
 115                 {
 116                         get { return parent_conn; }
 117                         set { parent_conn = (SqliteConnection)value; }
 118                 }
 119
 120                 IDbConnection IDbCommand.Connection
 121                 {
 122                         get
 123                         {
 124                                 return parent_conn;
 125                         }
 126                         set
 127                         {
 128                                 if (!(value is SqliteConnection))
 129                                 {
 130                                         throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
 131                                 }
 132                                 parent_conn = (SqliteConnection) value;
 133                         }
 134                 }
 135
 136                 public SqliteParameterCollection Parameters {
 137                         get {
 138                                 if (sql_params == null)
 139                                         sql_params = new SqliteParameterCollection();
 140                                 return sql_params;
 141                         }
 142                 }
 143
 144                 IDataParameterCollection IDbCommand.Parameters  {
 145                         get { return Parameters; }
 146                 }
 147
 148                 public IDbTransaction Transaction {
 149                         get { return transaction; }
 150                         set { transaction = value; }
 151                 }
 152
 153                 public UpdateRowSource UpdatedRowSource
 154                 {
 155                         get { return upd_row_source; }
 156                         set { upd_row_source = value; }
 157                 }
 158                 #endregion
 159
 160                 #region Internal Methods
 161
 162                 internal int NumChanges ()
 163                 {
 164                         if (parent_conn.Version == 3)
 165                                 return Sqlite.sqlite3_changes(parent_conn.Handle);
 166                         else
 167                                 return Sqlite.sqlite_changes(parent_conn.Handle);
 168                 }
 169
 170                 private void BindParameters3 (IntPtr pStmt)
 171                 {
 172                         if (sql_params == null) return;
 173                         if (sql_params.Count == 0) return;
 174                         int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
 175                         for (int i = 1; i <= pcount; i++)
 176                         {
 177                                 String name = Sqlite.HeapToString (Sqlite.sqlite3_bind_parameter_name (pStmt, i), Encoding.UTF8);
 178                                 SqliteParameter param = null;
 179                                 if (name != null)
 180                                         param = sql_params[name] as SqliteParameter;
 181                                 else
 182                                         param = sql_params[i-1] as SqliteParameter;
 183
 184                                 if (param.Value == null) {
 185                                         Sqlite.sqlite3_bind_null (pStmt, i);
 186                                         continue;
 187                                 }
 188
 189                                 Type ptype = param.Value.GetType ();
 190                                 if (ptype.IsEnum)
 191                                         ptype = Enum.GetUnderlyingType (ptype);
 192
 193                                 SqliteError err;
 194
 195                                 if (ptype.Equals (typeof (String)))
 196                                 {
 197                                         String s = (String)param.Value;
 198                                         err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, -1, (IntPtr)(-1));
 199                                 }
 200                                 else if (ptype.Equals (typeof (DBNull)))
 201                                 {
 202                                         err = Sqlite.sqlite3_bind_null (pStmt, i);
 203                                 }
 204                                 else if (ptype.Equals (typeof (Boolean)))
 205                                 {
 206                                         bool b = (bool)param.Value;
 207                                         err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
 208                                 } else if (ptype.Equals (typeof (Byte)))
 209                                 {
 210                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
 211                                 }
 212                                 else if (ptype.Equals (typeof (Char)))
 213                                 {
 214                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
 215                                 }
 216                                 else if (ptype.IsEnum)
 217                                 {
 218                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 219                                 }
 220                                 else if (ptype.Equals (typeof (Int16)))
 221                                 {
 222                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
 223                                 }
 224                                 else if (ptype.Equals (typeof (Int32)))
 225                                 {
 226                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 227                                 }
 228                                 else if (ptype.Equals (typeof (SByte)))
 229                                 {
 230                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
 231                                 }
 232                                 else if (ptype.Equals (typeof (UInt16)))
 233                                 {
 234                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
 235                                 }
 236                                 else if (ptype.Equals (typeof (DateTime)))
 237                                 {
 238                                         DateTime dt = (DateTime)param.Value;
 239                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
 240                                 }
 241                                 else if (ptype.Equals (typeof (Double)))
 242                                 {
 243                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
 244                                 }
 245                                 else if (ptype.Equals (typeof (Single)))
 246                                 {
 247                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
 248                                 }
 249                                 else if (ptype.Equals (typeof (UInt32)))
 250                                 {
 251                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
 252                                 }
 253                                 else if (ptype.Equals (typeof (Int64)))
 254                                 {
 255                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
 256                                 }
 257                                 else if (ptype.Equals (typeof (Byte[])))
 258                                 {
 259                                         err = Sqlite.sqlite3_bind_blob (pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1));
 260                                 }
 261                                 else
 262                                 {
 263                                         throw new ApplicationException("Unkown Parameter Type");
 264                                 }
 265                                 if (err != SqliteError.OK)
 266                                 {
 267                                         throw new ApplicationException ("Sqlite error in bind " + err);
 268                                 }
 269                         }
 270                 }
 271
 272                 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
 273                 {
 274                         if (parent_conn.Version == 3)
 275                         {
 276                                 SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
 277                                 if (err != SqliteError.OK)
 278                                         throw new SqliteSyntaxException (GetError3());
 279                         } else {
 280                                 IntPtr errMsg;
 281                                 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
 282
 283                                 if (err != SqliteError.OK)
 284                                 {
 285                                         string msg = "unknown error";
 286                                         if (errMsg != IntPtr.Zero) {
 287                                                 msg = Marshal.PtrToStringAnsi (errMsg);
 288                                                 Sqlite.sqliteFree (errMsg);
 289                                         }
 290                                         throw new SqliteSyntaxException (msg);
 291                                 }
 292                         }
 293                 }
 294
 295                 // Executes a statement and ignores its result.
 296                 private void ExecuteStatement (IntPtr pStmt) {
 297                         int cols;
 298                         IntPtr pazValue, pazColName;
 299                         ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
 300                 }
 301
 302                 // Executes a statement and returns whether there is more data available.
 303                 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
 304                         SqliteError err;
 305
 306                         if (parent_conn.Version == 3)
 307                         {
 308                                 err = Sqlite.sqlite3_step (pStmt);
 309                                 if (err == SqliteError.ERROR)
 310                                         throw new SqliteExecutionException (GetError3());
 311                                 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
 312                                 cols = Sqlite.sqlite3_column_count (pStmt);
 313                         }
 314                         else
 315                         {
 316                                 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
 317                                 if (err == SqliteError.ERROR)
 318                                         throw new SqliteExecutionException ();
 319                         }
 320
 321                         if (err == SqliteError.BUSY)
 322                                 throw new SqliteBusyException();
 323
 324                         if (err == SqliteError.MISUSE)
 325                                 throw new SqliteExecutionException();
 326
 327                         // err is either ROW or DONE.
 328                         return err == SqliteError.ROW;
 329                 }
 330
 331                 #endregion
 332
 333                 #region Public Methods
 334
 335                 object ICloneable.Clone ()
 336                 {
 337                         return new SqliteCommand (sql, parent_conn, transaction);
 338                 }
 339
 340                 public void Cancel ()
 341                 {
 342                 }
 343
 344                 public string BindParameters2()
 345                 {
 346                         string text = sql;
 347
 348                         // There used to be a crazy regular expression here, but it caused Mono
 349                         // to go into an infinite loop of some sort when there were no parameters
 350                         // in the SQL string.  That was too complicated anyway.
 351
 352                         // Here we search for substrings of the form [:?]wwwww where w is a letter or digit
 353                         // (not sure what a legitimate Sqlite3 identifier is), except those within quotes.
 354
 355                         char inquote = (char)0;
 356                         int counter = 0;
 357                         for (int i = 0; i < text.Length; i++) {
 358                                 char c = text[i];
 359                                 if (c == inquote) {
 360                                         inquote = (char)0;
 361                                 } else if (inquote == (char)0 && (c == '\'' || c == '"')) {
 362                                         inquote = c;
 363                                 } else if (inquote == (char)0 && (c == ':' || c == '?')) {
 364                                         int start = i;
 365                                         while (++i < text.Length && char.IsLetterOrDigit(text[i])) { } // scan to end
 366                                         string name = text.Substring(start, i-start);
 367                                         SqliteParameter p;
 368                                         if (name.Length > 1)
 369                                                 p = Parameters[name] as SqliteParameter;
 370                                         else
 371                                                 p = Parameters[counter] as SqliteParameter;
 372                                         string value = "'" + Convert.ToString(p.Value).Replace("'", "''") + "'";
 373                                         text = text.Remove(start, name.Length).Insert(start, value);
 374                                         i += value.Length - name.Length - 1;
 375                                         counter++;
 376                                 }
 377                         }
 378
 379                         return text;
 380                 }
 381
 382                 public void Prepare ()
 383                 {
 384                         // There isn't much we can do here.  If a table schema
 385                         // changes after preparing a statement, Sqlite bails,
 386                         // so we can only compile statements right before we
 387                         // want to run them.
 388
 389                         if (prepared) return;
 390
 391                         if (Parameters.Count > 0 && parent_conn.Version == 2)
 392                         {
 393                                 sql = BindParameters2();
 394                         }
 395                         prepared = true;
 396                 }
 397
 398                 IDbDataParameter IDbCommand.CreateParameter()
 399                 {
 400                         return CreateParameter ();
 401                 }
 402
 403                 public SqliteParameter CreateParameter ()
 404                 {
 405                         return new SqliteParameter ();
 406                 }
 407
 408                 public int ExecuteNonQuery ()
 409                 {
 410                         int rows_affected;
 411                         ExecuteReader (CommandBehavior.Default, false, out rows_affected);
 412                         return rows_affected;
 413                 }
 414
 415                 public object ExecuteScalar ()
 416                 {
 417                         SqliteDataReader r = (SqliteDataReader)ExecuteReader ();
 418                         if (r == null || !r.Read ()) {
 419                                 return null;
 420                         }
 421                         object o = r[0];
 422                         r.Close ();
 423                         return o;
 424                 }
 425
 426                 IDataReader IDbCommand.ExecuteReader ()
 427                 {
 428                         return ExecuteReader ();
 429                 }
 430
 431                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 432                 {
 433                         return ExecuteReader (behavior);
 434                 }
 435
 436                 public SqliteDataReader ExecuteReader ()
 437                 {
 438                         return ExecuteReader (CommandBehavior.Default);
 439                 }
 440
 441                 public new SqliteDataReader ExecuteReader (CommandBehavior behavior)
 442                 {
 443                         int r;
 444                         return ExecuteReader (behavior, true, out r);
 445                 }
 446
 447                 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
 448                 {
 449                         Prepare ();
 450
 451                         // The SQL string may contain multiple sql commands, so the main
 452                         // thing to do is have Sqlite iterate through the commands.
 453                         // If want_results, only the last command is returned as a
 454                         // DataReader.  Otherwise, no command is returned as a
 455                         // DataReader.
 456
 457                         IntPtr psql; // pointer to SQL command
 458
 459                         // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
 460                         // character (iso8859).  But if you give the --enable-utf8 option to the configure script, then the
 461                         // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
 462                         // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
 463                         // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
 464                         // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
 465                         //
 466                         // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
 467                         // default.  Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
 468                         // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
 469                         // of what Sqlite is treating them as,
 470
 471                         // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
 472
 473                         if (parent_conn.Version == 2)
 474                                 psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
 475                         else
 476                                 psql = Marshal.StringToHGlobalUni (sql.Trim());
 477
 478                         IntPtr pzTail = psql;
 479                         IntPtr errMsgPtr;
 480
 481                         parent_conn.StartExec ();
 482
 483                         rows_affected = 0;
 484
 485                         try {
 486                                 while (true) {
 487                                         IntPtr pStmt;
 488
 489                                         GetNextStatement(pzTail, out pzTail, out pStmt);
 490
 491                                         if (pStmt == IntPtr.Zero)
 492                                                 throw new Exception();
 493
 494                                         // pzTail is positioned after the last byte in the
 495                                         // statement, which will be the NULL character if
 496                                         // this was the last statement.
 497                                         bool last = Marshal.ReadByte(pzTail) == 0;
 498
 499                                         try {
 500                                                 if (parent_conn.Version == 3)
 501                                                         BindParameters3 (pStmt);
 502
 503                                                 if (last && want_results)
 504                                                         return new SqliteDataReader (this, pStmt, parent_conn.Version);
 505
 506                                                 ExecuteStatement(pStmt);
 507
 508                                                 if (last) // rows_affected is only used if !want_results
 509                                                         rows_affected = NumChanges ();
 510
 511                                         } finally {
 512                                                 if (parent_conn.Version == 3)
 513                                                         Sqlite.sqlite3_finalize (pStmt);
 514                                                 else
 515                                                         Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
 516                                         }
 517
 518                                         if (last) break;
 519                                 }
 520
 521                                 return null;
 522                         } finally {
 523                                 parent_conn.EndExec ();
 524                                 Marshal.FreeHGlobal (psql);
 525                         }
 526                 }
 527
 528                 public int LastInsertRowID ()
 529                 {
 530                         return parent_conn.LastInsertRowId;
 531                 }
 532
 533                 private string GetError3() {
 534                         return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));
 535                 }
 536         #endregion
 537         }
 538 }
 539 #endif