date:20080121

Hi All,
I still had the problem below:
> sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
> 897./src/vdbeapi.c: No such file or directory.
>in ./src/vdbeapi.c
> Current language:  auto; currently c

when I used sqlite3_bind_int. I couldn't figure out what is the problem and how 
to fit it. It used to work last week.
Could you please help.
Thanks,
JO


- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 4:38:52 PM
Subject: Re: [sqlite] Program is crahed on sqlite3_bind_int

Thanks!! So how to fix this problem..
-JP



- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 3:11:52 PM
Subject: Re: [sqlite] Program is crahed on sqlite3_bind_int

It is accessing a mutex.  Looks like you have something wrong with a 
shared library or DLL.

Joanne Pham wrote:
> Hi All,
> The exactly error message below:
> sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
> 897./src/vdbeapi.c: No such file or directory.
>in ./src/vdbeapi.c
> Current language:  auto; currently c
> Thanks,
> JP
> 
> 
> - Original Message 
> From: Joanne Pham <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Monday, January 21, 2008 2:33:37 PM
> Subject: [sqlite] Program is crahed on sqlite3_bind_int
> 
> Hi All,
> I am using sqlite 3.5.2 and Suddenly my program is crashed on 
> sqlite3_bind_int command 
> The error message from the crash is :
> 
> sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
> 897./src/vdbeapi.c: No such file or directory.
>in ./src/vdbeapi.c
> 
> I had no clue what is going on and Don't know how to fix this problem.
> Do you know what is problem with sqlite3_bind_int? It used to work last week 
> and suddenly it didn't work any more.
> Your help is greatly appreciated
> JP
> 
> 
>  
> 
> Never miss a thing.  Make Yahoo your home page. 
> http://www.yahoo.com/r/hs
> 
> 
>  
> 
> Looking for last minute shopping deals?  
> Find them fast with Yahoo! Search.  
> http://tools.search.yahoo.com/newsearch/category.php?category=shopping


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.   
http://tools.search.yahoo.com/newsearch/category.php?category=shopping


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Re: [sqlite] Program is crahed on sqlite3_bind_int

Thanks!! So how to fix this problem..
-JP



- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 3:11:52 PM
Subject: Re: [sqlite] Program is crahed on sqlite3_bind_int

It is accessing a mutex.  Looks like you have something wrong with a 
shared library or DLL.

Joanne Pham wrote:
> Hi All,
> The exactly error message below:
> sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
> 897./src/vdbeapi.c: No such file or directory.
>in ./src/vdbeapi.c
> Current language:  auto; currently c
> Thanks,
> JP
> 
> 
> - Original Message 
> From: Joanne Pham <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Monday, January 21, 2008 2:33:37 PM
> Subject: [sqlite] Program is crahed on sqlite3_bind_int
> 
> Hi All,
> I am using sqlite 3.5.2 and Suddenly my program is crashed on 
> sqlite3_bind_int command 
> The error message from the crash is :
> 
> sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
> 897./src/vdbeapi.c: No such file or directory.
>in ./src/vdbeapi.c
> 
> I had no clue what is going on and Don't know how to fix this problem.
> Do you know what is problem with sqlite3_bind_int? It used to work last week 
> and suddenly it didn't work any more.
> Your help is greatly appreciated
> JP
> 
> 
>  
> 
> Never miss a thing.  Make Yahoo your home page. 
> http://www.yahoo.com/r/hs
> 
> 
>  
> 
> Looking for last minute shopping deals?  
> Find them fast with Yahoo! Search.   
> http://tools.search.yahoo.com/newsearch/category.php?category=shopping


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

Re: [sqlite] How to create a database in run-time

2008-01-21 Thread John Stanton


Luis Esteban Fajardo Bravo wrote:
Hi! we've using SQLite on our software since about a month ago, and 
still i have some questions about how does sqlite works at all.


I have the following sourcecode to check if a database is already 
created, if yes then just open, if not, open it and create the 
appropiate schema for the database:


