/// <summary> /// Initializes the statement and attempts to get all information about parameters in the statement /// </summary> /// <param name="sqlbase">The base SQLite object</param> /// <param name="stmt">The statement</param> /// <param name="strCommand">The command text for this statement</param> /// <param name="previous">The previous command in a multi-statement command</param> internal SqliteStatement(SQLiteBase sqlbase, SqliteStatementHandle stmt, string strCommand, SqliteStatement previous) { _sql = sqlbase; _sqlite_stmt = stmt; _sqlStatement = strCommand; // Determine parameters for this statement (if any) and prepare space for them. int nCmdStart = 0; int n = _sql.Bind_ParamCount(this); int x; string s; if (n > 0) { if (previous != null) nCmdStart = previous._unnamedParameters; _paramNames = new string[n]; _paramValues = new SqliteParameter[n]; for (x = 0; x < n; x++) { s = _sql.Bind_ParamName(this, x + 1); if (String.IsNullOrEmpty(s)) { s = String.Format(CultureInfo.InvariantCulture, ";{0}", nCmdStart); nCmdStart++; _unnamedParameters++; } _paramNames[x] = s; _paramValues[x] = null; } } }
internal long _version; // Matches the version of the connection /// <summary> /// Internal constructor, initializes the datareader and sets up to begin executing statements /// </summary> /// <param name="cmd">The SqliteCommand this data reader is for</param> /// <param name="behave">The expected behavior of the data reader</param> internal SqliteDataReader(SqliteCommand cmd, CommandBehavior behave) { _command = cmd; _version = _command.Connection._version; _commandBehavior = behave; _activeStatementIndex = -1; _activeStatement = null; _rowsAffected = -1; _fieldCount = 0; if (_command != null) NextResult(); }
internal abstract int ColumnIndex(SqliteStatement stmt, string columnName);
internal override bool IsNull(SqliteStatement stmt, int index) { return this.ColumnAffinity(stmt, index) == TypeAffinity.Null; }
internal override string GetText(SqliteStatement stmt, int index) { return UTF8ToString(UnsafeNativeMethods.sqlite3_column_text(stmt._sqlite_stmt, index), -1); }
internal override int ColumnIndex(SqliteStatement stmt, string columnName) { int x = ColumnCount(stmt); for (int n = 0; n < x; n++) { if (String.Compare(columnName, ColumnName(stmt, n), StringComparison.OrdinalIgnoreCase) == 0) { return n; } } return -1; }
internal override int Bind_ParamIndex(SqliteStatement stmt, string paramName) { return UnsafeNativeMethods.sqlite3_bind_parameter_index(stmt._sqlite_stmt, ToUTF8(paramName)); }
internal override void Bind_Blob(SqliteStatement stmt, int index, byte[] blobData) { int n = UnsafeNativeMethods.sqlite3_bind_blob(stmt._sqlite_stmt, index, blobData, blobData.Length, null); if (n > 0) throw new SqliteException(n, SQLiteLastError()); }
internal abstract string GetText(SqliteStatement stmt, int index);
internal abstract Int64 GetInt64(SqliteStatement stmt, int index);
internal abstract Int32 GetInt32(SqliteStatement stmt, int index);
internal abstract double GetDouble(SqliteStatement stmt, int index);
internal abstract string ColumnTableName(SqliteStatement stmt, int index);
internal abstract string ColumnDatabaseName(SqliteStatement stmt, int index);
internal abstract string ColumnOriginalName(SqliteStatement stmt, int index);
internal override SqliteStatement Prepare(SqliteConnection cnn, string strSql, SqliteStatement previous, uint timeout, out string strRemain) { SqliteStatementHandle stmt = null; string ptr = null; int len = 0; int n = 17; int retries = 0; SqliteStatement cmd = null; var rnd = new Random(); var starttick = (uint) Environment.TickCount; while ((n == 17 || n == 6 || n == 5) && retries < 3) { n = UnsafeNativeMethods.sqlite3_prepare16(_sql, strSql, strSql.Length, out stmt, out ptr); len = -1; if (n == 17) { retries++; } else if (n == 1) { if (String.Compare(SQLiteLastError(), "near \"TYPES\": syntax error", StringComparison.OrdinalIgnoreCase) == 0) { int pos = strSql.IndexOf(';'); if (pos == -1) { pos = strSql.Length - 1; } string typedefs = strSql.Substring(0, pos + 1); strSql = strSql.Substring(pos + 1); strRemain = ""; while (cmd == null && strSql.Length > 0) { cmd = Prepare(cnn, strSql, previous, timeout, out strRemain); strSql = strRemain; } if (cmd != null) { cmd.SetTypes(typedefs); } return cmd; } else if (_buildingSchema == false && String.Compare(SQLiteLastError(), 0, "no such table: TEMP.SCHEMA", 0, 26, StringComparison.OrdinalIgnoreCase) == 0) { strRemain = ""; _buildingSchema = true; try { while (cmd == null && strSql.Length > 0) { cmd = Prepare(cnn, strSql, previous, timeout, out strRemain); strSql = strRemain; } return cmd; } finally { _buildingSchema = false; } } } else if (n == 6 || n == 5) // Locked -- delay a small amount before retrying { // Keep trying, but if we've exceeded the command's timeout, give up and throw an error if ((uint) Environment.TickCount - starttick > timeout) { throw new SqliteException(n, SQLiteLastError()); } else { // Otherwise sleep for a random amount of time up to 150ms Sleep(rnd.Next(1, 150)); } } } if (n > 0) throw new SqliteException(n, SQLiteLastError()); strRemain = UTF8ToString(ptr, len); var hdl = stmt; if (stmt != null) { cmd = new SqliteStatement(this, hdl, strSql.Substring(0, strSql.Length - strRemain.Length), previous); } return cmd; }
internal override void Bind_Text(SqliteStatement stmt, int index, string value) { int n = UnsafeNativeMethods.sqlite3_bind_text(stmt._sqlite_stmt, index, value, value.Length, null); if (n > 0) throw new SqliteException(n, SQLiteLastError()); }
internal abstract long GetBytes(SqliteStatement stmt, int index, int nDataoffset, byte[] bDest, int nStart, int nLength);
internal override int Bind_ParamCount(SqliteStatement stmt) { return UnsafeNativeMethods.sqlite3_bind_parameter_count(stmt._sqlite_stmt); }
internal abstract long GetChars(SqliteStatement stmt, int index, int nDataoffset, char[] bDest, int nStart, int nLength);
internal override TypeAffinity ColumnAffinity(SqliteStatement stmt, int index) { return (TypeAffinity) UnsafeNativeMethods.sqlite3_column_type(stmt._sqlite_stmt, index); }
internal abstract DateTime GetDateTime(SqliteStatement stmt, int index);
internal override double GetDouble(SqliteStatement stmt, int index) { return UnsafeNativeMethods.sqlite3_column_double(stmt._sqlite_stmt, index); }
internal abstract bool IsNull(SqliteStatement stmt, int index);
internal override long GetBytes(SqliteStatement stmt, int index, int nDataOffset, byte[] bDest, int nStart, int nLength) { int nCopied = nLength; int nlen = UnsafeNativeMethods.sqlite3_column_bytes(stmt._sqlite_stmt, index); var ptr = UnsafeNativeMethods.sqlite3_column_blob(stmt._sqlite_stmt, index); if (bDest == null) { return nlen; } if (nCopied + nStart > bDest.Length) { nCopied = bDest.Length - nStart; } if (nCopied + nDataOffset > nlen) { nCopied = nlen - nDataOffset; } if (nCopied > 0) { Array.Copy(ptr, nStart + nDataOffset, bDest, 0, nCopied); } else { nCopied = 0; } return nCopied; }
internal abstract int GetCursorForTable(SqliteStatement stmt, int database, int rootPage);
internal override int GetCursorForTable(SqliteStatement stmt, int db, int rootPage) { return -1; }
internal abstract long GetRowIdForCursor(SqliteStatement stmt, int cursor);
/// <summary> /// Moves to the next resultset in multiple row-returning SQL command. /// </summary> /// <returns>True if the command was successful and a new resultset is available, False otherwise.</returns> public override bool NextResult() { CheckClosed(); SqliteStatement stmt = null; int fieldCount; while (true) { if (_activeStatement != null && stmt == null) { // Reset the previously-executed statement _activeStatement._sql.Reset(_activeStatement); // If we're only supposed to return a single rowset, step through all remaining statements once until // they are all done and return false to indicate no more resultsets exist. if ((_commandBehavior & CommandBehavior.SingleResult) != 0) { for (; ; ) { stmt = _command.GetStatement(_activeStatementIndex + 1); if (stmt == null) break; _activeStatementIndex++; stmt._sql.Step(stmt); if (stmt._sql.ColumnCount(stmt) == 0) { if (_rowsAffected == -1) _rowsAffected = 0; _rowsAffected += stmt._sql.Changes; } stmt._sql.Reset(stmt); // Gotta reset after every step to release any locks and such! } return false; } } // Get the next statement to execute stmt = _command.GetStatement(_activeStatementIndex + 1); // If we've reached the end of the statements, return false, no more resultsets if (stmt == null) return false; // If we were on a current resultset, set the state to "done reading" for it if (_readingState < 1) _readingState = 1; _activeStatementIndex++; fieldCount = stmt._sql.ColumnCount(stmt); // If the statement is not a select statement or we're not retrieving schema only, then perform the initial step if ((_commandBehavior & CommandBehavior.SchemaOnly) == 0 || fieldCount == 0) { if (stmt._sql.Step(stmt)) { _readingState = -1; } else if (fieldCount == 0) // No rows returned, if fieldCount is zero, skip to the next statement { if (_rowsAffected == -1) _rowsAffected = 0; _rowsAffected += stmt._sql.Changes; stmt._sql.Reset(stmt); continue; // Skip this command and move to the next, it was not a row-returning resultset } else // No rows, fieldCount is non-zero so stop here { _readingState = 1; // This command returned columns but no rows, so return true, but HasRows = false and Read() returns false } } // Ahh, we found a row-returning resultset eligible to be returned! _activeStatement = stmt; _fieldCount = fieldCount; _fieldTypeArray = null; if ((_commandBehavior & CommandBehavior.KeyInfo) != 0) LoadKeyInfo(); return true; } }
internal abstract object GetValue(SqliteStatement stmt, int index, SQLiteType typ);
internal override int Reset(SqliteStatement stmt) { int n = UnsafeNativeMethods.sqlite3_reset(stmt._sqlite_stmt); // If the schema changed, try and re-prepare it if (n == 17) // SQLITE_SCHEMA { // Recreate a dummy statement var timeout = (uint)(stmt._command._commandTimeout * 1000); string str; using (SqliteStatement tmp = Prepare(null, stmt._sqlStatement, null, timeout, out str)) { // Finalize the existing statement stmt._sqlite_stmt.Dispose(); // Reassign a new statement pointer to the old statement and clear the temporary one stmt._sqlite_stmt = tmp._sqlite_stmt; tmp._sqlite_stmt = null; // Reapply parameters stmt.BindParameters(); } return -1; // Reset was OK, with schema change } if (n == 6 || n == 5) // SQLITE_LOCKED || SQLITE_BUSY { return n; } if (n > 0) { throw new SqliteException(n, SQLiteLastError()); } return 0; // We reset OK, no schema changes }
/// <summary> /// Prepares a SQL statement for execution. /// </summary> /// <param name="cnn">The source connection preparing the command. Can be null for any caller except LINQ</param> /// <param name="strSql">The SQL command text to prepare</param> /// <param name="previous">The previous statement in a multi-statement command, or null if no previous statement exists</param> /// <param name="timeoutMS">The timeout to wait before aborting the prepare</param> /// <param name="strRemain">The remainder of the statement that was not processed. Each call to prepare parses the /// SQL up to to either the end of the text or to the first semi-colon delimiter. The remaining text is returned /// here for a subsequent call to Prepare() until all the text has been processed.</param> /// <returns>Returns an initialized SqliteStatement.</returns> internal abstract SqliteStatement Prepare(SqliteConnection cnn, string strSql, SqliteStatement previous, uint timeoutMS, out string strRemain);
internal override void Bind_Int64(SqliteStatement stmt, int index, long value) { int n = UnsafeNativeMethods.sqlite3_bind_int64(stmt._sqlite_stmt, index, value); if (n > 0) throw new SqliteException(n, SQLiteLastError()); }
/// <summary> /// Steps through a prepared statement. /// </summary> /// <param name="stmt">The SqliteStatement to step through</param> /// <returns>True if a row was returned, False if not.</returns> internal abstract bool Step(SqliteStatement stmt);
internal override void Bind_DateTime(SqliteStatement stmt, int index, DateTime dt) { var value = ToUTF8(dt); int n = UnsafeNativeMethods.sqlite3_bind_text(stmt._sqlite_stmt, index, value, value.Length, null); if (n > 0) throw new SqliteException(n, SQLiteLastError()); }
/// <summary> /// Initializes the statement and attempts to get all information about parameters in the statement /// </summary> /// <param name="sqlbase">The base SQLite object</param> /// <param name="stmt">The statement</param> /// <param name="strCommand">The command text for this statement</param> /// <param name="previous">The previous command in a multi-statement command</param> internal SqliteStatement(SQLiteBase sqlbase, SqliteStatementHandle stmt, string strCommand, SqliteStatement previous) { _sql = sqlbase; _sqlite_stmt = stmt; _sqlStatement = strCommand; // Determine parameters for this statement (if any) and prepare space for them. int nCmdStart = 0; int n = _sql.Bind_ParamCount(this); int x; string s; if (n > 0) { if (previous != null) { nCmdStart = previous._unnamedParameters; } _paramNames = new string[n]; _paramValues = new SqliteParameter[n]; for (x = 0; x < n; x++) { s = _sql.Bind_ParamName(this, x + 1); if (String.IsNullOrEmpty(s)) { s = String.Format(CultureInfo.InvariantCulture, ";{0}", nCmdStart); nCmdStart++; _unnamedParameters++; } _paramNames[x] = s; _paramValues[x] = null; } } }
internal override void Bind_Null(SqliteStatement stmt, int index) { int n = UnsafeNativeMethods.sqlite3_bind_null(stmt._sqlite_stmt, index); if (n > 0) throw new SqliteException(n, SQLiteLastError()); }
/// <summary> /// Resets a prepared statement so it can be executed again. If the error returned is SQLITE_SCHEMA, /// transparently attempt to rebuild the SQL statement and throw an error if that was not possible. /// </summary> /// <param name="stmt">The statement to reset</param> /// <returns>Returns -1 if the schema changed while resetting, 0 if the reset was sucessful or 6 (SQLITE_LOCKED) if the reset failed due to a lock</returns> internal abstract int Reset(SqliteStatement stmt);
internal override string Bind_ParamName(SqliteStatement stmt, int index) { return UTF8ToString(UnsafeNativeMethods.sqlite3_bind_parameter_name(stmt._sqlite_stmt, index), -1); }
internal abstract void Bind_Double(SqliteStatement stmt, int index, double value);
internal override int ColumnCount(SqliteStatement stmt) { return UnsafeNativeMethods.sqlite3_column_count(stmt._sqlite_stmt); }
internal abstract void Bind_Int64(SqliteStatement stmt, int index, Int64 value);
internal override string ColumnType(SqliteStatement stmt, int index, out TypeAffinity nAffinity) { var p = UnsafeNativeMethods.sqlite3_column_decltype(stmt._sqlite_stmt, index); nAffinity = ColumnAffinity(stmt, index); if (!string.IsNullOrEmpty(p)) { return UTF8ToString(p, -1); } string[] ar = stmt.TypeDefinitions; if (ar != null) { if (index < ar.Length && ar[index] != null) return ar[index]; } return String.Empty; }
internal abstract void Bind_Text(SqliteStatement stmt, int index, string value);
internal override string ColumnTableName(SqliteStatement stmt, int index) { return UTF8ToString(UnsafeNativeMethods.sqlite3_column_table_name(stmt._sqlite_stmt, index), -1); }
internal abstract void Bind_Blob(SqliteStatement stmt, int index, byte[] blobData);
internal override long GetInt64(SqliteStatement stmt, int index) { return UnsafeNativeMethods.sqlite3_column_int64(stmt._sqlite_stmt, index); }
internal abstract void Bind_DateTime(SqliteStatement stmt, int index, DateTime dt);
internal override DateTime GetDateTime(SqliteStatement stmt, int index) { return ToDateTime(UnsafeNativeMethods.sqlite3_column_text(stmt._sqlite_stmt, index), -1); }
internal abstract void Bind_Null(SqliteStatement stmt, int index);
internal override long GetChars(SqliteStatement stmt, int index, int nDataOffset, char[] bDest, int nStart, int nLength) { int nCopied = nLength; string str = GetText(stmt, index); int nlen = str.Length; if (bDest == null) { return nlen; } if (nCopied + nStart > bDest.Length) { nCopied = bDest.Length - nStart; } if (nCopied + nDataOffset > nlen) { nCopied = nlen - nDataOffset; } if (nCopied > 0) { str.CopyTo(nDataOffset, bDest, nStart, nCopied); } else { nCopied = 0; } return nCopied; }
internal abstract int Bind_ParamCount(SqliteStatement stmt);
/// <summary> /// Helper function to retrieve a column of data from an active statement. /// </summary> /// <param name="stmt">The statement being step()'d through</param> /// <param name="index">The column index to retrieve</param> /// <param name="typ">The type of data contained in the column. If Uninitialized, this function will retrieve the datatype information.</param> /// <returns>Returns the data in the column</returns> internal override object GetValue(SqliteStatement stmt, int index, SQLiteType typ) { if (IsNull(stmt, index)) return DBNull.Value; TypeAffinity aff = typ.Affinity; Type t = null; if (typ.Type != DbType.Object) { t = SQLiteTypeToType(typ); aff = TypeToAffinity(t); } switch (aff) { case TypeAffinity.Blob: if (typ.Type == DbType.Guid && typ.Affinity == TypeAffinity.Text) { return new Guid(GetText(stmt, index)); } var n = (int) GetBytes(stmt, index, 0, null, 0, 0); var b = new byte[n]; GetBytes(stmt, index, 0, b, 0, n); if (typ.Type == DbType.Guid && n == 16) { return new Guid(b); } return b; case TypeAffinity.DateTime: return GetDateTime(stmt, index); case TypeAffinity.Double: if (t == null) { return GetDouble(stmt, index); } return Convert.ChangeType(GetDouble(stmt, index), t, null); case TypeAffinity.Int64: if (t == null) { return GetInt64(stmt, index); } return Convert.ChangeType(GetInt64(stmt, index), t, null); default: return GetText(stmt, index); } }
internal abstract string Bind_ParamName(SqliteStatement stmt, int index);
internal override long GetRowIdForCursor(SqliteStatement stmt, int cursor) { return 0; }
internal abstract int Bind_ParamIndex(SqliteStatement stmt, string paramName);
internal abstract int ColumnCount(SqliteStatement stmt);
internal override bool Step(SqliteStatement stmt) { var rnd = new Random(); var starttick = (uint)Environment.TickCount; var timeout = (uint)(stmt._command._commandTimeout * 1000); while (true) { int n = UnsafeNativeMethods.sqlite3_step(stmt._sqlite_stmt); if (n == 100) { return true; } if (n == 101) { return false; } if (n > 0) { // An error occurred, attempt to reset the statement. If the reset worked because the // schema has changed, re-try the step again. If it errored our because the database // is locked, then keep retrying until the command timeout occurs. int r = this.Reset(stmt); if (r == 0) { throw new SqliteException(n, SQLiteLastError()); } if ((r == 6 || r == 5) && stmt._command != null) // SQLITE_LOCKED || SQLITE_BUSY { // Keep trying, but if we've exceeded the command's timeout, give up and throw an error if ((uint)Environment.TickCount - starttick > timeout) { throw new SqliteException(r, SQLiteLastError()); } // Otherwise sleep for a random amount of time up to 150ms Sleep(rnd.Next(1, 150)); } } } }
/// <summary> /// Closes the datareader, potentially closing the connection as well if CommandBehavior.CloseConnection was specified. /// </summary> public override void Close() { try { if (_command != null) { try { try { // Make sure we've not been canceled if (_version != 0) { try { while (NextResult()) { } } catch { } } _command.ClearDataReader(); } finally { // If the datareader's behavior includes closing the connection, then do so here. if ((_commandBehavior & CommandBehavior.CloseConnection) != 0 && _command.Connection != null) { // We need to call Dispose on the command before we call Dispose on the Connection, // otherwise we'll get a SQLITE_LOCKED exception. var conn = _command.Connection; _command.Dispose (); conn.Close(); _disposeCommand = false; } } } finally { if (_disposeCommand) _command.Dispose(); } } _command = null; _activeStatement = null; _fieldTypeArray = null; } finally { if (_keyInfo != null) { _keyInfo.Dispose(); _keyInfo = null; } } }
internal abstract string ColumnType(SqliteStatement stmt, int index, out TypeAffinity nAffinity);