EzDevInfo.com

Does sqlite support any kind of IF(condition) statement in a select

Does sqlite support the sql function "if" in the select statement?

for example

select if( length( a ) > 4 , a , ' ') as b
from foo

which would return a if the length was over 4 chars long. or else it would return ' ' as b

If it does support a condition in the select what is the syntax is should be using?

I have checked http://sqlite.org/lang_corefunc.html but I can't see it.

Opening database file from within SQLite command-line shell

I'm using the SQLite Command Line Shell. As documented, I can open a database by supplying it as an argument to the executable:

sqlite3 data.db

This may seem totally inconsequential - and it very nearly is! - but I cannot figure out how to open a database file from within the tool after having invoked it without supplying the file as a command-line argument (if I, say, double-click sqlite3.exe in Windows). What is the command within the SQLite shell tool to specify a database file?

What are the best practices for SQLite on Android?

What would be considered the best practices when executing queries on an SQLite db within an Android app?

Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground ? Or should I use the UI Thread ? I suppose that db queries can be "heavy" and should not use the UI thread as it can lock up the app - resulting in an ANR.

If I have several AsyncTasks, should they share a connection or should they open a connection each?

Are there any best practices for these scenarios?

Core Data vs SQLite 3 [closed]

I am already quite familiar with relational databases and have used SQLite (and other databases) in the past. However, Core Data has a certain allure, so I am considering spending some time to learn it for use in my next application.

Is there much benefit to using Core Data over SQLite, or vice versa? What are the pros/cons of each?

I find it hard to justify the cost of learning Core Data when Apple doesn't use it for many of its flagship applications like Mail.app or iPhoto.app - instead opting for SQLite databases. SQLite is also used extensively on the iPhone.

Can those familiar with using both comment on their experience? Perhaps, as with most things, the question is deeper than just using one over the other?

Is there a Sqlite equivalent to MySQL's DESCRIBE [table]?

I'm just getting started learning Sqlite. It would be nice to be able to see the details for a table, like MySQL's DESCRIBE [table]. PRAGMA table_info [table] isn't good enough, as it only has basic information (for example, it doesn't show if a column is a field of some sort or not). Does Sqlite have a way to do this?

How to set Sqlite3 to be case insensitive when string comparing?

I want to select records from sqlite3 database by string matching. But if I use '=' in the where clause, I found that sqlite3 is case sensitive. Can anyone tell me how to use string comparing case-insensitive? Thank you very much!

sqlite3-ruby install error on Ubuntu

I have the following error during sqlite3-ruby install:

Building native extensions.  This could take a while...
ERROR:  Error installing sqlite3-ruby:
    ERROR: Failed to build gem native extension.

/usr/bin/ruby1.8 extconf.rb
checking for sqlite3.h... no
sqlite3.h is missing. Try 'port install sqlite3 +universal' or 'yum install sqlite3-devel'
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers.  Check the mkmf.log file for more
details.  You may need configuration options.

Provided configuration options:
    --with-opt-dir
    --without-opt-dir
    --with-opt-include
    --without-opt-include=${opt-dir}/include
    --with-opt-lib
    --without-opt-lib=${opt-dir}/lib
    --with-make-prog
    --without-make-prog
    --srcdir=.
    --curdir
    --ruby=/usr/bin/ruby1.8
    --with-sqlite3-dir
    --without-sqlite3-dir
    --with-sqlite3-include
    --without-sqlite3-include=${sqlite3-dir}/include
    --with-sqlite3-lib
    --without-sqlite3-lib=${sqlite3-dir}/lib


Gem files will remain installed in /usr/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.3.1 for inspection.
Results logged to /usr/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.3.1/ext/sqlite3/gem_make.out

sqlite3.h is located in /usr/include/

sudo gem install sqlite3-ruby --without-sqlite3-include=/usr/include

doesn't work

ERROR:  While executing gem ... (OptionParser::InvalidOption)
    invalid option: --without-sqlite3-include=/usr/include

Ubuntu 10.04

What is the difference between libsqlite3.dylib and libsqlite3.0.dylib?

I'm getting started with SQLite databases in an app I'm working on. I've not run into issues yet but one of the early steps from this tutorial is linking the SQLite3 framework. The tutorial calls for libsqlite3.0.dylib but I noticed another one libsqlite3.dylib. Is the latter just a symlink to the latest v3 library like the convention for package managers on UNIX or is there a difference?

Adam

Google Analytics SDK 3.0 _sqlite3 linker errors in iOS

I'm integrating Google Analytics SDK 3.0 in my project. But I am getting linker errors when try to build my project.

As mentioned in the documentation, I have linked following libraries in my project,

• libGoogleAnalyticsServices.a
• AdSupport.framework
• CoreData.framework
• SystemConfiguration.framework
• libz.dylib

Even then, I get following errors on building the project,

d: warning: directory not found for option '-L"/Users/....NameProject/Libraries/Google Analytics"'
"_sqlite3_bind_blob", referenced from:
  -[TAGDataLayerPersistentStoreImpl writeEntriesToDatabase:expireTime:] in libGoogleAnalyticsServices.a(TAGDataLayerPersistentStoreImpl.o)
"_sqlite3_bind_int", referenced from:
  -[TAGDataLayerPersistentStoreImpl deleteEntries:] in libGoogleAnalyticsServices.a(TAGDataLayerPersistentStoreImpl.o)
"_sqlite3_bind_int64", referenced from:
  -[TAGDataLayerPersistentStoreImpl writeEntriesToDatabase:expireTime:] in libGoogleAnalyticsServices.a(TAGDataLayerPersistentStoreImpl.o)
  -[TAGDataLayerPersistentStoreImpl peekEntryIds:] in libGoogleAnalyticsServices.a(TAGDataLayerPersistentStoreImpl.o)

