[sqlite] Value returned by sqlite3_column_bytes for strings

Hi all, Consider the following scenario, 1. A table contains a column of type text. 2. The value of this column for the first row is say linux. If we execute the SQL statement: select name from some_tbl where id = ? using sqlite3_step() API, then what is the value returned by

[sqlite] How to do 5,000,000 selects as fast as possible

Dear all, We have to do 5,000,000 selects from a database with 4 record (using C API). We do it as follow: 1. create table refGene (geneName vchar, geneID vchar, chromo vchar, strand char(1), txStart number, txEnd number, cdsStart number, cdsEnd number, exonNum number, exonStart

Re: [sqlite] How to do 5,000,000 selects as fast as possible

Briefly... sqlite_prepare_v2(db, select geneName,exonStart,exonEnd from refGene where chromo=? and txStart =? and txEnd=? ... etc etc) start loop of 500 records Use bindings to assign the parameters. step through it Reset and clear bindings. end loop sqlite3_finalize();

Re: [sqlite] Value returned by sqlite3_column_bytes for strings

2009/7/3 chandan chandan.b...@globaledgesoft.com: Hi all,    Consider the following scenario,    1. A table contains a column of type text.    2. The value of this column for the first row is say linux. If we execute the SQL statement: select name from some_tbl where id = ? using

Re: [sqlite] How to import an empty value(NULL) into database from a file?

On Fri, 03 Jul 2009 11:12:26 +0800, Kermit Mei kermit@gmail.com wrote: Hello, how can I import an empty value into the database from a file. Using the command line tool to import a csv file, you can't import NULLs. A line like val1,,val3 would translate in val1, empty string, val3 in your

Re: [sqlite] Nested Inner Join Help

On Fri, 03 Jul 2009 00:53:12 +0100, Ed Hawke edward.ha...@hawkeyeinnovations.co.uk wrote: Thank you again Igor. By run-time defined fields I meant column names that SQL would not recognise until the query was executed, and therefore are only defined when the statement is run. I am aware that

Re: [sqlite] Value returned by sqlite3_column_bytes for strings

Thanks for the reply! Simon Davies wrote: 2009/7/3 chandan chandan.b...@globaledgesoft.com: Hi all, Consider the following scenario, 1. A table contains a column of type text. 2. The value of this column for the first row is say linux. If we execute the SQL statement: select

[sqlite] referential integrity and INSERT OR REPLACE

question: How do I maintain referential integrity on a INSERT OR REPLACE given it does not call the delete trigger on the offending rows? thanks, james ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] How to do 5,000,000 selects as fast as possible

On Fri, Jul 03, 2009 at 03:02:57PM +0800, knightfeng wrote: We have to do 5,000,000 selects from a database with 4 record (using C API). Are you sure you really want/need an SQL database for this? ___ sqlite-users mailing list

Re: [sqlite] referential integrity and INSERT OR REPLACE

On Thu, 02 Jul 2009 19:28:17 -0700, James Gregurich bayoubenga...@mac.com wrote: question: How do I maintain referential integrity on a INSERT OR REPLACE given it does not call the delete trigger on the offending rows? Please correct me if I'm wrong, but considering the two cases INSERT OR

Re: [sqlite] Nested Inner Join Help

All I meant was that in a database you have defined tables with defined column names. These are defined before any SQL statements are executed and therefore I would classify them as pre-defined. Contrast this with the labels applied to tables to create a separate reference to them within an

Re: [sqlite] Nested Inner Join Help

Ed Hawke wrote: All I meant was that in a database you have defined tables with defined column names. These are defined before any SQL statements are executed and therefore I would classify them as pre-defined. Contrast this with the labels applied to tables to create a separate reference to

Re: [sqlite] Nested Inner Join Help

Fine. Do the a2-style (for want of a better way of defining them) names exist outside the SQL statement which defines them? i.e. if I execute the example statement that you gave me, then later execute a statement which references c2 will that work? Will other statements in the same

Re: [sqlite] Nested Inner Join Help

Ed Hawke wrote: Fine. Do the a2-style (for want of a better way of defining them) names exist outside the SQL statement which defines them? Why does it matter? i.e. if I execute the example statement that you gave me, then later execute a statement which references c2 will that work? No.

Re: [sqlite] Nested Inner Join Help

Hello EH Do the a2-style (for want of a better way of defining them) names EH exist outside the SQL statement which defines them? Tables and columns have fixed names, which you define when you write your database schema, e.g. CREATE TABLE myTable (id INT, value TEXT); But you can give these

