Re: [sqlite] Query Doesn't Find Record

[Doug Currie]

On Mar 12, 2009, at 12:08 AM, jonwood wrote:

> Doug Currie-2 wrote:
>>
>> Note the '/'s
>>
>
> What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1')  
> return?
> Does DATE() simply have no effect whatsoever?

Sorry to be cryptic.

sqlite> select date('2009/12/03');

sqlite> select date('2009-12-03');
2009-12-03
sqlite>

The '/'s format is not supported by DATE.

See: http://www.sqlite.org/lang_datefunc.html

e

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Doesn't Find Record

[jonwood]

Doug Currie-2 wrote:
> 
> Note the '/'s
> 

What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1') return?
Does DATE() simply have no effect whatsoever?
-- 
View this message in context: 
http://www.nabble.com/Query-Doesn%27t-Find-Record-tp22469520p22469578.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Doesn't Find Record

[Doug Currie]

On Mar 12, 2009, at 12:01 AM, jonwood wrote:

> PaymentDate=2009/01/05

Note the '/'s

> And then I ran the following query:
>
> SELECT * FROM Payments WHERE FK_CustomerID=5 AND DATE(PaymentDate) >=
> DATE('2009-01-01') AND DATE(PaymentDate) <= DATE('2009-03-11')

Note the '-'s.

'2009/' > '2009-'

e

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

[Dennis Cote]

Roger Binns wrote:
> Jean-Christophe Deschamps wrote:
>   
>> I'd like to have the group opinion about a feature I would find utterly 
>> useful in _standard_ SQLite.
>> 
>
> You are aware that "standard" SQLite is used in devices with a few
> kilobytes of memory through workstations and servers with gigabytes of it!
>
>   

Whether he is aware of that or not is largely irrelevant, though I 
suspect he is aware of this fact. He is asking for support for a 
mechanism that will provide most, if not all, the benefits of the ICU 
extension using much less memory and far fewer CPU cycles. This would 
benefit the users of small devices more than those using workstations, 
but it could be beneficial to all.

It would be almost universally beneficial if it could be omitted using a 
compiler define. Then even the small additional overhead of his proposal 
wouldn't impact those users who have no need for anything beyond ASCII.

> As far as I can tell you want some extra "standard" collation sequences
> and propose shortcuts that will get them mostly right.  And you want
> someone else to write the code!
>   

No, he is asking for standard support for "user defined" collating 
sequences. And, yes, he wants some else to write the code since it (like 
many other facilities) is not nearly as useful if it is not included in 
the standard SQLite released by Richard. He has even offered to pay to 
have it developed.

> SQLite makes it very easy to have extensions and to register them.  For
> example see http://sqlite.org/c3ref/auto_extension.html
>
> Generally the best approach would be to produce the code as an
> extension, document and test it well and then add to the contributions
> page at http://sqlite.org/contrib - once enough developers have used it
> and vouched for its utility then it would be far easier to lobby for
> incorporation into the "standard" SQLite.
>   

The problem with extensions is precisely that they are not universally 
available. If my application uses an extension to provide collation 
sequences, then I can not safely use any of the many GUI database 
browsers to manipulate the data since the GUI browser won't have access 
to the extension functions.
 
> For you to convince me of the utility of the code, you'd need to list
> which locales it gets right and which it gets wrong.  Software can seem
> pretty dumb to users almost getting some things right.
>
>   
His proposal doesn't rely on locales. It uses "user defined" strings to 
define a collating sequence. It will be right where you can define a 
collating sequence using a string, and a suitable string is defined.

He has already said that it won't be suitable for multiple byte 
characters or many other languages. For those cases where it is not 
suitable, a user could continue to use the ICU extension just as they 
can now.

Dennis Cote


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Proposal for SQLite and non pure ASCII letters

[Jean-Christophe Deschamps]

Roger,



>You are aware that "standard" SQLite is used in devices with a few
>kilobytes of memory through workstations and servers with gigabytes of it!

That's precisely why such approach is interesting!


>As far as I can tell you want some extra "standard" collation sequences
>and propose shortcuts that will get them mostly right.  And you want
>someone else to write the code!

Phew!  It wasn't not my intention to see anyone here go ballistic 
reading my post.

But look:
   1) I never pretended at any "standard", just a useful set of features
   2) what I said is that it can be made to fit _most_ needs with little
  requirements in memory and cycles
   3) I offered (twice) to pay a reasonable fee for such development if it
  implies some branching from mainstream core. The support page 
indeed reads:
If you would like professional support for SQLite or if you want custom 
modifications performed by the original author or SQLite, these 
services are available for a modest fee. For additional information 
visit 
http://www.hwaci.com/sw/sqlite/prosupport.html
 
or contact:
D. Richard Hipp
Hwaci - Applied Software Research
704.948.4565
d...@hwaci.com

I don't make this up you know.


>SQLite makes it very easy to have extensions and to register them.  For
>example see 
>http://sqlite.org/c3ref/auto_extension.html

True, but I think that no extension will ever overload consistanly the 
macros (!)
and other deep code (like inside FTS3) involved in REGEX, ORDER, 
GLOB/LIKE, UPPER...
Or maybe I overlook something obvious.
OTOH, having collation and comparison located in _code_ makes them 
difficult to port.


>Generally the best approach would be to produce the code as an
>extension, document and test it well and then add to the contributions
>page at http://sqlite.org/contrib - once 
>enough developers have used it
>and vouched for its utility then it would be far easier to lobby for
>incorporation into the "standard" SQLite.

I'm not lobbying, nor asking for good practice guidance. I was asking 
the group their
opinion about the usefulness of such feature. As far as I can see, it 
would require to
be part of the core to deliver full power.

I understand how difficult it is for some english-only developpers or 
users, having to
support code for non-english speakers / writers. I just thought it was 
fading now.


>For you to convince me of the utility of the code, you'd need to list
>which locales it gets right and which it gets wrong.  Software can seem
>pretty dumb to users almost getting some things right.

Please make me the favor to _read_ my post. I believe you have enough 
experience to
understand it and think about the _practical_ usefulness of such 
behavior. It doesn't
work with "locales" at all. It allows the user to declare its own set 
of characters
and the way SQLite should handle them for low-level operations. It's a 
DIY thing!

