Re: [sqlite] translate time comparison statement

[EMAIL PROTECTED] wrote: Mark Wyszomierski <[EMAIL PROTECTED]> wrote: SELECT school_name from schools WHERE julianday('now') - julianday(arrival_date) > 7 Dennis Cote <[EMAIL PROTECTED]> wrote: SELECT school_name from schools WHERE date(arrival_date) < da

Re: [sqlite] translate time comparison statement

now', 'localtime')) - julianday(date(arrival_date)) > 7 Dennis Cote

Re: [sqlite] translate time comparison statement

localtime like this. SELECT school_name from schools WHERE date(arrival_date) < date('now', 'localtime', '-7 days'); HTH Dennis Cote

Re: [sqlite] LIMIT keyword does work in an UPDATE statement

lue in the ref column. This is not necessarily the same thing. Dennis Cote

Re: [sqlite] LIMIT keyword does work in an UPDATE statement

do the trick. Basically you use a select to find the id (i.e. the primary key) of the record to update, and then update that record only. update tbl1 set sts='busy' where id in (select id from tbl1 where sts='ready' order by ref desc limit 1); HTH Dennis Cote

Re: [sqlite] How to optimize select queries

are used. HTH Dennis Cote

Re: [sqlite] limiting table size?

pace. Even if future CPUs and multiple parallel writers let you increase your write rate by a factor of 1,000,000 you are still good for at least 292 years. HTH Dennis Cote

Re: [sqlite] 2nd question about 'localtime'

table where date(datefield) = date('now'); HTH Dennis Cote

Re: [sqlite] limiting table size?

or when you try to do an insert (because of the autoincrement constraint on the id). When this happens you will need to run the following update to reset the lowest id to 1 before repeating the failed insert. update fifo set id = id - (select min(id) - 1 from fifo); HTH Dennis Cote

Re: [sqlite] Using time and date values

n readable in its raw form. JS This is a good idea, but you have the standard number wrong. It should be ISO 8601. See http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html for more details. HTH Dennis Cote

Re: [sqlite] Can't commit transaction - SQL statements in progress

thing left on the vector when you get your error is what you are looking for. HTH Dennis Cote

Re: [sqlite] sqlite3_create_function in delphi

a lowercase copy of a string. Dennis Cote

Re: [sqlite] Re: functions that return tables

Igor Tandetnik wrote: From: "Dennis Cote" <[EMAIL PROTECTED]> I don't know of a way to do what you want with a user defined function, but your example can be solved quite simply using SQL. The following query will return a table with the required results. select * from t

Re: [sqlite] functions that return tables

sc limit 3; If you have an index on col then it will also be very fast regardless of the size of the table, if not it will do a single table scan to find the three maximum values. HTH Dennis Cote

Re: [sqlite] Rows to columns

Dennis Cote

Re: [sqlite] Request for comment: Proposed SQLite API changes

nd sqlite3_finalize(). Dennis Cote

Re: [sqlite] Request for comment: Proposed SQLite API changes

d the need for SQLite to copy the SQL statement in many (if not most) cases. If it ever needed to recompile the statement, it would be able to use the string that the caller already had. SQLite would then only make its own copy if it was passed SQLITE_TRANSIENT. Dennis Cote

Re: [sqlite] quotes around text in returned data?

. Richard, Try this: select int_col, quote(text_col), quote(other_text_col) from table; HTH Dennis Cote

Re: [sqlite] Request for comment: Proposed SQLite API changes

lized in your ExecuteNonQuery function. So it would only require additional memory for one copy of the SQL string for the length of time that that function takes to execute. That storage would be released when the prepared statement is finalized. I don't think this is a severe penalty for any user. Dennis Cote

Re: [sqlite] type confusion

SQLite has become a slightly less compatible database engine. Well this post certainly drifted away from where I started but at least that is off my chest. In summary: SQLite is good. Standards are good. I hope SQLite moves towards the SQL standard. Dennis Cote

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

integer division. This extra work will take additional time. Dennis Cote

Re: [sqlite] Request for comment: Proposed SQLite API changes

