Does anyone know why a SQL SELECT query returns no rows when SELECTing from an empty table, but when trying to SELECT the AVG from a column in an empty table it returns < null >? The difference in behavior just seems odd to me. I’m using a sqlite database if that makes any difference. Here are the two queries: Normal select: SELECT ...
I am running a SQLite database within a Perl CGI script which is being accessed by DBD::SQLite. This is being run as a straight CGI on Apache. The DBI connection works fine and selects are able to be run. However, when I attempt to do an insert I get a die with the following error: DBD::SQLite::st execute failed: unable to open databa...
I'll like to find Nth percentile. for example: table: htwt; columns: name, gender, height, weight result: | gender | 90% height | 90% weight | | male | 190 | 90 | | female | 180 | 80 | ...
Cant add foreign key constraint in sqlite ........... ...
Hi , I want to know what is the performance difference between sqlite3_exec() and sqlite3_get_table(). is there a performance difference...? ...
What would be the syntax (if it's possible), for example, to create a table called Car_Model that has a foreign key to a table Car_Make, and give Car_Make a column which is the number of Car_Models that exist of that Car_Make. (If this seems trivial or homework-like it's because I am just playing with some python at home trying to recre...
I want to know optimization techniques for databases that has nearly 80,000 records, list of possibilities for optimizing i am using for my mobile project in android platform i use sqlite,i takes lot of time to retreive the data Thanks ...
I'm creating Android application that contains a large amount of data. It takes lot of time to access the data. What are optimization technique that I can use ? Are there any optimized queries? ...
I need to perform bulk insetion into a sqlite database in visual studio. Does anybody know how to do this? ...
Suppose I have a search screen that is intended for looking up items. There are various optional search options on the screen that will cause the SQL query statement to vary. Here are some example searches: Description search Description search + item supplier id Description search + item supplier id + item hierarchy level 1 id Descri...
I'm struggling to find a good tool to help me migrate a MySQL database to SQLite. I've seen a couple of different Perl scripts (including one discovered here), but they do not appear to work correctly for me. ...
I have to insert 8000+ records into a SQLite database using Django's ORM. This operation needs to be run as a cronjob about once per minute. At the moment I'm using a for loop to iterate through all the items and then insert them one by one. Example: for item in items: entry = Entry(a1=item.a1, a2=item.a2) entry.save() What is...
I am looking for recommendations for a db to use for a small commercial web store (about 10000 products, about 200 entries a day) written in RoR. Is sqlite3 sufficient for this or should I go for MySQL? How complicated is migration between these two at a later stage if required? ...
The query SELECT * FROM Table WHERE Path LIKE 'geo-Africa-Egypt-%' can be optimized as: SELECT * FROM Table WHERE Path >= 'geo-Africa-Egypt-' AND Path < 'geo-Africa-Egypt-zzz' But how can be this done: select * from foodDb where Food LIKE '%apples%"; how this can be optimized? ...
I'm using the SQLite.net provider and VS Entity Designer to design and code against a sqlite database. At present, a model sqlite database is checked in to the source tree. However, this is binary which is inconvenient for use with a revision control system. I'd like to check in a serialized version using sqlite's .dump, but am not sur...
I have a pair of Django models with a foreign key, one of which is searchable with django-fts, say: class Foo(models.Model): ... class Bar(fts.SearchableModel): foo = models.ForeignKey(Foo) When I have an instance of Foo, in view I can insert print foo.bar_set.all() and I see an array of results. However if I try to use it in...
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...
I'm using a database as a kind of cache - the purpose is to store currently known "facts" (the details aren't that interesting). Crucially, however, I'd like to avoid inserting duplicate facts. To do so, the current data access code uses insert or ignore in many places. I'd like to use the entity framework to benefit from it's managea...
Hi everyone, I am looking for a fast (as in huge performance, not quick fix) solution for persisting and retrieving tens of millions of small (around 1k) binary objects. Each object should have a unique ID for retrieval (preferably, a GUID or SHA). Additional requirements is that it should be usable from .NET and it shouldn't require ad...
I would like to get feedback from all you seasoned developers as to what methodology would be the more "correct" or "efficient" way of implementing my solution. I have a 4.5 MB flat file that is some 16,000 rows with 13 columns. I know I can import this into SQLite and create my data model but would it be more iPhone efficient to use th...