BTW locales are far from perfection. For instance: you have to search 
text, say an address book in a cellphone, with FTS3 and you know the 
base may have words or names in a dozen european languages. How would 
you do?  ICU? Huge and slow, but even then: which "locale" would you use?

I'm in no way saying that ICU is a bad thing. Of course it's been 
extremely carefully coded and reviewed. It's a very nice building block 
... but only for those that need the most comprehensive solution 
available (and can afford to bring it aboard).

Perhaps the best way is practice: what's the way to find this guy named 
Éric or is it éric, or Eric, or eric? He lives in MÜNCHEN, München, 
MUNCHEN, Munchen or ... Munich.


To answer another post by Ian, yes I've had a look at ICU.  Of course 
ICU knows about its size, but what can they do about it, since their 
goal is to implement the most complete support possible? And again, 
that's a very good thing. But my reluctance is elsewhere: I do not 
adhere that much in locales. I use more than a strictly defined locale 
"locale" and less than a world locale!

Also, yes I write most users. BTW SQLite would possibly have more 
"rest-of-the-world" fans if a more simple handling of diacritics were 
available!  Look: we in Western Europe have been computerized in ALL 
PURE ASCII CAPITALS for many years. Then in mixed casing, but always 
ASCII. Now is perhaps the time for something else.



Jean-Christophe 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE : Constraint question

[Dennis Cote]

REPKA_Maxime_NeufBox wrote:
> ->> Why is it possible to change data not defined in the constraint :
> Exemple : enter TEXT if the column is INTERGER ??
>  enter 25 caracters if column is declared VARCHAR(15) ??
> I thought i will get an error return
> See exemple below :
>
>   

As Martin has already pointed out, this is expected behavior due to 
SQLite's more flexible manifest data typing extensions to SQL.

However, you can explicitly add the equivalent constraints to your table 
definitions if you really want them. For example:

create table t (
a varchar(15) check (length(a) <= 15),
b integer check (typeof(b) = 'integer')
);
insert into t values('one', 1);
insert into t values('two', 'three');
insert into t values('one hundred twenty three million...', 123456789);

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested SELECTS using UNION and INTERSECT syntax problems....

[Dennis Cote]

sorka wrote:
> I can't for the life of me figure this out. I'm trying to do a nested select
> like this:
>
> SELECT x FROM (( UNION ) INTERSECT ( UNION
> )) WHERE X=
>
> Each of the select a through d statements all return the same column x. If I
> remove the inner parentheses, it executes just fine but of course the
> results are wrong because C UNION D was not executed prior to the INTERSECT. 
>
> Also, in each sub select case, a, b, c, and d, are all selecting on
> different FTS3 tables using MATCH so I can't take advantage of FTS3's newer
> nested parentheses with AND OR NOT hence the nested selects since MATCH can
> only be used once per select.
>
> Any ideas? I really don't want to have to resort to using temporary tables.
>   
You need to use a separate select for each compound operator. Something 
like this should work as you expect.

select x from
(
select x from
(select x from a
union
select x from b)
as ab
intersect
select x from
(select x from c
union
select x from d)
as cd
) as abcd
where x > ?;

HTH
Dennis Cote

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposal for SQLite and non pure ASCII letters

[Roger Binns]

Jean-Christophe Deschamps wrote:
> I'd like to have the group opinion about a feature I would find utterly 
> useful in _standard_ SQLite.

You are aware that "standard" SQLite is used in devices with a few
kilobytes of memory through workstations and servers with gigabytes of it!

As far as I can tell you want some extra "standard" collation sequences
and propose shortcuts that will get them mostly right.  And you want
someone else to write the code!

SQLite makes it very easy to have extensions and to register them.  For
example see http://sqlite.org/c3ref/auto_extension.html

Generally the best approach would be to produce the code as an
extension, document and test it well and then add to the contributions
page at http://sqlite.org/contrib - once enough developers have used it
and vouched for its utility then it would be far easier to lobby for
incorporation into the "standard" SQLite.

For you to convince me of the utility of the code, you'd need to list
which locales it gets right and which it gets wrong.  Software can seem
pretty dumb to users almost getting some things right.

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Proposal for SQLite and non pure ASCII letters

[Jean-Christophe Deschamps]

Hello group,


I'd like to have the group opinion about a feature I would find utterly 
useful in _standard_ SQLite.

Here's a rewrite of mails sent to hwaci about it, without success so far.

Note: I guess that non pure ASCII characters in the sample strings 
below will translate to '?', but you can obviously get the idea.

---
First I'd like to congratulate contributors to SQLite. You know who and 
why!

Now I'd like to either submit a request or ask for a quotation wrt (yet 
another) new feature in SQLite.

SQLite has been supporting Unicode for a long time but Unicode brings 
its own problems. Collating is one of the most important. I know one 
can add a user-defined collating function but I think it doesn't solve 
all situations.

I feel the need for a different implementation of collating support. 
Not every user of SQLite needs full universal collating support à la 
ICU. It's huge and slows things down significantly.

Most users only have a very small number of close languages to deal 
with, e.g. European Western ones.  For 98% of those users only the 
diacritics are a problem, but these accented letters are only a tiny 
subset of Unicode for a given language.

Hence I believe a large user base could benefit of having a new 
"USERSTRING" collating support which could be implemented in only two 
couples of strings of equal length _stored_ in a reserved table of a 
database and a few lines of fast C code in the SQLite library.

The first string, call it OrderString, would define the specific 
character set required by this precise application and its sort order 
(ORDER BY). Any char not found there should be handled as per its 
Unicode value.  This would allow users to explicit the charset and 
sorting order they need, without having to bring giant tables and slow 
code in the picture.

The second string, call it ClassString, would hold the character class 
for each character in the first string.  Its use is for LIKE operator 
and FTS3 support, after lookup in Orderstring to get letter index in 
this string. You can choose to have both upper- and lower-case letters 
there (case sensitive LIKE) or only lowercase (case insensitive LIKE).

The third string, call it UpperString, would hold the uppercase version 
of the charset.

The fourth string, call it LowerString, would hold the lowercase 
version of the charset. Should be identical to UpperString for 
languages that don't have casing.

