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);
184 if (name == null) continue;
186 SqliteParameter param = sql_params[name];
188 if (param.Value == null) {
189 Sqlite.sqlite3_bind_null (pStmt, i);
193 Type ptype = param.Value.GetType ();
197 if (ptype.Equals (typeof (String)))
199 String s = (String)param.Value;
200 err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, -1, (IntPtr)(-1));
202 else if (ptype.Equals (typeof (DBNull)))
204 err = Sqlite.sqlite3_bind_null (pStmt, i);
206 else if (ptype.Equals (typeof (Boolean)))
208 bool b = (bool)param.Value;
209 err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
210 } else if (ptype.Equals (typeof (Byte)))
212 err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
214 else if (ptype.Equals (typeof (Char)))
216 err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
218 else if (ptype.Equals (typeof (Int16)))
220 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
222 else if (ptype.Equals (typeof (Int32)))
224 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
226 else if (ptype.Equals (typeof (SByte)))
228 err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
230 else if (ptype.Equals (typeof (UInt16)))
232 err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
234 else if (ptype.Equals (typeof (DateTime)))
236 DateTime dt = (DateTime)param.Value;
237 err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
239 else if (ptype.Equals (typeof (Double)))
241 err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
243 else if (ptype.Equals (typeof (Single)))
245 err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
247 else if (ptype.Equals (typeof (UInt32)))
249 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
251 else if (ptype.Equals (typeof (Int64)))
253 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
255 else if (ptype.Equals (typeof (Byte[])))
257 err = Sqlite.sqlite3_bind_blob (pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1));
261 throw new ApplicationException("Unkown Parameter Type");
263 if (err != SqliteError.OK)
265 throw new ApplicationException ("Sqlite error in bind " + err);
270 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
272 if (parent_conn.Version == 3)
274 SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
275 if (err != SqliteError.OK)
276 throw new SqliteSyntaxException (GetError3());
281 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
283 if (err != SqliteError.OK)
285 string msg = "unknown error";
286 if (errMsg != IntPtr.Zero)
288 msg = Marshal.PtrToStringAnsi (errMsg);
289 Sqlite.sqliteFree (errMsg);
291 throw new SqliteSyntaxException (msg);
296 // Executes a statement and ignores its result.
297 private void ExecuteStatement (IntPtr pStmt) {
299 IntPtr pazValue, pazColName;
300 ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
303 // Executes a statement and returns whether there is more data available.
304 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
307 if (parent_conn.Version == 3)
309 err = Sqlite.sqlite3_step (pStmt);
310 if (err == SqliteError.ERROR)
311 throw new SqliteExecutionException (GetError3());
312 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
313 cols = Sqlite.sqlite3_column_count (pStmt);
317 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
318 if (err == SqliteError.ERROR)
319 throw new SqliteExecutionException ();
322 if (err == SqliteError.BUSY)
323 throw new SqliteBusyException();
325 if (err == SqliteError.MISUSE)
326 throw new SqliteExecutionException();
328 // err is either ROW or DONE.
329 return err == SqliteError.ROW;
334 #region Public Methods
336 public void Cancel ()
340 public string BindParameters2()
344 // There used to be a crazy regular expression here, but it caused Mono
345 // to go into an infinite loop of some sort when there were no parameters
346 // in the SQL string. That was too complicated anyway.
348 // Here we search for substrings of the form :wwwww where w is a letter or digit
349 // (not sure what a legitimate Sqlite3 identifier is), except those within quotes.
351 char inquote = (char)0;
352 for (int i = 0; i < text.Length; i++) {
356 } else if (inquote == (char)0 && (c == '\'' || c == '"')) {
358 } else if (inquote == (char)0 && c == ':') {
360 while (++i < text.Length && char.IsLetterOrDigit(text[i])) { } // scan to end
361 string name = text.Substring(start, i-start);
362 string value = "'" + Convert.ToString(Parameters[name].Value).Replace("'", "''") + "'";
363 text = text.Replace(name, value);
364 i += value.Length - name.Length - 1;
371 public void Prepare ()
373 // There isn't much we can do here. If a table schema
374 // changes after preparing a statement, Sqlite bails,
375 // so we can only compile statements right before we
378 if (prepared) return;
380 if (Parameters.Count > 0 && parent_conn.Version == 2)
382 sql = BindParameters2();
388 IDbDataParameter IDbCommand.CreateParameter()
390 return CreateParameter ();
393 public SqliteParameter CreateParameter ()
395 return new SqliteParameter ();
398 public int ExecuteNonQuery ()
401 ExecuteReader (CommandBehavior.Default, false, out rows_affected);
402 return rows_affected;
405 public object ExecuteScalar ()
407 SqliteDataReader r = ExecuteReader ();
408 if (r == null || !r.Read ()) {
416 IDataReader IDbCommand.ExecuteReader ()
418 return ExecuteReader ();
421 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
423 return ExecuteReader (behavior);
426 public SqliteDataReader ExecuteReader ()
428 return ExecuteReader (CommandBehavior.Default);
431 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
434 return ExecuteReader (behavior, true, out r);
437 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
441 // The SQL string may contain multiple sql commands, so the main
442 // thing to do is have Sqlite iterate through the commands.
443 // If want_results, only the last command is returned as a
444 // DataReader. Otherwise, no command is returned as a
447 IntPtr psql; // pointer to SQL command
449 // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
450 // character (iso8859). But if you give the --enable-utf8 option to the configure script, then the
451 // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
452 // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
453 // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
454 // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
456 // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
457 // default. Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
458 // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
459 // of what Sqlite is treating them as,
461 // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
463 if (parent_conn.Version == 2)
464 psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
466 psql = Marshal.StringToCoTaskMemUni (sql.Trim());
468 IntPtr pzTail = psql;
471 parent_conn.StartExec ();
479 GetNextStatement(pzTail, out pzTail, out pStmt);
481 if (pStmt == IntPtr.Zero)
482 throw new Exception();
484 // pzTail is positioned after the last byte in the
485 // statement, which will be the NULL character if
486 // this was the last statement.
487 bool last = Marshal.ReadByte(pzTail) == 0;
490 if (parent_conn.Version == 3)
491 BindParameters3 (pStmt);
493 if (last && want_results)
494 return new SqliteDataReader (this, pStmt, parent_conn.Version);
496 ExecuteStatement(pStmt);
498 if (last) // rows_affected is only used if !want_results
499 rows_affected = NumChanges ();
502 if (parent_conn.Version == 3)
503 Sqlite.sqlite3_finalize (pStmt);
505 Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
513 parent_conn.EndExec ();
514 Marshal.FreeCoTaskMem (psql);
518 public int LastInsertRowID ()
520 if (parent_conn.Version == 3)
521 return Sqlite.sqlite3_last_insert_rowid(parent_conn.Handle);
523 return Sqlite.sqlite_last_insert_rowid(parent_conn.Handle);
526 private string GetError3() {
527 return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));