sqlite3

update statement in sqlite

sqlite3_stmt *updateStmt = nil; if (updateStmt == nil) { const char *sql = " update PPM set amount = ? "; if (sqlite3_prepare_v2(appDelegate.PPMdatabase, sql, -1,&updateStmt, NULL)!= SQLITE_OK) { NSAssert (0,@"Error while creating update statement. '%s'",sqlite3_errmsg(appDelegate.PPMdatabase)...

how to get list of all column names from given table in sqlite 3 ? (table might be empty)

now I use: PRAGMA table_info(table_name) construct, but it don't allow me to narrow search result to only column names, as it turns out much of unwanted data. That is array of arrays Array ( [0] => Array ( [cid] => 0 [name] => id [type] => INTEGER [notnull] => 0 ...

sqlite3_open - Can't open database ?

Hello, I have the following statement in my code: if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) but it doesn't seem to be opening my database. Any ideas ? ...

sqlite3_prepare_v2 problem

Hello, I'm having a bit of trouble with statement: if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) in the code below, the code is jumping out of the IF at this point. Anyone any thoughts ? // Open the database from the users filessytem if(sqlite3_open([databasePath UTF8String], &database) =...

sqlite3_prepare_v2 problem

Hello, I'm getting the following error: * Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: '* +[NSString stringWithUTF8String:]: NULL cString' on the line of code below: NSString *aName = [NSString stringWithUTF8String(char*)sqlite3_column_text(compiledStatement, 1)]; I'm not sure whats going on here,...

Formatting SQL query output in Python/SQLite 3

I use a simple SQL query in Python to grab records from a SQLite 3 database: cursor.execute ("SELECT due, task FROM tasks WHERE due <> '' ORDER BY due ASC") rows = cursor.fetchall() for row in rows: print '\n%s %s' % (row[0], row[1]) The due field in the database is set to DATE type, so the query re...

SQL query to count records.

I have a table EntryLog(Name String, CheckIn Boolean) I want to count the number of checkins against each name. How do I write a query to get the result as a single resultset? ...

Update statement in sqlite

Hey what i want to do is update a table if the field exsits or insert the new value as given by the user. For this what i do is select everything present in the table and check if the name entered by the user exists den update it or else insert it. However the problem is when there is no data in the table the query fails and no data i...

query to count words SQL lite 3

Is there a way to count words in a text string? I'm using SQL lite 3 and I'm trying to write a query that takes a bunch of long strings of text, and counts the number of words in each one. I also want to ignore html tags (or anything between carats) such as paragraph tags, break tags, etc. So when I run a query selecting text from the ...

Why aren't my sqlite3 foreign keys working?

I run the following code from a python interpreter, and expect the insert statement to fail and throw some kind of exception. But it's not happening: Python 2.6.5 (r265:79096, Mar 19 2010, 21:48:26) [MSC v.1500 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> conn = s...

select query in sqlite

hey can some tell me the query in sqlite3 which can be used to select the entry only once if it exists more than once in my database ...

Sqlite doesn't seem to like my delete statement

I've got the following iphone code, which seems to be failing: sqlite3_stmt *dbps; NSString *sql = @"delete from days where day=?1;insert into days(disabled,recipe_id,day) values(?2,?3,?1)"; int rc = sqlite3_prepare_v2(db, sql.UTF8String, -1, &dbps, NULL); ... The 'rc' return code is 1, meaning SQLITE_ERROR (SQL error or missing datab...

sqlite3_bind_text not binding the values

Below is my code NSFileManager *fileManager = [NSFileManager defaultManager]; NSArray *dbPath=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES); NSString *documentsDir = [dbPath objectAtIndex:0]; NSString *writableDBPath= [documentsDir stringByAppendingPathComponent:@"mydb.sqlite"]; success = [fileManager ...

SQLite and custom order by

I have a table with categories: ID Category "1","Baking" "3","Family" "4","Entertaining" "5","Children" "6","Desserts" Now I would like to order the result of the select statement to ID Category "4","Entertaining" "3","Family" "1","Baking" "5","Children" "6","Desserts" for example. In MySQL, you'd do ...

Insert into a table if a condition is met with sqlite3

if I have two tables: files(id, owner) and share(file_id, user), where owner and user would be primary IDs from a theoretical user table, how can I insert an entry into share only if the user doing the sharing owns that file? This is a stripped down example, so I'll just use a literal for the one doing the share operation -- normally th...

How to change SQLite DB path for each request in Django?

I would like to dynamically change path to sqlite database depending on currently logged user using Django framework. Basically each user should have his own sqlite database file. Anyone with experience? Thanks. ...

pysqlite - how to save images.

Hello, I need to save an image file into sqlite database in python. I could not find a solution. How can I do it? Thanks in advance. ...

sharing a :memory: database between different threads in python using sqlite3 package

I would like to create a :memory: database in python and access it from different threads. Essentially something like: class T(threading.Thread): def run(self): self.conn = sqlite3.connect(':memory:') # do stuff with the database for i in xrange(N): T().start() and have all the connections referring to the sam...

Select query in sqlite

hey what i want to do is select everything from the table and insert data into it if its not already present in the table or update the data if present in the table. The problem here is if my table is empty there is nothing to select and hence the query is not executed and therefore it neither inserts nor updates my table. NOTE: My inser...

How to order SQLite-results containing umlauts and other special characters?

I would like to order my results from SQLite according to the rules used for German. This means a character like "�" is treated like "ae", or "�" like "ue". At this point the solution looks like this: SELECT * FROM data ORDER BY REPLACE(REPLACE(REPLACE(UPPER(einrichtung),'�','AE'),'�','OE'),'�','UE') LIMIT 0,20 The solution should no...