Let me elaborate how the OrderString can cope with letters in two or 
more intervals. To compare strings, we need only know how to compare 
single characters.
Let C and D be the characters to compare and let A be the first and Z 
the last character in OrderString. Also let Idx(char) be the position 
of char in Orderstring. We can proceed as follows:

ic = Index(C, OrderString)   // index of C or -1 if not found
id = Index(D, OrderString)   // index of D or -1 if not found
If C != -1
   If D != -1
 Return Sign(ic - id)
   Else
 Return Sign(D - A)
   Endif
Else
   If D != -1
 Return Sign(C - A)
   Else
 Return Sign(C - D)
   Endif
Endif

In plain language, if C & D are both inside or outside the user 
charset, then compare them directly within their set (user or 
rest-of-Unicode). Else compare the char not in userset against the 
first char in userset. This trick makes the userset a separate "branch" 
off Unicode, sorted apart from the Unicode "sequence".

The pseudo-code above should be compiled very efficiently by any decent 
compiler. OrderString not only defines the userset and its sort order, 
but it also defines the "letter" class for REGEXes, upper and lower 
classes being also well defined by UpperString and LowerString.

The LIKE operator goes along quite the same line. With notation above:

ic = Index(C, OrderString)   // index of C or -1 if not found
id = Index(D, OrderString)   // index of D or -1 if not found
If C != -1 AND If D != -1
 Return (ClassString[ic] == ClassString[id])
ElseIf C != -1 AND If D != -1
 Return (C == D)
Else
 Return False
Endif

If we would like to have LIKE act depending on a CASE_SENSITIVE flag 
[sounds it could be the compareInfo.noCase flag], then it would require 
ClassString be in fact TWO strings, UpperClassString and 
LowerClassString. Anyway, once the choice made, it's as easy to 
implement with straightforward code.

As an example, I'm interested in finding customers first or last names, 
street addresses or city names mostly for French customers. I may also 
have some Spanish and Portugese names around, as well as names from 
Netherlands, Germany, Switzerland, Italy. I would use FTS3 virtual 
tables knowing that FTS3 will use my user supplied strings to record 
words in pure ASCII, but I could use any kind of search with data 
containing diacritics: it would work like a charm transparently.

I would start with something like (mailing this could partly destroy it!):

OrderString =
  

Re: [sqlite] Slow performance with Sum function

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Trainor, Chris
Sent: Wednesday, March 11, 2009 5:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow performance with Sum function

> Do not be tempted by the incremental vacuum feature.  Incremental 
> vacuum will reduce the database size as content is deleted, but it 
> will not reduce fragmentation.  In fact, incremental vacuum will 
> likely increase fragmentation.  Incremental vacuum is just a variation

> on auto_vacuum.  It is designed for flash memory with zero seek
latency.

> D. Richard Hipp
> d...@hwaci.com

Thanks for the reply, but I am confused again.  Is incremental vacuum
different from the vacuum command?  It seems like vacuum would
defragment the database according to the description here:
http://www.sqlite.org/lang_vacuum.html 

=
No, the auto_vacuum command differs from the regular vacuum command.
The auto_vacuum command does not reduce (and may increase
fragmentation).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow performance with Sum function

[Trainor, Chris]

> Do not be tempted by the incremental vacuum feature.  Incremental  
> vacuum will reduce the database size as content is deleted, but it  
> will not reduce fragmentation.  In fact, incremental vacuum will  
> likely increase fragmentation.  Incremental vacuum is just a variation

> on auto_vacuum.  It is designed for flash memory with zero seek
latency.

> D. Richard Hipp
> d...@hwaci.com

Thanks for the reply, but I am confused again.  Is incremental vacuum
different from the vacuum command?  It seems like vacuum would
defragment the database according to the description here:
http://www.sqlite.org/lang_vacuum.html 

"The VACUUM command cleans the main database by copying its contents to
a temporary database file and reloading the original database file from
the copy. This eliminates free pages, aligns table data to be
contiguous, and otherwise cleans up the database file structure."

If incremental vacuum and vacuum are the same, then I am still uncertain
of what to do about my original problem.  Any ideas on why the sum
function is slow on my existing table, but it is fast on a copy of the
table?  Also, after calling vacuum, sum is fast on the original table.

Here's my original question:

I am trying to use the Sum function on a column in a table with ~450K
rows in it.  

Select sum(Col4) from Table1

Where Table1 looks like this:

Create TABLE Table1 (
Col1 INTEGER NOT NULL,
Col2 INTEGER NOT NULL,
Col3 INTEGER NOT NULL,
Col4 BIGINT NOT NULL,
Col5 BIGINT NOT NULL,
Col6 BLOB NOT NULL,
Col7 CHAR(1) DEFAULT '0',
Col8 NUMERIC(2) NOT NULL,
Col9 NUMERIC(2) NOT NULL,
Col10 INTEGER NOT NULL,
Col11 INTEGER NOT NULL,
CONSTRAINT FK_1 FOREIGN KEY (Col1) REFERENCES Table2 (Col1)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT PK_1 PRIMARY KEY (Col10, Col11, Col1, Col3 DESC) );


It takes over 2 minutes to execute when using the original table.  I
created an exact copy of the table with the same indices and constraints
and inserted all the data from the original table into it.  Summing that
column on the copied table only takes a few seconds.

I am guessing that using the copied table is faster because it has all
of its data arranged contiguously, but that is just a guess.

Can anyone shed some light on this?  Making a copy of the table is not
an option, so is there anything I can do to get better performance from
the original table?

Thanks
The information contained in this email message and its attachments
is intended
only for the private and confidential use of the recipient(s) named
above, unless the sender expressly agrees otherwise. Transmission
of email over the Internet
 is not a secure communications medium. If you are requesting or
have requested
the transmittal of personal data, as defined in applicable privacy
laws by means
 of email or in an attachment to email you must select a more
secure alternate means of transmittal that supports your
obligations to protect such personal data. If the reader of this
message is not the intended recipient and/or you have received this
email in error, you must take no action based on the information in
this email and you are hereby notified that any dissemination,
misuse, copying, or disclosure of this communication is strictly
prohibited. If you have received
this communication in error, please notify us immediately by email
and delete the original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE : Constraint question