if ( sqlite3_open_v2(path,&historydb,SQLITE_OPEN_READWRITE,NULL) != 
SQLITE_OK) {


   // Create the database

   sqlite3_close(historydb);

   if (sqlite3_open(path,&historydb) == SQLITE_OK) {

   // Now we create the default tables

   strcpy(query,"create table parameters (param_id integer 
primary key, param_name text(100));create table data_values (timestamp 
integer(4), value real, param_id integer(2));");


   rc = sqlite3_exec(historydb,query,process_query,NULL,&errmsg);

   if (rc == SQLITE_OK) {

   strcpy(query,"pragma default_cache_size=40;");

   rc=sqlite3_exec(historydb,query,process_query,NULL,&errmsg);

   }

   if (rc != SQLITE_OK) {

   // An error ocurred during database creation

  printf("SQL Error: ",errmsg);

   sqlite3_free(errmsg);

   return 0;

   }

   } else {

   // Unable to create database

  return 0;

   }

   }


This works fine under linux, and in windows if i run it onto gdb, but 
not if i call my object code from the command prompt, my question is if 
there's a better solution for check if the database is already created, 
something like a "describe" SQL Command (in oracle) that help to know if 
the schema is already there on the database?


Thank you!

You can use an "access" call to see if the file exists.  If you open it 
to test for existence you can do a "magic" test and look for the first 
few bytes being "SQLite" to authenticate it as an Sqlite DB.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How to create a database in run-time

2008-01-21 Thread Luis Esteban Fajardo Bravo

Hi! we've using SQLite on our software since about a month ago, and 
still i have some questions about how does sqlite works at all.


I have the following sourcecode to check if a database is already 
created, if yes then just open, if not, open it and create the 
appropiate schema for the database:


if ( sqlite3_open_v2(path,&historydb,SQLITE_OPEN_READWRITE,NULL) != SQLITE_OK) {

   // Create the database

   sqlite3_close(historydb);

   if (sqlite3_open(path,&historydb) == SQLITE_OK) {

   // Now we create the default tables

   strcpy(query,"create table parameters (param_id integer primary key, 
param_name text(100));create table data_values (timestamp integer(4), value real, 
param_id integer(2));");

   rc = sqlite3_exec(historydb,query,process_query,NULL,&errmsg);

   if (rc == SQLITE_OK) {

   strcpy(query,"pragma default_cache_size=40;");

   rc=sqlite3_exec(historydb,query,process_query,NULL,&errmsg);

   }

   if (rc != SQLITE_OK) {

   // An error ocurred during database creation

  printf("SQL Error: ",errmsg);

   sqlite3_free(errmsg);

   return 0;

   }

   } else {

   // Unable to create database

  return 0;

   }

   }


This works fine under linux, and in windows if i run it onto gdb, but 
not if i call my object code from the command prompt, my question is if 
there's a better solution for check if the database is already created, 
something like a "describe" SQL Command (in oracle) that help to know if 
the schema is already there on the database?


Thank you!

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Program is crahed on sqlite3_bind_int

2008-01-21 Thread John Stanton

It is accessing a mutex.  Looks like you have something wrong with a 
shared library or DLL.


Joanne Pham wrote:

Hi All,
The exactly error message below:
sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897 ./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c
Current language:  auto; currently c
Thanks,
JP


- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 2:33:37 PM
Subject: [sqlite] Program is crahed on sqlite3_bind_int

Hi All,
I am using sqlite 3.5.2 and Suddenly my program is crashed on sqlite3_bind_int command 
The error message from the crash is :


sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c

I had no clue what is going on and Don't know how to fix this problem.
Do you know what is problem with sqlite3_bind_int? It used to work last week 
and suddenly it didn't work any more.
Your help is greatly appreciated
JP


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Program is crahed on sqlite3_bind_int

Hi All,
The exactly error message below:
sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897 ./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c
Current language:  auto; currently c
Thanks,
JP


- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 21, 2008 2:33:37 PM
Subject: [sqlite] Program is crahed on sqlite3_bind_int

Hi All,
I am using sqlite 3.5.2 and Suddenly my program is crashed on sqlite3_bind_int 
command 
The error message from the crash is :

sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c

I had no clue what is going on and Don't know how to fix this problem.
Do you know what is problem with sqlite3_bind_int? It used to work last week 
and suddenly it didn't work any more.
Your help is greatly appreciated
JP


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

[sqlite] Program is crahed on sqlite3_bind_int

