I'm deleting a tree of data stored in sqlite and was looking for the
most efficient way to do it.
I thought the best solution was to delete the row and then delete all
the orphaned rows the referenced it in a loop
delete from category where id = 5;
To delete the orphaned rows I repeat one of the
On 2008 Jan, 18, at 19:34, [EMAIL PROTECTED] wrote:
But instead of all that trouble, why not just say:
id IN (1,2,3,4,5,...,N)
Well, I guess the reason I did not say that is because: I had not yet
had my SQL lesson for the day.
All is wonderful now. Thank you for this, and the
Jerry Krinock <[EMAIL PROTECTED]> wrote:
> My query:
>
> DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N)
>
> using the C API. When N exceeds 999, I get an error stating that the
> maximum depth of 1000 has been exceeded, and this is documented in
>
My query:
DELETE FROM `table1` WHERE (`id`=1 OR `id`=2 OR `id`=3 OR ... OR `id`=N)
using the C API. When N exceeds 999, I get an error stating that the
maximum depth of 1000 has been exceeded, and this is documented in http://www.sqlite.org/limits.html
, item 5.
Of course, I could fix
OK I figured out SQLITE_THREADSAFE=0 for the second question...
And it seems the answer for the first question is yes, but if you know
a simpler way please share it with us, thanks!
-- sword
On Sat, 19 Jan 2008 09:57:10 +0900
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> Hello all,
>
> I've
I have a query that should return results and sqlite3_step is, sometimes,
returning SQLITE_DONE instead of SQLITE_ROW.
I have a table, meta_data, with two columns
id INTEGER primary key, Soundid INTEGER
which I know at the time of execution has the rows
ID SOUNDID
38 39
39
Hello all,
I've read http://www.sqlite.org/lockingv3.html but am still not sure about
multithread and locking in 3.5.4.
I have a multithread application that has a single connection to a single
SQLite3 database. Since it's multithreaded, SQL statements are thrown to
a single SQLite3 object
Doh! Wrong thread. :) :) :)
> -Original Message-
> From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 18, 2008 5:14 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SQLite character comparisons
>
> On Fri, Jan 18, 2008 at 11:04:09PM +, Evans, Mark
On Fri, Jan 18, 2008 at 11:04:09PM +, Evans, Mark (Tandem) wrote:
> Think of NULL as "value is unknown".
>
> With zero length blob/text, value is known: 0-length blob/text
OK, I will :)
--
pozdrawiam / regards
Lothar Scholz
wrote:
Is there a difference between NULL und zero byte length BLOB/TEXT?
Yes. "fieldName IS NULL" test will return true for the former and false
for the latter, for one thing.
Igor Tandetnik
-
To
Think of NULL as "value is unknown".
With zero length blob/text, value is known: 0-length blob/text
Cheers,
Mark
> -Original Message-
> From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 18, 2008 4:51 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite]
> -Original Message-
> From: Lothar Scholz [mailto:[EMAIL PROTECTED]
> Sent: Friday, January 18, 2008 3:50 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Is there a difference between NULL und zero
> byte length BLOB/TEXT?
>
> Hello,
>
> The last question for today:
> Is there a
On Fri, Jan 18, 2008 at 02:13:51PM -0800, Darren Duncan wrote:
> Don't read too much into that statement; I'm was not raising the
> auto-trim thing.
Yes, yes - I know... "Roma locuta"...
--
pozdrawiam / regards
Hello,
The last question for today:
Is there a difference between NULL und zero byte length BLOB/TEXT?
Need this information for my SQLite GUI Frontend.
--
Best regards,
Lothar Scholz mailto:[EMAIL PROTECTED]
At 10:57 PM +0100 1/18/08, Zbigniew Baniewski wrote:
On Fri, Jan 18, 2008 at 12:32:36PM -0800, Darren Duncan wrote:
> If trailing spaces were supposed to be insignificant for an equality test,
then it should not be possible to define a string value containing
trailing spaces at all.
Yes,
On Fri, Jan 18, 2008 at 12:32:36PM -0800, Darren Duncan wrote:
> If trailing spaces were supposed to be insignificant for an equality test,
> then it should not be possible to define a string value containing
> trailing spaces at all.
Yes, yes: quite right... the above reminds me something... ;)
At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:
"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data,
Hi all,
SQLite version 3.5.3 - with custom virtual table module.
I am having a problem with assertion failure following processing of an INSERT
statement relative to a virtual table. Everything works through the call for
VUpdate, then it asserts in vdbe.o in leg for Dup. I'm wondering if
On Sat, Jan 19, 2008 at 03:23:32AM +0700, Lothar Scholz wrote:
> IT>Does this really make sense to you?
>
> Yes the only reason left for a BLOB would be a containing zero byte and
> any illegal UTF8 sequence of bytes.
Or wanting to avoid collations that are aware of, say, Unicode
Trying to figure out why (with one particular customer) some queries have
very different timings on one machine compared to another machine.
It has to do with updating one particular SQLite table with more recent data
from an Interbase table.
I give the database (S for SQLite and I for
"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different
At 10:43 AM -0500 1/17/08, Fowler, Jeff wrote:
Hello All,
I've used SQL Server for over 15 years, Oracle off & on when I have no
choice, but SQLite for a couple weeks. I've just learned (today) that
SQLite respects trailing spaces when comparing two character fields.
I.e. 'SQLITE' <> 'SQLITE '
Hello Igor,
Saturday, January 19, 2008, 12:02:15 AM, you wrote:
IT> You misunderstand the dynamics of datatypes then.
Yes maybe. With the current implementation i really do not understand
the point anyway neither with my understanding nor with yours.
IT> Wait a minute. Didn't you just say that
On Fri, Jan 18, 2008 at 04:41:12PM +, Simon Davies wrote:
> Thus the results are from executing the SQL
> SELECT 'column1, column2, column3' FROM some_table;
> which I believe tallies with the results you see.
Thanks: it's probably the best picture, what is exactly going on there.
--
John Stanton wrote:
Philip Nick wrote:
I tracked down the problem. I was linking an old version of sqlite3
(3.0.8).
That was the old version we used. I apparently doesn't cache between
calls.
My code now performs IO very similar to the CLI. Each subsequent query
once
the db is open does
Philip Nick wrote:
I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.
My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the
Lothar Scholz
wrote:
Subject: Re: Re: What is the precise definition of an identifier?
Further, SQLite also interprets strings enclosed in square brackets
as identifiers (for compatibility with MS Access, I believe):
Okay thanks. Do [ and " literals have any escaping for example "" in
the
Hello Igor,
Saturday, January 19, 2008, 1:26:26 AM, you wrote:
IT> In addition to that, in SQL any string enclosed in double quotes is also
IT> an identifier. E.g. these are valid statements:
IT> Further, SQLite also interprets strings enclosed in square brackets as
IT> identifiers (for
Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Dumping a database with images i run into the 1 million byte per
> SQL statement limit. I thought that the usual way to backup a database
> is the sqlite.exe and dump and eval method.
>
> Shouldn't this limit be dynamic instead of hard wired
Hello,
Dumping a database with images i run into the 1 million byte per
SQL statement limit. I thought that the usual way to backup a database
is the sqlite.exe and dump and eval method.
Shouldn't this limit be dynamic instead of hard wired into a compile
constant?
--
Best regards,
Lothar
Lothar Scholz
wrote:
I have to write an sqlite syntax highligher for an editor
and at the moment i use the following token BNF syntax.
ident := '_' | letter ( letter | '_' | digit )*
I believe dollar sign is allowed in the middle (as the first character,
it denotes a named parameter).
In
Lothar Scholz <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I have to write an sqlite syntax highligher for an editor
> and at the moment i use the following token BNF syntax.
>
> ident := '_' | letter ( letter | '_' | digit )*
>
> Is this correct?
This is correct depending on your definition of
Hello,
I have to write an sqlite syntax highligher for an editor
and at the moment i use the following token BNF syntax.
ident := '_' | letter ( letter | '_' | digit )*
Is this correct?
I would appreciate an additional section in the documentation
that defines the few missing low level
Lothar Scholz
wrote:
Friday, January 18, 2008, 8:09:02 PM, you wrote:
Lothar Scholz
wrote:
it seems that "Lothar" is stored as a TEXT value but when i store
X'4C6F74686172' it is a BLOB.
What is the reason for it?
Same reason 1 is an integer literal but '1' is a string literal.
Upgrading to 3.5.4 will be bennificial. You should be able to pass the
connection around to the various threads with no problems.
Just make sure you compile (.configure --enable-threadsafe) and you should be
good. No need to add your own mutex as its done internally by the sqlite code.
On 18/01/2008, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> ...and now the contents of $columns (SQL variable) in the statement above,
> has been replaced with the contents of $columns (TCL variable) - because the
> variable names are "compatible". So - that was my assumption - we've got now:
>
[EMAIL PROTECTED] wrote:
"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:
DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong
ex:
DateDiff (month, 1-1-2007, 3-30-2007) will return 2
Its that right ?
So datediff('month', '2008-02-01
On Thu, 17 Jan 2008, [EMAIL PROTECTED] wrote:
"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:
DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong
ex:
DateDiff (month, 1-1-2007, 3-30-2007) will return 2
Its that right ?
So datediff('month',
Writing, adding and using your own functions within SQLite is pretty
easy. That's probably your best bet to solve this problem.
-T
> -Original Message-
> From: Fowler, Jeff [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 11:59 PM
> To: sqlite-users@sqlite.org
>
Lothar Scholz <[EMAIL PROTECTED]> wrote:
>
> Another question, how would you realiable represent contrl characters
> in the range 1-31 in a string? It is not really good to add them as
> plain code in text files and SQLite does not have C like backslash
> quoting. Especially the automatic
Hello Igor,
Friday, January 18, 2008, 8:09:02 PM, you wrote:
IT> Lothar Scholz
IT> wrote:
>> it seems that "Lothar" is stored as a TEXT value but when i store
>> X'4C6F74686172' it is a BLOB.
>> What is the reason for it?
IT> Same reason 1 is an integer literal but '1' is a string literal.
On Fri, Jan 18, 2008 at 02:54:40PM +0100, Michael Schlenker wrote:
> Not really true.
> If the part is wrapped in {} then for Tcl the $column is just an ordinary
> string with no other meaning than foobar, and NO substitution takes place
> before the string is passed to SQLite.
Yes, as I wrote
Zbigniew Baniewski schrieb:
On Fri, Jan 18, 2008 at 02:54:40PM +0100, Michael Schlenker wrote:
$name is an application variable if it appears in a place where an
application variable is valid.
[..]
Your usage fails, because the select list is no valid place to use
application variables, so
I tracked down the problem. I was linking an old version of sqlite3 (3.0.8).
That was the old version we used. I apparently doesn't cache between calls.
My code now performs IO very similar to the CLI. Each subsequent query once
the db is open does not require re-reading the whole db.
My next
Zbigniew Baniewski schrieb:
On Thu, Jan 17, 2008 at 11:13:59PM -0500, D. Richard Hipp wrote:
The rules of TCL parsing are that text within {...} gets passed into
its command exactly as written with the outermost {...} removed. [..]
In other words, the $columns was *not* expanded by TCL. It
On Jan 17, 2008 3:48 PM, Philip Nick <[EMAIL PROTECTED]> wrote:
> Greetings,
>
> Currently I am using sqlite3 in a multi-process/multi-threaded server
> setting.
opening a file is very slow. You need to move it out of your loop.
> I have noticed my program basically reads the whole database
On Thu, Jan 17, 2008 at 11:13:59PM -0500, D. Richard Hipp wrote:
> The rules of TCL parsing are that text within {...} gets passed into
> its command exactly as written with the outermost {...} removed. [..]
> In other words, the $columns was *not* expanded by TCL. It got
> passed down into
Lothar Scholz
wrote:
it seems that "Lothar" is stored as a TEXT value but when i store
X'4C6F74686172' it is a BLOB.
What is the reason for it?
Same reason 1 is an integer literal but '1' is a string literal.
X'4C6F74686172' is a blob literal.
The dynamic typing should look at the
data
Perhaps some enterprising soul could write the datediff function and put it
in the wiki for everyone to use. In fact a separate area just for
user-written functions might be quite helpful.
For what it's worth, I have one that truncates a time (stored in time_t
format) down to the start of an
Hello,
it seems that "Lothar" is stored as a TEXT value but when i store
X'4C6F74686172' it is a BLOB.
What is the reason for it? The dynamic typing should look at the
data content and not the literal form. So i would consider this a bug.
For convenience i tried to always use hex quotes no
Fowler, Jeff wrote:
Hello All,
SQLite newbie here. I've looked through the email archives and website
trying to find out how to compute the difference in months between two
given dates. Each date is in -MM-DD HH:MM:SS format.
The best I've been able to come up with seems rather ugly:
51 matches
Mail list logo
