sqlite

best practices for using sqlite for a database queue

I am using an sqlite database for a producer-consumer queue. One or more producers INSERT one row at a time with a new autoincremented primary key. There is one consumer (implemented in java, uses the sqlite-jdbc library) and I want it to read a batch of rows and delete them. It seems like I need transactions to do this but trying to u...

Avoid App.config file in project when using with SQLite/SubSonic (or any other database)

I have a project which I am developing with SQLite and SubSonic 3. I want to know that how can I avoid the App.config file altogether. How do I tell my program to use SQLite Data provider. I mean, how can I tell it this: <DbProviderFactories> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework...

flat-file database php application

I'm creating and app that will rely on a database, and I have all intention on using a flat file db, is there any serious reasons to stay away from this? I'm using mimesis (http://mimesis.110mb.com) it's simpler than using mySQL, which I have to admit I have little experience with. I'm wondering about the security of the db. but the fil...

Sorting results when autocomplete matches multiple columns in SQL

I've run into an issue with an autocomplete field I'm working on. The field I'm working with is composed of the form "<NAME> (<CODE>)". When a user starts typing in text, I want to display any results that match either NAME or CODE. For example, if this list contains items and their codes, like "Personal Computer (PC)", then I'd want ...

Problem pushing sqlite3 db to heroku: lib/taps/schema.rb:30:in `sqlite_config': undefined method `[]' for nil:NilClass (NoMethodError)

I have a Sinatra DataMapper app hitting a sqlite3 database that I am attempting to deploy to Heroku. First pass, I included my database file in the git repo. This works, as in the app runs, but production data does not belong in the repository. It doesn't really work though because the database is read-only. I then removed the db fil...

which sqlite statement can produce a result to indicate duplicate entries? Android

For my application, I am trying to add entries without having a duplicate entry, and if there are a duplicate notify the user and have him try again. Using SQLite, which I know very very little about, I have tried these two ways and want to know which one is better, more efficient or a better way to develop it? First way: db.execSQL("I...

SQLite dialect error with Hibernate

I have been trying to use the hibernate dialect for SQLite from http://code.google.com/p/hibernate-sqlite/ in a project, but have hit a problem with something like the following... SQLQuery query = session .createSQLQuery("select id from collection where collection = (:collection_name);"); query.setParameter("collection_name", col...

Flex / Air sqlite async issue

Hi, I am writing an app that parses a csv file to an array and then insert the array into a sqlite database. I am having issues with Async connection to the sqlite database, I get a 3106 error...... I think the problem is that it executes the next statement before the previous is finished but I can't find a way to deal with this. Any he...

Is Core Data useful for readonly data too?

Hi, I'm creating an iPhone App and am wondering whether Core Data is better for readonly data than a SQLite database. It feels like the SQLite DB is the better choice, is that right? Can I even pre-fill the Core Data storage? Basically, I need like 3 tables with a bunch (up to 3000) of entities each. I then want to list the data in Tabl...

Debugging SQLite statements

I have this SQL statement running on my database and I'm sure it's right, but it's not making any change to the database. How can I debug it? The query: UPDATE task SET name=?, description=?, startDate=?, dueDate=?, complete=?, status=?, percentComplete=?, taskType=? WHERE rowId=:rowId I conditionally bind the first 8 parameters dep...

Python pysqlite2 dbapi2 problem

I'm having an issue with the line: from pysqlite2 import dbapi2 as sqlite The error i'm getting is: ImportError: /usr/lib/python2.4/site-packages/pysqlite2/_sqlite.so: undefined symbol: sqlite3_enable_shared_cache What can I do to solve this problem? Thanks! ...

Provide a database packaged with the .APK file or host it separately on a website?

Here is some background about my app: I am developing an Android app that will display a random quote or verse to the user. For this I am using an SQLite database. The size of the DB would be approximately 5K to 10K records, possibly increasing to upto 1M in later versions as new quotes and verses are added. Thus the user would need to ...

How to select rows where a column value is empty/null using Zend_Db?

I have an SQLite database, eventually will be a MySQL database and I'm using Zend Framework. I'm trying to fetch all the rows in a table where the 'date_accepted' column is empty/null/doesn't have a value. This is what I have so far: public function fetchAllPending() { $select = $this->getDbTable()->select(); $select->where('date_acce...

SQL - SQLite count consecutive numbers

I have a table with the following data: id | numbers | date ---------------------------------- 1 | -1-4-6- | 2009-10-26 15:30:20 2 | -1-4-7- | 2009-10-26 16:45:10 3 | -4-5-8- | 2009-10-27 11:21:34 4 | -2-6-7- | 2009-10-27 13:12:56 5 | -1-3-4- | 2009-10-28 14:22:14 6 | -1-2-4- | 2009-10-29 20:28:16 . . ....... . .....................

SQLite and Portuguese-br characters

I'm developing an app that requires the storage of Portuguese characters. I was wondering if I need to do any configuration to prepare my SQLite db to store those considered special characters. When I query a db table that contains those characters I get a '?' (without quotes) in their place. best regards, mp ...

NS_ERROR_FILE_IS_LOCKED when several requests using SQLite on mozilla plateform

I'm trying to use Storage mechanism in mozilla platform (in thundebird 3.0). The following code is used after each test to erase the table present in the database: function tearDown() { let database = new Database(); let req1 = "SELECT name FROM sqlite_master WHERE type='table'"; let statement = database.connection.createStateme...

Designing the storage for a very large game world.

I'm starting up game programming again. 10 years ago I was making games in qbasic and I havn't done any game programming since, so I am quite rusty. I have been programming all the time though, I am web developer/DBA/admin now. I have several questions, but I'm going to limit it to one per post. The game I am working on is going to be l...

SQLite expression to count rows grouped by week

I have a table with the only relevant column being completionDate, a number of seconds after the epoch that an item was marked complete. I would like to do a select statement to count the number of items complete, grouped by the week in which they were marked complete. So far, I've got something like this: SELECT (completionDate-min)/...

Mysql query. What is the difference between Join and a SubQuery?

I always though join gets the results of something and then joins only using those results. SELECT * FROM tbl AS t1 JOIN tbl2 AS t2 ON t1.id = t2.foreignId JOIN tbl3 AS t3 ON t2.id = t3.foreignId WHERE t1.date > SOMEDATE From my understanding it will run the where statement and get only the results that fall within the date range. Th...

iPhone sqlite3 locked, can only read, not write.

So I have been working on this project for a short while now. I have no problems with reading data from the DB, and formatting it into UITableViews and what not. But now I am wanting also to write to the DB as well. The problem is I keep getting a "Database is Locked" error from sqlite. After messing around with the original version I ha...