....

What is causing these errors? Am I missing anything?

Appreciate your help.

SOLUTION:

I solved it by linking my project with libsqlite3.0 library. The Google analytics documentation missed out mentioning to link this library. Hope this helps.

How to retrieve inserted id after inserting row in SQLite using Python?

How to retrieve inserted id after inserting row in SQLite using Python? I have table like this:

id INT AUTOINCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)

I insert a new row with example data username="test" and password="test". How do I retrieve the generated id in a transaction safe way? This is for a website solution, where two people may be inserting data at the same time. I know I can get the last read row, but I don't think that is transaction safe. Can somebody give me some advice?

Copying data from one sqlite db to another

I have 2 sqlite databases with common data but with different purposes and I wanted to avoid reinserting data, so I was wondering if it was possible to copy a whole table from one DB to another. I didn't find anything about this on Google regarding sqlite databases so my hopes are not high.

Multiple Table SQLite DB Adapter(s) in Android?

I was reading the Android SQLite NotePad tutorial that referenced creating a DB Adapter class to create and access a DB table. When dealing with a multi-table SQLite Database, is it best practice to create a different Adapter Class for each table or create a single DB Adapter class for the entire Android Application?

My application uses multiple tables and I was hoping not to have to have a single massive adapter class. the problem, however, is that I have a nested subclass of SQLiteOpenHelper per the NotePad Example within each adapter. When the first table is accessed, everything works fine. When I then try to access the second tble(from a different activity) my app crashes.

At first, I thought the crash was being caused by a versioning issue, but both adapters now have the same database version and it's still crashing.

Here's an example of one of the DB Adapters for the table. The other adapters all follow the same format with varying implementations.

public class InfoDBAdapter {
    public static final String ROW_ID = "_id";
    public static final String NAME = "name";

    private static final String TAG = "InfoDbAdapter";
    private static final String DATABASE_NAME = "myappdb";
    private static final String DATABASE_TABLE = "usersinfo";
    private static final int DATABASE_VERSION = 1;


    private static final String DATABASE_CREATE = "create table usersinfo (_id integer primary key autoincrement, "
            + NAME
            + " TEXT," + ");";

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " //$NON-NLS-1$//$NON-NLS-2$
                    + newVersion + ", which will destroy all old data"); //$NON-NLS-1$
            //db.execSQL("DROP TABLE IF EXISTS usersinfo"); //$NON-NLS-1$
            onCreate(db);
        }
    }


    public InfoDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }


    public InfoDBAdapter open() throws SQLException {
        this.mDbHelper = new DatabaseHelper(this.mCtx);
        this.mDb = this.mDbHelper.getWritableDatabase();
        return this;
    }

    /**
     * close return type: void
     */
    public void close() {
        this.mDbHelper.close();
    }


    public long createUser(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(NAME, name);
        return this.mDb.insert(DATABASE_TABLE, null, initialValues);
    }


    public boolean deleteUser(long rowId) {

        return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$
    }


    public Cursor fetchAllUsers() {

        return this.mDb.query(DATABASE_TABLE, new String[] { ROW_ID,
                NAME}, null, null, null, null, null);
    }


    public Cursor fetchUser(long rowId) throws SQLException {

        Cursor mCursor =

        this.mDb.query(true, DATABASE_TABLE, new String[] { ROW_ID, NAME}, ROW_ID + "=" + rowId, null, //$NON-NLS-1$
                null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }


    public boolean updateUser(long rowId, String name) {
        ContentValues args = new ContentValues();
        args.put(NAME, name);
        return this.mDb
                .update(DATABASE_TABLE, args, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$
    }
}

When the first adapter, in this case usersinfo, is accessed, everything works as expected. Let's say I have another adapter for friend info that follows the same structure as above, when it is accessed by a different activity, it would seem to me that the nested subclass of SQLiteOpenHelper would attempt to create the database again. Obviously something is wrong because in that scenario, my app crashes.

So is the standard practice within Android to create a single mammoth db adapter instead of individual adapters per table?

SQLite with encryption/password protection

I'm just learning to use SQLite and I was curious if such is possible:

1. Encryption of the database file?

2. Password protect opening of the database?

PS. I know that there is this "SQLite Encryption Extension (SEE).", but according to the documentation, "The SEE is licensed software...." and "The cost of a perpetual source code license for SEE is US $2000."

sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings

Using SQLite3 in Python, I am trying to store a compressed version of a snippet of UTF-8 HTML code.

Code looks like this:

...
c = connection.cursor()
c.execute('create table blah (cid integer primary key,html blob)')
...
c.execute('insert or ignore into blah values (?, ?)',(cid, zlib.compress(html)))

At which point at get the error:

sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.

If I use 'text' rather than 'blob' and don't compress the HTML snippet, it works all fine (db is to large though). When I use 'blob' and compress via Python zlib library, I get the above error message. I looked around but couldn't find a simple answer for this one.

npm install for some packages (sqlite3, socket.io) fail with error MSB8020 on Windows 7

When trying to install some node.js packages (sqlite3 and socket.io in particular) using npm install socket.io on my Windows 7 machine with Visual Studio 2012 (and not 2010) I had some failures that looked like this:

C:\Program Files (x86)\MSBuild\Microsoft.Cpp\v4.0\V110\Microsoft.Cpp.Platform.targets(35,5): error MSB8020: The builds tools for Visual Studio 2010 (Platform Toolset = 'v100') cannot be found. To build using the v100 build tools, either click the Project menu or right-click the solution, and then select "Update VC++ Projects...". Install Visual Studio 2010 to build using the Visual Studio 2010 build tools.