Beispiel #1
0
        public SQLiteDataReader ExecuteReader()
        {
            Sqlite3.Vdbe     stmt = Prepare();
            SQLiteDataReader sdr  = new SQLiteDataReader(this, stmt);

            return(sdr);
        }
Beispiel #2
0
        /// <summary>
        /// Creates new instance of SQLiteVdbe class by compiling a statement
        /// </summary>
        /// <param name="query"></param>
        /// <returns>Vdbe</returns>
        public SQLiteVdbe(SQLiteDatabase db, String query)
        {
            vm = null;

            // prepare and compile
            Sqlite3.sqlite3_prepare_v2(db.Connection(), query, query.Length, ref vm, 0);
        }
Beispiel #3
0
 public void Dispose()
 {
     Finalize();
     _columnNameList.Clear();
     _cmd = null;
     _vd  = null;
 }
Beispiel #4
0
        object ReadCol(Sqlite3.Vdbe stmt, int index, Type clrType)
        {
            var type = Sqlite3.sqlite3_column_type(stmt, index);

            if (type == Sqlite3.SQLITE_NULL)
            {
                return(null);
            }
            else
            {
                if (clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32))
                {
                    return(Convert.ChangeType(Sqlite3.sqlite3_column_int(stmt, index), clrType, System.Threading.Thread.CurrentThread.CurrentCulture));
                }
                else if (clrType == typeof(UInt32) || clrType == typeof(Int64))
                {
                    return(Convert.ChangeType(Sqlite3.sqlite3_column_int64(stmt, index), clrType, System.Threading.Thread.CurrentThread.CurrentCulture));
                }
                else if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal))
                {
                    return(Convert.ChangeType(Sqlite3.sqlite3_column_double(stmt, index), clrType, System.Threading.Thread.CurrentThread.CurrentCulture));
                }
                else if (clrType == typeof(String))
                {
                    return(Convert.ChangeType(Sqlite3.sqlite3_column_text(stmt, index), clrType, System.Threading.Thread.CurrentThread.CurrentCulture));
                }
                else
                {
                    throw new NotSupportedException("Don't know how to read " + clrType);
                }
            }
        }
Beispiel #5
0
        public Result Finalize(IDbStatement stmt)
        {
            var dbStatement = (DbStatement)stmt;

            Sqlite3.Vdbe internalStmt = dbStatement.InternalStmt;
            return((Result)Sqlite3.sqlite3_finalize(ref internalStmt));
        }
Beispiel #6
0
        public int ExecuteNonQuery()
        {
            Sqlite3.Vdbe stmt = Prepare();
            var          r    = Sqlite3.sqlite3_step(stmt);

            switch (r)
            {
            case Sqlite3.SQLITE_ERROR:
                string msg = Sqlite3.sqlite3_errmsg(_db);
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, msg);

            case Sqlite3.SQLITE_DONE:
                int rowsAffected = Sqlite3.sqlite3_changes(_db);
                Sqlite3.sqlite3_finalize(ref stmt);
                return(rowsAffected);

            case Sqlite3.SQLITE_CANTOPEN:
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, "Cannot open database file");

            case Sqlite3.SQLITE_CONSTRAINT:
                string msgC = Sqlite3.sqlite3_errmsg(_db);
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, msgC);

            default:
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, "Unknown error");
            }
        }
        // Executes a statement and ignores its result.
        private void ExecuteStatement(Sqlite3.Vdbe pStmt)
        {
            int    cols;
            IntPtr pazValue, pazColName;

            ExecuteStatement(pStmt, out cols, out pazValue, out pazColName);
        }
Beispiel #8
0
 public void close()
 {
     if (m_st != null)
     {
         Sqlite3.sqlite3_finalize(ref m_st);
         m_st = null;
     }
 }
        private void GetNextStatement(string pzStart, ref string pzTail, ref Sqlite3.Vdbe pStmt)
        {
            SqliteError err = (SqliteError)Sqlite3.sqlite3_prepare_v2(parent_conn.Handle2, pzStart, pzStart.Length, ref pStmt, ref pzTail);

            if (err != SqliteError.OK)
            {
                throw new SqliteSyntaxException(parent_conn.Handle2.errCode, GetError3());
            }
        }
Beispiel #10
0
        private Sqlite3.Vdbe Prepare(String strStatement, Object[] values)
        {
            Sqlite3.Vdbe ppStmt = new Sqlite3.Vdbe();
            Sqlite3.sqlite3_prepare_v2(m_db, strStatement, strStatement.Length, ref ppStmt, 0);
            checkError();

            Bind(ppStmt, values);
            return(ppStmt);
        }
Beispiel #11
0
 Sqlite3.Vdbe Prepare()
 {
     Sqlite3.Vdbe ppStmt = new Sqlite3.Vdbe();
     if (Sqlite3.sqlite3_prepare_v2(_db, CommandText, CommandText.Length, ref ppStmt, 0) != Sqlite3.SQLITE_OK)
     {
         throw new SQLiteException(Sqlite3.sqlite3_errmsg(_db));
     }
     BindAll(ppStmt);
     return(ppStmt);
 }
        private void GetNextStatement(string pzStart, ref string pzTail, ref Sqlite3.Vdbe pStmt)
        {
            UTF8Encoding encoding = new UTF8Encoding();
            SqliteError  err      = (SqliteError)Sqlite3.sqlite3_prepare(parent_conn.Handle2, pzStart, pzStart.Length, ref pStmt, ref pzTail);

            if (err != SqliteError.OK)
            {
                throw new SqliteSyntaxException(GetError3());
            }
        }
Beispiel #13
0
        internal static Exception GetSqliteError(Sqlite3.sqlite3 db, Sqlite3.Vdbe st)
        {
            /* SQLite often doesn't report anything useful, unless you reset the statement first */
            if (st != null)
            {
                Sqlite3.sqlite3_reset(st);
            }

            int    errorcode = Sqlite3.sqlite3_errcode(db);
            string errmsg    = Sqlite3.sqlite3_errmsg(db);

            switch (errorcode)
            {
            case SQLITE_OK:
                return(null);

            case Sqlite3.SQLITE_INTERNAL:
            case Sqlite3.SQLITE_NOTFOUND:
                return(MakeInternalError(errmsg));

            case Sqlite3.SQLITE_NOMEM:
                return(new OutOfMemoryException());

            case Sqlite3.SQLITE_ERROR:
            case Sqlite3.SQLITE_PERM:
            case Sqlite3.SQLITE_ABORT:
            case Sqlite3.SQLITE_BUSY:
            case Sqlite3.SQLITE_LOCKED:
            case Sqlite3.SQLITE_READONLY:
            case Sqlite3.SQLITE_INTERRUPT:
            case Sqlite3.SQLITE_IOERR:
            case Sqlite3.SQLITE_FULL:
            case Sqlite3.SQLITE_CANTOPEN:
            case Sqlite3.SQLITE_PROTOCOL:
            case Sqlite3.SQLITE_EMPTY:
            case Sqlite3.SQLITE_SCHEMA:
                return(MakeOperationalError(errmsg));

            case Sqlite3.SQLITE_CORRUPT:
                return(MakeDatabaseError(errmsg));

            case Sqlite3.SQLITE_TOOBIG:
                return(MakeDataError(errmsg));

            case Sqlite3.SQLITE_CONSTRAINT:
            case Sqlite3.SQLITE_MISMATCH:
                return(MakeIntegrityError(errmsg));

            case Sqlite3.SQLITE_MISUSE:
                return(MakeProgrammingError(errmsg));

            default:
                return(MakeDatabaseError(errmsg));
            }
        }