Hi All,
I am using sqlite 3.5.2 and Suddenly my program is crashed on sqlite3_bind_int 
command 
The error message from the crash is :

sqlite3_bind_int64 (pStmt=0x40058a28, i=1, iValue=10) at ./src/vdbeapi.c:897
897 ./src/vdbeapi.c: No such file or directory.
in ./src/vdbeapi.c

I had no clue what is going on and Don't know how to fix this problem.
Do you know what is problem with sqlite3_bind_int? It used to work last week 
and suddenly it didn't work any more.
Your help is greatly appreciated
JP


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

[sqlite] propagate changes of schema to many shema-identical databases

2008-01-21 Thread Jean-Christophe Roux

Hello,
I have a pretty high number of sqlite databases, each of them with the same 
schema but different data. Once in a while, I need to change the schema and I 
am propagating that change through a bash script that takes for instance my 
alter table command an applies it to every targeted database file. 
Is there a better way to do that?
Thanks





  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

RE: [sqlite] Re: Access from Multiple Processes

2008-01-21 Thread James Dennett

(Top-posting and overquoting fixed.)

On Monday, January 21, 2008 1:57 PM, Mark Riehl wrote:
>
> On Jan 21, 2008 4:48 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> > Mark Riehl <[EMAIL PROTECTED]>
wrote:
> > > For some reason I haven't been able to track down, SQLite is
creating
> > > a journal file after the C++ process connects, however, the
journal
> > > file doesn't go away.
> >
> > Are you, by any chance, opening a transaction and keeping it open?
> >
> > Igor Tandetnik
>
> I'm just executing SQL insert statements.  I'm not using the BEGIN
> TRANSACTION; ... END TRANSACTION; wrappers around the inserts.
> 
> I thought that there was an implied BEGIN ...COMMIT around every
> INSERT statement?

There is.  And if your code has no active transactions, SQLite allows
other processes to access the database.  So something odd is happening,
but I don't think you've shared enough information for this list to
guess what.

-- James


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Access from Multiple Processes

2008-01-21 Thread Mark Riehl

I'm just executing SQL insert statements.  I'm not using the BEGIN
TRANSACTION; ... END TRANSACTION; wrappers around the inserts.

I thought that there was an implied BEGIN ...COMMIT around every
INSERT statement?

Mark

On Jan 21, 2008 4:48 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Mark Riehl <[EMAIL PROTECTED]> wrote:
> > For some reason I haven't been able to track down, SQLite is creating
> > a journal file after the C++ process connects, however, the journal
> > file doesn't go away.
>
> Are you, by any chance, opening a transaction and keeping it open?
>
> Igor Tandetnik
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Strange error "Incomplete SQL"

2008-01-21 Thread Dennis Cote


zak mc kracken wrote:

(I oversimplified the db to show the problem)

$ cat error
create table t(c);
select c from t; --COMMENT
$ sqlite < error
Incomplete SQL: select c from t; --COMMENT
$ sqlite -version
2.8.17

