/// <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);