[Martin Engelschalk]

Hi,

sqlite does not enforce datatypes. In this, sqlites works differently 
from other database engines.

See http://www.sqlite.org/different.html and search for "*Manifest typing"
The key sentence is
*"SQLite thus allows the user to store any value of any datatype into 
any column regardless of the declared type of that column."

See also http://www.sqlite.org/datatype3.html

Martin

REPKA_Maxime_NeufBox wrote:
> Hello,
> I am working on Database not for a long time. From SQLITE Tutorial exam
> table :*
>
> ->> Why is it possible to change data not defined in the constraint :
> Exemple : enter TEXT if the column is INTERGER ??
>  enter 25 caracters if column is declared VARCHAR(15) ??
> I thought i will get an error return
> See exemple below :
>
> sqlite> pragma table_info('exam');
> 0|ekey|INTEGER|0||1
> 1|fn|VARCHAR(15)|0||0
> 2|ln|VARCHAR(30)|0||0
> 3|exam|INTEGER|0||0
> 4|score|DOUBLE|0||0
> 5|timeEnter|DATE|0||0
>
> sqlite> select * from exam;
> 1|Bob|Anderson|1|75.0|2009-02-24 09:41:04
> 2|Bob|Anderson|2|82.0|2009-02-24 10:11:45
>
> sqlite> UPDATE main.'exam' SET fn='Bob12345678901234567890' WHERE ekey =
> '1';
> sqlite> UPDATE main.'exam' SET exam='NN' WHERE ekey = '1';
> sqlite> select * from exam;
> 1|Bob12345678901234567890|Anderson|NN|75.0|2009-02-24 09:41:04
> 2|Bob|Anderson|2|82.0|2009-02-24 10:11:45
> sqlite>
>
> Sincères salutations
> Maxime REPKA
> Tel : 02.31.34.75.65
> MailTo:repka.max...@neuf.fr
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE : Constraint question

[REPKA_Maxime_NeufBox]

Hello,
I am working on Database not for a long time. From SQLITE Tutorial exam
table :*

->> Why is it possible to change data not defined in the constraint :
Exemple : enter TEXT if the column is INTERGER ??
 enter 25 caracters if column is declared VARCHAR(15) ??
I thought i will get an error return
See exemple below :

sqlite> pragma table_info('exam');
0|ekey|INTEGER|0||1
1|fn|VARCHAR(15)|0||0
2|ln|VARCHAR(30)|0||0
3|exam|INTEGER|0||0
4|score|DOUBLE|0||0
5|timeEnter|DATE|0||0

sqlite> select * from exam;
1|Bob|Anderson|1|75.0|2009-02-24 09:41:04
2|Bob|Anderson|2|82.0|2009-02-24 10:11:45

sqlite> UPDATE main.'exam' SET fn='Bob12345678901234567890' WHERE ekey =
'1';
sqlite> UPDATE main.'exam' SET exam='NN' WHERE ekey = '1';
sqlite> select * from exam;
1|Bob12345678901234567890|Anderson|NN|75.0|2009-02-24 09:41:04
2|Bob|Anderson|2|82.0|2009-02-24 10:11:45
sqlite>

Sincères salutations
Maxime REPKA
Tel : 02.31.34.75.65
MailTo:repka.max...@neuf.fr

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT queries and NULL value parameters

[Sylvain Pointeau]

you can also use ifnull(myvar1,'') = ifnull(myvar2,'')

or something in the same way.

I used it for avoiding creating 2 queries for each cases.

Cheers,
Sylvain

On Wed, Mar 11, 2009 at 4:14 PM, Jim Wilcoxson  wrote:

> I used the Solid database for many years, since they came out with
> their Linux version back in 1998.  Initially they had this behavior.
> Later they added = NULL so that it worked like IS NULL.  My guess is
> that, standard or not, this was so utterly confusing to most people
> that it was better to be slightly non-standard than field all the
> support questions.
>
> Here's what Microsoft does (not that they should be any kind of
> standard bearer, that's for sure!)  From
> http://msdn.microsoft.com/en-us/library/aa196339(SQL.80).aspx:
>
> --
> Care must be taken when comparing null values. The behavior of the
> comparison depends on the setting of the SET ANSI_NULLS option.
>
> When SET ANSI_NULLS is ON, a comparison in which one or more of the
> expressions is NULL does not yield either TRUE or FALSE; it yields
> UNKNOWN. This is because a value that is unknown cannot be compared
> logically against any other value. This occurs if either an expression
> is compared to the literal NULL, or if two expressions are compared
> and one of them evaluates to NULL. For example, this comparison always
> yields UNKNOWN when ANSI_NULLS is ON:
>
> ytd_sales > NULL
>
> This comparison also yields UNKNOWN any time the variable contains the
> value NULL:
>
> ytd_sales > @MyVariable
>
> Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This
> can add complexity to the WHERE clause. For example, the Region column
> in the Northwind Customers table allows null values. If a SELECT
> statement is to test for null values in addition to others, it must
> include an IS NULL clause:
>
> SELECT CustomerID, CompanyName, Region
> FROM Northwind.dbo.Customers
> WHERE Region IN ('WA', 'SP', 'BC')
>   OR Region IS NULL
>
> Transact-SQL supports an extension that allows for the comparison
> operators to return TRUE or FALSE when comparing against null values.
> This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is
> OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA
> contains a null value and FALSE when ColumnA contains some value
> besides NULL. Also, a comparison of two expressions that have both
> evaluated to null values yields TRUE. With ANSI_NULLS set OFF, this
> SELECT statement returns all the rows in the Customer table for which
> Region is a null value:
>
> SELECT CustomerID, CompanyName, Region
> FROM Northwind.dbo.Customers
> WHERE Region = NULL
> 
>
> I didn't see where MySQL supports this extension, so the safest thing
> is to do it like the SQL standard says.
>
> The other problem with allowing = NULL comparisons is that it would be
> confusing when binding parameters.  If you say:
>
>  con.execute('''
>select f1 from tab where f2 = ?
>  ''', (NULL,))
>
> That means you want the Python variable NULL, not the SQL constant
> NULL.  And you can't use 'NULL', because that's a string with 4
> characters, not the SQL constant.  I think that's probably why the IS
> NULL syntax is required.
>
> Jim
>
>
> On 3/11/09, Hynes, Tom  wrote:
> > Thanks for the quick response!  Yes, I understand the differences between
> > querying with IS NULL vs. = NULL.  But I had always thought that when
> using
> > *parameter binding* a NULL query parameter would be treated like the IS
> NULL
> > case when doing the comparison, not the equality case.  Hmm, Sounds like
> > I've had a misconception about this.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT queries and NULL value parameters

