sqlite

SQLite journal file not going away

Hi all I have an sqlite database I am writing and reading from. Most of the time I am writing, but occasionally I read something. The routine for writing (skeletonized, error checking removed etc..) goes something like this: st1 and st2 are already prepared along with the initialization of the database. -(BOOL) saveHvalue:(int) fid t...

does sqlite supports any kind of IF(condition) statement in the select

does anyone know if sqlite supports the sql function "if" in the select.. 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 doe support a condition in the select what is the syntax is should be using? i have checked ht...

How to create a statically linked extension for sqlite?

sqlite3_auto_extension looks like a good way to register a statically linked extension. But I don't understand the callback declaration: void (*xEntryPoint)(void); Shouldn't the callback be like sqlite3_extension_init? int sqlite3_extension_init( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi ) ...

Foreign Key Triggers in SQLite

SQLite comes with a utility, genfkey, that will generate triggers to enforce foreign key constraints. Here is the source. There is a README as well, just change previous url to f=sqlite/tool/genfkey.README (stackoverflow only letting me post one url) Two pairs of triggers are generated per FK: BEFORE INSERT and BEFORE UPDATE on refere...

Android quotes within an sql query string

I want to perform a query like the following: uvalue = EditText( some user value ); p_query = "select * from mytable where name_field = '" + uvalue + "'" ; mDb.rawQuery( p_query, null ); if the user enters a single quote in their input it crashes. If you change it to: p_query = "select * from mytable where name_field = \"" + uvalu...

Alter SQLite DB structure when upgrading app

I would like to use the EntityFramework model to connect to an embedded SQLite database in my C# application. Everything works fine but as this is the first version of the software I expect future changes to have a cause changes to the structure of the database. I am concerned since I do not know when a user would upgrade from ver.1 to...

How to get binary dump of SQLite memory db

Hello! If i have code like this $dbSL = Zend_Db::factory('pdo_sqlite', array('dbname'=>':memory:')); $dbSL->query('CREATE TABLE ...'); $dbSL->query('CREATE TABLE ...'); ... After that i whant take binary dump of this SQLite db Thx in advice! ...

Versioned data store (sqlite?).

http://www.sqlite.org/lang%5Fsavepoint.html I'm looking for a datastore with the following properties: I need to 'mark' the state frequently (sub second interval). I need to be able to revert the datastore to a previous mark (with no appreciable delay). I only need to keep the last few hundred 'marks'. (i.e. I never need to revert to...

SQLite: Need Date >= (x) number of days from today

Trying to select a date (x) days from today's date, where the date would be start of day (e.g. 12:00am that day). For example, a query with date 5 days earlier.. @"select pkey, dateofmod from data WHERE dateofmod >= date('now', '? days')" , [NSNumber numberWithInt:-5]; doesn't seem to work. (using FMDB). ...

SQLite - running on Device

FMDB runs great in the simulator, but I get the following errors when I try to run it on the device. "_sqlite3_step", referenced from: -[FMResultSet next] in FMResultSet.o -[FMDatabase executeUpdate:arguments:] in FMDatabase.o "_sqlite3_column_name", referenced from: -[FMResultSet setupColumnNames] in FMResultSet.o ...

Why does $dbh->do('VACUUM') fail with Perl's DBD::SQLite?

I want to do VACUUM at a certain time on a SQLite database under Perl, but it always says DBD::SQLite::db do failed: cannot VACUUM from within a transaction So how do I do this? my %attr = ( RaiseError => 0, PrintError => 1, AutoCommit => 0 ); my $dbh = DBI->connect('dbi:SQLite:dbname='.$file'','',\%attr) or die $DBI::errstr;...

In SQLite3, will this select statement benefit from two indexes?

I am using a DBD::SQLite in memory database. I have defined the following indexes for a table: CREATE INDEX x ON ss (a, b); CREATE INDEX y ON ss (c, d, e, o); Will the following select statement use these two indexes? SELECT f, g FROM ss WHERE a = ? AND b = ? AND c = ? AND d = ? AND e = ? And, I should only make indexes on those co...

Can I read from a SQLite db while writing to it on the iPhone?

Hey Guys, Is it possible to read from a SQLite db while it's being written to? I'm aware that access is blocked for writes when it's being written to, but is that the same for reads? ...

How can I UPDATE rows returned by a SELECT in a loop?

I have a loop on the rows returned by an SQL SELECT statement, and, after some processing on a row's data, I sometimes want to UPDATE the row's value. The processing in the loop's body is non-trivial, and I can't write it in SQL. When I try to execute the UPDATE for the selected row I get an error (under Perl's DBD::SQLite::st execute ...

Suggestion for database design - intensive insert and delete large amount of records

Hi, We have a database with a very simple schema: CREATE TABLE IF NOT EXISTS tblIndex( frame_type INT, pts VARCHAR(5), ts_start INT primary key, ts_end INT) And the application scenario is : Every second user will insert 2 ~ 50 records ,and the ts_start fields of those records is always increasing. After ...

creating text files from sqlite data

I have started to use (or, try to use) sqlite for a simple catalog. What I want to do is be able to take out the information for each catalogued item from the sqlite, and export it into a text file. e.g. Title1, Genre1, Author1 Title2, Genre2, Author2 Title3, Genre3, Author3 I don't want these to be in columns, just a single line. A...

Can I store HTML as XML in a Sqlite3 database?

Is it possible to do this? The HTML files in question all conform to: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt; EDIT: How would you store a number of HTML pages with sequential IDs associated with them in a database? (Oh, I'm new to databases). EDIT: N...

Parameter substitution for a SQLite "IN" clause

I am trying to use parameter substitution with SQLite within Python for an IN clause. Here is a complete running example that demonstrates: import sqlite3 c = sqlite3.connect(":memory:") c.execute('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)') for name in 'Ubuntu Fedora Puppy DSL SuSE'.split(): c.execute('...

SQLite sort by price doesn't work

Hi everyone. I have a column called price and all the values are TEXT so like this: "$26.71". I want to sort this column in descending order but i can't because the column is not defined as INTEGER and it has a "$" character in front of all the values. What should i do in order to make this work?? Thanks. ...

How to ensure sqlite isn't caching specific select queries?

I'm in the situation that I'm using sqlite with ActiveRecord and Rails (also, this is JRuby and so I'm actually using the jdbcsqlite adapter, in case that matters). Now, I'm trying to insert a row into the table attention_seekers, but only if there is no other existing similar row. Accordingly, unless AttentionSeeker.find(:first, :con...