sqlite3

how to search for whole words efficiently in a sqlite db

Hi, I have a table that has a title column. I want to search for whole words like foo. so match " hi foo bye" o "foo", but not "foobar" or "hellofoo". Is there a way without changing the table structure to do this? I currently use 3 like queries, but it is too slow, I have " select * from articles where title like '% foo' or title...

SQLite3 in C#.NET

Hello once again. I'm trying to use SQLite3 in C#.NET. I've googled around and found some different API's. I also checked SQLite's website for wrappers. I like them, but want to write my own wrapper without using an added dependency. All wrappers I find that don't require an added dependency aren't free. I'm wondering how one would rea...

Best way to search sqlite database

In my application ,am work with a large database.Nearly 75000 records present in a table(totally 6 tables are there).i want to get a data from three different table at a time.i completed that.but the search process was slow.how can i optimise the searching process? ...

What is the recommended way to write this sql statement?

Hi , What's better way to format following sql statement considering both readability and performance. Thanks. sql = (char *)" SELECT * ,rowid FROM tblEvent_basic " " WHERE " " service_id = ? AND " " (" " (start_time >= ? AND start_time <= ?) OR " ...

PDO Insert Help

Hello all, I'm having a bit of trouble inserting into a sqlite3 database with pdo. You'll have to excuse my ignorance with PDO, it seems so foreign coming from Python's database interface. So here's my problem. I have a simple insert: $dbh = new PDO('sqlite:vets.db'); $count = $dbh->exec("INSERT INTO vets(name,email,clinic,streetna...

Favorite way to synchronize sqlite3 db on iPhone over HTTP

I currently have an sqlite3 database in my iPhone application. The database has grown to 10MB+ on its own with all of the data I've thrown at it so far. Some of the data I'd rather not have part of the App Bundle as well. If I provide just the schema, what's your favorite way of syncing all of the tables over HTTP? ...

iPhone Sqlite Performance Problem

Hey guys, here is the low down. I have one table, consisting of a primary key(col1), text(col2), and text(col3). Basically a map. This table contains about 200k rows. It basically takes me about 1.x seconds to retrieve a single row (this is all I want). I'm basically using select * from table where col2 = 'some value'. I've tried creat...

appending string in existing string in sqlite3 manager fro firefox?

hello i have i want to do something like this. i have 4 rows with unique id 1,2,3,4 all four rows contains some string like option1,option2,option3,option4 now i want to add "a ) " to the option1, "b ) " to the option2 and so on so is there a way i can do this with a query.currently i am adding these to a lots of rows manually ...

How to change the collation of sqlite3 database to sort case insensitively?

I have a query for sqlite3 database which provides the sorted data. The data are sorted on the basis of a column which is a varchar column "Name". Now when I do the query select * from tableNames Order by Name; It provides the data like this. Pen Stapler pencil Means it is considering the case sensitive stuff. The way...

Cannot insert data into an sqlite3 database using Python.

I can successfully use Python to create a database and run the execute() method to create 2 new tables and specify the column names. However, I cannot insert data into the database. This is the code that I am trying to use to insert the data into the database: #! /usr/bin/env python import sqlite3 companies = ('GOOG', 'AAPL', 'MSFT') ...

Error in Django running on Apache/mod_wsgi

Recently i asked a question regarding an error in apache/mod_wsgi recognizing the python script directory. The community kindly answered the question resulting in a successful installation. Now I have a different error, the server daemon (well, technically is a windows service, I say tomato you say...) doesn't find any of the models, her...

Crossplatform Sqlite3 Wrapper for C++

Hi, I am looking for a crossplatform Sqlite wrapper for Sqlite3. Any suggestions? Thanks ...

Read CSV file into sqlite3 on the iPhone

Is there a way to read a CSV file into sqlite3 on the iPhone? Is there sqlite3 functionality similar to the SQL commands LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE TABLENAME FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; Or is there an approach that converts the CSV file into a format that sqlite3 can read aut...

Writing to SQLite3 on iPhone simulation.

I have an app that is running in the simulator. I read and write from a sqlite3 data source. However, if i restart the app, then all datg that i had previously wrote to the db is lost. The data is always in its original state. Now back when i was developing this app i thought i read somewhere that data can not be persisted via iphone si...

When to use SQLITE_TRANSIENT vs SQLITE_STATIC?

I would like to create/update text columns in sqlite3. When i retrieve rows after the create/update, the text is '?'. Integer values are properly persisted however. My text statements look like this: sqlite3_bind_text(update_statment, 5, [[dt stringFromDate:self.updated] UTF8String], -1, SQLITE_TRANSIENT); I've tried SQLITE_TRAN...

Defined behavior of cast from unsigned char * to char * in Objective-C

I understand the difference between unsigned char * and char * types. I also understand how to use reinterpret_cast to cast an unsigned char * to a char * in C++. I'm using sqlite3 in Objective-C and am trying to get an NSString from a call to sqlite3_column_text(...); To do this, I'm basically doing: char *cParam = (char *)sqlite...

Sqlite : Sql to finding the most complete prefix

I have a sqlite table containing records of variable length number prefixes. I want to be able to find the most complete prefix against another variable length number in the most efficient way: eg. The table contains a column called prefix with the following numbers: 1. 1234 2. 12345 3. 123456 What would be an efficient sqlite query ...

Importing CSV file in SQLITE3 results in a blinking cursor with no action!

Hello, I ran the following command in SQLITE3 command line tool. sqlite> .import out.txt Test And the following is the result: sqlite> .import out.txt Test ...> "...>" keeps showing up if I hit enter. It looks like it's expecting another parameter, except I can't find anything on Google. Thanks always ...

SQL adding composite primary key through alter table option

I have already created a table to the database. Table is something like following (I am using sqlite on mac for iPhone dev) create table ( attendanceDate varchar, studentNo integer, absentDetail varchar ); Now I have already committed this statements. What I need is the following: Add a composite primary key (attandanc...

Which full-text search package should I use for SQLite3?

SQLite3 appears to come with three different full-text search engines, called FTS1, FTS2, and FTS3. The documentation available on the website mentions that FTS1 is stable, FTS2 is in development, and that you should use FTS2. Examples I find online use FTS3, which is in CVS, and not documented versus FTS2. None of the full-text searc...