sqlite

What is more usual to persist data when developing Android applications?

Hi there, I'm developing an application that would need to persist data locally in Android devices. I'm aware that Android provides two packages for database persistence, the normal "java.sql" and the "android.database.sqlite"? Which one is used more for persistence? I suppose that "android.database.sqlite" is the implementation of "jav...

Blackberry thread hanging on SQLite statement prepare

Hi Gurus. I'm developing an application that implements an EditField in which you can type a word and while typing the application will filter the contents that are being shown on screen. For this, I'm starting a new thread when the user types. This thread extracts the information from a SQLite table and then shows it on the UI. I use...

Can SQLite handle 90 million records?

Or should I use a different hammer to fix this problem. I've got a very simple use-case for storing data, effectively a sparse matrix, which I've attempted to store in a SQLite database. I've created a table: create TABLE data ( id1 INTEGER KEY, timet INTEGER KEY, value REAL ) into which I insert a lot of data, (800 elements every 10...

SQLite: How to count references to each ROWID in a table, then insert the count

I need some help writing a SQL statement. I have two tables in a SQLite database: CREATE TABLE users ( user_id INTEGER PRIMARY KEY, item_id INTEGER ); CREATE TABLE items ( item_id INTEGER PRIMARY KEY, ref_count INTEGER ); I'm seeking a SQLite statement that will be like the following pseudocode: for each row in items ite...

Efficient use of SQL Transactions

My application currently needs to upload a large amount of data to a database server (SQL Server) and locally on a SQLite database (local cache). I have always used Transactions when inserting data to a database for speed purposes. But now that I am working with something like 20k rows or more per insert batch, I am worried that Transa...

Use custom SQLite database for Android

Say I want to create a database using the Android SDK's sqlite tool. What would the file extension have to be? I would think .db or .sqlite3 or .sqlite? How would I import it in my source code (Is there a method I should call?) and where in my source code? I was thinking in the onCreate method of a class that implements SqliteOpenHelper....

Is the onUpgrade method ever called?

Is the onUpgrade method of SQLiteOpenHelper ever called? If so, when is it called and by what? Or if it is not called by developers, then why is it there? What should be done in the method I have seen examples where it drops all the tables, but then a comment says that dropping all the tables is NOT what you should do. ...

Why are SQLite transactions bound to harddisk rotation?

There's a following statement in SQLite FAQ: A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second. As I know there's a cache on the harddisk and there might be also an extra cache in the disk driver that abstract the operation that i...

Android - Storing Audio Files into the SQLite Database

Hi Folks, I am developing an application for android phone, In this I need to use at least 400 audio file which can be played for some respective texts, Now my question is which is the optimized way to do this.. One solution is putting all the audio files in the resource folder and referring from there, this will n...

Is it possible to store System.Decimal.MinValue and System.Decimal.MinValue in SQLite?

Considering that the maximum size of a data type supported by SQLite is 8 bytes, can I store a Decimal.MinValue, Decimal.MaxValue in a NUMERIC column. I am using the ADO.NET provider for SQLite by phxsoftware. Does the driver internally store the value as a string and do the conversion both ways? I do get a System.OverflowException whi...

Accessing .sqlite file in Android 2.1

I have a .sqlite file with one table in it. i want to access this data. Please tell me, where to copy this .sqlite file (in eclipse project) and how to access the data from this .sqlite file. please suggest some code or liniks.. [i am using Android 2.1, dont know the database version..] Thank you.. ...

Storing XML/HTML files inside a SQLite database - Possible?

Hi, Is it possible to directly store a XML/HTML file inside a SQLite database? I'm writing a program in python which is supposed to parse XML/HTML files and store the values inside the database. However, the fields inside the XML/HTML files may vary and I thought it would be easier to simply store the entire XML/HTML file inside the da...

Find package dependencies from database table

I have two tables in a database, one lists package and one lists dependencies: packages id | name --------- 0 | xorg 1 | gnome-session 2 | gnome-panel 3 | gnome-mixer-applet 4 | gnome-media depends package | depends ----------------- 1 | 0 2 | 1 3 | 2 4 | 2 Obviously, if I want to find out what a packag...

SQL query to query nearby points of interest based on lat/long - SQLite

Given a database that contains three fields: Latitude Longitude Proximity Where Lat and Long are GPS coordinates, and Proximity is (some unit - feet? Seconds? Minutes?) And given the user's current GPS lat/long... I want to write a SQL query that will retrieve all rows where the user is within "Proximity" of those rows. And the tric...

Autocomplete list from SQLite with criteria

Can someone point me to right direction, how to create an adapter for AutoCompleteTextView, which would be getting data from SQLite DB, using data, which user entered into the text field? I want to use the user-entered data to filter suggestions for autocompletion. I imagine that adapter should always take user-entered data as soon as ch...

SQLite: update a record if exists, otherwise insert it?

I tried INSERT OR REPLACE INTO, but it doesn't preserve the row id when it replaces the record to update it. Another option is do it in two steps: INSERT OR IGNORE INTO then UPDATE, but I would prefer a one step solution. So I am wondering if SQLite has something like the MERGE keyword or other simple solutions? ...

NHibernate and SQLite woes

I'm having problems with getting nhibernate to play nice with sqlite: My NHibernate configuration: <NHibernate> <hibernate xmlns="urn:nhibernate-configuration-2.2"> <session-factory> <property name="connection.driver_class">NHibernate.Driver.SQLite20Driver</property> <property name="connection.provider">NHibernate.Con...

sql - find duplicates in two columns

Hello, I have a table with four columns, where col1 & col2 contain similar values (INT). I now want to know, if there are duplicates in col1 and/or col2. i.e., col1 | col2 -----+----- 111 | 222 333 | 444 111 | 333 555 | 111 → Duplicates: 111 (3x) and 333 (2x). I use sqlite, but I think this is a basic sql question. Thanks, mspo...

Problem searching my SQLite database which contains cyrillic data

Hi, I have a problem searching my SQLite database, which contains data written with cyrillic characters. If the key word is also cyrillic, then everything is ok, but if not, then I can`t get the result in my Android application. Does anyone have an idea how can I implement searching the cyrilic data no matter the charset of the keywor...

ActiveRecord::Base.connection.execute duplicates sql output

I am a newbie to rails and i needed to execute some sql query and output it to the view. In my controller i used something like "@prob = ActiveRecord::Base.connection.execute("...") and then simply displayed the @prob in the view. However the output in the view keeps coming twice as 0 result column name result . The sql query was @cp...