error was SQLITE_SCHEMA. This would give you the best of both worlds, backwards compatibility and a cleaner API for new code. Dennis Cote

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

plementing it like this. select (15.0/8.0)|0; select ~~(15.0/8.0); both of these expressions return the truncated value, 1. This also avoids the conversion of the values to text that round() does. HTH Dennis Cote

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

your work on it) so it is ultimately your decision how SQLite will work. I just think that you should very carefully consider any changes that will lead to less conformance with the standard for the language that you are trying to implement. Dennis Cote

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

should also be made to ensure that explicitly declared column types are honored by SQLite. I would also like to thank DRH for taking the time to request and evaluate input from the group before making this decision and changing SQLite's behavior. Dennis Cote

Re: [sqlite] Re: Number of rows in a query result

Puneet Kishor wrote: Igor Tandetnik wrote: Alfredo Cole <[EMAIL PROTECTED]> wrote: In order to update a progress bar, I need to know the total number of rows returned by a query, similar to MySQL's mysql_num_rows. Is there a function like that in the C API? I may have overlooked it, but

Re: [sqlite] Time scale on INSERT and UPDATE

index is available that can be used to retrieve the records in the correct order and eliminate the need for a sort. As you can see, the short answer is "it depends...". HTH Dennis Cote

Re: [sqlite] .import null values

David Finlayson wrote: Thanks for the link . In section 6 of this page there is mention of Affinity Modes, how do you activate these? See section 2.1 of that page. As it is now you can get completely irrational behavior with mathematical operators:

Re: [sqlite] .import null values

our mind. However, if you want numeric values in your database you must import into columns declared with some numeric type. HTH Dennis Cote

Re: [sqlite] built-in functrion suggestion: size of blob

the blob into memory, then use a join to get the blob data. create table blob_info (id integer primary key, size integer); create table blob_data (id integer primary key references blob_info(id), data blob); HTH Dennis Cote

Re: [sqlite] .import null values

in the file and does the inserts with the correct data bound to the columns as the correct type (see sqlite3_bind_double() and sqlite3_bind_null()). HTH Dennis Cote

Re: [sqlite] Re: in memory SQLite in C/C++

separate sqlite3* database pointer. Dennis Cote

Re: [sqlite] sqlite_column_text converting result to a c++ string

. There are also links to other wrappers at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers (scroll down to C++). Dennis Cote

Re: [sqlite] Instr, Locate or Splite

in func.c for some examples of using the custom function API routines (the same routines are used to define all the standard functions; sum, round, substr, etc.). HTH Dennis Cote

Re: [sqlite] sqlite_column_text converting result to a c++ string

)); if (p == NULL) p = ""; // or some other sentinel value std::string zName(p); Dennis Cote

Re: [sqlite] sqlite_column_text converting result to a c++ string

ary pointer variable if you prefer. HTH Dennis Cote

Re: [sqlite] Speed Test Done !

s show in file. Richard, Can you just dump a few sample lines from your table using the following? select * from T limit 25; select typeof(A) from T limit 25; That will give us an idea of the format of your data. Thanks Dennis Cote

Re: [sqlite] Speed Test Done !

heck the memory usage for sqlite3 after you have imported the data and again after you create the index? Dennis Cote

Re: [sqlite] Speed Test Done !

think you will be pleasantly surprised. HTH Dennis Cote

Re: [sqlite] Anyone ?

d work. HTH Dennis Cote

Re: [sqlite] ANN: Sqlite3Explorer version 2.0 released

with a database open. --- sqlite3explorer2 --- Access violation at address 004B68E8 in module 'sqlite3Explorer2.exe'. Read of address 002C. --- OK --- Dennis Cote

Re: [sqlite] Rewrite of where.c without DOUBLE Values

your issue immediately. Dennis Cote

Re: [sqlite] Bitwise comparison

ed set Indicator = Indiocator & ~2 -- clear IsOnDisability where Indicator & 8 HTH Dennis Cote

Re: [sqlite] Speed Test not work (pt2)

st be something strange about your CSV file. If you zip it up and send it to me I will give it a try on my end. Dennis Cote