[Hynes, Tom]

Thanks for the quick response!  Yes, I understand the differences between 
querying with IS NULL vs. = NULL.  But I had always thought that when using 
*parameter binding* a NULL query parameter would be treated like the IS NULL 
case when doing the comparison, not the equality case.  Hmm, Sounds like I've 
had a misconception about this.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Wednesday, March 11, 2009 10:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT queries and NULL value parameters

On Wed, Mar 11, 2009 at 9:14 AM, Hynes, Tom  wrote:
>> ... yes, this is expected.
>
> Can you explain that a bit more?  I certainly would not have expected it.  
> Thanks.

sqlite> CREATE TABLE foo (a);
sqlite> INSERT INTO foo VALUES (1);
sqlite> INSERT INTO foo VALUES ('ab');
sqlite> INSERT INTO foo VALUES ('');
sqlite> INSERT INTO foo VALUES (NULL);
sqlite> SELECT * FROM foo;
1
ab


sqlite> SELECT Count(*) FROM foo;
4
sqlite> SELECT Count(*) FROM foo WHERE a = 1;
1
sqlite> SELECT Count(*) FROM foo WHERE a = '';
1
sqlite> SELECT Count(*) FROM foo WHERE a = NULL;
0
sqlite> SELECT Count(*) FROM foo WHERE a IS NULL;
1
sqlite> SELECT Count(*) FROM foo WHERE a IS NULL OR a = '';
2
sqlite>

>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Martin Engelschalk
> Sent: Wednesday, March 11, 2009 8:59 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SELECT queries and NULL value parameters
>
> Hi,
>
> yes, this is expected. Note that you use the = - operator in WHERE
> maybenullcolumn = @value
> and NULL = NULL evaluates to false.
> This is SQL standard.
>
> Martin
>
> diego.d...@bentley.com wrote:
>> Hello,
>>
>> In my usage of SQLite, I found a behavior that might be considered a
>> bug, but I would like others' input on it.
>>
>> Consider the following table with a single row, with one column
>> containing a null value:
>>
>> CREATE TABLE MyTable (id integer primary key autoincrement, label
>> char(255), maybenullcolumn integer);
>> INSERT INTO MyTable (label) VALUES ('Label');
>>
>> If one tries to retrieve that row with the "IS NULL" syntax, it works
>> fine (the following statement returns 1):
>> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn IS NULL;
>>
>> ... but if one uses parameters, the statement returns 0:
>> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn = @value;
>> ...
>> sqlite3_bind_null(pStmt, 1);
>>
>> Is this expected?
>>
>> Thanks,
>>
>> Diego
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT queries and NULL value parameters

[P Kishor]

On Wed, Mar 11, 2009 at 9:14 AM, Hynes, Tom  wrote:
>> ... yes, this is expected.
>
> Can you explain that a bit more?  I certainly would not have expected it.  
> Thanks.

sqlite> CREATE TABLE foo (a);
sqlite> INSERT INTO foo VALUES (1);
sqlite> INSERT INTO foo VALUES ('ab');
sqlite> INSERT INTO foo VALUES ('');
sqlite> INSERT INTO foo VALUES (NULL);
sqlite> SELECT * FROM foo;
1
ab


sqlite> SELECT Count(*) FROM foo;
4
sqlite> SELECT Count(*) FROM foo WHERE a = 1;
1
sqlite> SELECT Count(*) FROM foo WHERE a = '';
1
sqlite> SELECT Count(*) FROM foo WHERE a = NULL;
0
sqlite> SELECT Count(*) FROM foo WHERE a IS NULL;
1
sqlite> SELECT Count(*) FROM foo WHERE a IS NULL OR a = '';
2
sqlite>

>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Martin Engelschalk
> Sent: Wednesday, March 11, 2009 8:59 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SELECT queries and NULL value parameters
>
> Hi,
>
> yes, this is expected. Note that you use the = - operator in WHERE
> maybenullcolumn = @value
> and NULL = NULL evaluates to false.
> This is SQL standard.
>
> Martin
>
> diego.d...@bentley.com wrote:
>> Hello,
>>
>> In my usage of SQLite, I found a behavior that might be considered a
>> bug, but I would like others' input on it.
>>
>> Consider the following table with a single row, with one column
>> containing a null value:
>>
>> CREATE TABLE MyTable (id integer primary key autoincrement, label
>> char(255), maybenullcolumn integer);
>> INSERT INTO MyTable (label) VALUES ('Label');
>>
>> If one tries to retrieve that row with the "IS NULL" syntax, it works
>> fine (the following statement returns 1):
>> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn IS NULL;
>>
>> ... but if one uses parameters, the statement returns 0:
>> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn = @value;
>> ...
>> sqlite3_bind_null(pStmt, 1);
>>
>> Is this expected?
>>
>> Thanks,
>>
>> Diego
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT queries and NULL value parameters

[Hynes, Tom]

> ... yes, this is expected.

Can you explain that a bit more?  I certainly would not have expected it.  
Thanks.

Tom

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Martin Engelschalk
Sent: Wednesday, March 11, 2009 8:59 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SELECT queries and NULL value parameters

Hi,

yes, this is expected. Note that you use the = - operator in WHERE 
maybenullcolumn = @value
and NULL = NULL evaluates to false.
This is SQL standard.

Martin