if I remove the '--COMMENT' there is no error... why?
the problem persist using /*COMMENT*/ style

  
This is a bug that was corrected in version 3.5.1 of SQLite (see 
http://www.sqlite.org/changes.html). It works as expected in current 
versions.


Do you really need to use version 2.8.17?

Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Access from Multiple Processes


Mark Riehl <[EMAIL PROTECTED]> wrote:

For some reason I haven't been able to track down, SQLite is creating
a journal file after the C++ process connects, however, the journal
file doesn't go away.


Are you, by any chance, opening a transaction and keeping it open?

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Access from Multiple Processes

2008-01-21 Thread Mark Riehl

I'm running version SQLite 3.5.2 under Linux on a small ARM processor
with a flash drive.

I've got a single-threaded C++ process that inserts data into an
SQLite database, and a series of PHP (v5.2.4) scripts using PDO that
insert to and read from the same database.  In terms of the
percentages, the C++ process is responsible for ~99% of the data going
into the database.  I open the connection to the SQLite database when
the C++ application starts, and close the connection when it shuts
down.  The PHP opens and closes the connection during each session.

For some reason I haven't been able to track down, SQLite is creating
a journal file after the C++ process connects, however, the journal
file doesn't go away.  At this point, the database is locked for the
PHP scripts and remains locked until I manually remove the journal
file.

Questions:
- Why would the journal file remain?
- Is there a problem leaving the connection open from the C++ process?
- Looking at the SQLite documentation, I see that there are 5 possible
locked states.  Is there a way for me to query the database (maybe via
sqlite3) to determine which state the database is in?

Thanks for the help,
Mark

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Strange error "Incomplete SQL"

2008-01-21 Thread Aristotle Pagaltzis

* Aristotle Pagaltzis <[EMAIL PROTECTED]> [2008-01-21 22:29]:
> $ echo -e '\n;' >> error
> $ sqlite < x
> $

Err, the 2nd line is of course

> $ sqlite < error

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Strange error "Incomplete SQL"

2008-01-21 Thread Aristotle Pagaltzis

* zak mc kracken <[EMAIL PROTECTED]> [2008-01-21 20:10]:
> $ cat error
> create table t(c);
> select c from t; --COMMENT
> $ sqlite < error
> Incomplete SQL: select c from t; --COMMENT
> $ sqlite -version
> 2.8.17
> 
> if I remove the '--COMMENT' there is no error... why?
> the problem persist using /*COMMENT*/ style

$ echo -e '\n;' >> error
$ sqlite < x
$

The sqlite shell doesn’t parse SQL, it just looks for a semicolon
as a statement terminator, so it sees your comment after it sees
the SELECT statement, but doesn’t find a terminator after that.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Strange error "Incomplete SQL"

2008-01-21 Thread zak mc kracken

(I oversimplified the db to show the problem)

$ cat error
create table t(c);
select c from t; --COMMENT
$ sqlite < error
Incomplete SQL: select c from t; --COMMENT
$ sqlite -version
2.8.17

if I remove the '--COMMENT' there is no error... why?
the problem persist using /*COMMENT*/ style

-- 
By ZeD

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite character comparisons

2008-01-21 Thread Dennis Cote


Fowler, Jeff wrote:

I may very well be mistaken - wouldn't be the first time!! I tried to
access the actual specification from ANSI (www.ansi.org), but you either
have to purchase them or access one of the "drafts" (which a couple
folks in this thread have done), but the draft may or may not match the
actual standard. While Dr. Hipp's new RTRIM collation makes this a moot
point, the source for my statement came from this article:
http://support.microsoft.com/kb/316626

  
After reading the MS article, I dug into the standard some more to see 
if I had missed something.


It seems there is a requirement that all "standard character set names" 
define a default collation that has the PAD SPACE property (see section 
10.6 general rule 2).


2) A  specifies the name of a character 
set that is defined by a
national or international standard. The character repertoire of CS, 
implied by the character set name>, are defined by the standard defining the 
character set identified by that
. The default collating sequence of the 
character set is defined
by the order of the characters in the standard and has the PAD SPACE 
characteristic.
There is a similar definition for an "implementation defined character 
set name" in general rule 3.
3) An  specifies the name 
of a character set that
is implementation-defined. The character repertoire of CS, implied by 
the character set name>, are implementation-defined. The default collating 
sequence of the
character set and whether the collating sequence has the NO PAD 
characteristic or the PAD

SPACE characteristic is implementation-defined.
But syntax rule 1 in that section seems to say that what is supported is 
implementation defined.
1) The s and character set name>s that

are supported are implementation-defined.
These are character sets, and predefined collations, can be used to 
create additional collations as described in section 11.32

 ::=
CREATE COLLATION  FOR 
FROM 
[  ]
 ::= 
 ::=
NO PAD
| PAD SPACE
If the pad characteristic is not given explicitly, it is inherited 
implicitly from the existing collation name.


So if an implementation provides a standard character set, such as 
SQL_TEXT, it must also have a default collation with the PAD SPACE 
property that is the default collation for strings in that character 
set. The user can create a new collation that explicitly overrides the 
pad characteristic and use that for NO PAD operation.


But ultimately there is no requirement to provide any standard character 
sets. So all bets are off when switching implementations (as Darren has 
suggested).


Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLite character comparisons

2008-01-21 Thread Fowler, Jeff

Hi Dennis,