Re: [sqlite] Getting a Unique column from a query

Could you explain what you are trying to do in more detail. Dennis Cote

Re: [sqlite] Need to do a Test.

On 9/30/05, Dennis Cote <[EMAIL PROTECTED]> wrote: > > > But seriously, shouldn't the .import meta command in the sqlite shell do > this already? Sorry for the self reply, but I just checked the source, the .import command does do a BEGIN and COMMIT around the insert statemen

Re: [sqlite] Need to do a Test.

does 25 inserts/second instead of 50,000 inserts/second. -- D. Richard Hipp <[EMAIL PROTECTED]> Well its a little too late for that now, isn't it. :-) But seriously, shouldn't the .import meta command in the sqlite shell do this already? Dennis Cote

Re: [sqlite] Need to do a Test.

Be warned that there are problems importing CSV data with quote delimiters on the fields (the quotes are included in the table as part of the field values). You can try a select to dump your table. select * from T; HTH Dennis Cote

Re: [sqlite] primary, secondary keys

) is the same as some other row that already exists in the table. SQL requires that the primary key of each row be unique. HTH Dennis Cote

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

Ralf Junker wrote: This can be fixed by checking the column affinity for a value when it is stored. If an integer value is being stored in a column with numeric affinity, then store the value as a REAL value rather than as an INTEGER value. This will perform the same conversion that the

Re: [sqlite] the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)

On 9/29/05, Darren Duncan <[EMAIL PROTECTED]> wrote: > > At 11:07 AM -0600 9/29/05, Dennis Cote wrote: > >As you can see, the result of exact (integer) division is also exact > >(integer) with implementation defined precision and scale. The > >result of an exp

Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?

. Dennis Cote

Re: [sqlite] Getting CVS source without CVS

=sqlite directly. It lets you view or download the individual files, or get diffs from previous versions. HTH Dennis Cote

Re: [sqlite] Running SQLite in memory-only mode

use a lot of RAM, and it isn't saved when you quit. HTH Dennis Cote

Re: [sqlite] Lib and H files

or CVS. I use it to build test versions of sqlite from CVS. As a matter of fact, the Windows binaries on the SQLite download page are generated using MinGW running under Linux to cross compile the source files for the Windows environment. Check it out at http://www.mingw.org/. Dennis Cote

Re: [sqlite] Lib and H files

. HTH, and good luck with SQLite. Dennis Cote

Re: [sqlite] Network-based DB performance for Mozilla

Brett Wilson wrote: Hi everybody, I'm working on replacing a lot of Firefox's storage systems to use sqlite. It has been going well so far except for one issue. The database file is stored in the user's Mozilla profile directory. In companies and Universities using Linux, this directory is

[sqlite] test suite failures

Hi All, I just built the latest SQLite from CVS head (3.2.5+) on Windows using MinGW. This is the same build process I normally use with no problems. When I run the test suite I am getting failures on some tests as shown below: 12 errors out of 13455 tests Failures on these tests:

Re: [sqlite] Problem with DETACH on 2.8.16

quested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. C:\Documents and Settings\DennisC> I don't have 2.8.16 handy, so I can't try that version. I will try again with latest as well. Dennis Cote

Re: [sqlite] Where are temporary tables/indices stored?

table, just like the sqlite_master table, called sqlite_temp_master. Try this: create temp table t(1,b); select * from sqlite_temp_master; HTH Dennis Cote

Re: [sqlite] How to insert a binary file into the database of sqlite in C++?

.seekg(0, ios::beg); stringstream sbuf; sbuf << mp3.rdbuf(); char* buf = sbuf.str(); sqlite3_prepare(..., "insert into foo values(?);", -1, , ...); sqlite3_bind_blob(stmt, 1, buf, sz, SQLITE_TRANSIENT); sqlite3_step(stmt); HTH Dennis Cote

