sqlite

C# SQLite Bulk Insert or Parameterized Insert Efficenecy

I have a DataTable which I want to save to a SQLite Database Table. Here is my dilemma, I don't know which way to go. At most the DataTable would contain 65,000 rows and probably 12 columns. So, would it be faster to save the DataTable to a CSV file and then Bulk Insert it into SQLite (which I have no idea how to do) or would it be ...

Prepared statements and the IN expression

I have a database where users can search for records that have on or more of a list of items. I'm using IN to do the search, but I can't get IN to work with prepared statements. This is what I've tried: SELECT * FROM tbl1 WHERE col IN (?) But the prepared statement treats the list of items I pass it as a single item. How can I make th...

SQLite subquery not working in PHP

I'm working on a simple voting system, using php and sqlite. I prebuild a whole scenario and tested it in the sqlite commandline, where it worked nicely. But one query, which should return all possible answers of a poll and the users that voted for it, doesn't work anymore... Tables: CREATE TABLE polls ( question CHAR(200) ); CREATE ...

SQLite gurus: how can I display very detailed metadata about a file?

I have a situation where a simple query against a table is returning incorrect values in Flex, but not in other GUI front-ends to SQLite. select title from ttl where ttl.ttl = 140 // ttl is also the column name of the PK column is returning the title that belongs to the row whose PK = 1400. ttl.ttl is defined as int datatype. Again...

SQLite INSERT Statement

I have create the following insert method by researching the internet but I know it needs some work since it quite bulky and unsightly. I have tested it and it works fairly well but I know it could work better. If possible could someone show me how to convert this method to use parameters and/or increase its efficency? public static vo...

sqlite3 and cursor.description

When using the sqlite3 module in python, all elements of cursor.description except the column names are set to None, so this tuple cannot be used to find the column types for a query result (unlike other DB-API compliant modules). Is the only way to get the types of the columns to use pragma table_info(table_name).fetchall() to get a des...

C# SQLite error Insufficient parameters supplied to the command

I get the error SQLite error Insufficient parameters supplied to the command when running an insert statement in my program. However, I've got it narrowed down to it will only occur if I try to insert data into two different tables in succession. Meaning I insert data into one and everything in the method is diposed of since I am using...

Insert into SQLite with variables using javascript

Hi, I am developing an extension for firefox and i have created a SQLite DB. When inserting values into the table i get an error message: Error: Permission denied for http://en.wikipedia.org to call method UnnamedClass.toString on <>. The string values to be inserted are stored in a variable. var book = "Harry Potter"; var myInsertQuer...

Can I get the table names from an SQL query with Perl's DBI?

I am writing small snippets in Perl and DBI (SQLite yay!) I would like to log some specific queries to text files having the same filename as that of the table name(s) on which the query is run. Here is the code I use to dump results to a text file : sub dumpResultsToFile { my ( $query ) = @_; # Prepare and execute the query ...

Synchronize Core Data with SQL or MySQL Via SQLite

I am trying to build an iPhone application using core data's built in table management. I want synchronize the data with an MSSQL or MySQL database. Core data is compatible with SQLite so I thought it might work as a bridge. Can anyone think of how one might achieve this functionality? ...

Fast IO Disallowed and SQLite.

I have a windows service which uses the SQLite database backend. I can run the service fine under my account, however when I run it under the Local System account it runs into issues. I used process monitor and found out it encounters a FAST IO DISALLOWED when attempting to use the database.db-journal file. The actual line is below: 10:...

Why does SQLite complain about the syntax of my prepared statement?

I'm having trouble getting a prepared statement in sqlite3 to work. I'm working with Perl and the Perl DBD framework. Below is the code I use: #This is a function I have defined sub query($@){ my $st = $db->prepare(shift); $st->execute(@_); } #And it is used like so query("UPDATE rooms SET name = ?, SET capacity = ? WHERE id = ...

How do I list all the available views of a particular table in SQLite ?

I want to access all the particular views of any particular table in Sqlite . I know I can get the list of all the available tables in the database using sqlite_master SELECT name from sqlite_master WHERE type='table'; And the list of all the available views using SELECT name from sqlite_master WHERE type ='view'; But I want to f...

iPhone: Sharing data between native and webapp

Hi! I would need to share data (not necessarily a large amount) between a native iPhone app, and a safari/webkit javascript app... Do you know how this could be done ? I considered sqlite.. But it seems an application can only read/write inside its own bundle (so, not in the webkit databases directory), and the other way, i guess javasc...

SQL Client on Android

Hi, I'm developing on Android and currently haev various methods which manage my database in a convenient class. What I'd ideally like, though, is a (preferably GUI) sql client that would allow me to view the contents of a different apps' DB. Eg: I install my own app (App1). It creates and uses a DB I install I use SQLClient to exam...

How do I check in SQLite whether a table exists?

How do I, reliably, check in SQLite, whether a particular user table exists? I am not asking for unreliable ways like checking if a "select *" on the table returned an error or not ( is this even a good idea? ). The reason is like this: In my program, I need to create and then populate some tables if they do not exist already. If the...

Is there a way to tell whether a function is getting executed in a unittest?

I'm using a config file to get the info for my database. It always gets the hostname and then figures out what database options to use from this config file. I want to be able to tell if I'm inside a unittest here and use the in memory sqlite database instead. Is there a way to tell at that point whether I'm inside a unittest, or will...

SQLite Reset Primary Key Field

I have a few tables in SQLite and I am trying to figure out how to reset the autoincremented database field. I read that: "DELETE FROM tablename" should delete everything and reset the autoincremement field back to 0 but when I do this it just deletes the data. When a new record is inserted the autoincrement picks up where it left off ...

SQL query logging for SQLite?

I need to log queries from a number of applications that use SQLite. Introducing logging to the applications would in this case not be a feasible solution in practice. So how can I enable query logging in SQLite itself? ...

Error in my SQLite syntax

New to SQLite so I don't know what I'm doing wrong. I'm just getting an error saying: SQLSTATE[HY000]: General error: 1 near "CREATE": syntax error Here's my SQL: CREATE TABLE users ( id INTEGER NOT NULL PRIMARY KEY, date_created DATETIME NOT NULL, date_updated DATETIME NOT NULL, username VARCHAR(32) NOT NULL, password VARC...