How to display blob value using x’abc’ binary string literal syntax?
select quote(uid) from blobs returns: X’0123456789ABCDEF0123456789ABCDEF’ and if you really need it in lowercase, you can use: select lower(quote(uid)) from blobs
sqlite
SELECT INTO statement in sqlite
Instead of SELECT * INTO equipments_backup FROM equipments try CREATE TABLE equipments_backup AS SELECT * FROM equipments
Truncate a SQLite table if it exists?
It is the two step process: Delete all data from that table using: Delete from TableName Then: DELETE FROM SQLITE_SEQUENCE WHERE name=”TableName”;
Will SQLite performance degrade if the database size is greater than 2 gigabytes?
There is no 2 GB limit. SQLite database files have a maximum size of about 140 TB. On a phone, the size of the storage (a few GB) will limit your database file size, while the memory size will limit how much data you can retrieve from a query. Furthermore, Android cursors have a limit … Read more
svn cleanup: sqlite: database disk image is malformed
First, open command/terminal at repository root (folder which has .svn as child folder): cd /path/to/repository Download sqlite3 and put executable sqlite3 at root of folder. You do an integrity check on the sqlite database that keeps track of the repository (/path/to/repository/.svn/wc.db): sqlite3 .svn/wc.db “pragma integrity_check” That should report some errors. Then you might be able … Read more
How to have an automatic timestamp in SQLite?
Just declare a default value for a field: CREATE TABLE MyTable( ID INTEGER PRIMARY KEY, Name TEXT, Other STUFF, Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP ); However, if your INSERT command explicitly sets this field to NULL, it will be set to NULL.
How to set unique constraint over multiple columns when any one can be null?
In sqlite, all null are differences. I think the best way to solve this issue is to set column c not null with a special default value. Then use the default value (for example 0, ”) to represent null. edit 1 you can easily extend this solution to any columns create table test ( a … Read more
SQLite accent-insensitive search
Set up a collation using sqlite3_create_collation and then use it like this: SELECT * FROM users WHERE name LIKE “Andre%” COLLATE NOACCENTS
sqlite get ROWID
SELECT rowid, * FROM tbl1 WHERE letter=”B”
Case sensitive and insensitive like in SQLite
You can use the UPPER keyword on your case insensitive field then upper-case your like statement. e.g. SELECT * FROM mytable WHERE caseSensitiveField like ‘test%’ AND UPPER(caseInsensitiveField) like ‘G2%’