Re: [sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?

Dennis Cote wrote: Nick, Each call to the random() function in your statement generates a new random number. To reuse the same random number multiple times you need to save it somewhere. The SQL statement below should do what you want. It save the random number in a temp table which

Re: [sqlite] Can you use random(*) to retrieve a pseudo random row from 3.2.3?

n a temp table which is joined to your table (effectively the same random number is appended to each row in your table). select *, rand.number from MyTable join (select random(*) as number) as rand where start_col >= rand.number and end_col < rand.number; HTH Dennis Cote

Re: [sqlite] Import CSV in sqlite3?

e" You can of course change the file and table names when you build the command string to pass to the system call. HTH Dennis Cote

Re: [sqlite] ATTACH, query and differentiate

in this case). Try this instead; select case :uid % 10 when 1 then (select otherdata from main.sometable where id = :uid / 10) when 2 then (select otherdata from database2.sometable where id = :uid / 10) end as otherdata; Dennis Cote

Re: [sqlite] ATTACH, query and differentiate

can then bind the :uid value (or generate the sql string on the fly) and get the requested data from the correct database. HTH Dennis Cote

Re: [sqlite] Slow Queries, FROM order and indexes

ould be especially useful for SQLite since everything must be hand-tuned. I believe that Richard is currently working on improving the query optimization of sqlite, but your best bet right now is using the explain command to examine how sqlite will execute various queries. Thanks! Aaron HTH Dennis Cote

Re: [sqlite] how to get last autoincrement value?

_rowid(); This will return the rowid of the last inserted row, which is the key for a table with an integer primary key (SQLite's version of autoincrement keys). It can also be used to get the rowid for tables with other kinds of keys or even no user specified key. HTH Dennis Cote

Re: [sqlite] Long retrieval times

erion. I'm not sure if it really is faster in your application, but it may be much faster if there are many rows that equal the criterion and are skipped by using the index. If most rows are selected then there is probably little gain. HTH Dennis Cote

Re: [sqlite] How to get the size of the value

e per database" characteristic of sqlite by doing this. HTH Dennis Cote

Re: [sqlite] Newbie Help Please

.def file into the LIB utility and it will generate an sqlite3.lib which you can link with your project. The following command is used in the Makefile. lib /machine:i386 /def:sqlite3.def HTH Dennis Cote

Re: [sqlite] problem of compréhention of sq lite

ation about the tables and indexes (sp) in the database. You should never use the sqlite3 pointer to extract info about the database. It is internal data, and subject to change at any time. Dennis Cote

Re: [sqlite] malloc(0) in sqlite3_exec

the sqlite3MallocRaw() function also have this check just for consistency and safety? Dennis Cote

Re: [sqlite] Comparator bug? Overflow?

Oops, hit send to soon. I'm using sqlite under Windows, compiled using MinGW (gcc 3.4.2). What is your environment? Dennis Cote

Re: [sqlite] Comparator bug? Overflow?

p" for instructions sqlite> create table t(j integer primary key); sqlite> insert into t(j) values(1); sqlite> insert into t(j) values(2); sqlite> insert into t(j) values(3); sqlite> select * from t where j > 2; 3 sqlite> Dennis Cote

Re: [sqlite] malloc(0) in sqlite3_exec

the common case of users using sqlite3_exec() for statments such as "BEGIN TRANSACTION". Admittedly, this is a very small performance gain. Dennis Cote

Re: [sqlite] Update/Join Howto?

the temp table back into the permanent table insert into c_det select * from t_det; --delete the temp table drop table t_det; commit transaction HTH Dennis Cote

Re: [sqlite] Sporadic "library routine called out of sequence" from Tcl interface?

/sqlite-users@sqlite.org/msg05936.html HTH Dennis Cote

Re: [sqlite] errors compiling with MS VC++ 6.0

. A sample lib command line is given below. lib /machine:i386 /def:sqlite3.def HTH Dennis Cote

Re: [sqlite] Query on multiple tables

, this should equivalent to your query; Select tbl1.ID, tbl1.fld1, tbl1.fld2 /*(15 fields total, all from tbl1)*/ from tbl1 where tbl1.ID=4 Dennis Cote

Re: [sqlite] quoting strings issue

$sth = $dbh->prepare($sql); $rc = $sth->execute($string); HTH Dennis Cote

Re: [sqlite] completion of sqlite3_exec

Eno, Your call to sqlite3_exec() will return only after the the query is completely finished. Can't you free your data structures then? HTH Dennis Cote

Re: [sqlite] Newbie API questions

integer constants 0 and -1 to the required function pointer type. To call these APIs on a machine with 64 bit pointers and 32 bit integers you have the same issues as with the sqlite3 handle. You need to ensure that you pass a 64 bit pointer to these routines, passing integer values may not work. Dennis Cote

Re: [sqlite] prepared statement and database schema changed problem

o sqlite3_errcode()) after the error is reported. When you receive the SQLITE_SCHEMA error, you must finalize and then re-prepare your SQL statement before you can retry executing it. You can't simply re-execute it by calling sqlite3_step() again. HTH Dennis Cote