I may very well be mistaken - wouldn't be the first time!! I tried to
access the actual specification from ANSI (www.ansi.org), but you either
have to purchase them or access one of the "drafts" (which a couple
folks in this thread have done), but the draft may or may not match the
actual standard. While Dr. Hipp's new RTRIM collation makes this a moot
point, the source for my statement came from this article:
http://support.microsoft.com/kb/316626


As I'd mentioned, we have both SQL Server 2005 and Oracle 10g and they
default to ignore trailing spaces on character compares, although it may
be possible to turn this off for those who don't like it. I believe (but
am not sure) that MYSql and PostGreSQL ignore them by default also. I
understand many of the arguments folks have against this and there's not
much point to continuing the discussion; I won't change their mind and
they won't change mine! But I guess that's why we have Republicans and
Democrats :-)

But all is well - the RTRIM collation option is a neat and simple
solution. My thanks to Dr. Hipp for that, and also to everyone who has
shown an interest in this issue.

- Jeff


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 21, 2008 11:46 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons

Fowler, Jeff wrote:
> Hello All,
>  
> Not trying to be antagonistic, but I'm curious to know how many of you
agree with Darren's sentiments on this issue. To restate briefly, ANSI
SQL-92 specifies that when comparing two character fields, trailing
spaces should be ignored. Correct me if I'm wrong Darren, but you feel
this is a bad decision, and in fact SQLite's implementation of character
comparison (respecting trailing spaces) is superior to ANSI's specs.
Keep in mind this is not some obscure issue that can be subject to
different interpretations by different vendors; it's very clearly
stated: "The ANSI standard requires padding for the character strings
used in comparisons so that their lengths match before comparing them."
>   
Jeff,

I think you are mistaken about what the ANSI spec says.

There are two string types in ANSI SQL, character strings (which come is
several subtypes), and binary strings. The following excerpts are taken
from the SQL:1999 spec.

Section 4.2.1 Character Strings and Collations describes the operations
on character strings. It describes comparisons as
> Given a collating sequence, two character strings are identical if and

> only if they are equal in accordance with the comparison rules 
> specified in Subclause 8.2, . The collating 
> sequence used for a particular comparison is determined as in 
> Subclause 4.2.3, ''Rules determining collating sequence usage''.
Binary strings are defined in Section 4.3 as;
> A binary string is a sequence of octets that does not have either a 
> character set or collation associated with it.
And their comparison is detailed in 4.3.1 as;
> All binary strings are mutually comparable. A binary string is 
> identical to another binary string if and only if it is equal to that 
> binary string in accordance with the comparison rules specified in 
> Subclause 8.2, .

General Rules 3 and 4 of section 8.2  describe the
comparison of these strings. I have copied these sections below.

> 3) The comparison of two character strings is determined as follows:
>
> a) Let CS be the collating sequence indicated in Subclause 4.2.3, 
> ''Rules determining collating sequence usage'', based on the declared 
> types of the two character strings.
>
> b) If the length in characters of X is not equal to the length in 
> characters of Y, then the shorter string is effectively replaced, for 
> the purposes of comparison, with a copy of itself that has been 
> extended to the length of the longer string by concatenation on the 
> right of one or more pad characters, where the pad character is chosen

> based on CS. If CS has the NO PAD characteristic, then the pad 
> character is an implementation-dependent character different from any 
> character in the character set of X and Y that collates less than any 
> string under CS. Otherwise, the pad character is a .
>
> c) The result of the comparison of X and Y is given by the collating 
> sequence CS.
>
> d) Depending on the collating sequence, two strings may compare as 
> equal even if they are of different lengths or contain different 
> sequences of characters.
> When any of the operations
> MAX, MIN, and DISTINCT reference a grouping column, and the UNION, 
> EXCEPT, and INTERSECT operators refer to character strings, the 
> specific value selected by these operations from a set of such equal 
> values is implementation-dependent.
>
> NOTE 129 - If the coercibility characteristic of the comparison is 
> Coercible, then the collating sequence used is the default defined for

> the character repertoire. See also other Syntax Rules in this 
> Subclause, Subclause 10.6, , and 
>

Re: [sqlite] SQLite character comparisons

2008-01-21 Thread drh

