[sqlite] Question about expected query result??

2004-05-13 Thread Shawn Anderson
I have the following table/data

ClientIPClientDomain
63.149.28.33mail.serverlocation.com
63.149.28.33mail.serverlocation.com
63.149.28.33mail.serverlocation.com
63.149.28.33mail.serverlocation.com
211.141.67.7xmailserver.org
211.141.67.7xmailserver.org
211.141.67.7xmailserver.org
68.80.189.111   ravensorb
128.205.7.58acsu.buffalo.edu
66.35.250.206   sc8-sf-list1.sourceforge.net
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
128.205.7.57acsu.buffalo.edu
68.85.92.99 eye-catcher.com

When I run the following query, I get back 0 results -- anyone have any
thoughts? I am expecting to get back 3 records.

SELECT 
DISTINCT ClientIP, ClientDomain, COUNT(ClientDomain) AS
ClientDomainCount
FROM 
SMTPLog 
WHERE 
ClientDomainCount > 1 
GROUP BY 
ClientIP
ORDER BY 
ClientDomainCount DESC, ClientDomain ASC


RE: [sqlite] sqlite with Visual Basic

2004-05-13 Thread Greg Obleshchuk


Hi Carlos,
Sorry mate I didn't point out the wrappers on Sqlite.Org
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Greg 