diego.d...@bentley.com wrote:
> Hello,
>
> In my usage of SQLite, I found a behavior that might be considered a
> bug, but I would like others' input on it.
>
> Consider the following table with a single row, with one column
> containing a null value:
>
> CREATE TABLE MyTable (id integer primary key autoincrement, label
> char(255), maybenullcolumn integer);
> INSERT INTO MyTable (label) VALUES ('Label');
>
> If one tries to retrieve that row with the "IS NULL" syntax, it works
> fine (the following statement returns 1):
> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn IS NULL;
>
> ... but if one uses parameters, the statement returns 0:
> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn = @value;
> ...
> sqlite3_bind_null(pStmt, 1);
>
> Is this expected?
>
> Thanks,
>
> Diego
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT INNER JOIN a second database

[Kees Nuyt]

On Wed, 11 Mar 2009 06:12:37 -0700 (PDT), Derek Developer
 wrote:

>I have read and searched but I am not able to
>get the following statement to run:
> SELECT MyID, Zip FROM TableOne d 
>  LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n
>  ON n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip
>
>I just get error at "."
>
>I tried specifiying the databse name without the file extension by no joy.
>What am I missing here?

Perhaps: 
ATTACH DATABASE 'DatabaseTwo.sdb' AS db2;
SELECT MyID, Zip 
  FROM TableOne d 
  LEFT OUTER JOIN db2.TableTwo n ON n.MyID=d.MyID 
 WHERE d.Zip > 8 
 ORDER BY d.Zip;
DETACH DATABASE db2;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] tool to browse a sqlite database

[RB Smissaert]

This is now all sorted and it was indeed a simple bug in the wrapper.
When parsing out the create table statement it hadn't anticipated the double
quotes surrounding the tables and fields.
I understand that this is in fact the standard/recommended way, although I
don't do it myself and prefer: CREATE TABLE Table1([Field1] Integer etc.

The wrapper has been updated and (for VB/VBA users) can be downloaded here:
www.datenhaus.de/Downloads/dhRichClient3.zip
www.datenhaus.de/Downloads/dhRichClient3-Demo.zip

RBS



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker
Sent: 08 March 2009 14:38
To: General Discussion of SQLite Database
Subject: Re: [sqlite] tool to browse a sqlite database

RB Smissaert wrote:

>What SQLite version produced the file World.db3?

I am not 100% sure about the exact SQLite version which I used to create the
original World.db3, but I am VACUUMing it regularly to bring it up to date
with recent versions. So I expect it should be some version after 3.6.8.

>I ask as my wrapper doesn't pick correctly the fields of a table.
>This is Olaf Schmidt's VB wrapper dhRichClient with SQLite 3.6.11.

I just did a PRAGMA integrity_check; on World.db3 with the SQLite3.exe v.
3.6.11 and it reports 'ok'. Looks like a wrapper problem to me.

>BTW, SQLiteSpy looks a very nice GUI tool.

Thanks!

Ralf 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert in C

[mrobi002]

Hi Rajesh Nair,

It works perfectly,

Thank you,

Michael

> If you want to use sqlite3_exec function then try this
>
> char *zSQL = sqlite3_mprintf("INSERT INTO probes VALUES(%Q)", temp);
> sqlite3_exec(db, zSQL, 0, 0, 0);
> sqlite3_free(zSQL);
>
> This will format "temp" to hold any special chars which may generate
> some errors.
>
> eg:- temp = " Rajesh's Test " will be formated to " Rajesh''s Test "
>
> On 3/11/09, Martin Engelschalk  wrote:
>> Hi,
>>
>> use sqlite3_prepare and sqlite3_bind.
>> See http://www.sqlite.org/capi3ref.html#sqlite3_prepare
>> and sqlite3_bind_text under
>> http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
>>
>>const char* szTail=0;
>>sqlite3_stmt* pVM;
>>
>> int nRet = sqlite3_prepare(mpDB, "insert into table1 values(?)", -1,
>> , );
>>nRes = sqlite3_bind_text(mpVM, 1, "Hello", -1, SQLITE_TRANSIENT);
>>nRest = sqlite3_step(mpVM);
>>
>> HTH
>> Martin
>>
>> mrobi...@cs.fiu.edu wrote:
>>> Good Morning,
>>>
>>> I would like to write in C the equivalent code for:
>>>
>>>insert into table1 values('Hello');
>>>
>>> using a variable  char temp[20]= "Hello";
>>>
>>> instead of the literal Hello
>>>
>>>
>>> I have used multiple variations of the following, but no luck
>>>
>>> char temp[20]= "Hello";
>>> sql = "INSERT INTO probes VALUES('%s', :temp)";
>>> rc = sqlite3_exec(db, sql, NULL, NULL, );
>>>
>>> Thank you
>>>
>>> Michael
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Regards
> Rajesh Nair
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT INNER JOIN a second database

[John Machin]

On 12/03/2009 12:12 AM, Derek Developer wrote:
> I have read and searched but I am not able to get the following statement to 
> run:
> SELECT MyID, Zip FROM TableOne d LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n ON 
> n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip
> 
> I just get error at "."
> 
> I tried specifiying the databse name without the file extension by no joy.
> What am I missing here?

The fact that you need to supply not a filename but a database-name. See
http://www.sqlite.org/syntaxdiagrams.html#single-source

What's a database-name? See http://www.sqlite.org/lang_attach.html

HTH,
John

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT INNER JOIN a second database

[P Kishor]

On Wed, Mar 11, 2009 at 8:12 AM, Derek Developer
 wrote:
> I have read and searched but I am not able to get the following statement to 
> run:
> SELECT MyID, Zip FROM TableOne d LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n ON 
> n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip
>
> I just get error at "."
>
> I tried specifiying the databse name without the file extension by no joy.
> What am I missing here?
>


well, for one, do you have an "ATTACH 'DatabseTwo.sdb' AS db2" first?

If yes, you could do

SELECT..
FROM TableOne d LEFT OUTER JOIN db2.TableTwo n ON n.MyID=d.MyID
WHERE d.Zip > 8 ORDER BY d.Zip
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LEFT INNER JOIN a second database

[Derek Developer]

I have read and searched but I am not able to get the following statement to 
run:
SELECT MyID, Zip FROM TableOne d LEFT OUTER JOIN DatabseTwo.sdb.TableTwo n ON 
n.MyID=d.MyID WHERE d.Zip > 8 ORDER BY d.Zip