John Elrick <[EMAIL PROTECTED]> wrote:
> 
> If we are ignoring trailing spaces, then by definition:
> 
> ' ' = ''
> 
> and for that matter:
> 
> '  ' = '  '
> 

Good point.  I added these as test cases for the new RTRIM
collation.  http://www.sqlite.org/cvstrac/chngview?cn=4735

--
D. Richard Hipp <[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite character comparisons

2008-01-21 Thread Dennis Cote

Fowler, Jeff wrote:

Hello All,

Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, and in fact SQLite's implementation of character comparison (respecting trailing spaces) is superior to ANSI's specs. Keep in mind this is not some obscure issue that can be subject to different interpretations by different vendors; it's very clearly stated: "The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them."

Jeff,

I think you are mistaken about what the ANSI spec says.

There are two string types in ANSI SQL, character strings (which come is
several subtypes), and binary strings. The following excerpts are taken
from the SQL:1999 spec.

Section 4.2.1 Character Strings and Collations describes the operations
on character strings. It describes comparisons as
Given a collating sequence, two character strings are identical if and
only if they are equal in
accordance with the comparison rules specified in Subclause 8.2,
‘‘’’. The
collating sequence used for a particular comparison is determined as
in Subclause 4.2.3, ‘‘Rules

determining collating sequence usage’’.

Binary strings are defined in Section 4.3 as;
A binary string is a sequence of octets that does not have either a
character set or collation associated

with it.

And their comparison is detailed in 4.3.1 as;
All binary strings are mutually comparable. A binary string is
identical to another binary string
if and only if it is equal to that binary string in accordance with
the comparison rules specified in

Subclause 8.2, ‘‘’’.

General Rules 3 and 4 of section 8.2 describe the
comparison of these strings. I have copied these sections below.

3) The comparison of two character strings is determined as follows:

a) Let CS be the collating sequence indicated in Subclause 4.2.3,
‘‘Rules determining collating
sequence usage’’, based on the declared types of the two character
strings.

b) If the length in characters of X is not equal to the length in
characters of Y, then the shorter
string is effectively replaced, for the purposes of comparison, with a
copy of itself that has
been extended to the length of the longer string by concatenation on
the right of one or more
pad characters, where the pad character is chosen based on CS. If CS
has the NO PAD
characteristic, then the pad character is an implementation-dependent
character different
from any character in the character set of X and Y that collates less
than any string under

CS. Otherwise, the pad character is a .

c) The result of the comparison of X and Y is given by the collating
sequence CS.

d) Depending on the collating sequence, two strings may compare as
equal even if they are
of different lengths or contain different sequences of characters.
When any of the operations

MAX, MIN, and DISTINCT reference a grouping column, and the UNION, EXCEPT,
and INTERSECT operators refer to character strings, the specific value
selected by these

operations from a set of such equal values is implementation-dependent.

NOTE 129 – If the coercibility characteristic of the comparison is
Coercible, then the collating sequence
used is the default defined for the character repertoire. See also
other Syntax Rules in this Subclause,
Subclause 10.6, ‘‘’’, and Subclause
11.30, ‘‘’’.

4) The comparison of two binary string values, X and Y, is determined
by comparison of their
octets with the same ordinal position. If Xi and Yi are the values of
the i-th octets of X and Y,
respectively, and if Lx is the length in octets of X AND Ly is the
length in octets of Y, then X is

equal to Y if and only if Ly = Ly and if Xi = Yi for all i.
I note that there is a typo in rule 4 for binary strings; Ly = Ly should
be Lx = Ly, since binary strings can only be compared for equality.

Rule 3.b details how strings of unequal length are to be compared. It
allows exactly the operation performed by SQLite, since it allows
collating sequences to have a NO PAD characteristic which results in the
shorter string comparing less than the longer string.

This distinction also appears in section 4.12 which discusses type
conversions and mixing of data types. It says;
Values corresponding to the data types CHARACTER, CHARACTER VARYING,
and CHARACTER
LARGE OBJECT are mutually assignable if and only if they are taken
from the same character
repertoire. If they are from different character repertoires, then the
value of the source of the
assignment must be translated to the character repertoire of the
target before an assignment is
possible. Such translation may be implementation-defined and
implicitly performed, in which case
the two character data types are also mutually assignab

Re: [sqlite] SQLite character comparisons

2008-01-21 Thread John Elrick


Fowler, Jeff wrote:

Hello All,
 
Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. 


My $0.02.

If we are ignoring trailing spaces, then by definition:

' ' = ''

and for that matter:

'  ' = '  '

whereas

'xx' <> 'xxx'

Therefore, from the standpoint of arbitrary text,

A may or may not actually be identical to B when A = B

I don't consider that type of result to be predicable and therefore 
would respectfully state that the standard is a potential source of bugs.


What could be useful is a function:

SELECT * FROM FOO WHERE TRIM(BAR) = TRIM(:bar)

or even a shortcut function along the lines of:

SELECT * FROM FOO WHERE TEXT_EQUIVALENT(BAR, :bar)

However, in my opinion, the operator '=' is better reserved as defining 
strict equality.



John

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: Re: To VACUUM - or not to VACUUM?


Zbigniew Baniewski  wrote:

On Mon, Jan 21, 2008 at 10:43:39AM -0500, Igor Tandetnik wrote:


Now, if you expect the amount of data to grow in the future, it is
fine to leave free pages in. They will be reused as necessary. In
other words, you don't need to VACUUM if you are happy with your file
retaining the high water mark size (the size reflecting the largest
amount of data it ever held).


Thanks, that's I was afraid of: that there's something subtle, I'm
missing.


Another alternative is, rather than deleting just the data from the file 
with DELETE FROM $table statements, to physically delete the whole file, 
recreate the schema, then insert data from memory database. This way, 
since you recreate the file from scratch each time, you won't need to 
vaccuum.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: To VACUUM - or not to VACUUM?

On Mon, Jan 21, 2008 at 10:43:39AM -0500, Igor Tandetnik wrote:

> Now, if you expect the amount of data to grow in the future, it is fine 
> to leave free pages in. They will be reused as necessary. In other 
> words, you don't need to VACUUM if you are happy with your file 
> retaining the high water mark size (the size reflecting the largest 
> amount of data it ever held).

Thanks, that's I was afraid of: that there's something subtle, I'm missing.
-- 
pozdrawiam / regards

Zbigniew Baniewski

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: To VACUUM - or not to VACUUM?


Zbigniew Baniewski  wrote:

On Mon, Jan 21, 2008 at 09:59:08AM -0500, Igor Tandetnik wrote:
In the paper above I've found a ready-to-use recipe, which I'm using
since, because is very practical. First I'm copying the data from
"physical file" into "memory database", then I'm operating in memory
- and, at the end of work, all the memory contents is written back
into the file.

So, I'm deleting the file contents first:

 SELECT name FROM sqlite_master WHERE type='table'
 DELETE FROM $name

...then attaching "physical" file...

 ATTACH /full/path/to/dbase AS app

...and then putting the "memory database" contents into file:

 SELECT name FROM sqlite_master WHERE type='table'
 INSERT INTO app.$name SELECT * FROM $name

...detaching it in the end: DETACH app

So, back to my question: do I need VACUUM at all?


You may need to (unless you are happy with the database file never 
getting smaller). Suppose your work on the memory database involved 
mainly deleting records. The amount of data you put back into the file 
will then be less than the amount of data it used to hold at the 
beginning of the operation. This would necessarily mean there are some 
free pages in the file, and the file is larger than it strictly needs to 
be. VACUUM will reclaim free pages and reduce the size of the file.


Now, if you expect the amount of data to grow in the future, it is fine 
to leave free pages in. They will be reused as necessary. In other 
words, you don't need to VACUUM if you are happy with your file 
retaining the high water mark size (the size reflecting the largest 
amount of data it ever held).


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: To VACUUM - or not to VACUUM?


Zbigniew Baniewski  wrote:

Perhaps I had false idea on what's going on, when working with
attached database. If I properly understood, in such case all the
operations are taking place directly in memory


No, any changes to the attached database are written to that database's 
file by the time transaction ends, just as they are for the main 
database. Unless you attach a :memory: database, of course.



If it's working the way described above


It's not.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: To VACUUM - or not to VACUUM?

On Mon, Jan 21, 2008 at 09:59:08AM -0500, Igor Tandetnik wrote:

> The point of VACUUM is to reclaim pages freed by deleting records (or by 
> updating with less data, e.g. by removing large BLOBs). If you don't 
> modify the database, there's no reason to VACUUM it.
> 
> And of course, if you really want to, you can detach a database, open it 
> again directly, and vaccuum it on that new connection.

OK, perhaps I wrote not enough:

http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html

In the paper above I've found a ready-to-use recipe, which I'm using since,
because is very practical. First I'm copying the data from "physical file"
into "memory database", then I'm operating in memory - and, at the end of
work, all the memory contents is written back into the file.

So, I'm deleting the file contents first:

  SELECT name FROM sqlite_master WHERE type='table'
  DELETE FROM $name

...then attaching "physical" file...

  ATTACH /full/path/to/dbase AS app

...and then putting the "memory database" contents into file:

  SELECT name FROM sqlite_master WHERE type='table'
  INSERT INTO app.$name SELECT * FROM $name

...detaching it in the end: DETACH app

So, back to my question: do I need VACUUM at all?
-- 
pozdrawiam / regards

Zbigniew Baniewski

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: To VACUUM - or not to VACUUM?

On Mon, Jan 21, 2008 at 09:59:08AM -0500, Igor Tandetnik wrote:

> DETACH doesn't do any writing, it just disassociates a given connection 
> from a paricular DB file. I don't understand what you mean by "real" 
> database. Is there any other kind?

Perhaps I had false idea on what's going on, when working with attached
database. If I properly understood, in such case all the operations are
taking place directly in memory, and when DETACH-ing such database, only
then the "in-memory" database contents is written to physical database file
(if you don't like "real"), the one on the disk.

If it's working the way described above - it seems to me, that the physical
file is always entirely overwritten with the memory contents (when detaching).
So, it's never fragmented. And in conclusion: there's no need for VACUUM.

But I'm not sure, about the above... is it really working the way, I'm
supposing - or I'm wrong about it?
-- 
pozdrawiam / regards

Zbigniew Baniewski

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: How do I add primary key on existing table?


Mohd Radzi Ibrahim <[EMAIL PROTECTED]>
wrote:

Using ALTER TABLE to add a constraint or a new column marked as
PRIMARY KEY is not supported. You can get pretty much the same
effect by doing:

 CREATE UNIQUE INDEX custsales_i ON custsales(id, type);

Dan.


Thanks for the suggestion. Will it still work for the REPLACE command
to identify indentical record?


Yes. REPLACE conflict resolution kicks in upon violation of any 
uniqueness constraint.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: SQLite Dataypes


Vishal Mailinglist  wrote:

Out of curiosity , in SQLite datatypes
http://sqlite.org/datatype3.html
its said that all the data in version 2.x all data was stored as
ASCII text , does this mean even if I create a table as

create table some(id INTEGER, location varchar(10)) ;

The data in column id and location both are in ASCII text


Yes.


if yes
what about in SQLite 3.


This is no longer the case in SQLite3.


Also am I correct in saying version 3 or 2 SQLite will not raise
error even if the data inserted is not as per data type mentioned in
create table syntax.


Correct. The declared type of a column is essentially treated as a 
suggestion. SQLite will attempt to convert incoming data to this 
suggested type, but if it can't, then it will store the data in its 
original type. This is the crux of manifest typing.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: To VACUUM - or not to VACUUM?


Zbigniew Baniewski  wrote:

In the docs there is a remark: "VACUUM only works on the main
database. It is not possible to VACUUM an attached database file".

I'm not sure: if one works with "attached" database, making a
"dbase-shot" of its contents to real database file, when ending the
work - does there exist any need to perform VACUUM?


The point of VACUUM is to reclaim pages freed by deleting records (or by 
updating with less data, e.g. by removing large BLOBs). If you don't 
modify the database, there's no reason to VACUUM it.


And of course, if you really want to, you can detach a database, open it 
again directly, and vaccuum it on that new connection.



If I'm correct - attached database, when DETACH-ed, will be saved
into real database file "from the beginning to end", thus no
fragmentation there at all?


DETACH doesn't do any writing, it just disassociates a given connection 
from a paricular DB file. I don't understand what you mean by "real" 
database. Is there any other kind?


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] To VACUUM - or not to VACUUM?