Beispiel #14
0
 public static int Step(Sqlite3.Vdbe statement)
 {
     if (statement == null)
     {
         return(Sqlite3.SQLITE_OK);
     }
     else
     {
         return(Sqlite3.sqlite3_step(statement));
     }
 }
Beispiel #15
0
        public static Sqlite3.Vdbe Prepare2(Sqlite3.sqlite3 db, string query)
        {
            Sqlite3.Vdbe stmt = new Sqlite3.Vdbe();
            var          r    = Sqlite3.sqlite3_prepare_v2(db, query, System.Text.UTF8Encoding.UTF8.GetByteCount(query), ref stmt, 0);

            if (r != 0)
            {
                throw SQLiteException.New((Result)r, GetErrmsg(db));
            }
            return(stmt);
        }
Beispiel #16
0
 internal SqliteDataReader(SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
 {
     command             = cmd;
     rows                = new List <object[]>();
     column_names_sens   = new Hashtable();
     column_names_insens = new Hashtable(StringComparer.InvariantCultureIgnoreCase);
     closed              = false;
     current_row         = -1;
     reading             = true;
     ReadpVm(pVm, version, cmd);
     ReadingDone();
 }
        /// <summary>
        /// Creates new instance of SQLiteVdbe class by compiling a statement
        /// </summary>
        /// <param name="query"></param>
        /// <returns>Vdbe</returns>
        public SQLiteVdbe(SQLiteDatabase db, String query)
        {
            vm = null;

            // prepare and compile
            #if NET_35
              Sqlite3.PrepareV2NoTail
            #else
            Sqlite3.sqlite3_prepare_v2
            #endif
            (db.Connection(), query, query.Length, ref vm, 0);
        }
Beispiel #18
0
        Sqlite3.Vdbe Prepare()
        {
            Sqlite3.Vdbe ppStmt = new Sqlite3.Vdbe();
            int          n      = Sqlite3.sqlite3_prepare_v2(_db, CommandText, CommandText.Length, ref ppStmt, 0);

            if (n != Sqlite3.SQLITE_OK)
            {
                throw new SQLiteException(n, SQLiteLastError());
            }
            BindAll(ppStmt);
            return(ppStmt);
        }
Beispiel #19
0
 internal SqliteDataReader(SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
 {
     command             = cmd;
     rows                = new List <object[]>();
     column_names_sens   = new Dictionary <String, Object>();
     column_names_insens = new Dictionary <String, Object>(StringComparer.OrdinalIgnoreCase);
     closed              = false;
     current_row         = -1;
     reading             = true;
     ReadpVm(pVm, version, cmd);
     ReadingDone();
 }
Beispiel #20
0
        private void Bind(Sqlite3.Vdbe stmt, Object[] values)
        {
            for (int i = 0; values != null && i < values.Length; i++)
            {
                var obj = values[i];
                if (obj == null)
                {
                    Sqlite3.sqlite3_bind_null(stmt, i + 1);
                }
                else if (obj is Byte || obj is UInt16 || obj is SByte || obj is Int16 || obj is Int32)
                {
                    Sqlite3.sqlite3_bind_int(stmt, i + 1, Convert.ToInt32(obj, CultureInfo.InvariantCulture));
                }
                else if (obj is UInt32 || obj is Int64)
                {
                    Sqlite3.sqlite3_bind_int64(stmt, i + 1, Convert.ToInt64(obj, CultureInfo.InvariantCulture));
                }
                else if (obj is Single || obj is Double || obj is Decimal)
                {
                    Sqlite3.sqlite3_bind_double(stmt, i + 1, Convert.ToDouble(obj, CultureInfo.InvariantCulture));
                }
                else if (obj is String)
                {
                    Sqlite3.sqlite3_bind_text(stmt, i + 1, (String)obj, ((String)obj).Length, null);
                }
                else if (obj is MutableString)
                {
                    Sqlite3.sqlite3_bind_text(stmt, i + 1, obj.ToString(), ((MutableString)obj).Length, null);
                }
                else if (obj is byte[])
                {
                    Sqlite3.sqlite3_bind_blob(stmt, i + 1, (byte[])obj, ((byte[])obj).Length, null);
                }
                else if (obj is RubySymbol)
                {
                    String val = ((RubySymbol)obj).ToString();
                    Sqlite3.sqlite3_bind_text(stmt, i + 1, val, val.Length, null);
                }
                else if (obj is Boolean)
                {
                    String val = ((Boolean)obj) ? "true" : "false";
                    Sqlite3.sqlite3_bind_text(stmt, i + 1, val, val.Length, null);
                }
                else
                {
                    String val = obj.ToString();
                    Sqlite3.sqlite3_bind_text(stmt, i + 1, val, val.Length, null);
                }

                checkError();
            }
        }
Beispiel #21
0
 public static int sqlite3_prepare_v2(
     sqlite3 db,               /* Database handle. */
     string zSql,              /* UTF-8 encoded SQL statement. */
     int nBytes,               /* Length of zSql in bytes. */
     ref sqlite3_stmt ppStmt,  /* OUT: A pointer to the prepared statement */
     ref string pzTail         /* OUT: End of parsed string */
     )
 {
     int rc;
       rc = sqlite3LockAndPrepare( db, zSql, nBytes, 1, null, ref  ppStmt, ref pzTail );
       Debug.Assert( rc == SQLITE_OK || ppStmt == null );  /* VERIFY: F13021 */
       return rc;
 }
Beispiel #22
0
        public IDbStatement Prepare2(IDbHandle db, string query)
        {
            var dbHandle = (DbHandle)db;
            var stmt     = new Sqlite3.Vdbe();

            int r = Sqlite3.sqlite3_prepare_v2(dbHandle.InternalDbHandle, query, -1, ref stmt, 0);

            if (r != 0)
            {
                throw SQLiteException.New((Result)r, GetErrmsg(db));
            }
            return(new DbStatement(stmt));
        }
        public IDbStatement Prepare2(IDbHandle db, string query)
        {
            var dbHandle = (DbHandle) db;
            var stmt = new Sqlite3.Vdbe();

            int r = Sqlite3.sqlite3_prepare_v2(dbHandle.InternalDbHandle, query, -1, ref stmt, 0);

            if (r != 0)
            {
                throw SQLiteException.New((Result) r, GetErrmsg(db));
            }
            return new DbStatement(stmt);
        }
        internal SqliteDataReader(SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
        {
            command           = cmd;
            rows              = new ArrayList();
            column_names_sens = new Hashtable();

            column_names_insens = new Hashtable(CaseInsensitiveHashCodeProvider.DefaultInvariant,
                                                CaseInsensitiveComparer.DefaultInvariant);

            closed      = false;
            current_row = -1;
            reading     = true;
            ReadpVm(pVm, version, cmd);
            ReadingDone();
        }
Beispiel #25
0
        public int ExecuteNonQuery <T>(T toInsert)
        {
            Sqlite3.Vdbe stmt = new Sqlite3.Vdbe();
            int          n    = Sqlite3.sqlite3_prepare_v2(_db, CommandText, CommandText.Length, ref stmt, 0);

            if (n != Sqlite3.SQLITE_OK)
            {
                throw new SQLiteException(n, SQLiteLastError());
            }

            var props = GetProps(typeof(T));
            var ncols = Sqlite3.sqlite3_column_count(stmt);
            var cols  = new System.Reflection.PropertyInfo[ncols];

            _bindings.Clear();
            for (int i = 0; i < props.Length; i++)
            {
                Bind("@" + props[i].Name, props[i].GetValue(toInsert, null));
            }
            BindAll(stmt);
            var r = Sqlite3.sqlite3_step(stmt);

            switch (r)
            {
            case Sqlite3.SQLITE_ERROR:
                string msg = Sqlite3.sqlite3_errmsg(_db);
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, msg);

            case Sqlite3.SQLITE_DONE:
                int rowsAffected = Sqlite3.sqlite3_changes(_db);
                Sqlite3.sqlite3_finalize(ref stmt);
                return(rowsAffected);

            case Sqlite3.SQLITE_CANTOPEN:
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, "Cannot open database file");

            case Sqlite3.SQLITE_CONSTRAINT:
                string msgC = Sqlite3.sqlite3_errmsg(_db);
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, msgC);

            default:
                Sqlite3.sqlite3_finalize(ref stmt);
                throw new SQLiteException(r, "Unknown error");
            }
        }
        private static void ExecuteCommand(Sqlite3.sqlite3 db, string command)
        {
            int rc;

            Sqlite3.Vdbe vm = null;
            if (Sqlite3.sqlite3_prepare_v2(db, command, command.Length, ref vm, 0) != Sqlite3.SQLITE_OK)
            {
                throw new InvalidOperationException(string.Format("Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg(db)));
            }
            rc = Sqlite3.sqlite3_step(vm);
            if (rc != Sqlite3.SQLITE_DONE && rc != Sqlite3.SQLITE_ROW)
            {
                throw new InvalidOperationException(string.Format("Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg(db)));
            }
            Sqlite3.sqlite3_finalize(vm);
        }
Beispiel #27
0
        void BindAll(Sqlite3.Vdbe stmt)
        {
            int nextIdx = 1;

            foreach (var b in _bindings)
            {
                if (b.Name != null)
                {
                    b.Index = Sqlite3.sqlite3_bind_parameter_index(stmt, b.Name);
                }
                else
                {
                    b.Index = nextIdx++;
                }
            }
            foreach (var b in _bindings)
            {
                if (b.Value == null)
                {
                    Sqlite3.sqlite3_bind_null(stmt, b.Index);
                }
                else
                {
                    if (b.Value is Byte || b.Value is UInt16 || b.Value is SByte || b.Value is Int16 || b.Value is Int32)
                    {
                        Sqlite3.sqlite3_bind_int(stmt, b.Index, Convert.ToInt32(b.Value));
                    }
                    else if (b.Value is UInt32 || b.Value is Int64)
                    {
                        Sqlite3.sqlite3_bind_int64(stmt, b.Index, Convert.ToInt64(b.Value));
                    }
                    else if (b.Value is Single || b.Value is Double || b.Value is Decimal)
                    {
                        Sqlite3.sqlite3_bind_double(stmt, b.Index, Convert.ToDouble(b.Value));
                    }
                    else if (b.Value is String)
                    {
                        Sqlite3.sqlite3_bind_text(stmt, b.Index, b.Value.ToString(), -1, null);
                    }
                }
            }
        }
        // Executes a statement and returns whether there is more data available.
        internal bool ExecuteStatement(Sqlite3.Vdbe pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName)
        {
            SqliteError err;

            //if (parent_conn.Version == 3)
            //{
            err = (SqliteError)Sqlite3.sqlite3_step(pStmt);

            if (err == SqliteError.ERROR)
            {
                throw new SqliteExecutionException(GetError3());
            }

            pazValue   = IntPtr.Zero;
            pazColName = IntPtr.Zero; // not used for v=3
            cols       = Sqlite3.sqlite3_column_count(pStmt);

            /*
             * }
             * else
             * {
             * err = (SqliteError)Sqlite3.sqlite3_step(pStmt, out cols, out pazValue, out pazColName);
             * if (err == SqliteError.ERROR)
             * throw new SqliteExecutionException ();
             * }
             */
            if (err == SqliteError.BUSY)
            {
                throw new SqliteBusyException();
            }

            if (err == SqliteError.MISUSE)
            {
                throw new SqliteExecutionException();
            }

            // err is either ROW or DONE.
            return(err == SqliteError.ROW);
        }
Beispiel #29
0
 private Sqlite3.Vdbe Prepare(String strStatement, Object[] values)
 {
     Sqlite3.Vdbe ppStmt=new Sqlite3.Vdbe();
     Sqlite3.sqlite3_prepare_v2(m_db, strStatement, strStatement.Length, ref ppStmt, 0);
     checkError();
     
     Bind(ppStmt, values);
     return ppStmt;
 }
        public void Issue_124()
        {
            Console.WriteLine("Test Start.");

            Sqlite3.sqlite3 db = null;
            Sqlite3.sqlite3_open(":memory:", out db);
            Sqlite3.Vdbe stmt = null;
            string       zero = null;
            string       val;

            Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

            //create table
            {
                Sqlite3.sqlite3_prepare_v2(db, "create table Test (val REAL NOT NULL)", -1, ref stmt, ref zero);
                Sqlite3.sqlite3_step(stmt);
                Sqlite3.sqlite3_finalize(stmt);
            }

            //insert 0.1
            {
                Sqlite3.sqlite3_prepare_v2(db, "insert into Test(val) values ('0.1')", -1, ref stmt, ref zero);
                Sqlite3.sqlite3_step(stmt);
                Sqlite3.sqlite3_finalize(stmt);
            }
            //insert 0.1
            {
                Sqlite3.sqlite3_prepare_v2(db, "insert into Test(val) values ('0.2')", -1, ref stmt, ref zero);
                Sqlite3.sqlite3_step(stmt);
                Sqlite3.sqlite3_finalize(stmt);
            }

            //insert 0.000000001
            {
                Sqlite3.sqlite3_prepare_v2(db, "insert into Test(val) values ('0.000000001')", -1, ref stmt, ref zero);
                Sqlite3.sqlite3_step(stmt);
                Sqlite3.sqlite3_finalize(stmt);
            }

            //invariant culture
            {
                System.Console.WriteLine("invariant culture");
                Sqlite3.sqlite3_prepare_v2(db, "select val from Test", -1, ref stmt, ref zero);
                Sqlite3.sqlite3_step(stmt);
                val = Sqlite3.sqlite3_column_text(stmt, 0);
                System.Console.WriteLine("value: " + val);
                Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("ru");
                Sqlite3.sqlite3_step(stmt);
                val = Sqlite3.sqlite3_column_text(stmt, 0);
                System.Console.WriteLine("value: " + val);
                Sqlite3.sqlite3_step(stmt);
                val = Sqlite3.sqlite3_column_text(stmt, 0);
                System.Console.WriteLine("value: " + val);
                Sqlite3.sqlite3_finalize(stmt);
            }

            //ru-ru culture
            {
                System.Console.WriteLine("ru");
                Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("ru");
                Sqlite3.sqlite3_prepare_v2(db, "select val from Test", -1, ref stmt, ref zero);
                Sqlite3.sqlite3_step(stmt);
                val = Sqlite3.sqlite3_column_text(stmt, 0);
                System.Console.WriteLine("value: " + val);
                Sqlite3.sqlite3_step(stmt);
                val = Sqlite3.sqlite3_column_text(stmt, 0);
                System.Console.WriteLine("value: " + val);
                Sqlite3.sqlite3_finalize(stmt);
            }

            Console.WriteLine("Test Done.");
        }
Beispiel #31
0
 /*
 ** 2003 April 6
 **
 ** The author disclaims copyright to this source code.  In place of
 ** a legal notice, here is a blessing:
 **
 **    May you do good and not evil.
 **    May you find forgiveness for yourself and forgive others.
 **    May you share freely, never taking more than you give.
 **
 *************************************************************************
 ** This file contains code used to implement the VACUUM command.
 **
 ** Most of the code in this file may be omitted by defining the
 ** SQLITE_OMIT_VACUUM macro.
 *************************************************************************
 **  Included in SQLite3 port to C#-SQLite;  2008 Noah B Hart
 **  C#-SQLite is an independent reimplementation of the SQLite software library
 **
 **  SQLITE_SOURCE_ID: 2011-05-19 13:26:54 ed1da510a239ea767a01dc332b667119fa3c908e
 **
 *************************************************************************
 */
 //#include "sqliteInt.h"
 //#include "vdbeInt.h"
 /*
 ** Finalize a prepared statement.  If there was an error, store the
 ** text of the error message in *pzErrMsg.  Return the result code.
 */
 static int vacuumFinalize(sqlite3 db, sqlite3_stmt pStmt, string pzErrMsg)
 {
     int rc;
     rc = sqlite3VdbeFinalize(ref pStmt);
     if (rc != 0)
     {
         sqlite3SetString(ref pzErrMsg, db, sqlite3_errmsg(db));
     }
     return rc;
 }
        // private function for reading rows and creating table and columns
        private int ReadNextRow(Vdbe vm, DataTable table)
        {
            int columnCount = table.Columns.Count;
            if(columnCount == 0)
            {
                if((columnCount = ReadColumnNames(vm, table)) == 0)
                    return Sqlite3.SQLITE_ERROR;
            }

            int resultType;
            if((resultType = Sqlite3.sqlite3_step(vm)) == Sqlite3.SQLITE_ROW)
            {
                object[] columnValues = new object[columnCount];

                for(int i = 0; i < columnCount; i++)
                {
                    int columnType = Sqlite3.sqlite3_column_type(vm, i);
                    switch(columnType)
                    {
                        case Sqlite3.SQLITE_INTEGER:
                            {
                                table.Columns[i].DataType = typeof(Int64);
                                columnValues[i] = Sqlite3.sqlite3_column_int(vm, i);
                                break;
                            }
                        case Sqlite3.SQLITE_FLOAT:
                            {
                                table.Columns[i].DataType = typeof(Double);
                                columnValues[i] = Sqlite3.sqlite3_column_double(vm, i);
                                break;
                            }
                        case Sqlite3.SQLITE_TEXT:
                            {
                                table.Columns[i].DataType = typeof(String);
                                columnValues[i] = Sqlite3.sqlite3_column_text(vm, i);
                                break;
                            }
                        case Sqlite3.SQLITE_BLOB:
                            {
                                table.Columns[i].DataType = typeof(Byte[]);
                                columnValues[i] = Sqlite3.sqlite3_column_blob(vm, i);
                                break;
                            }
                        default:
                            {
                                table.Columns[i].DataType = null;
                                columnValues[i] = "";
                                break;
                            }
                    }
                }
                table.Rows.Add(columnValues);
            }
            return resultType;
        }
        // private function for creating Column Names
        // Return number of colums read
        private int ReadColumnNames(Vdbe vm, DataTable table)
        {
            String columnName = "";
            int columnType = 0;
            // returns number of columns returned by statement
            int columnCount = Sqlite3.sqlite3_column_count(vm);
            object[] columnValues = new object[columnCount];

            try
            {
                // reads columns one by one
                for(int i = 0; i < columnCount; i++)
                {
                    columnName = Sqlite3.sqlite3_column_name(vm, i);
                    columnType = Sqlite3.sqlite3_column_type(vm, i);

                    switch(columnType)
                    {
                        case Sqlite3.SQLITE_INTEGER:
                            {
                                // adds new integer column to table
                                table.Columns.Add(columnName, Type.GetType("System.Int64"));
                                break;
                            }
                        case Sqlite3.SQLITE_FLOAT:
                            {
                                table.Columns.Add(columnName, Type.GetType("System.Double"));
                                break;
                            }
                        case Sqlite3.SQLITE_TEXT:
                            {
                                table.Columns.Add(columnName, Type.GetType("System.String"));
                                break;
                            }
                        case Sqlite3.SQLITE_BLOB:
                            {
                                table.Columns.Add(columnName, Type.GetType("System.byte[]"));
                                break;
                            }
                        default:
                            {
                                table.Columns.Add(columnName, Type.GetType("System.String"));
                                break;
                            }
                    }
                }
            }
            catch
            {
                return 0;
            }
            return table.Columns.Count;
        }
        public SqliteDataReader ExecuteReader(CommandBehavior behavior, bool want_results, out int rows_affected)
        {
            Prepare();

            // The SQL string may contain multiple sql commands, so the main
            // thing to do is have Sqlite iterate through the commands.
            // If want_results, only the last command is returned as a
            // DataReader.  Otherwise, no command is returned as a
            // DataReader.

            //IntPtr psql; // pointer to SQL command

            // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
            // character (iso8859).  But if you give the --enable-utf8 option to the configure script, then the
            // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
            // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
            // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
            // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
            //
            // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
            // default.  Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
            // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
            // of what Sqlite is treating them as,

            // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.

            /*
             * if (parent_conn.Version == 2)
             * psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
             * else
             * psql = Marshal.StringToHGlobalUni (sql.Trim());
             */
            string queryval = sql.Trim();
            string pzTail   = sql.Trim();
            IntPtr errMsgPtr;

            parent_conn.StartExec();

            rows_affected = 0;

            try
            {
                while (true)
                {
                    Sqlite3.Vdbe pStmt = null;

                    queryval = pzTail;
                    GetNextStatement(queryval, ref pzTail, ref pStmt);

                    if (pStmt == null)
                    {
                        throw new Exception();
                    }

                    // pzTail is positioned after the last byte in the
                    // statement, which will be the NULL character if
                    // this was the last statement.
                    bool last = pzTail.Length == 0;

                    try
                    {
                        if (parent_conn.Version == 3)
                        {
                            BindParameters3(pStmt);
                        }

                        if (last && want_results)
                        {
                            return(new SqliteDataReader(this, pStmt, parent_conn.Version));
                        }

                        ExecuteStatement(pStmt);

                        if (last) // rows_affected is only used if !want_results
                        {
                            rows_affected = NumChanges();
                        }
                    }
                    finally
                    {
                        //if (parent_conn.Version == 3)
                        Sqlite3.sqlite3_finalize(pStmt);
                        //else
                        //	Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
                    }

                    if (last)
                    {
                        break;
                    }
                }

                return(null);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return(null);
            }
            finally
            {
                parent_conn.EndExec();
                //Marshal.FreeHGlobal (psql);
            }
        }
Beispiel #35
0
 public DbStatement(Sqlite3.Vdbe internalStmt)
     : this()
 {
     InternalStmt = internalStmt;
 }
Beispiel #36
0
        /*
        ** Compile the UTF-8 encoded SQL statement zSql into a statement handle.
        */
        static int sqlite3Prepare(
            sqlite3 db,               /* Database handle. */
            string zSql,              /* UTF-8 encoded SQL statement. */
            int nBytes,               /* Length of zSql in bytes. */
            int saveSqlFlag,          /* True to copy SQL text into the sqlite3_stmt */
            Vdbe pReprepare,          /* VM being reprepared */
            ref sqlite3_stmt ppStmt,  /* OUT: A pointer to the prepared statement */
            ref string pzTail         /* OUT: End of parsed string */
            )
        {
            Parse pParse;             /* Parsing context */
              string zErrMsg = "";      /* Error message */
              int rc = SQLITE_OK;       /* Result code */
              int i;                    /* Loop counter */

              /* Allocate the parsing context */
              pParse = new Parse();//sqlite3StackAllocZero(db, sizeof(*pParse));
              if ( pParse == null )
              {
            rc = SQLITE_NOMEM;
            goto end_prepare;
              }
              pParse.pReprepare = pReprepare;
              pParse.sLastToken.z = "";

              Debug.Assert( ppStmt == null );//  assert( ppStmt && *ppStmt==0 );
              //Debug.Assert( 0 == db.mallocFailed );
              Debug.Assert( sqlite3_mutex_held( db.mutex ) );

              /* Check to verify that it is possible to get a read lock on all
              ** database schemas.  The inability to get a read lock indicates that
              ** some other database connection is holding a write-lock, which in
              ** turn means that the other connection has made uncommitted changes
              ** to the schema.
              **
              ** Were we to proceed and prepare the statement against the uncommitted
              ** schema changes and if those schema changes are subsequently rolled
              ** back and different changes are made in their place, then when this
              ** prepared statement goes to run the schema cookie would fail to detect
              ** the schema change.  Disaster would follow.
              **
              ** This thread is currently holding mutexes on all Btrees (because
              ** of the sqlite3BtreeEnterAll() in sqlite3LockAndPrepare()) so it
              ** is not possible for another thread to start a new schema change
              ** while this routine is running.  Hence, we do not need to hold
              ** locks on the schema, we just need to make sure nobody else is
              ** holding them.
              **
              ** Note that setting READ_UNCOMMITTED overrides most lock detection,
              ** but it does *not* override schema lock detection, so this all still
              ** works even if READ_UNCOMMITTED is set.
              */
              for ( i = 0; i < db.nDb; i++ )
              {
            Btree pBt = db.aDb[i].pBt;
            if ( pBt != null )
            {
              Debug.Assert( sqlite3BtreeHoldsMutex( pBt ) );
              rc = sqlite3BtreeSchemaLocked( pBt );
              if ( rc != 0 )
              {
            string zDb = db.aDb[i].zName;
            sqlite3Error( db, rc, "database schema is locked: %s", zDb );
            testcase( db.flags & SQLITE_ReadUncommitted );
            goto end_prepare;
              }
            }
              }

              sqlite3VtabUnlockList( db );

              pParse.db = db;
              pParse.nQueryLoop = (double)1;
              if ( nBytes >= 0 && ( nBytes == 0 || zSql[nBytes - 1] != 0 ) )
              {
            string zSqlCopy;
            int mxLen = db.aLimit[SQLITE_LIMIT_SQL_LENGTH];
            testcase( nBytes == mxLen );
            testcase( nBytes == mxLen + 1 );
            if ( nBytes > mxLen )
            {
              sqlite3Error( db, SQLITE_TOOBIG, "statement too long" );
              rc = sqlite3ApiExit( db, SQLITE_TOOBIG );
              goto end_prepare;
            }
            zSqlCopy = zSql.Substring( 0, nBytes );// sqlite3DbStrNDup(db, zSql, nBytes);
            if ( zSqlCopy != null )
            {
              sqlite3RunParser( pParse, zSqlCopy, ref zErrMsg );
              sqlite3DbFree( db, ref zSqlCopy );
              //pParse->zTail = &zSql[pParse->zTail-zSqlCopy];
            }
            else
            {
              //pParse->zTail = &zSql[nBytes];
            }
              }
              else
              {
            sqlite3RunParser( pParse, zSql, ref zErrMsg );
              }
              Debug.Assert( 1 == (int)pParse.nQueryLoop );

              //if ( db.mallocFailed != 0 )
              //{
              //  pParse.rc = SQLITE_NOMEM;
              //}
              if ( pParse.rc == SQLITE_DONE )
            pParse.rc = SQLITE_OK;
              if ( pParse.checkSchema != 0 )
              {
            schemaIsValid( pParse );
              }
              if ( pParse.rc == SQLITE_SCHEMA )
              {
            sqlite3ResetInternalSchema( db, 0 );
              }
              //if ( db.mallocFailed != 0 )
              //{
              //  pParse.rc = SQLITE_NOMEM;
              //}
              //if (pzTail != null)
              {
            pzTail = pParse.zTail == null ? "" : pParse.zTail.ToString();
              }
              rc = pParse.rc;
            #if !SQLITE_OMIT_EXPLAIN
              if ( rc == SQLITE_OK && pParse.pVdbe != null && pParse.explain != 0 )
              {
            string[] azColName = new string[] {
            "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment",
            "selectid", "order", "from", "detail"
            };
            int iFirst, mx;
            if ( pParse.explain == 2 )
            {
              sqlite3VdbeSetNumCols( pParse.pVdbe, 4 );
              iFirst = 8;
              mx = 12;
            }
            else
            {
              sqlite3VdbeSetNumCols( pParse.pVdbe, 8 );
              iFirst = 0;
              mx = 8;
            }
            for ( i = iFirst; i < mx; i++ )
            {
              sqlite3VdbeSetColName( pParse.pVdbe, i - iFirst, COLNAME_NAME,
              azColName[i], SQLITE_STATIC );
            }
              }
            #endif

              Debug.Assert( db.init.busy == 0 || saveSqlFlag == 0 );
              if ( db.init.busy == 0 )
              {
            Vdbe pVdbe = pParse.pVdbe;
            sqlite3VdbeSetSql( pVdbe, zSql, (int)( zSql.Length - ( pParse.zTail == null ? 0 : pParse.zTail.Length ) ), saveSqlFlag );
              }
              if ( pParse.pVdbe != null && ( rc != SQLITE_OK /*|| db.mallocFailed != 0 */ ) )
              {
            sqlite3VdbeFinalize( pParse.pVdbe );
            Debug.Assert( ppStmt == null );
              }
              else
              {
            ppStmt = pParse.pVdbe;
              }

              if ( zErrMsg != "" )
              {
            sqlite3Error( db, rc, "%s", zErrMsg );
            sqlite3DbFree( db, ref zErrMsg );
              }
              else
              {
            sqlite3Error( db, rc, 0 );
              }

              /* Delete any TriggerPrg structures allocated while parsing this statement. */
              while ( pParse.pTriggerPrg != null )
              {
            TriggerPrg pT = pParse.pTriggerPrg;
            pParse.pTriggerPrg = pT.pNext;
            sqlite3DbFree( db, ref pT );
              }

            end_prepare:

              //sqlite3StackFree( db, pParse );
              rc = sqlite3ApiExit( db, rc );
              Debug.Assert( ( rc & db.errMask ) == rc );
              return rc;
        }
Beispiel #37
0
        /*
        ** Rerun the compilation of a statement after a schema change.
        **
        ** If the statement is successfully recompiled, return SQLITE_OK. Otherwise,
        ** if the statement cannot be recompiled because another connection has
        ** locked the sqlite3_master table, return SQLITE_LOCKED. If any other error
        ** occurs, return SQLITE_SCHEMA.
        */
        static int sqlite3Reprepare( Vdbe p )
        {
            int rc;
              sqlite3_stmt pNew = new sqlite3_stmt();
              string zSql;
              sqlite3 db;

              Debug.Assert( sqlite3_mutex_held( sqlite3VdbeDb( p ).mutex ) );
              zSql = sqlite3_sql( (sqlite3_stmt)p );
              Debug.Assert( zSql != null );  /* Reprepare only called for prepare_v2() statements */
              db = sqlite3VdbeDb( p );
              Debug.Assert( sqlite3_mutex_held( db.mutex ) );
              string dummy = "";
              rc = sqlite3LockAndPrepare( db, zSql, -1, 0, p, ref pNew, ref dummy );
              if ( rc != 0 )
              {
            if ( rc == SQLITE_NOMEM )
            {
              //        db.mallocFailed = 1;
            }
            Debug.Assert( pNew == null );
            return rc;
              }
              else
              {
            Debug.Assert( pNew != null );
              }
              sqlite3VdbeSwap( (Vdbe)pNew, p );
              sqlite3TransferBindings( pNew, (sqlite3_stmt)p );
              sqlite3VdbeResetStepResult( (Vdbe)pNew );
              sqlite3VdbeFinalize( (Vdbe)pNew );
              return SQLITE_OK;
        }
        private void BindParameters3(Sqlite3.Vdbe pStmt)
        {
            if (sql_params == null)
            {
                return;
            }
            if (sql_params.Count == 0)
            {
                return;
            }

            int pcount = Sqlite3.sqlite3_bind_parameter_count(pStmt);

            for (int i = 1; i <= pcount; i++)
            {
                String name = Sqlite3.sqlite3_bind_parameter_name(pStmt, i);

                SqliteParameter param = null;
                if (name != null)
                {
                    param = sql_params[name] as SqliteParameter;
                }
                else
                {
                    param = sql_params[i - 1] as SqliteParameter;
                }

                if (param.Value == null)
                {
                    Sqlite3.sqlite3_bind_null(pStmt, i);
                    continue;
                }

                Type ptype = param.Value.GetType();
                if (ptype.IsEnum)
                {
                    ptype = Enum.GetUnderlyingType(ptype);
                }

                SqliteError err;

                if (ptype.Equals(typeof(String)))
                {
                    String s = (String)param.Value;
                    err = (SqliteError)Sqlite3.sqlite3_bind_text(pStmt, i, s, -1, null);
                }
                else if (ptype.Equals(typeof(DBNull)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_null(pStmt, i);
                }
                else if (ptype.Equals(typeof(Boolean)))
                {
                    bool b = (bool)param.Value;
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, b ? 1 : 0);
                }
                else if (ptype.Equals(typeof(Byte)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (Byte)param.Value);
                }
                else if (ptype.Equals(typeof(Char)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (Char)param.Value);
                }
                else if (ptype.IsEnum)
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (Int32)param.Value);
                }
                else if (ptype.Equals(typeof(Int16)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (Int16)param.Value);
                }
                else if (ptype.Equals(typeof(Int32)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (Int32)param.Value);
                }
                else if (ptype.Equals(typeof(SByte)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (SByte)param.Value);
                }
                else if (ptype.Equals(typeof(UInt16)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int(pStmt, i, (UInt16)param.Value);
                }
                else if (ptype.Equals(typeof(DateTime)))
                {
                    DateTime dt = (DateTime)param.Value;
                    err = (SqliteError)Sqlite3.sqlite3_bind_text(pStmt, i, dt.ToString("yyyy-MM-dd HH:mm:ss.fff"), -1, null);
                }
                else if (ptype.Equals(typeof(Decimal)))
                {
                    string val = ((Decimal)param.Value).ToString(CultureInfo.InvariantCulture);
                    err = (SqliteError)Sqlite3.sqlite3_bind_text(pStmt, i, val, val.Length, null);
                }
                else if (ptype.Equals(typeof(Double)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_double(pStmt, i, (Double)param.Value);
                }
                else if (ptype.Equals(typeof(Single)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_double(pStmt, i, (Single)param.Value);
                }
                else if (ptype.Equals(typeof(UInt32)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int64(pStmt, i, (UInt32)param.Value);
                }
                else if (ptype.Equals(typeof(Int64)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_int64(pStmt, i, (Int64)param.Value);
                }
                else if (ptype.Equals(typeof(Byte[])))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_blob(pStmt, i, (byte[])param.Value, ((byte[])param.Value).Length, null);
                }
                else if (ptype.Equals(typeof(Guid)))
                {
                    err = (SqliteError)Sqlite3.sqlite3_bind_text(pStmt, i, param.Value.ToString(), param.Value.ToString().Length, null);
                }
                else
                {
                    throw new ApplicationException("Unkown Parameter Type");
                }
                if (err != SqliteError.OK)
                {
                    throw new ApplicationException("Sqlite error in bind " + err);
                }
            }
        }
Beispiel #39
0
 public CSqliteResult(Sqlite3.Vdbe stmt, boolean bNoCopy)
 {
     m_bNoCopy = bNoCopy;
     m_st = stmt;
 }
Beispiel #40
0
 static int sqlite3LockAndPrepare(
     sqlite3 db,               /* Database handle. */
     string zSql,              /* UTF-8 encoded SQL statement. */
     int nBytes,               /* Length of zSql in bytes. */
     int saveSqlFlag,          /* True to copy SQL text into the sqlite3_stmt */
     Vdbe pOld,                /* VM being reprepared */
     ref sqlite3_stmt ppStmt,  /* OUT: A pointer to the prepared statement */
     ref string pzTail         /* OUT: End of parsed string */
     )
 {
     int rc;
       //  assert( ppStmt!=0 );
       ppStmt = null;
       if ( !sqlite3SafetyCheckOk( db ) )
       {
     return SQLITE_MISUSE_BKPT();
       }
       sqlite3_mutex_enter( db.mutex );
       sqlite3BtreeEnterAll( db );
       rc = sqlite3Prepare( db, zSql, nBytes, saveSqlFlag, pOld, ref ppStmt, ref pzTail );
       if ( rc == SQLITE_SCHEMA )
       {
     sqlite3_finalize( ppStmt );
     rc = sqlite3Prepare( db, zSql, nBytes, saveSqlFlag, pOld, ref ppStmt, ref  pzTail );
       }
       sqlite3BtreeLeaveAll( db );
       sqlite3_mutex_leave( db.mutex );
       return rc;
 }
Beispiel #41
0
 public void close()
 {
     if (m_st != null)
     {
         Sqlite3.sqlite3_finalize(ref m_st);
         m_st = null;
     }
 }
Beispiel #42
0
        internal void ReadpVm(Sqlite3.Vdbe pVm, int version, SqliteCommand cmd)
        {
            int    pN;
            IntPtr pazValue;
            IntPtr pazColName;
            bool   first = true;

            int[] declmode = null;

            while (true)
            {
                bool hasdata = cmd.ExecuteStatement(pVm, out pN, out pazValue, out pazColName);

                // For the first row, get the column information
                if (first)
                {
                    first = false;

                    if (version == 3)
                    {
                        // A decltype might be null if the type is unknown to sqlite.
                        decltypes = new string[pN];
                        declmode  = new int[pN];                        // 1 == integer, 2 == datetime
                        for (int i = 0; i < pN; i++)
                        {
                            string decl = Sqlite3.sqlite3_column_decltype(pVm, i);
                            if (decl != null)
                            {
                                decltypes[i] = decl.ToLower(
#if !SQLITE_WINRT
                                    System.Globalization.CultureInfo.InvariantCulture
#endif
                                    );
                                if (decltypes[i] == "int" || decltypes[i] == "integer")
                                {
                                    declmode[i] = 1;
                                }
                                else if (decltypes[i] == "date" || decltypes[i] == "datetime")
                                {
                                    declmode[i] = 2;
                                }
                            }
                        }
                    }

                    columns = new string[pN];
                    for (int i = 0; i < pN; i++)
                    {
                        string colName;
                        //if (version == 2) {
                        //	IntPtr fieldPtr = Marshal.ReadIntPtr (pazColName, i*IntPtr.Size);
                        //	colName = Sqlite.HeapToString (fieldPtr, ((SqliteConnection)cmd.Connection).Encoding);
                        //} else {
                        colName = Sqlite3.sqlite3_column_name(pVm, i);
                        //}
                        columns[i] = colName;
                        column_names_sens [colName]   = i;
                        column_names_insens [colName] = i;
                    }
                }

                if (!hasdata)
                {
                    break;
                }

                object[] data_row = new object [pN];
                for (int i = 0; i < pN; i++)
                {
                    /*
                     * if (version == 2) {
                     *      IntPtr fieldPtr = Marshal.ReadIntPtr (pazValue, i*IntPtr.Size);
                     *      data_row[i] = Sqlite.HeapToString (fieldPtr, ((SqliteConnection)cmd.Connection).Encoding);
                     * } else {
                     */
                    switch (Sqlite3.sqlite3_column_type(pVm, i))
                    {
                    case 1:
                        long val = Sqlite3.sqlite3_column_int64(pVm, i);

                        // If the column was declared as an 'int' or 'integer', let's play
                        // nice and return an int (version 3 only).
                        if (declmode[i] == 1 && val >= int.MinValue && val <= int.MaxValue)
                        {
                            data_row[i] = (int)val;
                        }

#if !SQLITE_WINRT
                        // Or if it was declared a date or datetime, do the reverse of what we
                        // do for DateTime parameters.
                        else if (declmode[i] == 2)
                        {
                            data_row[i] = DateTime.FromFileTime(val);
                        }
#endif
                        else
                        {
                            data_row[i] = val;
                        }

                        break;

                    case 2:
                        data_row[i] = Sqlite3.sqlite3_column_double(pVm, i);
                        break;

                    case 3:
                        data_row[i] = Sqlite3.sqlite3_column_text(pVm, i);

                        // If the column was declared as a 'date' or 'datetime', let's play
                        // nice and return a DateTime (version 3 only).
                        if (declmode[i] == 2)
                        {
                            if (data_row[i] == null)
                            {
                                data_row[i] = null;
                            }
                            else
                            {
                                data_row[i] = DateTime.Parse((string)data_row[i], System.Globalization.CultureInfo.InvariantCulture);
                            }
                        }
                        break;

                    case 4:
                        //int blobbytes = Sqlite3.sqlite3_column_bytes16 (pVm, i);
                        byte[] blob = Sqlite3.sqlite3_column_blob(pVm, i);
                        //byte[] blob = new byte[blobbytes];
                        //Marshal.Copy (blobptr, blob, 0, blobbytes);
                        data_row[i] = blob;
                        break;

                    case 5:
                        data_row[i] = null;
                        break;

                    default:
                        throw new Exception("FATAL: Unknown sqlite3_column_type");
                        //}
                    }
                }

                rows.Add(data_row);
            }
        }
Beispiel #43
0
 void executeUpdates()
 {
     for (int x = 0; x < ctrPaneles; x++)
     {
         if(!String.IsNullOrEmpty(updates[x])){
         string select = "select votos from candidato where nombre='" + updates[x] + "';";
         Sqlite3.Vdbe pstmt = new Sqlite3.Vdbe();
         Sqlite3.sqlite3_prepare_v2(Program.db, select, select.Length, ref pstmt, 0);
         Sqlite3.sqlite3_step(pstmt);
         string des=Sqlite3.sqlite3_column_text(pstmt, 0);
         int voto = desencriptarint( SoapHexBinary.Parse(des).Value);
         voto++;
         string enc = new SoapHexBinary(encriptarint(voto)).ToString();
         string update = "update candidato set votos='" + enc + "' where nombre='" + updates[x] + "';";
         string error = "";
         if (Sqlite3.sqlite3_exec(Program.db, update, null, null, ref error) != Sqlite3.SQLITE_OK)
         {
             MessageBox.Show(error);
         }
         Sqlite3.sqlite3_finalize(pstmt);
             
         }
     }
 }
Beispiel #44
0
        /// <summary>
        /// Converts a USDX 1.01 or CMD 1.01 database to Vocaluxe format
        /// </summary>
        /// <param name="FilePath">Database file path</param>
        /// <returns>True if succeeded</returns>
        private static bool ConvertFrom101(string FilePath)
        {
            SQLiteConnection connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + FilePath;
            SQLiteCommand command;
            SQLiteDataReader reader = null;

            try
            {
                connection.Open();
            }
            catch (Exception)
            {
                return false;
            }

            command = new SQLiteCommand(connection);

            command.CommandText = "PRAGMA table_info(US_Scores);";
            reader = command.ExecuteReader();


            bool dateExists = false;

            //Check for column Date
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (reader.GetName(i) == "name")
                    {
                        if (reader.GetString(i) == "Date")
                            dateExists = true;
                        break;
                    }
                }
            }


            reader.Close();

            //This is a USDX 1.01 DB
            if (!dateExists)
                command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores";
            else // This is a CMD 1.01 DB
                command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
            command.ExecuteNonQuery();

            // convert from CP1252 to UTF8
            List<SData> scores = new List<SData>();
            List<SData> songs = new List<SData>();

            Sqlite3.sqlite3 OldDB;
            int res = Sqlite3.sqlite3_open(FilePath, out OldDB);

            if (res != Sqlite3.SQLITE_OK)
            {
                CLog.LogError("Error opening Database: " + FilePath + " (" + Sqlite3.sqlite3_errmsg(OldDB) + ")");
            }
            else
            {
                Sqlite3.Vdbe Stmt = new Sqlite3.Vdbe();
                res = Sqlite3.sqlite3_prepare_v2(OldDB, "SELECT id, Artist, Title FROM Songs", -1, ref Stmt, 0);

                if (res != Sqlite3.SQLITE_OK)
                {
                    CLog.LogError("Error query Database: " + FilePath + " (" + Sqlite3.sqlite3_errmsg(OldDB) + ")");
                }
                else
                {
                    //Sqlite3.sqlite3_step(Stmt);

                    Encoding UTF8 = Encoding.UTF8;
                    Encoding CP1252 = Encoding.GetEncoding(1252);

                    while (Sqlite3.sqlite3_step(Stmt) == Sqlite3.SQLITE_ROW)
                    {
                        SData data = new SData();

                        data.id = Sqlite3.sqlite3_column_int(Stmt, 0);

                        byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1);
                        if (bytes != null)
                            data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
                        else
                            data.str1 = "Someone";

                        bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 2);
                        if (bytes != null)
                            data.str2 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
                        else
                            data.str2 = "Someone";

                        songs.Add(data);
                    }
                    Sqlite3.sqlite3_finalize(Stmt);
                }

                Stmt = new Sqlite3.Vdbe();

                if (!dateExists)
                    res = Sqlite3.sqlite3_prepare_v2(OldDB, "SELECT id, PlayerName FROM Scores", -1, ref Stmt, 0);
                else
                    res = Sqlite3.sqlite3_prepare_v2(OldDB, "SELECT id, PlayerName, Date FROM Scores", -1, ref Stmt, 0);

                if (res != Sqlite3.SQLITE_OK)
                {
                    CLog.LogError("Error query Database: " + FilePath + " (" + Sqlite3.sqlite3_errmsg(OldDB) + ")");
                }
                else
                {
                    //Sqlite3.sqlite3_step(Stmt);

                    Encoding UTF8 = Encoding.UTF8;
                    Encoding CP1252 = Encoding.GetEncoding(1252);

                    while (Sqlite3.sqlite3_step(Stmt) == Sqlite3.SQLITE_ROW)
                    {
                        SData data = new SData();

                        data.id = Sqlite3.sqlite3_column_int(Stmt, 0);

                        byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1);
                        if (bytes != null)
                            data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
                        else
                            data.str1 = "Someone";

                        if (dateExists)
                            data.ticks = UnixTimeToTicks(Sqlite3.sqlite3_column_int(Stmt, 2));

                        scores.Add(data);
                    }
                    Sqlite3.sqlite3_finalize(Stmt);
                }
            }
            Sqlite3.sqlite3_close(OldDB);

            SQLiteTransaction _Transaction = connection.BeginTransaction();      
             
            // update Title and Artist strings
            foreach (SData data in songs)
            {
                command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
                command.Parameters.Add("@title", System.Data.DbType.String, 0).Value = data.str2;
                command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1;
                command.Parameters.Add("@id", System.Data.DbType.Int32, 0).Value = data.id;
                command.ExecuteNonQuery();
            }           

            // update player names
            foreach (SData data in scores)
            {
                if (!dateExists)
                    command.CommandText = "UPDATE Scores SET [PlayerName] = @player WHERE [id] = @id";
                else
                {
                    command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
                    command.Parameters.Add("@date", System.Data.DbType.Int64, 0).Value = data.ticks;
                }
                command.Parameters.Add("@player", System.Data.DbType.String, 0).Value = data.str1;
                command.Parameters.Add("@id", System.Data.DbType.Int32, 0).Value = data.id;
                command.ExecuteNonQuery();
            }
            _Transaction.Commit();

            //Delete old tables after conversion
            command.CommandText = "DROP TABLE US_Scores;";
            command.ExecuteNonQuery();

            command.CommandText = "DROP TABLE US_Songs;";
            command.ExecuteNonQuery();

            reader.Dispose();
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return true;
        }
Beispiel #45
0
 public CSqliteResult(Sqlite3.Vdbe stmt, boolean bNoCopy)
 {
     m_bNoCopy = bNoCopy;
     m_st      = stmt;
 }
Beispiel #46
0
        /*
        ** Compile the UTF-16 encoded SQL statement zSql into a statement handle.
        */
        static int sqlite3Prepare16(
            sqlite3 db,              /* Database handle. */
            string zSql,             /* UTF-15 encoded SQL statement. */
            int nBytes,              /* Length of zSql in bytes. */
            bool saveSqlFlag,         /* True to save SQL text into the sqlite3_stmt */
            ref sqlite3_stmt ppStmt, /* OUT: A pointer to the prepared statement */
            ref string pzTail        /* OUT: End of parsed string */
            )
        {
            /* This function currently works by first transforming the UTF-16
            ** encoded string to UTF-8, then invoking sqlite3_prepare(). The
            ** tricky bit is figuring out the pointer to return in pzTail.
            */
            string zSql8;
            string zTail8 = "";
            int rc = SQLITE_OK;

            assert( ppStmt );
            *ppStmt = 0;
            if( !sqlite3SafetyCheckOk(db) ){
            return SQLITE_MISUSE_BKPT;
            }
            sqlite3_mutex_enter(db.mutex);
            zSql8 = sqlite3Utf16to8(db, zSql, nBytes, SQLITE_UTF16NATIVE);
            if( zSql8 !=""){
            rc = sqlite3LockAndPrepare(db, zSql8, -1, saveSqlFlag, null, ref ppStmt, ref zTail8);
            }

            if( zTail8 !="" && pzTail !=""){
            /* If sqlite3_prepare returns a tail pointer, we calculate the
            ** equivalent pointer into the UTF-16 string by counting the unicode
            ** characters between zSql8 and zTail8, and then returning a pointer
            ** the same number of characters into the UTF-16 string.
            */
            Debugger.Break (); // TODO --
            //  int chars_parsed = sqlite3Utf8CharLen(zSql8, (int)(zTail8-zSql8));
            //  pzTail = (u8 *)zSql + sqlite3Utf16ByteLen(zSql, chars_parsed);
            }
            sqlite3DbFree(db,ref zSql8);
            rc = sqlite3ApiExit(db, rc);
            sqlite3_mutex_leave(db.mutex);
            return rc;
        }