Re: [sqlite] Using variables within trigger definitions

Side BEGIN UPDATE LowDate SET date = SELECT MIN(Startdate) FROM Basis; INSERT INTO BASIS (Name,Startdate) VALUES ("Trigger", (SELECT date FROM LowDate )); END; HTH Dennis Cote

Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?

all be moved into a trigger that fires immediately after the insert. This can be useful for audit trail tables etc., where the information to be stored in the related table can be determined from existing tables and the new row. HTH Dennis Cote

Re: [sqlite] Column Names (revisited)

suspect some changes may be in the works based on some of the past mailing list chatter, but that's pure speculation. Dennis Cote

Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?

each table, which it does not. The limit clause is also unnecessary since the last_insert_rowid function always returns a single result. HTH Dennis Cote

Re: [sqlite] Desperate newbie: .def problem??

to the Include Directories list on the Directories page of the Project Options dialog. HTH Dennis Cote

Re: [sqlite] NULL representation/empty value in query results?

nding upon the number o SQL statements you need to modify. HTH Dennis Cote

Re: [sqlite] Table alias in sqlite

join), field1 (from the mri table), and field2 (from the hp_con table). HTH Dennis Cote P.S. your red highlighting didn't survive the trip through the mailing list manager.

Re: [sqlite] Is this possible in SQLite?

OM b WHERE id = 1; Rather than selecting the entire row that matches the old id, I select the new id and the data field from the row in the old table, and then insert them into the new table. This works for your simple example, but it may not be general enough depending upon how the values of th

Re: [sqlite] Is this possible in SQLite?

SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote

Re: [sqlite] thoughts on a web-based front end to sqlite3 db?

Richard Heyes wrote: Since noone has mentioned it yet, I'd suggest Brainfuck (http://www.muppetlabs.com/~breadbox/bf/). Very easy to learn (only eight instructions) so you should have something up and running pretty quickly. Notably, it has some of the most elegant code structure I've ever

Re: [sqlite] correct use of sqlite3_get_table

ation for the sqlite3_get_table() function at http://www.sqlite.org/capi3ref.html#sqlite3_get_table. It explains how the column header information is returned along with the actual data. You are looking at the heading for your column, the data is further into the results array. HTH Dennis Cote

Re: [sqlite] ticket 1147

ut the new API functions, the current sqlite3_column_name() API should probably be fixed so that it obeys the column name pragmas, as they are documented now, for backward compatibility. Dennis Cote

Re: [sqlite] more syntax errors ?

0 0 5 State Character(2) 0 0 6 Zip Character(10) 0 0 7 Phone1Character(13) 0 0 8 Phone2Character(13) 0 0 9 Fax Character(13) 0 0 sqlite> HTH Dennis Cote

Re: [sqlite] tricky date time problem

te end group by minute union select 0, 0 where not exists (select * from event_data limit 1) HTH Dennis Cote

Re: [sqlite] User functions

custom functions. HTH Dennis Cote

Re: [sqlite] SQL optimization

from t_gloss as e1 join t_gloss as e2 on e1.entryid = e2.entryid and e1.type = case e2.type when 0 then 1 else 0 end where e2.value = ? order by value HTH Dennis Cote

<    6   7   8   9   10   11   12   >