> -Original Message-
> From: Jalil Vaidya [mailto:[EMAIL PROTECTED] 
> Sent: Friday, 14 May 2004 11:13 AM
> To: Carlos Garces; [EMAIL PROTECTED]
> Subject: Re: [sqlite] sqlite with Visual Basic
> 
> There is a VBWrapper.zip in the old SQLite yahoo group's file 
> section. The archive contains wrapper over the SQLite API so 
> that it can be used from VB.
> The API declarations for VB are also in the archieve.
> Get it from here:
> 
> http://f4.grp.yahoofs.com/v1/EBqkQKgMWwt8clzVakFnZ6GAGVancQ9q-
> 4gKNXEFX9QQmKIvHaVaTJ9rp1fz-XHaFxRIq46etnp1v_WDUrQPABeSNps/VB%
> 20Wrapper
> 
> If you cannot download from the link above then you will have 
> to join the group to get it.
> 
> HTH,
> 
> Jalil Vaidya
> 
> Disclaimer: I have never used this wrapper myself.
> 
> --- Carlos Garces <[EMAIL PROTECTED]> wrote:
> > Hi!
> > I can use SQLLite with Visual Basic without using other 
> external DLL 
> > Any sample of using sqlite.dll API?
> > 
> > Thanks
> > Carlos Garcis
> 
> 
> =
> 01001010
> 0111
> 01101100
> 01101001
> 01101100
> 
> 
>   
>   
> __
> Do you Yahoo!?
> Yahoo! Movies - Buy advance tickets for 'Shrek 2'
> http://movies.yahoo.com/showtimes/movie?mid=1808405861 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] sqlite with Visual Basic

2004-05-13 Thread Jalil Vaidya
There is a VBWrapper.zip in the old SQLite yahoo
group's file section. The archive contains wrapper
over the SQLite API so that it can be used from VB.
The API declarations for VB are also in the archieve.
Get it from here:

http://f4.grp.yahoofs.com/v1/EBqkQKgMWwt8clzVakFnZ6GAGVancQ9q-4gKNXEFX9QQmKIvHaVaTJ9rp1fz-XHaFxRIq46etnp1v_WDUrQPABeSNps/VB%20Wrapper

If you cannot download from the link above then you
will have to join the group to get it.

HTH,

Jalil Vaidya

Disclaimer: I have never used this wrapper myself.

--- Carlos Garces <[EMAIL PROTECTED]> wrote:
> Hi!
> I can use SQLLite with Visual Basic without using
> other external DLL
> Any sample of using sqlite.dll API?
> 
> Thanks
> Carlos Garcés


=
01001010
0111
01101100
01101001
01101100




__
Do you Yahoo!?
Yahoo! Movies - Buy advance tickets for 'Shrek 2'
http://movies.yahoo.com/showtimes/movie?mid=1808405861 

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[sqlite] sqlite with Visual Basic

2004-05-13 Thread Carlos Garces
Hi!
I can use SQLLite with Visual Basic without using other external DLL
Any sample of using sqlite.dll API?

Thanks
Carlos Garcés


.



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Safety of VACUUM?

2004-05-13 Thread Christian Smith
On Thu, 13 May 2004, David Given wrote:

>
>I'm running sqlite 0.8.6 on a Debian stable build. I have no idea whether it's
>been compiled with the thread-safety flag or not, there are no release notes.


Not sure whether that's a typo or not, but testing has 2.8.13 available,
so you may want to upgrade to the latest version of sqlite and try again.


>
>Is this expected behaviour? If so, why doesn't the documentation warn people
>that VACUUM is not thread-safe? And if not, why is my program falling over?
>

Probably not a threading issue, as the VACUUM is done in another process
(the sqlite shell.) You may be using a buggy version of sqlite (see
above.)


Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



[sqlite] Safety of VACUUM?

2004-05-13 Thread David Given
I have a program (spey, http://spey.sf.net, an email spam filter) that 
maintains a database of recently seen email addresses using Sqlite. Everyone 
works very nicely. Periodically a process runs that purges the database of 
stale addresses. This is just a little script that runs the sqlite shell 
command with a fixed SQL script.

The last thing on the script is VACUUM, which I put in to shrink the database.

However, running VACUUM seems to confuse the main program. After vacuuming, 
the next time the main program tries to touch the database, it gets an error 
back (data access error, error number -1). It seems that VACUUM causes any 
existing database handles to become stale. Removing the call to VACUUM makes 
everything work fine.

I'm running sqlite 0.8.6 on a Debian stable build. I have no idea whether it's 
been compiled with the thread-safety flag or not, there are no release notes.

Is this expected behaviour? If so, why doesn't the documentation warn people 
that VACUUM is not thread-safe? And if not, why is my program falling over?

-- 
+- David Given --McQ-+ "Opportunity is missed by most people because it's
|  [EMAIL PROTECTED]| dressed in overalls and looks like work." ---
| ([EMAIL PROTECTED]) | Thomas Edison
+- www.cowlark.com --+ 

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread basil . thomas
Yep. basically our "type less" string fields should have user definable
operator overload functions. Sounds like a big change that I doubt DRH would
implement anytime soon but it would definitely solve some of these
integer/numeric/string/datetime/etc.. conversion/comparisons. We would also
have to agree on what would constitute a constant custom data type
declaration. 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:41 AM
To: Thomas, Basil
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?







Not only comparison, but all expressions - arithmetic etc.



   
 [EMAIL PROTECTED] 
 com   
To 
 13/05/2004 16:33  [EMAIL PROTECTED],  
   [EMAIL PROTECTED]  
cc 
   [EMAIL PROTECTED] 
   Subject 
   RE: [sqlite] SQLite version 3   
   design question:  '500'=500?
   
   
   
   
   
   




I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?







Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.




 "Fred Williams"
 <[EMAIL PROTECTED]
 on.net>To
   <[EMAIL PROTECTED]>
 13/05/2004 15:12   cc
   <[EMAIL PROTECTED]>
   Subject
   RE: [sqlite] SQLite version 3
   design question:  '500'=500?










I guess you read different books than I.  The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things.  Like all languages it evolved, and
has not remained "simple."

Fred

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread VTenneti





Not only comparison, but all expressions - arithmetic etc.



   
 [EMAIL PROTECTED] 
 com   
To
 13/05/2004 16:33  [EMAIL PROTECTED],  
   [EMAIL PROTECTED]  
cc
   [EMAIL PROTECTED] 
   Subject
   RE: [sqlite] SQLite version 3   
   design question:  '500'=500?  
   
   
   
   
   
   




I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?







Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.




 "Fred Williams"
 <[EMAIL PROTECTED]
 on.net>To
   <[EMAIL PROTECTED]>
 13/05/2004 15:12   cc
   <[EMAIL PROTECTED]>
   Subject
   RE: [sqlite] SQLite version 3
   design question:  '500'=500?










I guess you read different books than I.  The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things.  Like all languages it evolved, and
has not remained "simple."

Fred

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


This e-mail may
be privileged and/or confidential, and the sender does not waive any
related rights and obligations. Any distribution, use or copying of this
e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne
renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion,
utilisation 

RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread VTenneti





Ditto that.



   
 [EMAIL PROTECTED] 
 com   
To
 13/05/2004 16:33  [EMAIL PROTECTED],  
   [EMAIL PROTECTED]  
cc
   [EMAIL PROTECTED] 
   Subject
   RE: [sqlite] SQLite version 3   
   design question:  '500'=500?  
   
   
   
   
   
   




I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?







Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.




 "Fred Williams"
 <[EMAIL PROTECTED]
 on.net>To
   <[EMAIL PROTECTED]>
 13/05/2004 15:12   cc
   <[EMAIL PROTECTED]>
   Subject
   RE: [sqlite] SQLite version 3
   design question:  '500'=500?










I guess you read different books than I.  The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things.  Like all languages it evolved, and
has not remained "simple."

Fred

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


This e-mail may
be privileged and/or confidential, and the sender does not waive any
related rights and obligations. Any distribution, use or copying of this
e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne
renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion,
utilisation ou copie de ce message ou des renseignements 

Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Doug Currie
Firebird 1.5

SQL> CREATE TABLE test1(a VARCHAR(100));
SQL> INSERT INTO test1 VALUES('501');
SQL> INSERT INTO test1 VALUES('  502  ');
SQL> SELECT * FROM test1 WHERE a=501;

A
===
501

SQL> SELECT * FROM test1 WHERE a=502;

A
===
  502

SQL> SELECT * FROM test1 WHERE a<'502';

A
===
501
  502

SQL> CREATE TABLE test2(b INTEGER);
SQL> INSERT INTO test2 VALUES(503);
SQL> INSERT INTO test2 VALUES(504);
SQL> SELECT * FROM test2 WHERE b='503';

   B


 503

SQL> SELECT * FROM test2 WHERE b>'503';

   B


 504

e

Thursday, May 13, 2004, 4:42:24 AM, DRH wrote:

> George Ionescu wrote:
>> 
>> However, wanting to test how the engine compares strings and numbers:
>> 
>> SELECT 'match' WHERE '500' = 500;
>> 
>> returns 'match'; also, the following statements return the same result:
>> 
>> SELECT 'match' WHERE '500' = 500;
>> SELECT 'match' WHERE '500' = 499 + 1;
>> 

> Who can tell me what other SQL database engines do with
> the following?

> CREATE TABLE test1(a VARCHAR(100));
> INSERT INTO test1 VALUES('501');
> INSERT INTO test1 VALUES('  502  ');
> SELECT * FROM test1 WHERE a=501;
> SELECT * FROM test1 WHERE a=502;
> SELECT * FROM test1 WHERE a<'502';

> Or how about this:

> CREATE TABLE test2(b INTEGER);
> INSERT INTO test2 VALUES(503);
> INSERT INTO test2 VALUES(504);
> SELECT * FROM test2 WHERE b='503';
> SELECT * FROM test2 WHERE b>'503';




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Peter Boehm
> Who can tell me what other SQL database engines do with
> the following?
>
>CREATE TABLE test1(a VARCHAR(100));
>INSERT INTO test1 VALUES('501');
>INSERT INTO test1 VALUES('  502  ');
>SELECT * FROM test1 WHERE a=501;
>SELECT * FROM test1 WHERE a=502;
>SELECT * FROM test1 WHERE a<'502';
Gupta's SQLBase:

TABLE CREATED

1 ROW INSERTED

1 ROW INSERTED

A

501
1 ROW SELECTED

A

0 ROWS SELECTED

A

501
  502
2 ROWS SELECTED

> Or how about this:
>
>CREATE TABLE test2(b INTEGER);
>INSERT INTO test2 VALUES(503);
>INSERT INTO test2 VALUES(504);
>SELECT * FROM test2 WHERE b='503';
>SELECT * FROM test2 WHERE b>'503';
>
>
Gupta's SQLBase:

TABLE CREATED

1 ROW INSERTED

1 ROW INSERTED

  B
===
503
1 ROW SELECTED

  B
===
504
1 ROW SELECTED

Peter

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread Fred Williams
I guess you read different books than I.  The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things.  Like all languages it evolved, and
has not remained "simple."

Fred

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread VTenneti





Funny, I thought SQL was a more down to earth version of E. F. Codd's
mathematical model 'Relational Algebra', with some concepts taken from
Relational Calculus.

To say types and strong typing weren't part of the original SQL concept
isn't right. Typing gives data semantics.

Vijay




   
 "Fred Williams"   
 <[EMAIL PROTECTED] 
 on.net>To 
   "Darren Duncan" 
 13/05/2004 14:23  <[EMAIL PROTECTED]>,  
   <[EMAIL PROTECTED]>   
cc 
   
   Subject 
   RE: [sqlite] SQLite version 3   
   design question:  '500'=500?
   
   
   
   
   
   




I seem to remember many, many moons ago when studying SQL for the first
time, learning that SQL syntax treats everything as character data no
matter
what the DB column data type is.

Also, I think this was to make the SQL syntax more "natural English
language" like.  I believe the original intent of SQL syntax was to be
simple enough even management could understand it.  Naturally that didn't
work :-)

Fred

> -Original Message-
> From: Darren Duncan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 3:39 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] SQLite version 3 design question: '500'=500?
>
>
> At 8:19 PM -0400 5/12/04, D. Richard Hipp wrote:
> >The development team is making progress on SQLite version 3.0.
> >But we've run across an interesting puzzle.  What should be
> >returned by this:
> >
> > SELECT '500'=500;
> >
> >Is the result "0" or "1"?  In other words, what happens when
> >you compare a number to a string that looks like that number.
> >Are they equal or not?
>
> In my experience, strings are implicitely cast as numbers when used
> in a numerical context, which includes comparisons with either
> literal numbers or numerical columns.  So I vote for the above two
> items having the result "TRUE".
>
> Take this for example, pretending that "bar" is a numerical column
> that does not have a uniqueness constraint.
>
> INSERT INTO foo (bar) VALUES (5);
> INSERT INTO foo (bar) VALUES ('5');
>
> SELECT bar FROM foo WHERE bar = 5;
> SELECT bar FROM foo WHERE bar = '5';
>
> With most database engines that I'm aware of, both of the insert
> statements will work whether the column is a string or a number, and
> both select statements will return 2 rows (assuming table empty
> before we started).  That just seems normal.
>
> Also, such behaviour will benefit lazy programmers that generate SQL
> from code because they can just put '' around all literal values
> regardless of the data type, and it will just work.
>
> As for what the SQL standard says ...
>
> For reference:
> SQL-2003 Foundation, 6.12 "", p201
> SQL-2003 Foundation, 8.2 "", p375
>
...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread Felipe Lopes
I agree that compatibility is what counts... 