Re: [sqlite] Nested Inner Join Help

On 3/07/2009 7:08 AM, Ed Hawke wrote: Out of interest, would I be able to use binding on the run-time defined fields? If I wanted to use: select * from A join B b1 on (A.Column3 = b1.ID) join C c1 on (b1.Column1 = c1.ID) join D d1 on (b1.Column2 = d1.ID) join B b2

[sqlite] How can a COMMIT attempt result in SQLITE_BUSY ?

Hello List, I'm wondering how it can happen that after a successfull BEGIN TRANSACTION; statement and some inserts or update statements, that the final COMMIT failes with a busy error code ? Shouldn't that be impossible because there can be only one running transaction ? I'm using shared cache

Re: [sqlite] referential integrity and INSERT OR REPLACE

I read on another posting in the archives that it does not. However, I haven't tried it myself. -James Simon Slavin Fri, 03 Jul 2009 09:44:22 -0700 On 3 Jul 2009, at 3:28am, James Gregurich wrote: How do I maintain referential integrity on a INSERT OR REPLACE given it does not call

Re: [sqlite] referential integrity and INSERT OR REPLACE

On Fri, 03 Jul 2009 11:29:14 -0700, James Gregurich bayouben...@mac.com wrote: based on my reading of the docs for INSERT OR REPLACE, it will delete rows for ANY constraint violation, not just one involving the primary key. Is that reading wrong? You are right, for UNIQUE constraint

[sqlite] SQL Query Question

Can someone help me with this? Suppose you have a Table/Recordset that has these columns: Date (string) Color (string) Offset (long) I want to extract from this Table/Recordset 40 contiguous records from 15 locations within the dataset, each referenced by the Date. For example, say I have

Re: [sqlite] referential integrity and INSERT OR REPLACE

nuts. that makes INSERT OR REPLACE worthless if you have tables dependent on one another. Is there any way to manually get a list of records for which there would be a conflict if a given record was inserted? On Fri, 03 Jul 2009 11:29:14 -0700, James Gregurich bayouben...@mac.com wrote:

Re: [sqlite] SQL Query Question

On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: Suppose my 15 Dates are: 2009-03-03 2008-11-05 2008-07-10 ... ... 2007-07-23 Assuming this is a SORTED dataset in ascending order by Date, I would need to extract 40 records that start with the record at 2009-03-03, then 40

Re: [sqlite] SQL Query Question

On 4/07/2009 9:01 AM, Simon Slavin wrote: On 3 Jul 2009, at 10:03pm, Rick Ratchford wrote: Suppose my 15 Dates are: 2009-03-03 2008-11-05 2008-07-10 ... ... 2007-07-23 Assuming this is a SORTED dataset in ascending order by Date, I would need to extract 40 records that start with

Re: [sqlite] SQL Query Question

The recordset/table that holds all the current data could be 1000, 3000, 10,000, ??? records. Total records fetched, however, would be just 15 x 40 = 600. You have 15 dates that mark the start of each 40 record segment. So plainly stated, with my 15 dates referred to as Date1 to Date15: 1.

[sqlite] Limit Rows Retrieved

Perhaps to solve the larger problem I have in a different post, I have a question on retrieving records. How do you request a fixed number of records starting from a location in the data based searched for, all in one SQL statement? SELECT Date = '2009-01-01' ...plus the next x number of

Re: [sqlite] SQL Query Question

Rick Ratchford wrote: For example, say I have 15 Dates already extracted by a previous query. I need to now get the 40 records that start at each of those 15 Dates. Assuming this is a SORTED dataset in ascending order by Date, I would need to extract 40 records that start with the record at

Re: [sqlite] Limit Rows Retrieved

Rick Ratchford wrote: Perhaps to solve the larger problem I have in a different post, I have a question on retrieving records. How do you request a fixed number of records starting from a location in the data based searched for, all in one SQL statement? SELECT Date = '2009-01-01' ...plus

Re: [sqlite] How can a COMMIT attempt result in SQLITE_BUSY ?

Marcus Grimm wrote: I'm wondering how it can happen that after a successfull BEGIN TRANSACTION; statement and some inserts or update statements, that the final COMMIT failes with a busy error code ? Shouldn't that be impossible because there can be only one running transaction ? There may

Re: [sqlite] SQL Query Question

Thanks Igor! Excellent advice and example. It would appear that where my brain freezed is in the use of =. For the life of me, I could not think of what would instruct the DB to go beyond the fetch date. Really appreciate it! Cheers! Rick -Original Message- From: