sqlite

What causes a journal file to be created in SQLite?

I started noticing a weird behavior with my SQLite queries for my iPhone application. Whenever I execute an "INSERT" statement, a journal file is created beside my db file (the exact filename is "userdata.db-journal"). If I understand the docs correctly, this journal file is used by SQLite to be able to rollback in case the operation f...

why am i getting errors while installing pysqlite2.5.3

Am trying build pysqlite 2.5.3 package on SLSE 9, and am getting all sorts of compilation errors i.e. ... src/module.c:290: error: initializer element is not constant src/module.c:290: error: (near initialization for `_int_constants[27].constant_value') src/module.c:290: error: initializer element is not constant src/module.c:290: erro...

Connect to SQLite using ODBC without register database

Is it possible to connect to SQLite in C++ and using ODBC API without register the database in ODBC? I have code that uses ODBC talking to databases and don't want to rewrite for using SQLite and don’t want to register new ODBC connections. ...

Best Cocoa/Objective-C Wrapper Library for SQLite on iPhone

I'm developing for the iPhone and am looking for a good Cocoa/Objective-C library for working with SQLite. I don't want to use the standard procedural SQLite C API. I see options at sqlite.org under the Objective-C section, but am not sure which is the best in terms of library API design, stability, and functionality. I'd like to use ...

Reading and writing images to an SQLite DB for iPhone use

Hi All, I'm still new to iPhone Dev so be gentle. I've set up a SQLite DB that currently reads and writes NSStrings perfectly. The only problem I have now is that I also want to store an image in the database and recall it later. I've read up a bit on using NSData and encoding the image but I'm not entirely sure what the syntax is for ...

ActiveRecord::StatementInvalid: Could not find table 'tablename'

cis.statbib.org: script/console Loading development environment (Rails 2.2.2) Article.founc>> Article.count() ActiveRecord::StatementInvalid: Could not find table 'article' from /home/hadley/web/cis.statbib.org/vendor/rails/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb:29:in `table_structure' from /home/h...

Sharing an sqlite database between iPhone apps

Is this possible? Can I copy a database to the user's home directory and then access it with more than a single program? I'm guessing the answer is 'no', as it would make copy and paste pretty trivial, but I thought I'd ask. ...

SQL error: misuse of aggregate

SQLite version 3.4.0 What's wrong with aggregate functions? Additionally, I suspect that ORDER BY won't work as well. How to rewrite this? sqlite> SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2 ...> FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2 ...> WHERE p1.id ...

Get month from DATETIME in sqlite

I am trying to get extract the month from a DATETIME field in SQLite. month(dateField) does not work as well as strftime('%m', dateStart). Any ideas? ...

Use sqlite to manage Makefile build flags

I build for more than two dozen targets from a source tree with usually three active branches with both production and debug builds. To date I've used a personal Makefile that defines the target which includes a common Makefile that defines the compile flags which then includes the Makefile from a specific source tree. This works but I...

SQL - calculate percentage increase in values of a numeric column

Hi I want to calculate some trends based on the values I get in my SQL database. Particularly I am interested in getting the % increase of the values in one column (the value should be negative in case the values have decreased) Can someone please advice how I should do this? I cannot use stored procedures. Thanks. My table has the...

How to automate a process with the sqlite3.exe command line tool?

I'm trying to bulk load a lot of data ( 5.5 million rows ) into an SQLite database file. Loading via INSERTs seems to be far too slow, so I'm trying to use the sqlite3 command line tool and the .import command. It works perfectly if I enter the commands by hand, but I can't for the life of me work out how to automate it from a script ( ...

SQLite - a smart way to remove and add new objects

Hi, I have a table in my database and I want for each row in my table to have an unique id and to have the rows named sequently. For example: I have 10 rows, each has an id - starting from 0, ending at 9. When I remove a row from a table, lets say - row number 5, there occurs a "hole". And afterwards I add more data, but the "hole" is s...

SQLite database - cannot see updates

Hi, I have a sqlite database and I am adding to it new words. The problem is that I can see them added to a table only after restarting application. The "SELECT" statement doesn't "see" newly added elements before restarting application. Why may this happen? I am creating some kind of a dictionary. Here is how I add new items: const ...

SQLite - getting number of rows in a database

Hi, I want to get a number of rows in my table using "max(id)". When it returns NULL - if there are no rows in the table - I want to return 0. And when there are rows I want to return "max(id) + 1". My rows are being numbered from 0 and autoincreased. Here is my statement: SELECT CASE WHEN MAX(id) != NULL THEN (MAX(id) + 1) ELSE 0 EN...

Best way to reference image through out app?

My application is database driven. Each row contains the main content column I display in a UIWebView. Most of the rows (the content column) have a reference to image1 and some to image2. I convert these to base64 and add the image string into the row. However, if either image changes, it means I have to go back through all the rows ...

getting an insert ID from Webkit's HTML5 database storage

Webkit (on iPhone and Safari, at least) supports HTML5's database storage via SQLite. I'm trying to figure out how best to get the insert ID (there's an autoincrement field 'id' in the database) for the following transaction. db.transaction(function(tx) { tx.executeSql("INSERT INTO teams (name) VALUES (?)", [$('#team_name').val()]); ...

SQLite - sorting a table

Hi, I have a database in SQLlite and I'd like to sort my table in an alphabetical order. How can I do it? Is there a way to sort entries using only SQLite or do I have first to read a table into an array, sort it and afterwards to write it into a database? Here is my query: "SELECT entry FROM table WHERE id=?" I need to get data from t...

Using insert into database.table1 select * from table2 where id=some_value;

I want to copy a row from a table in a database to an identical table in another database. For testing purposes I created this: CREATE TABLE stuff (absid integer primary key, otherfield string ); and table 'stuff' is as above in two database, testdb1 and testdb2. Then I put two rows into 'stuff' in testdb1. From a command line, I can ...

System.Data.Sqlite FormatException using a parameter with LIKE

I'm using Sqlite as my database of choice in a C# forms app, with http://sqlite.phxsoftware.com/ System.Data.SQLite provider. I'm trying to implement a search function, but it's not playing nice... or I'm missing something. The simplified sql I'm using looks like this: SELECT * FROM Table WHERE column LIKE @boundParameter ESCAPE '!' ...