sqlite

SQLiteOpenHelper.getWriteableDatabase() null pointer exception on Android

I've had fine luck using SQLite with straight, direct SQL in Android, but this is the first time I'm wrapping a DB in a ContentProvider. I keep getting a null pointer exception when calling getWritableDatabase() or getReadableDatabase(). Is this just a stupid mistake I've made with initializations in my code or is there a bigger issue? ...

What is the best way to write a validation layer for SQLite

I'm using an ADO.net provider of SQLite. I want to steer around some of the "features" of SQLite, like allowing a string in an integer field and allowing a string longer than n in a field of type varchar(n). What is the best way to achieve this kind of validation? Stored procedures? triggers? I'm looking for a generic solution that appli...

Date comparison in SQLite

How do you declare a date datatype in a table? Is it simply the following? CREATE TABLE sampleDB (..., sampledate DATE, ...) Now, if I want to query from that using a string through a POST form: <?php $formdate = $_POST["inputname"]; if($formdate) { echo "Searching using query: ".$formdate."<br>"; $db = new S...

Is it a way to know whether Index was used for a particular sql query

Sometimes one when creating SQL queries one assumes that one of the Index should be used by the engine when getting the data. But not always. If some of the necessities are absent the engine will probably process the rows one by one. Is it a way (for sqlite) to know for sure that the index was used? I mean in cases more complex than SELE...

sqlite3 is chopping/cutting/truncating my text columns

I have values being cut off and would like to display the full values. Sqlite3 -column -header locations.dbs " select n.namelist, f.state, t.state from names n left join locations l on l.id = n.id left join statenames f on f.st = l.st left join statenames t on t.st = l.stto where n.timing > 200601 and count(n.timing)<=15" Which gives ...

SQLite - SELECT over multiple entries of the same data/column

I am a bit new to SQLite, and I am having a slight dilemma about my database design. I'll explain. Suppose you and your friends use a number of different nicknames on the Internet (can be more than one per person). What we have here is an ID of the person and a list of all nicknames that this person uses. This is a single entry in a larg...

phpMyAdmin for SQLite 3

Is there a good PHP client for SQLite 3 databases? I'm looking for something like phpMyAdmin of SQLBuddy that allows me to manage SQLite 3 databases, preferably one that uses the PDO (pdo_sqlite) driver. ...

Is it safe to include extra columns in the SELECT list of a SQLite GROUP BY query?

I have a simple SQLite table called "message": sequence INTEGER PRIMARY KEY type TEXT content TEXT I want to get the content of the last message of each type (as determined by its sequence). To my surprise, the following simple query works: SELECT MAX(sequence), type, content FROM message GROUP BY type Surprise, because I know that...

How much can SQLite store on the iPhone?

I have an idea for a webapp for the iPhone but its unknown to me how much data can be stored in mobile Safari's SQLite db. I tried searching through the Apple docs but found nothing: Safari Client-Side Storage and Offline Applications Programming Guide: Using the JavaScript Database ...

Convenient way to use LIKE case sensivity in sqlite

As I see LIKE operator can optimize query if I switch PRAGMA case_sensitive_like=ON. I measured, it really worked, queries "LIKE someth%" becomes ten times faster on a compartively large binary indexed tables. But the problem is that my library implemented as an add-on to my application, it maintains its own tables with any db it is conn...

Ruby "No Such File To Load - sqlite3" on OS X

I was trying to create a quick little script that would insert data into an SQLite db for me but I can't get past the first few steps. I have done "sudo gem install sqlite3-ruby", my Ruby version is 1.8.7 (I used the Ruby one click installer from rubyforge.org). My script is incredibly simple. It looks like this: #!/usr/bin/ruby -w re...

Android: SQLite and ListViews

Firstly, I have found many examples of how to grab data from a db and place it into a list, however this seems to be all for ListActivites. My list is part of the UI and therefore I can't use a ListActivity because it does not consume the whole screen (or can I?). This is the UI: <SlidingDrawer android:layout_width="wrap_content" an...

Finisar SQLite library for C# Unsuported file format

I've created a database an a table ("Mail") having 2 columns: id INTEGER, content INTEGER. In my aplication I have tested the connection and it works well. using Finisar.SQLite; ... string db = "mydatabase"; SQLiteConnectionsql_con = new SQLiteConnection("Data Source=" + db + ";Version=3;New=False;Compress=True;"); sql_con.Open(); sql...

Gears and Mysql sync

Hi, I am making an offline website that needs to sync every night with a server. Our employees work outside and sometimes underground and need to access the site for its database. But once home they must send us their findings. The first time they connect, I check with google gears if they have the sqlite database and if not I run a sc...

Passing generated id from table 1 (primary key) to table 2 (foreign key)??

Hi I am using (evaluating :)) Subsonic ActiveRecord mode for accessing sqLite. Need transaction to work. Regular stuff... Of ocurse! Following snippet explains the desired logic. I couldn't find the right example in docs. using (TransactionScope ts = new TransactionScope()) { using (SharedDbConnectionScope ...

Equivalent of SqLite Blob type in Subsonic?

In one SqLite table, I have a BLOB column for saving images (or binary data as a matter of fact). The table is Documents. Strangely, in Subsonic's ActiveRecord's Documents class, the type of that column shows as STRING which doesn't make sense. It should be byte array. Right? What am I missing here? How do I map SqLite BLOB column in S...

Variables in SqlLite

I was wondering if there is a known technique for saving and using variables in an SqlLite database. I am looking for something like the $something variables one can find under Oracle ...

Java Exception Error - Sqlite preparedStatement.setBlob

I'm placing and image into a databse, it could be eitehr an MYSQL database (the Server) or an SQLITE database (a Tablet PC for on the road). The Java application synchs with the server on a dialy basis, uploading new data and downloading any new data. Thats working terrirfic, the requirement is for it to be able to handle images as well....

Should I use SQLite for automatically build a list of banned IPs of comment spammers?

I am using a simple yet effective anti-spam system at a comments module which seems to be working flawlessly for more than a year now. Since it is capable of recognizing automated comment spam attempts, I am thinking of extending this security module with an ability of adding the offender IPs to a blacklist automatically. Do you think ...

Versioning SQLite databases with Subversion. Good idea or bad idea?

So I have a web system where every user gets a separate SQLite database which acts as a sort of their workspace. All of the database files are in a directory with database names that match up to the user ids. Many of these databases are created and rarely, if ever, edited. Others have many edits on a daily basis. To manage backups on t...