I just get error at "."

I tried specifiying the databse name without the file extension by no joy.
What am I missing here?



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert in C

[Martin Engelschalk]

Hi,

use sqlite3_prepare and sqlite3_bind.
See http://www.sqlite.org/capi3ref.html#sqlite3_prepare
and sqlite3_bind_text under
http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob

   const char* szTail=0;
   sqlite3_stmt* pVM;

int nRet = sqlite3_prepare(mpDB, "insert into table1 values(?)", -1, 
, );
   nRes = sqlite3_bind_text(mpVM, 1, "Hello", -1, SQLITE_TRANSIENT);
   nRest = sqlite3_step(mpVM);

HTH
Martin

mrobi...@cs.fiu.edu wrote:
> Good Morning,
>
> I would like to write in C the equivalent code for:
>
>insert into table1 values('Hello');
>
> using a variable  char temp[20]= "Hello";
>
> instead of the literal Hello
>
>
> I have used multiple variations of the following, but no luck
>
> char temp[20]= "Hello";
> sql = "INSERT INTO probes VALUES('%s', :temp)";
> rc = sqlite3_exec(db, sql, NULL, NULL, );
>
> Thank you
>
> Michael
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert in C

[mrobi002]

Good Morning,

I would like to write in C the equivalent code for:

   insert into table1 values('Hello');

using a variable  char temp[20]= "Hello";

instead of the literal Hello


I have used multiple variations of the following, but no luck

char temp[20]= "Hello";
sql = "INSERT INTO probes VALUES('%s', :temp)";
rc = sqlite3_exec(db, sql, NULL, NULL, );

Thank you

Michael

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Extract error text in C API

[Andy Sharp]

I did see that function and I had already tried that but it just gives me
"SQL logic error or missing database".  The error code from the step command
is 19 (constraint error).  I t almost looks like a different error, but if I
take out the triggers it works.

Thanks

Andy



On Wed, Mar 11, 2009 at 2:57 AM, D. Richard Hipp  wrote:

>
> On Mar 10, 2009, at 10:07 PM, Andy wrote:
>
> >
> > I am using triggers to handle database integrity as suggested by the
> > docs.
> > When a violation occurs the 'SELECT RAISE(ROLLBACK, "Blah")' is
> > executed.  I
> > want to get extract the 'Blah' text using the C API but cannot seem
> > to work
> > out how to do it.  I feel I am missing something obvious... can
> > anyone please
> > help.
> >
>
> http://www.sqlite.org/c3ref/errcode.html
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Jim Ursetto]

At 03:47am on 2009 March 08, VF did write:
> CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping);
> CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key);
> 
> I am trying to do an upsert with the following logic:
> 
> UPDATE MAPPINGS_$idx 
> SET counter = counter + 1
> , timeModified = CURRENT_TIMESTAMP
> WHERE 
>   key = ? AND 
>   mapping = ?;
> IF rowcount == 0 -- here tried rowid and other variations, too, it still
> fails at he IF
> BEGIN
> INSERT INTO MAPPINGS_$idx
>  ( key , mapping , rank , counter , timeCreated , timeModified)
>values ( ? , ? , 1 , 1 , CURRENT_TIMESTAMP , CURRENT_TIMESTAMP )
> END;  
 
> Unfortunately, it fails. I ended up having separate update, check the number
> of rows modified, and if 0 - do insert in a separate statement. It works,
> but painfully slow. Because of this I can't do BEGIN/COMMIT transaction from
> the SQL.

Is it possible for you to do the following?  The INSERT should fail
silently if you violate a uniqueness constraint; we also set the initial
counter to 0 as it will be immediately bumped to 1 by the UPDATE.  You
should be able to wrap the whole thing in a transaction.

***

INSERT OR IGNORE INTO MAPPINGS_$idx
 (key, mapping, rank, counter, timeCreated, timeModified)
   values (?, ?, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

UPDATE MAPPINGS_$idx SET counter = counter + 1, 
  timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?;

-- 
j...@3e8.org / 0x43340710 / 517B C658 D2CB 260D 3E1F  5ED1 6DB3 FBB9 4334 0710
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get_table and bind

[galeazzi]

Citando Igor Tandetnik :

> galea...@korg.it wrote:
>> is it possible to use a similar function to get_table but starting by
>> a statement in order to use the bind facilities?
>
> Anything wrong with calling sqlite3_step in a loop?
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I'm working on a real-time embedded  system so I need to fetch all  
rows of query and copy them in memory during a low priority task and  
then return this structure to the other tasks. The table is just a  
good structure for my aims, that's why I'd like to use it without  
implementing my own structure again.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"

[Mihai Limbasan]

manohar s wrote:
> Hi,
>  I am trying to execute "PRAGMA page_size=4096; Vacuum;" on a SQLite DB(Size
> 1.5 GB), On a drive which has 9 GB free space (But my C: has 150 MB free is
> this an issue?). But it is failing with "SQL Error:Database or disk is full"
> error everytime. SQLite version: 3.6.11.
>
> 1) Am I missing anything here?
>
> Regards,
> manohar.S
>   
The error refers to the file system hosting the temporary files 
location. Try setting the TEMP environment variable to a location with 
more free space, reboot your system, and it willwork. It's actually a 
really bad idea to have little space ont he file system hosting the 
temporary file location...

HTH,
Mihai
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Extract error text in C API

[D. Richard Hipp]

On Mar 10, 2009, at 10:07 PM, Andy wrote:

>
> I am using triggers to handle database integrity as suggested by the  
> docs.
> When a violation occurs the 'SELECT RAISE(ROLLBACK, "Blah")' is  
> executed.  I
> want to get extract the 'Blah' text using the C API but cannot seem  
> to work
> out how to do it.  I feel I am missing something obvious... can  
> anyone please
> help.
>

http://www.sqlite.org/c3ref/errcode.html

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] advice about opening an encrypted database

[D. Richard Hipp]

On Mar 10, 2009, at 8:31 PM, Dave Dyer wrote:

