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 ();
200 if (ptype.Equals (typeof (String)))
202 String s = (String)param.Value;
203 err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, -1, (IntPtr)(-1));
205 else if (ptype.Equals (typeof (DBNull)))
207 err = Sqlite.sqlite3_bind_null (pStmt, i);
209 else if (ptype.Equals (typeof (Boolean)))
211 bool b = (bool)param.Value;
212 err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
213 } else if (ptype.Equals (typeof (Byte)))
215 err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
217 else if (ptype.Equals (typeof (Char)))
219 err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
221 else if (ptype.Equals (typeof (Int16)))
223 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
225 else if (ptype.Equals (typeof (Int32)))
227 err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
229 else if (ptype.Equals (typeof (SByte)))
231 err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
233 else if (ptype.Equals (typeof (UInt16)))
235 err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
237 else if (ptype.Equals (typeof (DateTime)))
239 DateTime dt = (DateTime)param.Value;
240 err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
242 else if (ptype.Equals (typeof (Double)))
244 err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
246 else if (ptype.Equals (typeof (Single)))
248 err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
250 else if (ptype.Equals (typeof (UInt32)))
252 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
254 else if (ptype.Equals (typeof (Int64)))
256 err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
258 else if (ptype.Equals (typeof (Byte[])))
260 err = Sqlite.sqlite3_bind_blob (pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1));
264 throw new ApplicationException("Unkown Parameter Type");
266 if (err != SqliteError.OK)
268 throw new ApplicationException ("Sqlite error in bind " + err);
273 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
275 if (parent_conn.Version == 3)
277 SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
278 if (err != SqliteError.OK)
279 throw new SqliteSyntaxException (GetError3());
284 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
286 if (err != SqliteError.OK)
288 string msg = "unknown error";
289 if (errMsg != IntPtr.Zero)
291 msg = Marshal.PtrToStringAnsi (errMsg);
292 Sqlite.sqliteFree (errMsg);
294 throw new SqliteSyntaxException (msg);
299 // Executes a statement and ignores its result.
300 private void ExecuteStatement (IntPtr pStmt) {
302 IntPtr pazValue, pazColName;
303 ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
306 // Executes a statement and returns whether there is more data available.
307 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
310 if (parent_conn.Version == 3)
312 err = Sqlite.sqlite3_step (pStmt);
313 if (err == SqliteError.ERROR)
314 throw new SqliteExecutionException (GetError3());
315 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
316 cols = Sqlite.sqlite3_column_count (pStmt);
320 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
321 if (err == SqliteError.ERROR)
322 throw new SqliteExecutionException ();
325 if (err == SqliteError.BUSY)
326 throw new SqliteBusyException();
328 if (err == SqliteError.MISUSE)
329 throw new SqliteExecutionException();
331 // err is either ROW or DONE.
332 return err == SqliteError.ROW;
337 #region Public Methods
339 public void Cancel ()
343 public string BindParameters2()
347 // There used to be a crazy regular expression here, but it caused Mono
348 // to go into an infinite loop of some sort when there were no parameters
349 // in the SQL string. That was too complicated anyway.
351 // Here we search for substrings of the form [:?]wwwww where w is a letter or digit
352 // (not sure what a legitimate Sqlite3 identifier is), except those within quotes.
354 char inquote = (char)0;
356 for (int i = 0; i < text.Length; i++) {
360 } else if (inquote == (char)0 && (c == '\'' || c == '"')) {
362 } else if (inquote == (char)0 && (c == ':' || c == '?')) {
364 while (++i < text.Length && char.IsLetterOrDigit(text[i])) { } // scan to end
365 string name = text.Substring(start, i-start);
368 p = Parameters[name];
370 p = Parameters[counter];
371 string value = "'" + Convert.ToString(p.Value).Replace("'", "''") + "'";
372 text = text.Remove(start, name.Length).Insert(start, value);
373 i += value.Length - name.Length - 1;
381 public void Prepare ()
383 // There isn't much we can do here. If a table schema
384 // changes after preparing a statement, Sqlite bails,
385 // so we can only compile statements right before we
388 if (prepared) return;
390 if (Parameters.Count > 0 && parent_conn.Version == 2)
392 sql = BindParameters2();
398 IDbDataParameter IDbCommand.CreateParameter()
400 return CreateParameter ();
403 public SqliteParameter CreateParameter ()
405 return new SqliteParameter ();
408 public int ExecuteNonQuery ()
411 ExecuteReader (CommandBehavior.Default, false, out rows_affected);
412 return rows_affected;
415 public object ExecuteScalar ()
417 SqliteDataReader r = ExecuteReader ();
418 if (r == null || !r.Read ()) {
426 IDataReader IDbCommand.ExecuteReader ()
428 return ExecuteReader ();
431 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
433 return ExecuteReader (behavior);
436 public SqliteDataReader ExecuteReader ()
438 return ExecuteReader (CommandBehavior.Default);
441 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
444 return ExecuteReader (behavior, true, out r);
447 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
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
457 IntPtr psql; // pointer to SQL command
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.
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,
471 // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
473 if (parent_conn.Version == 2)
474 psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
476 psql = Marshal.StringToHGlobalUni (sql.Trim());
478 IntPtr pzTail = psql;
481 parent_conn.StartExec ();
489 GetNextStatement(pzTail, out pzTail, out pStmt);
491 if (pStmt == IntPtr.Zero)
492 throw new Exception();
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;
500 if (parent_conn.Version == 3)
501 BindParameters3 (pStmt);
503 if (last && want_results)
504 return new SqliteDataReader (this, pStmt, parent_conn.Version);
506 ExecuteStatement(pStmt);
508 if (last) // rows_affected is only used if !want_results
509 rows_affected = NumChanges ();
512 if (parent_conn.Version == 3)
513 Sqlite.sqlite3_finalize (pStmt);
515 Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
523 parent_conn.EndExec ();
524 Marshal.FreeHGlobal (psql);
528 public int LastInsertRowID ()
530 return parent_conn.LastInsertRowId;
533 private string GetError3() {
534 return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));