Felipe Lopes 

Em 12 May 2004, Shawn Anderson escreveu: 

>I agree, I would like to see compatibility with results from other SQL 
>engines... 
> 
>Shawn 
> 
>-Original Message- 
>From: Keith Herold [mailto:[EMAIL PROTECTED] 
>Sent: Wednesday, May 12, 2004 8:38 PM 
>To: [EMAIL PROTECTED] 
>Subject: RE: [sqlite] SQLite version 3 design question: '500'=500? 
> 
>In MS SQL 2000, through the query analyzer 
> 
> SELECT '500' = 500 
> 
>returns 500 . 
> 
> SELECT 500 = '500' 
> 
>returns 
> Server: Msg 170, Level 15, State 1, Line 1 
> Line 1: Incorrect syntax near '='. 
> 
>Beyond these, I have no preference on whether they are true or false; I am 
>less worried about what the programming languages say as far as PERL, etc. 
 
>I would rather the return look like the majority vote on what the 'other' 
>SQL engines/manufacturers do. 
> 
>--Keith 
> 
>> -Original Message- 
>> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
>> Sent: Wednesday, May 12, 2004 5:20 PM 
>> To: [EMAIL PROTECTED] 
>> Subject: [sqlite] SQLite version 3 design question: '500'=500? 
>> 
>> 
>> The development team is making progress on SQLite version 3.0. But 
>> we've run across an interesting puzzle. What should be returned by 
>> this: 
>> 
>> SELECT '500'=500; 
>> 
>> Is the result "0" or "1"? In other words, what happens when you 
>> compare a number to a string that looks like that number. 
>> Are they equal or not? 
>> 
>> Other languages return a mixture of results here. Strings and number 
>> compare equal in AWK, Perl, PHP, Tcl, and SQLite version 2. String 
>> and numbers are not equal in Python and Ruby. 
>> 
>> Based on my experience, I would choose to make strings and numbers 
>> equal. But there are complications to that approach in SQLite 3.0. 
>> SQLite 3 supports manifest typing with 4 basic types: NULL, NUMERIC, 
>> TEXT, and BLOB. Objects sort in that order: NULLs first, followed by 
>> NUMERICs in numerical order, then TEXT in a user-defined collating 
>> sequence and finally BLOBs in memcmp() order. So '500' occurs at a 
>> completely different place in the sort order from 500. If comparison 
>> operators are to be consistent with sort order, the following must be 
>> true: 
>> 
>> 500 < 600 
>> 600 < '500' 
>> 
>> But if that is the case, then clearly, '500' != 500. So unless 
>> somebody can come up with a better idea, SQLite version 3.0 will 
>> return "0" for the following: 
>> 
>> SELECT '500'=500; 
>> 
>> On the other hand, the following two statements will return "1" (or 
>> true): 
>> 
>> SELECT '500'+0=500; 
>> SELECT '500'=(500||''); 
>> 
>> Note that in other SQL engines, it is an error to compare a string to 
>> an integer (I think - somebody please correct me if I am wrong) so we 
>> cannot get any guidance there. 
>> 
>> Your thoughts? 
>> -- 
>> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 
>> 
>> 
>> - 
>> To unsubscribe, e-mail: [EMAIL PROTECTED] 
>> For additional commands, e-mail: [EMAIL PROTECTED] 
>> 
> 
>- 
>To unsubscribe, e-mail: [EMAIL PROTECTED] 
>For additional commands, e-mail: [EMAIL PROTECTED] 
> 
>- 
>To unsubscribe, e-mail: [EMAIL PROTECTED] 
>For additional commands, e-mail: [EMAIL PROTECTED] 
> 
>-- 


Felipe Lopes. 

_
Voce quer um iGMail protegido contra vírus e spams? 
Clique aqui: http://www.igmailseguro.ig.com.br
Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



RE: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Jarosław Nozderko
Sybase ASE 12.5.1:

CREATE TABLE test1(a VARCHAR(100))
INSERT INTO test1 VALUES('501')
INSERT INTO test1 VALUES('  502  ')
SELECT * FROM test1 WHERE a=501
SELECT * FROM test1 WHERE a=502
SELECT * FROM test1 WHERE a<'502'

Result: "Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed."

CREATE TABLE test2(b INTEGER)
INSERT INTO test2 VALUES(503)
INSERT INTO test2 VALUES(504)
SELECT * FROM test2 WHERE b='503'
SELECT * FROM test2 WHERE b>'503'

Same as above. It requires explicit use of 'convert' function.

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
[EMAIL PROTECTED]
IT/CCBS/RS - Analyst Programmer
 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 10:42 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Re: SQLite version 3 design question: '500'=500?
> 
> 
> George Ionescu wrote:
> > 
> > However, wanting to test how the engine compares strings 
> and numbers:
> > 
> > SELECT 'match' WHERE '500' = 500;
> > 
> > returns 'match'; also, the following statements return the 
> same result:
> > 
> > SELECT 'match' WHERE '500' = 500;
> > SELECT 'match' WHERE '500' = 499 + 1;
> > 
> 
> Who can tell me what other SQL database engines do with
> the following?
> 
> CREATE TABLE test1(a VARCHAR(100));
> INSERT INTO test1 VALUES('501');
> INSERT INTO test1 VALUES('  502  ');
> SELECT * FROM test1 WHERE a=501;
> SELECT * FROM test1 WHERE a=502;
> SELECT * FROM test1 WHERE a<'502';
> 
> Or how about this:
> 
> CREATE TABLE test2(b INTEGER);
> INSERT INTO test2 VALUES(503);
> INSERT INTO test2 VALUES(504);
> SELECT * FROM test2 WHERE b='503';
> SELECT * FROM test2 WHERE b>'503';
> 
> 
> -- 
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> 
> 
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> 
> 

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Fw: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Roy Black
Hello,

In mysql:
mysql> SELECT * FROM test1 WHERE a=501;
+--+
| a|
+--+
| 501  |
+--+
1 row in set (0.02 sec)

mysql> SELECT * FROM test1 WHERE a=502;
+---+
| a |
+---+
|   502 |
+---+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE a<'502';
+---+
| a |
+---+
| 501   |
|   502 |
+---+
2 rows in set (0.00 sec)

and for the second table:
mysql> SELECT * FROM test2 WHERE b='503';
+--+
| b|
+--+
|  503 |
+--+
1 row in set (0.09 sec)

mysql> SELECT * FROM test2 WHERE b>'503';
+--+
| b|
+--+
|  504 |
+--+
1 row in set (0.00 sec)

Regards,

- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, May 13, 2004 1:42 AM
Subject: Re: [sqlite] Re: SQLite version 3 design question: '500'=500?


Who can tell me what other SQL database engines do with
the following?

CREATE TABLE test1(a VARCHAR(100));
INSERT INTO test1 VALUES('501');
INSERT INTO test1 VALUES('  502  ');
SELECT * FROM test1 WHERE a=501;
SELECT * FROM test1 WHERE a=502;
SELECT * FROM test1 WHERE a<'502';

Or how about this:

CREATE TABLE test2(b INTEGER);
INSERT INTO test2 VALUES(503);
INSERT INTO test2 VALUES(504);
SELECT * FROM test2 WHERE b='503';
SELECT * FROM test2 WHERE b>'503';


-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread Darren Duncan
I have another suggestion which may help with the comparison for 
sorting issue, but you may find it a bit outrageous.

Essentially, I suggest making SQLite a little bit less typeless.

You already have multiple underlying data type representations as I 
recall, such as numbers, character strings, and binary strings. 
Currently, this type is bound to each individual value stored in a 
table column.

What I suggest instead is have typed columns where all values stored 
in that column are represented in the same way; eg numbers or 
strings.  You would have as many possible column types as you have 
underlying representations.  If you do this, then you no longer have 
any problem in figuring out how to sort.  All values in a column 
declared as a character string will sort as their character 
representations.  Likewise, all values in a number column will sort 
as numbers.  You will not have to cast between a string and a number 
when sorting.

Now, even with "typed" columns as I mention, they are still largely 
typeless compared to other databases.  Fields are not fixed width, do 
not have maximum lengths, are stored internally as character strings, 
have a distinct null value, and so on.

When a literal string is compared to a column, or assigned to it, 
then the literal will be implicitly cast to the same data type as the 
column.

Such a solution should be more or less backwards compatible, quite 
easy and/or simple to implement, and simplify a number of things.  It 
also provides a bit more strictness for those of us who want it. 
(For example, if I declare a numerical column, I would be happy for 
all values in that column to be treated like numbers regardless of 
the syntax used to insert the values.)

The main drawback I see is that this change may break some 
applications which assume numbers-treated-as-numbers and 
characters-treated-as-characters are stored in the same column at the 
same time.  But then again, I can't think of any real world example 
where this would be the case.

So what do you think?

-- Darren Duncan

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


RE: [sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread Peter Pistorius
I think it should return 0.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread Darren Duncan
At 8:19 PM -0400 5/12/04, D. Richard Hipp wrote:
The development team is making progress on SQLite version 3.0.
But we've run across an interesting puzzle.  What should be
returned by this:
SELECT '500'=500;

Is the result "0" or "1"?  In other words, what happens when
you compare a number to a string that looks like that number.
Are they equal or not?
In my experience, strings are implicitely cast as numbers when used 
in a numerical context, which includes comparisons with either 
literal numbers or numerical columns.  So I vote for the above two 
items having the result "TRUE".

Take this for example, pretending that "bar" is a numerical column 
that does not have a uniqueness constraint.

INSERT INTO foo (bar) VALUES (5);
INSERT INTO foo (bar) VALUES ('5');
SELECT bar FROM foo WHERE bar = 5;
SELECT bar FROM foo WHERE bar = '5';
With most database engines that I'm aware of, both of the insert 
statements will work whether the column is a string or a number, and 
both select statements will return 2 rows (assuming table empty 
before we started).  That just seems normal.

Also, such behaviour will benefit lazy programmers that generate SQL 
from code because they can just put '' around all literal values 
regardless of the data type, and it will just work.

As for what the SQL standard says ...

For reference:
SQL-2003 Foundation, 6.12 "", p201
SQL-2003 Foundation, 8.2 "", p375
8.2 Syntax Rule #3 says:
	The declared types of the corresponding fields of the two 
s shall be comparable.

6.12 Syntax Rule #6 says that a comparison between numeric data 
(EN,AN) and character data (C,VC,FC,CL) is syntactically valid 
without restriction (Y).

There are probably more references to this matter, which may be 
important, but it looks to me that the standard says you can compare 
numbers and strings which look like them.

At 11:44 PM -0700 5/12/04, Kevin Van Vechten wrote:
Another interesting data point, note the difference between ' and ".
Welcome to psql 7.4, the PostgreSQL interactive terminal.
kevin=# select '500'=500;
 ?column?
--
 t
(1 row)
kevin=# select "500"=500;
ERROR:  column "500" does not exist
Were you surprised?

Both the SQL standard and most SQL implementations that I know of 
always treat '' as literal strings and "" as optional identifier 
delimiters (their presence makes the identifier name case-sensitive, 
whereas a bareword identifier is case-insensitive, and delimited 
identifiers can also contain nearly any character, including 
whitespace) such as column or table names.

For reference:
SQL-2003 Foundation, 5.3 "", p143
SQL-2003 Foundation, 5.4 "Names and identifiers", p151
SQL-2003 Foundation, 6.6 "", p183
Mind you, MySQL is a bit different in that it uses `` as the 
identifier delimiter, unless that's just an option in addition to "". 
Technically, I think, the SQL standard says you can use almost 
anything as the delimiter, though "" is still the convention.

So '' only should be a string literal.  See also B'' for quoting bit 
strings as ones and zeros, and X'' for quoting binary strings as 
hexits, says the standard.

In case you want to do this research yourself, copies of the entire 
almost-final (indistinguishable delta) SQL-2003 spec are here in PDF 
form, from which I quote (and treat as gospel):
http://www.wiscorp.com/SQLStandards.html

-- Darren Duncan

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


[sqlite] Attach database

2004-05-13 Thread Jérôme VERITE
While trying to attach a readonly database to another one, I get the message
« root page number less than 2 “, It reproduces itself almost each time.

 

I precise the database which has to be attached is a readonly one.

 

Do you have some idea ?

 

Thanks a lot, and excuse me for my english.

 

 

PS: I am unable to create a view from the main database on tables which are
in an attached database. Is it normal ?

 

Jérôme.

 



[sqlite] Re: SQLite version 3 design question: '500'=500?

2004-05-13 Thread George Ionescu
Hello Dr. Hipp,
Hello SQLite users,

in MS SQL Server, the following line

SELECT '500' = 500;

returns a column having the alias '500' and the value 500 :-o

However, wanting to test how the engine compares strings and numbers:

SELECT 'match' WHERE '500' = 500;

returns 'match'; also, the following statements return the same result:

SELECT 'match' WHERE '500' = 500;
SELECT 'match' WHERE '500' = 499 + 1;

Hope I've been of some help.

Regards,
George Ionescu

RE: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread Jarosław Nozderko
Similar for Sybase ASE 12.5 (wasn't it MSSQL 
ancestor ?):

SELECT '500' = 500

500
===
500


SELECT 500 = '500'

Incorrect syntax near '='

I think "SELECT '500' = 500" is not a comparison here,
just selection of constant 500 with '500' as column
name, just like "select 500 as '500'".


Regards,
Jarek

> -Original Message-
> From: Shawn Anderson [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 2:50 AM
> To: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
> I agree, I would like to see compatibility with results from other SQL
> engines...
>
> Shawn 
>
> -Original Message-
> From: Keith Herold [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 12, 2004 8:38 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
> In MS SQL 2000, through the query analyzer
>
>SELECT '500' = 500
>
> returns 500 .
>
>SELECT 500 = '500'
>
> returns
>   Server: Msg 170, Level 15, State 1, Line 1
>   Line 1: Incorrect syntax near '='.
>
> Beyond these, I have no preference on whether they are true
> or false; I am
> less worried about what the programming languages say as far
> as PERL, etc. .
> I would rather the return look like the majority vote on what
> the 'other'
> SQL engines/manufacturers do.
>
> --Keith
>
> > -Original Message-
> > From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, May 12, 2004 5:20 PM
> > To: [EMAIL PROTECTED]
> > Subject: [sqlite] SQLite version 3 design question: '500'=500?
> >
> >
> > The development team is making progress on SQLite version 3.0. But
> > we've run across an interesting puzzle.  What should be returned by
> > this:
> >
> >  SELECT '500'=500;
> >
> > Is the result "0" or "1"?  In other words, what happens when you
> > compare a number to a string that looks like that number.
> > Are they equal or not?
> >
> > Other languages return a mixture of results here.  Strings
> and number
> > compare equal in AWK, Perl, PHP, Tcl, and SQLite version 2.  String
> > and numbers are not equal in Python and Ruby.
> >
> > Based on my experience, I would choose to make strings and numbers
> > equal.  But there are complications to that approach in
> SQLite 3.0. 
> > SQLite 3 supports manifest typing with 4 basic types: NULL,
> NUMERIC,
> > TEXT, and BLOB.  Objects sort in that order:  NULLs first,
> followed by
> > NUMERICs in numerical order, then TEXT in a user-defined collating
> > sequence and finally BLOBs in memcmp() order.  So '500' occurs at a
> > completely different place in the sort order from 500. If
> comparison
> > operators are to be consistent with sort order, the
> following must be
> > true:
> >
> >  500 < 600
> >  600 < '500'
> >
> > But if that is the case, then clearly, '500' != 500.  So unless
> > somebody can come up with a better idea, SQLite version 3.0 will
> > return "0" for the following:
> >
> > SELECT '500'=500;
> >
> > On the other hand, the following two statements will return "1" (or
> > true):
> >
> > SELECT '500'+0=500;
> > SELECT '500'=(500||'');
> >
> > Note that in other SQL engines, it is an error to compare a
> string to
> > an integer (I think - somebody please correct me if I am
> wrong) so we
> > cannot get any guidance there.
> >
> > Your thoughts?
> > --
> > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> >
> >
> >
> -
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
> 

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] SQLite version 3 design question: '500'=500?

2004-05-13 Thread Kevin Van Vechten
Another interesting data point, note the difference between ' and ".

Welcome to psql 7.4, the PostgreSQL interactive terminal.
kevin=# select '500'=500;
 ?column?
--
 t
(1 row)
kevin=# select "500"=500;
ERROR:  column "500" does not exist
On May 12, 2004, at 5:19 PM, D. Richard Hipp wrote:

Note that in other SQL engines, it is an error to compare
a string to an integer (I think - somebody please correct
me if I am wrong) so we cannot get any guidance there.
Your thoughts?


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]