2 // Mono.Data.SqliteClient.SqliteCommand.cs
4 // Represents a Transact-SQL statement or stored procedure to execute against
5 // a Sqlite database file.
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>
14 // Copyright (C) 2002 Vladimir Vukicevic
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:
24 // The above copyright notice and this permission notice shall be
25 // included in all copies or substantial portions of the Software.
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.
37 using System.Collections;
39 using System.Runtime.InteropServices;
40 using System.Text.RegularExpressions;
42 using System.Diagnostics;
43 using Group = System.Text.RegularExpressions.Group;
45 namespace Mono.Data.SqliteClient
47 public class SqliteCommand : IDbCommand
51 private SqliteConnection parent_conn;
52 //private SqliteTransaction transaction;
53 private IDbTransaction transaction;
56 private CommandType type;
57 private UpdateRowSource upd_row_source;
58 private SqliteParameterCollection sql_params;
59 private bool prepared = false;
63 #region Constructors and destructors
65 public SqliteCommand ()
70 public SqliteCommand (string sqlText)
75 public SqliteCommand (string sqlText, SqliteConnection dbConn)
81 public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
88 public void Dispose ()
96 public string CommandText
99 set { sql = value; prepared = false; }
102 public int CommandTimeout
104 get { return timeout; }
105 set { timeout = value; }
108 public CommandType CommandType
111 set { type = value; }
114 IDbConnection IDbCommand.Connection
122 if (!(value is SqliteConnection))
124 throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
126 parent_conn = (SqliteConnection) value;
130 public SqliteConnection Connection
132 get { return parent_conn; }
133 set { parent_conn = value; }
136 IDataParameterCollection IDbCommand.Parameters
138 get { return Parameters; }
141 public SqliteParameterCollection Parameters
145 if (sql_params == null) sql_params = new SqliteParameterCollection();
150 public IDbTransaction Transaction
152 get { return transaction; }
153 set { transaction = value; }
156 public UpdateRowSource UpdatedRowSource
158 get { return upd_row_source; }
159 set { upd_row_source = value; }
164 #region Internal Methods
166 internal int NumChanges ()
168 if (parent_conn.Version == 3)
169 return Sqlite.sqlite3_changes(parent_conn.Handle);
171 return Sqlite.sqlite_changes(parent_conn.Handle);
174 private void BindParameters3 (IntPtr pStmt)
176 if (sql_params == null) return;
177 if (sql_params.Count == 0) return;
179 int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
181 for (int i = 1; i <= pcount; i++)
183 String name = Sqlite.HeapToString (Sqlite.sqlite3_bind_parameter_name (pStmt, i), Encoding.UTF8);
185 SqliteParameter param = null;
187 param = sql_params[name];
189 param = sql_params[i-1];
191 if (param.Value == null) {
192 Sqlite.sqlite3_bind_null (pStmt, i);
196 Type ptype = param.Value.GetType ();
198 ptype = Enum.GetUnderlyingType (ptype);
202 if (ptype.Equals (typeof (String)))
204 String s = (String)param.Value;
205 err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, -1, (IntPtr)(-1));
207 else if (ptype.Equals (typeof (DBNull)))
209 err = Sqlite.sqlite3_bind_null (pStmt, i);
211 else if (ptype.Equals (typeof (Boolean)))
213 bool b = (bool)param.Value;
214 err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
215 } else if (ptype.Equals (typeof (Byte)))
217 err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
219 else if (ptype.Equals (typeof (Char)))
221 err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
223 else if (ptype.IsEnum)
225 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
227 else if (ptype.Equals (typeof (Int16)))
229 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
231 else if (ptype.Equals (typeof (Int32)))
233 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
235 else if (ptype.Equals (typeof (SByte)))
237 err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
239 else if (ptype.Equals (typeof (UInt16)))
241 err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
243 else if (ptype.Equals (typeof (DateTime)))
245 DateTime dt = (DateTime)param.Value;
246 err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
248 else if (ptype.Equals (typeof (Double)))
250 err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
252 else if (ptype.Equals (typeof (Single)))
254 err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
256 else if (ptype.Equals (typeof (UInt32)))
258 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
260 else if (ptype.Equals (typeof (Int64)))
262 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
264 else if (ptype.Equals (typeof (Byte[])))
266 err = Sqlite.sqlite3_bind_blob (pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1));
270 throw new ApplicationException("Unkown Parameter Type");
272 if (err != SqliteError.OK)
274 throw new ApplicationException ("Sqlite error in bind " + err);
279 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
281 if (parent_conn.Version == 3)
283 SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
284 if (err != SqliteError.OK)
285 throw new SqliteSyntaxException (GetError3());
290 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
292 if (err != SqliteError.OK)
294 string msg = "unknown error";
295 if (errMsg != IntPtr.Zero)
297 msg = Marshal.PtrToStringAnsi (errMsg);
298 Sqlite.sqliteFree (errMsg);
300 throw new SqliteSyntaxException (msg);
305 // Executes a statement and ignores its result.
306 private void ExecuteStatement (IntPtr pStmt) {
308 IntPtr pazValue, pazColName;
309 ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
312 // Executes a statement and returns whether there is more data available.
313 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
316 if (parent_conn.Version == 3)
318 err = Sqlite.sqlite3_step (pStmt);
319 if (err == SqliteError.ERROR)
320 throw new SqliteExecutionException (GetError3());
321 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
322 cols = Sqlite.sqlite3_column_count (pStmt);
326 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
327 if (err == SqliteError.ERROR)
328 throw new SqliteExecutionException ();
331 if (err == SqliteError.BUSY)
332 throw new SqliteBusyException();
334 if (err == SqliteError.MISUSE)
335 throw new SqliteExecutionException();
337 // err is either ROW or DONE.
338 return err == SqliteError.ROW;
343 #region Public Methods
345 public void Cancel ()
349 public string BindParameters2()
353 // There used to be a crazy regular expression here, but it caused Mono
354 // to go into an infinite loop of some sort when there were no parameters
355 // in the SQL string. That was too complicated anyway.
357 // Here we search for substrings of the form [:?]wwwww where w is a letter or digit
358 // (not sure what a legitimate Sqlite3 identifier is), except those within quotes.
360 char inquote = (char)0;
362 for (int i = 0; i < text.Length; i++) {
366 } else if (inquote == (char)0 && (c == '\'' || c == '"')) {
368 } else if (inquote == (char)0 && (c == ':' || c == '?')) {
370 while (++i < text.Length && char.IsLetterOrDigit(text[i])) { } // scan to end
371 string name = text.Substring(start, i-start);
374 p = Parameters[name];
376 p = Parameters[counter];
377 string value = "'" + Convert.ToString(p.Value).Replace("'", "''") + "'";
378 text = text.Remove(start, name.Length).Insert(start, value);
379 i += value.Length - name.Length - 1;
387 public void Prepare ()
389 // There isn't much we can do here. If a table schema
390 // changes after preparing a statement, Sqlite bails,
391 // so we can only compile statements right before we
394 if (prepared) return;
396 if (Parameters.Count > 0 && parent_conn.Version == 2)
398 sql = BindParameters2();
404 IDbDataParameter IDbCommand.CreateParameter()
406 return CreateParameter ();
409 public SqliteParameter CreateParameter ()
411 return new SqliteParameter ();
414 public int ExecuteNonQuery ()
417 ExecuteReader (CommandBehavior.Default, false, out rows_affected);
418 return rows_affected;
421 public object ExecuteScalar ()
423 SqliteDataReader r = ExecuteReader ();
424 if (r == null || !r.Read ()) {
432 IDataReader IDbCommand.ExecuteReader ()
434 return ExecuteReader ();
437 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
439 return ExecuteReader (behavior);
442 public SqliteDataReader ExecuteReader ()
444 return ExecuteReader (CommandBehavior.Default);
447 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
450 return ExecuteReader (behavior, true, out r);
453 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
457 // The SQL string may contain multiple sql commands, so the main
458 // thing to do is have Sqlite iterate through the commands.
459 // If want_results, only the last command is returned as a
460 // DataReader. Otherwise, no command is returned as a
463 IntPtr psql; // pointer to SQL command
465 // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
466 // character (iso8859). But if you give the --enable-utf8 option to the configure script, then the
467 // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
468 // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
469 // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
470 // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
472 // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
473 // default. Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
474 // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
475 // of what Sqlite is treating them as,
477 // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
479 if (parent_conn.Version == 2)
480 psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
482 psql = Marshal.StringToHGlobalUni (sql.Trim());
484 IntPtr pzTail = psql;
487 parent_conn.StartExec ();
495 GetNextStatement(pzTail, out pzTail, out pStmt);
497 if (pStmt == IntPtr.Zero)
498 throw new Exception();
500 // pzTail is positioned after the last byte in the
501 // statement, which will be the NULL character if
502 // this was the last statement.
503 bool last = Marshal.ReadByte(pzTail) == 0;
506 if (parent_conn.Version == 3)
507 BindParameters3 (pStmt);
509 if (last && want_results)
510 return new SqliteDataReader (this, pStmt, parent_conn.Version);
512 ExecuteStatement(pStmt);
514 if (last) // rows_affected is only used if !want_results
515 rows_affected = NumChanges ();
518 if (parent_conn.Version == 3)
519 Sqlite.sqlite3_finalize (pStmt);
521 Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
529 parent_conn.EndExec ();
530 Marshal.FreeHGlobal (psql);
534 public int LastInsertRowID ()
536 return parent_conn.LastInsertRowId;
539 private string GetError3() {
540 return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));