> using the standard sqlite encryption option:
>
> If I open a database I expect to be encrypted, and call
> sqlite_key to establish the expected key, how should I verify
> that the database is now open for business? Ie that the key
> was correct.
>
> Similarly, if I open a database might or might not be encrypted,
> how can I definitively determine that is the case, so I can ask
> the client for a password.
>
>
> It's not really satisfactory to just wait for some downstream
> query to fail, or to make a dummy query and assume that if it
> fails the reason is the lack of proper encryption keys.


The only thing you can do is to run an query and check to see if you  
get back SQLITE_NOTADB.  I suggest this query:

SELECT rowid FROM sqlite_master LIMIT 1;


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLState error codes from SQLite

[Felipe Sere]

Hi everyone,

I am starting to use SQL but I can not seem to find any good  
documentation on the SQLStates.
My little personal project is implemented in Java and I would like to  
handle the SQLExceptions correctly.
To do that I have to interpret the SQLState, but all I found is  
somehow locked down in the mailing list archives.

Could someone point me to a proper source?

Please keep me in CC as I am not subscribed to the list.

Thank you very much,

Felipe Sere

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compilation : undefined symbols.

[D. Richard Hipp]

On Mar 10, 2009, at 11:42 PM, Mayura S. wrote:

> Hello Sir,
>
> I downloaded sqlite 3.6.11 code for my project in my organisation.
> I'm not using the amalgamation code.  I'm building the source code  
> files in unix environment.
>
> I'm not new to sqlite, I have earlier worked on 3.2.2.
> My project need is very simple - to store and read data (persistent  
> data ).
> Hence there are no complex/conditional queries or sorting of any kind.
>
> I'm building the code with following switches, hence scaling down  
> Sqlite as much as possible to cater only store and read data.

Many of the OMIT options are incompatible with the amalgamation.  If  
you want to use the OMIT options, you must build from canonical  
sources.  Please see the 3rd paragraph at 
file://localhost/Users/drh/sqlite/website/bld/doc/compile.html 
#omitfeatures
>
>
> -DSQLITE_OS_OTHER=1
> -DSQLITE_THREADSAFE=0
> -DSQLITE_TEMP_STORE=3
> -DSQLITE_OMIT_AUTHORIZATION
> -DSQLITE_OMIT_AUTOINIT
> -DSQLITE_OMIT_BETWEEN_OPTIMIZATION
> -DSQLITE_OMIT_BLOB_LITERAL
> -DSQLITE_OMIT_BUILTIN_TEST
> -DSQLITE_OMIT_CHECK
> -DSQLITE_OMIT_COMPLETE
> -DSQLITE_OMIT_DATETIME_FUNCS
> -DSQLITE_OMIT_DECLTYPE
> -DSQLITE_OMIT_DEPRECATED
> -DSQLITE_OMIT_FLAG_PRAGMAS
> -DSQLITE_OMIT_FLOATING_POINT
> -DSQLITE_OMIT_GET_TABLE
> -DSQLITE_OMIT_INCRBLOB
> -DSQLITE_OMIT_INTEGRITY_CHECK
> -DSQLITE_OMIT_LIKE_OPTIMIZATION
> -DSQLITE_OMIT_LOAD_EXTENSION
> -DSQLITE_OMIT_LOCALTIME
> -DSQLITE_OMIT_PAGER_PRAGMAS
> -DSQLITE_OMIT_PROGRESS_CALLBACK
> -DSQLITE_OMIT_SCHEMA_PRAGMAS
> -DSQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
> -DSQLITE_OMIT_SHARED_CACHE
> -DSQLITE_OMIT_SUBQUERY
> -DSQLITE_OMIT_TCL_VARIABLE
> -DSQLITE_OMIT_TRACE
> -DSQLITE_OMIT_TRUNCATE_OPTIMIZATION
> -DSQLITE_OMIT_UTF16
> -DSQLITE_OMIT_XFER_OPT
> -DSQLITE_OMIT_ALTERTABLE
> -DSQLITE_OMIT_ANALYZE
> -DSQLITE_OMIT_ATTACH
> -DSQLITE_OMIT_AUTOINCREMENT
> -DSQLITE_OMIT_AUTOVACUUM
> -DSQLITE_OMIT_CAST
> -DSQLITE_OMIT_COMPOUND_SELECT
> -DSQLITE_OMIT_CONFLICT_CLAUSE
> -DSQLITE_OMIT_EXPLAIN
> -DSQLITE_OMIT_FOREIGN_KEY
> -DSQLITE_OMIT_PRAGMA
> -DSQLITE_OMIT_REINDEX
> -DSQLITE_OMIT_TEMPDB
> -DSQLITE_OMIT_VACUUM
> -DSQLITE_OMIT_VIEW
> -DSQLITE_OMIT_VIRTUALTABLE
> -DSQLITE_SYSTEM_MALLOC
>
>
> ... but I'm  getting the following symbols as Undefined.
>
> Error: L6218E: Undefined symbol sqlite3CreateView (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3Vacuum (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3Pragma (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3Attach (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3Detach (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3Reindex (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3Analyze (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3AlterRenameTable (referred  
> from sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3AlterFinishAddColumn  
> (referred from sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3AlterBeginAddColumn (referred  
> from sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3VtabFinishParse (referred  
> from sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3VtabBeginParse (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3VtabArgInit (referred from  
> sqlite.ptl).
> Error: L6218E: Undefined symbol sqlite3VtabArgExtend (referred from  
> sqlite.ptl)
>
>
> The above symbols are not available in the '#else' code of any  
> compiler switch. Most of the compiler switch do not have any '#else'  
> code.
> Please guide me on resolving these symbols.  All the above symbols  
> are used in parse.c file.
> I believe parse.c is a generated file.
> Should I make changes in parse.c file ? OR
> Should I stub those symbols in '#else' code ?
>
> Thanks in advance.
> Mayura
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"

[manohar s]

Hi,
 I am trying to execute "PRAGMA page_size=4096; Vacuum;" on a SQLite DB(Size
1.5 GB), On a drive which has 9 GB free space (But my C: has 150 MB free is
this an issue?). But it is failing with "SQL Error:Database or disk is full"
error everytime. SQLite version: 3.6.11.

1) Am I missing anything here?

Regards,
manohar.S
-- 
hope is the only thing which keeps us all happy
http://sqlyog.wordpress.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users