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 //
  13 // Copyright (C) 2002  Vladimir Vukicevic
  14 //
  15 // Permission is hereby granted, free of charge, to any person obtaining
  16 // a copy of this software and associated documentation files (the
  17 // "Software"), to deal in the Software without restriction, including
  18 // without limitation the rights to use, copy, modify, merge, publish,
  19 // distribute, sublicense, and/or sell copies of the Software, and to
  20 // permit persons to whom the Software is furnished to do so, subject to
  21 // the following conditions:
  22 //
  23 // The above copyright notice and this permission notice shall be
  24 // included in all copies or substantial portions of the Software.
  25 //
  26 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  27 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  28 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  29 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  30 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  31 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  32 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  33 //
  34
  35 using System;
  36 using System.Collections;
  37 using System.Text;
  38 using Mono.Unix;
  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.SqliteClient
  46 {
  47         public class SqliteCommand : IDbCommand
  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 ArrayList pStmts;
  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
  97                 public string CommandText
  98                 {
  99                         get { return sql; }
 100                         set { sql = value; }
 101                 }
 102
 103                 public int CommandTimeout
 104                 {
 105                         get { return timeout; }
 106                         set { timeout = value; }
 107                 }
 108
 109                 public CommandType CommandType
 110                 {
 111                         get { return type; }
 112                         set { type = value; }
 113                 }
 114
 115                 IDbConnection IDbCommand.Connection
 116                 {
 117                         get
 118                         {
 119                                 return parent_conn;
 120                         }
 121                         set
 122                         {
 123                                 if (!(value is SqliteConnection))
 124                                 {
 125                                         throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
 126                                 }
 127                                 parent_conn = (SqliteConnection) value;
 128                         }
 129                 }
 130
 131                 public SqliteConnection Connection
 132                 {
 133                         get { return parent_conn; }
 134                         set { parent_conn = value; }
 135                 }
 136
 137                 IDataParameterCollection IDbCommand.Parameters
 138                 {
 139                         get { return Parameters; }
 140                 }
 141
 142                 public SqliteParameterCollection Parameters
 143                 {
 144                         get
 145                         {
 146                                 if (sql_params == null) sql_params = new SqliteParameterCollection();
 147                                 return sql_params;
 148                         }
 149                 }
 150
 151                 public IDbTransaction Transaction
 152                 {
 153                         get { return transaction; }
 154                         set { transaction = value; }
 155                 }
 156
 157                 public UpdateRowSource UpdatedRowSource
 158                 {
 159                         get { return upd_row_source; }
 160                         set { upd_row_source = value; }
 161                 }
 162
 163                 #endregion
 164
 165                 #region Internal Methods
 166
 167                 internal int NumChanges ()
 168                 {
 169                         if (parent_conn.Version == 3)
 170                                 return Sqlite.sqlite3_changes(parent_conn.Handle);
 171                         else
 172                                 return Sqlite.sqlite_changes(parent_conn.Handle);
 173                 }
 174
 175                 private string ReplaceParams(Match m)
 176                 {
 177                         string input = m.Value;
 178                         if (m.Groups["param"].Success)
 179                         {
 180                                 Group g = m.Groups["param"];
 181                                 string find = g.Value;
 182                                 //FIXME: sqlite works internally only with strings, so this assumtion is mostly legit, but what about date formatting, etc?
 183                                 //Need to fix SqlLiteDataReader first to acurately describe the tables
 184                                 SqliteParameter sqlp = Parameters[find];
 185                                 string replace = Convert.ToString(sqlp.Value);
 186                                 if(sqlp.DbType == DbType.String)
 187                                 {
 188                                         replace =  "\"" + replace + "\"";
 189                                 }
 190
 191                                 input = Regex.Replace(input,find,replace);
 192                                 return input;
 193                         }
 194                         else
 195                         return m.Value;
 196                 }
 197
 198                 #endregion
 199
 200                 #region Public Methods
 201
 202                 public void Cancel ()
 203                 {
 204                 }
 205
 206                 public string ProcessParameters()
 207                 {
 208                         string processedText = sql;
 209
 210                         //Regex looks odd perhaps, but it works - same impl. as in the firebird db provider
 211                         //the named parameters are using the ADO.NET standard @-prefix but sqlite is considering ":" as a prefix for v.3...
 212                         //ref: http://www.mail-archive.com/sqlite-users@sqlite.org/msg01851.html
 213                         //Regex r = new Regex(@"(('[^']*?\@[^']*')*[^'@]*?)*(?<param>@\w+)+([^'@]*?('[^']*?\@[^']*'))*",RegexOptions.ExplicitCapture);
 214
 215                         //The above statement is true for the commented regEx, but I changed it to use the :-prefix, because now (12.05.2005 sqlite3)
 216                         //sqlite is using : as Standard Parameterprefix
 217
 218                         Regex r = new Regex(@"(('[^']*?\:[^']*')*[^':]*?)*(?<param>:\w+)+([^':]*?('[^']*?\:[^']*'))*",RegexOptions.ExplicitCapture);
 219                         MatchEvaluator me = new MatchEvaluator(ReplaceParams);
 220                         processedText = r.Replace(sql, me);
 221                         return processedText;
 222                 }
 223
 224                 public void Prepare ()
 225                 {
 226                         pStmts = new ArrayList();
 227                         string sqlcmds = sql;
 228
 229                         if (Parameters.Count > 0 && parent_conn.Version == 2)
 230                         {
 231                                 sqlcmds = ProcessParameters();
 232                         }
 233
 234                         SqliteError err = SqliteError.OK;
 235                         IntPtr psql = UnixMarshal.StringToAlloc(sqlcmds);
 236                         IntPtr pzTail = psql;
 237                         try {
 238                                 do { // sql may contain multiple sql commands, loop until they're all processed
 239                                         IntPtr pStmt = IntPtr.Zero;
 240                                         if (parent_conn.Version == 3)
 241                                         {
 242                                                 err = Sqlite.sqlite3_prepare (parent_conn.Handle, pzTail, sql.Length, out pStmt, out pzTail);
 243                                                 if (err != SqliteError.OK) {
 244                                                         string msg = Marshal.PtrToStringAnsi (Sqlite.sqlite3_errmsg (parent_conn.Handle));
 245                                                         throw new ApplicationException (msg);
 246                                                 }
 247                                         }
 248                                         else
 249                                         {
 250                                                 IntPtr errMsg;
 251                                                 err = Sqlite.sqlite_compile (parent_conn.Handle, pzTail, out pzTail, out pStmt, out errMsg);
 252
 253                                                 if (err != SqliteError.OK)
 254                                                 {
 255                                                         string msg = "unknown error";
 256                                                         if (errMsg != IntPtr.Zero)
 257                                                         {
 258                                                                 msg = Marshal.PtrToStringAnsi (errMsg);
 259                                                                 Sqlite.sqliteFree (errMsg);
 260                                                         }
 261                                                         throw new ApplicationException ("Sqlite error: " + msg);
 262                                                 }
 263                                         }
 264
 265                                         pStmts.Add(pStmt);
 266
 267                                         if (parent_conn.Version == 3)
 268                                         {
 269                                                 int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
 270                                                 if (sql_params == null) pcount = 0;
 271
 272                                                 for (int i = 1; i <= pcount; i++)
 273                                                 {
 274                                                         String name = Sqlite.sqlite3_bind_parameter_name (pStmt, i);
 275                                                         SqliteParameter param = sql_params[name];
 276                                                         Type ptype = param.Value.GetType ();
 277
 278                                                         if (ptype.Equals (typeof (String)))
 279                                                         {
 280                                                                 String s = (String)param.Value;
 281                                                                 err = Sqlite.sqlite3_bind_text (pStmt, i, s, s.Length, (IntPtr)(-1));
 282                                                         }
 283                                                         else if (ptype.Equals (typeof (DBNull)))
 284                                                         {
 285                                                                 err = Sqlite.sqlite3_bind_null (pStmt, i);
 286                                                         }
 287                                                         else if (ptype.Equals (typeof (Boolean)))
 288                                                         {
 289                                                                 bool b = (bool)param.Value;
 290                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
 291                                                         } else if (ptype.Equals (typeof (Byte)))
 292                                                         {
 293                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
 294                                                         }
 295                                                         else if (ptype.Equals (typeof (Char)))
 296                                                         {
 297                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
 298                                                         }
 299                                                         else if (ptype.Equals (typeof (Int16)))
 300                                                         {
 301                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
 302                                                         }
 303                                                         else if (ptype.Equals (typeof (Int32)))
 304                                                         {
 305                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 306                                                         }
 307                                                         else if (ptype.Equals (typeof (SByte)))
 308                                                         {
 309                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
 310                                                         }
 311                                                         else if (ptype.Equals (typeof (UInt16)))
 312                                                         {
 313                                                                 err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
 314                                                         }
 315                                                         else if (ptype.Equals (typeof (DateTime)))
 316                                                         {
 317                                                                 DateTime dt = (DateTime)param.Value;
 318                                                                 err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
 319                                                         }
 320                                                         else if (ptype.Equals (typeof (Double)))
 321                                                         {
 322                                                                 err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
 323                                                         }
 324                                                         else if (ptype.Equals (typeof (Single)))
 325                                                         {
 326                                                                 err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
 327                                                         }
 328                                                         else if (ptype.Equals (typeof (UInt32)))
 329                                                         {
 330                                                                 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
 331                                                         }
 332                                                         else if (ptype.Equals (typeof (Int64)))
 333                                                         {
 334                                                                 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
 335                                                         }
 336                                                         else
 337                                                         {
 338                                                                 throw new ApplicationException("Unkown Parameter Type");
 339                                                         }
 340                                                         if (err != SqliteError.OK)
 341                                                         {
 342                                                                 throw new ApplicationException ("Sqlite error in bind " + err);
 343                                                         }
 344                                                 }
 345                                         }
 346                                 } while ((int)pzTail - (int)psql < sql.Length);
 347                         } finally {
 348                                 UnixMarshal.Free(psql);
 349                         }
 350                         prepared=true;
 351                 }
 352
 353
 354                 IDbDataParameter IDbCommand.CreateParameter()
 355                 {
 356                         return CreateParameter ();
 357                 }
 358
 359                 public SqliteParameter CreateParameter ()
 360                 {
 361                         return new SqliteParameter ();
 362                 }
 363
 364                 public int ExecuteNonQuery ()
 365                 {
 366                         int rows_affected;
 367                         SqliteDataReader r = ExecuteReader (CommandBehavior.Default, false, out rows_affected);
 368                         return rows_affected;
 369                 }
 370
 371                 public object ExecuteScalar ()
 372                 {
 373                         SqliteDataReader r = ExecuteReader ();
 374                         if (r == null || !r.Read ()) {
 375                                 return null;
 376                         }
 377                         object o = r[0];
 378                         r.Close ();
 379                         return o;
 380                 }
 381
 382                 IDataReader IDbCommand.ExecuteReader ()
 383                 {
 384                         return ExecuteReader ();
 385                 }
 386
 387                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 388                 {
 389                         return ExecuteReader (behavior);
 390                 }
 391
 392                 public SqliteDataReader ExecuteReader ()
 393                 {
 394                         return ExecuteReader (CommandBehavior.Default);
 395                 }
 396
 397                 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
 398                 {
 399                         int r;
 400                         return ExecuteReader (behavior, true, out r);
 401                 }
 402
 403                 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
 404                 {
 405                         SqliteDataReader reader = null;
 406                         SqliteError err = SqliteError.OK;
 407                         IntPtr errMsg = IntPtr.Zero;
 408                         parent_conn.StartExec ();
 409
 410                         try
 411                         {
 412                                 if (!prepared)
 413                                 {
 414                                         Prepare ();
 415                                 }
 416                                 for (int i = 0; i < pStmts.Count; i++) {
 417                                         IntPtr pStmt = (IntPtr)pStmts[i];
 418
 419                                         // If want_results, return the results of the last statement
 420                                         // via the SqliteDataReader, and execute but ignore the results
 421                                         // of the other statements.
 422                                         if (i == pStmts.Count-1 && want_results)
 423                                         {
 424                                                 reader = new SqliteDataReader (this, pStmt, parent_conn.Version);
 425                                                 break;
 426                                         }
 427
 428                                         // Execute but ignore the results of these statements.
 429                                         if (parent_conn.Version == 3)
 430                                         {
 431                                                 err = Sqlite.sqlite3_step (pStmt);
 432                                         }
 433                                         else
 434                                         {
 435                                                 int cols;
 436                                                 IntPtr pazValue = IntPtr.Zero;
 437                                                 IntPtr pazColName = IntPtr.Zero;
 438                                                 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
 439                                         }
 440                                         // On error, misuse, or busy, don't bother with the rest of the statements.
 441                                         if (err != SqliteError.ROW && err != SqliteError.DONE) break;
 442                                 }
 443                         }
 444                         finally
 445                         {
 446                                 foreach (IntPtr pStmt in pStmts) {
 447                                         if (parent_conn.Version == 3)
 448                                         {
 449                                                 err = Sqlite.sqlite3_finalize (pStmt);
 450                                         }
 451                                         else
 452                                         {
 453                                                 err = Sqlite.sqlite_finalize (pStmt, out errMsg);
 454                                         }
 455                                 }
 456                                 parent_conn.EndExec ();
 457                                 prepared = false;
 458                         }
 459
 460                         if (err != SqliteError.OK &&
 461                             err != SqliteError.DONE &&
 462                             err != SqliteError.ROW)
 463                         {
 464                                 if (errMsg != IntPtr.Zero)
 465                                 {
 466                                         // TODO: Get the message text
 467                                 }
 468                                 throw new ApplicationException ("Sqlite error");
 469                         }
 470                         rows_affected = NumChanges ();
 471                         return reader;
 472                 }
 473
 474                 public int LastInsertRowID ()
 475                 {
 476                         if (parent_conn.Version == 3)
 477                                 return Sqlite.sqlite3_last_insert_rowid(parent_conn.Handle);
 478                         else
 479                                 return Sqlite.sqlite_last_insert_rowid(parent_conn.Handle);
 480                 }
 481         #endregion
 482         }
 483 }