RE: [sqlite] Oracle DECODE

[Drew, Stephen]

Richard,

Thanks for the response.

I do apologise, I made a mistake in my original mail - I have a macro
which allows me to easily switch between using 2 and 3 in my apps:

For SQLite 3:

#   define sqlite_set_result_string(a,b,c)
sqlite3_result_text(a,b,c,NULL)

This just allows me to not change my code to use either version, and I
mistakenly assumed the function was sqlite3_set_result_string without
double-checking first...

Regards,
Steve

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 12 April 2006 17:30
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Oracle DECODE

"Drew, Stephen" <[EMAIL PROTECTED]> wrote:
> Hello all,
>  
> I have written a function to replicate the Oracle DECODE function, 
> which I register whenever I open a SQLite3 (3.2.6) connection (see 
> bottom of email).
>  
> However it seems that certain decodes fail due to corrupted data.  I 
> have stepped through my code and seen that the value is correctly set 
> when the decode function is called, but when the value is retrieved 
> using sqlite3_column_value, it comes back as gibberish.
>  
> This does not always happen - the particular case involves five decode

> statements in the SELECT list.  If I move some of the DECODE 
> statements around or remove them, it works ok.
>  
> So - here are some questions:
>  
> * - Is it safe to call sqlite3_set_result_string( ) as I am doing,
i.e.
> with the value returned from sqlite3_value_text on the arguments?  (I 
> have included my decode function at the bottom of this email, and 
> happily release into the public domain).


There is no such API as sqlite3_set_result_string().  There was an API
sqlite_set_result_string() in SQLite version 2, which you appear to be
using.  But it is certainly not safe to mix APIs.
I'm surprised this does not segfault on you.

Use sqlite3_result_text() instead.  See

  http://www.sqlite.org/capi3ref.html

for additional information.  And/or use the function implementations in
the func.c source file

  http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c

as examples.

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

[sqlite] Oracle DECODE

[Drew, Stephen]

Hello all,
 
I have written a function to replicate the Oracle DECODE function, which
I register whenever I open a SQLite3 (3.2.6) connection (see bottom of
email).
 
However it seems that certain decodes fail due to corrupted data.  I
have stepped through my code and seen that the value is correctly set
when the decode function is called, but when the value is retrieved
using sqlite3_column_value, it comes back as gibberish.
 
This does not always happen - the particular case involves five decode
statements in the SELECT list.  If I move some of the DECODE statements
around or remove them, it works ok.
 
So - here are some questions:
 
* - Is it safe to call sqlite3_set_result_string( ) as I am doing, i.e.
with the value returned from sqlite3_value_text on the arguments?  (I
have included my decode function at the bottom of this email, and
happily release into the public domain).
 
* - My decode appears to not work correctly on numeric decodes e.g.
 
 DECODE ( , -42, 10,  )
 
doesn't work, but this does:
 
 DECODE ( , '-42', '10',  )

and all other text decodes work fine.
 
Can anyone offer any tips on what direction I should continue to
investigate this problem?
 
Cheers,
Steve



 
   void decode_sql_function(sqlite_func* context, IntM argc,
sqlite_value** argv)
   {
int pos=1;
if (argc>2)
{
sqlite_char* ach0 = sqlite_value_text(argv[0]);

while (pos < argc)
{
if ((pos+1) < argc)
{
sqlite_char* achp =
sqlite_value_text(argv[pos]);

if (ach0==NULL || achp==NULL) 
{
if (ach0==NULL &&
achp==NULL) {

sqlite_set_result_string(context,(const
char*)sqlite_value_text(argv[pos+1]),-1);
return;
}
}
else if (strcmp((const
char*)ach0,(const char*)achp)==0)
{

sqlite_set_result_string(context,(const
char*)sqlite_value_text(argv[pos+1]),-1);
return;
}
}
else
{

sqlite_set_result_string(context,(const
char*)sqlite_value_text(argv[pos]),-1);
return;
}
pos+=2;
}
}
sqlite_set_result_error(context,"Not enough arguments
for DECODE",-1);
   }

RE: [sqlite] Multithread access to DB

[Drew, Stephen]

http://www.sqlite.org/lockingv3.html

RESERVED 
A RESERVED lock means that the process is planning on writing to the
database file at some point in the future but that it is currently just
reading from the file. Only a single RESERVED lock may be active at one
time, though multiple SHARED locks can coexist with a single RESERVED
lock. RESERVED differs from PENDING in that new SHARED locks can be
acquired while there is a RESERVED lock.   

-Original Message-
From: Rafal Rusin [mailto:[EMAIL PROTECTED] 
Sent: 21 March 2006 15:03
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithread access to DB

Sqlite has 3 types of locks, which is weird for me.
Could You explain what is a "reserved lock"?
Read and read/write (exclusive) locks are apparent.

http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler

Best Regards,
Rafal Rusin

TouK Company
(www.touk.pl)

RE: [sqlite] Shared Memory Question

[Drew, Stephen]

Glenn,

I certainly would, but sadly this is for Win32 too.

Cheers,
Steve 

-Original Message-
From: Glenn McAllister [mailto:[EMAIL PROTECTED] 
Sent: 15 February 2006 16:45
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Shared Memory Question

This is likely a naive response, but on Linux have you thought using
/dev/shm?  It's a tmpfs ramdisk that is needed by POSIX shared memory
calls shm_open and shm_unlink in glibc 2.2 and above.  It grows and
shrinks as required and uses almost no memory if it's never populated
with files.

As a simple test I created /dev/shm/test.d using sqlite3, created a
simple table and populated it with a couple of rows of data.  I
connected to the database from another sqlite instance, and I was able
to read the data just fine.  After closing down both instances the test
database was still there (no surprise, it's a filesystem after all).

Dunno if that helps any.

Glenn McAllister
SOMA Networks, Inc.

Drew, Stephen wrote:
> Hello all,
>  
> An interesting use of the in-memory SQLite database would be to use it

> in shared memory and have the data shared between applications but 
> stored once.

RE: [sqlite] Shared Memory Question

[Drew, Stephen]

Sorry, just noticed this is very similar Mateus' email from earlier, so
will wait for any responses to that...

-Original Message-
From: Drew, Stephen 
Sent: 15 February 2006 15:33
To: sqlite-users@sqlite.org
Subject: [sqlite] Shared Memory Question

Hello all,
 
An interesting use of the in-memory SQLite database would be to use it
in shared memory and have the data shared between applications but
stored once. 
 
The background to this is I am making local replicated copies of a main
database, and as they are transient (they are read-only copies of the
main database), there is no real need to have them on disk, or at least
not permanently.
 
To get around locking issues, obviously the whole database would need to
be wrapped in a mutex (simple, slow approach), but what I was wondering
is:
 
Presumably, the in-memory database is in contiguous memory, as I guess
the format is the same as the disk-based database format.  Would it be a
lot of work to be able to construct an in-memory database with a chunk
of memory (allocated by the user), and a size?  The database could then
be created in shared memory, and a SQLITE_FULL error returned if the
database attempted to grow outside this size limit.  The shared memory
segment could be flushed to disk independently and when the application
decides.
 
Is this a reasonable request and would this be useful to anyone else?
Or is there something already present that I could use?
 
Many thanks,
Steve
 

[sqlite] Shared Memory Question

[Drew, Stephen]

Hello all,
 
An interesting use of the in-memory SQLite database would be to use it
in shared memory and have the data shared between applications but
stored once. 
 
The background to this is I am making local replicated copies of a main
database, and as they are transient (they are read-only copies of the
main database), there is no real need to have them on disk, or at least
not permanently.
 
To get around locking issues, obviously the whole database would need to
be wrapped in a mutex (simple, slow approach), but what I was wondering
is:
 
Presumably, the in-memory database is in contiguous memory, as I guess
the format is the same as the disk-based database format.  Would it be a
lot of work to be able to construct an in-memory database with a chunk
of memory (allocated by the user), and a size?  The database could then
be created in shared memory, and a SQLITE_FULL error returned if the
database attempted to grow outside this size limit.  The shared memory
segment could be flushed to disk independently and when the application
decides.
 
Is this a reasonable request and would this be useful to anyone else?
Or is there something already present that I could use?
 
Many thanks,
Steve
 

RE: [sqlite] Re: Database Disk Full

[Drew, Stephen]

Exactly, as per my original mail, there was no shortage of disk space on the 
drive in question, on the drive containing the temporary files, and the 
database file was 100kb.

So SQLITE_FULL was misleading in this case.

I will post further information about the exact cause when I have tracked it 
down.

Regards,
Steve 

-Original Message-
From: Michael Knigge [mailto:[EMAIL PROTECTED] 
Sent: 13 February 2006 13:34
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Database Disk Full


> Sigh...  what is wrong with a message "disk full" when the disk space 
> is exhausted?  Why is simple and to the point a problem?

The point is, that this error is returned everytime a write to the disk failed 
- even if (for example) the write failed because of a network error (NFS-Server 
is restarted for example).


I remember that I've got a "disk full" message from MS-Word last year when I 
tried to print to a PDF-Printer (free space on my disk: 14 GB).



Bye,
   Michael



> 
> Drew, Stephen wrote:
> 
>> John,
>>
>> Thanks for the reply.
>>
>> I disagree - my error message informs my users (who are technonerds)
>> that the disk or db file is full, when neither of these is the case.
>>
>> Surely you can see that even a different constant error message in this
>> context would be preferable?  SQLITE_WRITE_FAILED or something?
>>
>> As I say, I'm just altering the standard error text at the moment,
>> because it is misleading.
>>
>> Steve
>>
>> -Original Message-
>> From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 11 February 2006 
>> 09:58
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Re: Database Disk Full
>>
>> The first message informs all users of the problem.  The one you propose
>> might satisfy a technonerd, but confuse the more casual user.  There is
>> however a case for writing such a detailed message to syslog or similar
>> system log.
>>
>> Dave Dyer wrote:
>>
>>>> It is a reasonable assumption to make that the only thing which can
>>
>>
>> have changed since the last write is the disk becoming full.  A disk
>> cable falling off, head crash or mechanical disk failure is not only
>> unlikely but would crash the entire machine and make error detection and
>> recovery unlikely so testing for it is futile.
>>
>>>
>>> It is reasonable for a program like sqlite to operate on the 
>>> assumption that other hardware and software perform as intended, and 
>>> not attempt heroic error recovery.
>>>
>>> On the other hand, sqlite operates in the real world, and wierd shit 
>>> happens out there.  When something goes wrong, every bit of 
>>> information that is available should BE available to those trying to 
>>> clean up the mess.
>>>
>>> There is a huge difference, coming in in the morning after an expected
>>
>>
>>
>>> overnight run, finding it failed, and having the message
>>>
>>> database full
>>>
>>> verses having the message
>>>
>>> 09-feb-2006 03:13:12 database write failed, windows error code 14  for
>>
>>
>>
>>> f:\temp\vacuumtemp.txt, current file size = 10200K
>>>
>>>
>>
>>
>>
>>
> 
> 


-- 
Mit freundlichen Grüßen

Michael Knigge

S.E.T. Software GmbH
Lister Str. 15
30163 Hannover

Tel.:  +49 511 / 3 97 80 -23
Fax:   +49 511 / 3 97 80 -66
eMail: [EMAIL PROTECTED]

RE: [sqlite] Re: Database Disk Full

[Drew, Stephen]

John,

Thanks for the reply.

I disagree - my error message informs my users (who are technonerds)
that the disk or db file is full, when neither of these is the case.

Surely you can see that even a different constant error message in this
context would be preferable?  SQLITE_WRITE_FAILED or something?

As I say, I'm just altering the standard error text at the moment,
because it is misleading.

Steve

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: 11 February 2006 09:58
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Database Disk Full

The first message informs all users of the problem.  The one you propose
might satisfy a technonerd, but confuse the more casual user.  There is
however a case for writing such a detailed message to syslog or similar
system log.

Dave Dyer wrote:
>>It is a reasonable assumption to make that the only thing which can
have changed since the last write is the disk becoming full.  A disk
cable falling off, head crash or mechanical disk failure is not only
unlikely but would crash the entire machine and make error detection and
recovery unlikely so testing for it is futile.
> 
> 
> It is reasonable for a program like sqlite to operate on the 
> assumption that other hardware and software perform as intended, and 
> not attempt heroic error recovery.
> 
> On the other hand, sqlite operates in the real world, and wierd shit 
> happens out there.  When something goes wrong, every bit of 
> information that is available should BE available to those trying to 
> clean up the mess.
> 
> There is a huge difference, coming in in the morning after an expected

> overnight run, finding it failed, and having the message
> 
> database full
> 
> verses having the message
> 
> 09-feb-2006 03:13:12 database write failed, windows error code 14  for

> f:\temp\vacuumtemp.txt, current file size = 10200K
> 
> 

RE: [sqlite] Database Disk Full

[Drew, Stephen]

I suspect, although am not 100%, that a third-party database tool was
using the database file. As you say, I would have expected either that
tool or the actual program to have failed to open the file

However, we could be a lot more sure if we could see the Win32 error. In
the short-term I am going to add a modification to log this info
whenever WriteFile() fails in the code mentioned, and will let you know
if it is anything that looks suspicious.

Out of interest, the file is on a local NT filesystem drive. 

Thanks,
Steve

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 10 February 2006 17:28
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Database Disk Full

"Drew, Stephen" <[EMAIL PROTECTED]> wrote:
>> 
> It's just a little bit misleading if you're not familiar with the 
> circumstances it's raised in... I've had some confused colleagues 
> wondering why their 100kb DB on a disk with 15gb free would be out of 
> space :)
> 

I'm curious.  What was preventing them from writing to the file if it
was not a lack of disk space?  File permission problems should have been
detected when the file was opened.  What else would cause a short write?

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

RE: [sqlite] Database Disk Full

[Drew, Stephen]

Hello,

Thanks for the swift response.

Hmm I don't know to be honest, something generic perhaps like a failure
to write...

It's just a little bit misleading if you're not familiar with the
circumstances it's raised in... I've had some confused colleagues
wondering why their 100kb DB on a disk with 15gb free would be out of
space :)

It's nothing urgent for me, I will alter the error message I return to
offer more suggestions as to the cause, but given in Windows we can use
GetLastError() and format an error message, this information would no
doubt be of use to people in diagnosing why the file write failed.

Many thanks, and keep up the great work!

Steve

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 10 February 2006 17:01
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Database Disk Full

"Drew, Stephen" <[EMAIL PROTECTED]> wrote:
> Hello,
>  
> In sqlite3OsWrite function (in os_win.c)  the following code exists:
>  
>   while( amt>0 && (rc = WriteFile(id->h, pBuf, amt, , 0))!=0 &&
> wrote>0 ){
> 
> amt -= wrote;
> 
> pBuf = &((char*)pBuf)[wrote];
> 
>   }
> 
>   if( !rc || amt>(int)wrote ){
> 
> return SQLITE_FULL;
> 
>   }
> 
> Is this really a valid occasion to return SQLITE_FULL? 

What error code would you suggest as an alternative?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Database Disk Full

[Drew, Stephen]

Hello,
 
In sqlite3OsWrite function (in os_win.c)  the following code exists:
 
  while( amt>0 && (rc = WriteFile(id->h, pBuf, amt, , 0))!=0 &&
wrote>0 ){

amt -= wrote;

pBuf = &((char*)pBuf)[wrote];

  }

  if( !rc || amt>(int)wrote ){

return SQLITE_FULL;

  }

Is this really a valid occasion to return SQLITE_FULL?   Surely
WriteFile Win32 API call can fail to write a full for a plethora of
reasons, or am I missing something?

Many thanks in advance

Regards,

Steve

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

[Drew, Stephen]

Yes I use SQLite for replicating a main oracle database.

Occasionally (and I mean occasionally) a schema change in Oracle needs
to be propagated to the SQLite database. So this situation does arise
for me.

-Original Message-
From: Rob Lohman [mailto:[EMAIL PROTECTED] 
Sent: 03 November 2005 17:11
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes

>> Another proposal:  Suppose that when creating an sqlite3_stmt using 
>> sqlite3_prepare, the original SQL text was stored in the 
>> sqlite3_stmt.  Then when a schema change occurred, the statement was 
>> automatically recompiled and rebound.  There would no more 
>> SQLITE_SCHEMA errors.  But sqlite3_stmts would use a little more 
>> memory.  And sqlite3_step might take a little longer to initialize 
>> sometimes if it found it needed to rerun the parser.
>> 
>> What about this change?  Is it a worth-while tradeoff?
> 
> I'm a big fan of this change. We do quite a lot of bookkeeping in our 
> own code to do exactly the same thing at the moment. It would make me 
> very happy to delete that code.

This is not directly a question for you, Eric, but does anyone actually
have schema changes on a working database? I've never seen any schema
changes on my databases except for a new version, bugfixes etc.

Does anyone runs code that actually changes the schema of your database
as the normal process in an application? If so, for what reason?

Rob

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

[Drew, Stephen]

I'm for both changes... 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 03 November 2005 13:18
To: sqlite-users@sqlite.org
Subject: [sqlite] Request for comment: Proposed SQLite API changes

As currently implemented, when an error occurs during sqlite3_step(),
the function returns SQLITE_ERROR.  Then you have to call either
sqlite3_reset() or sqlite3_finalize() to find the actual error code.
Suppose this where to change in version 3.3.0 so that the actual error
code was returned by sqlite3_step().  That would mean that moving from
version 3.2.7 to 3.3.0 might involve some minor code changes. The API
would not be 100% backwards compatible.  But the API would be cleaner.  

What does the community think about such a change?

Another proposal:  Suppose that when creating an sqlite3_stmt using
sqlite3_prepare, the original SQL text was stored in the sqlite3_stmt.
Then when a schema change occurred, the statement was automatically
recompiled and rebound.  There would no more SQLITE_SCHEMA errors.  But
sqlite3_stmts would use a little more memory.  And sqlite3_step might
take a little longer to initialize sometimes if it found it needed to
rerun the parser.  

What about this change?  Is it a worth-while tradeoff?

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

RE: [sqlite] CHECK constraints

[Drew, Stephen]

Oracle 9.2

  CREATE TABLE ex1(
x INTEGER,
y NUMBER(21,18),
CHECK( xmailto:[EMAIL PROTECTED] 
Sent: 02 November 2005 23:31
To: sqlite-users@sqlite.org
Subject: [sqlite] CHECK constraints

In a CHECK constraint, if the expression is NULL (neither true nor
false) does the constraint fail?

Example:

  CREATE TABLE ex1(
x INTEGER,
y REAL,
CHECK( x

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

[Drew, Stephen]

I think this is the best solution in the real world, although I agree
with DRH that different division operators are a bad thing.

Surely if somebody wants the result as an integer, they can use
sqlite3_column_int() to pull the result out?  Or even write a function
to do whatever rounding they see fit.

Oracle, however, returns the following:


   CREATE TABLE test_table1(a INTEGER, b NUMBER(21,18));
   
   INSERT INTO test_table1 VALUES(5,5);
   
   SELECT a/2, b/2 FROM test_table1;



2.5 | 2.5

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 02 November 2005 15:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

Consider the following SQL:

   CREATE TABLE t1(a INTEGER, b REAL);
   INSERT INTO t1 VALUES(5,5);
   SELECT a/2, b/2 FROM t1;

>From the above SQL, SQLite version 3.2.7 and earlier will return

   2|2

If my proposed changes for 3.3.0 go in, then the result will be:

   2.5|2.5

If I understand what most people are saying, the SQL standard says that
the result should be:

   2|2.5

Does this correctly summarize the situation?

Do other SQL database engines consistently return the 3rd case?

Am I alone in thinking that a division operator that does different
things depending on the declared datatype of a column is an abomination?

Does anybody have a real-world example where any of this will actually
make a difference, or is this really just an academic argument?

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

RE: [sqlite] problems compiling 3.2.6

[Drew, Stephen]

Further update:

I get both these errors in Visual Studio 6.

As you say, I think the first is just that Visual Studio 6 has a missing
definition in winbase.h. I agree with you that it is in the
documentation (perhaps a check could be performed and it defined if it
doesn't exist...)

The second seems to be a limitation of VS6 - it certainly isn't a
problem in VS7. Can't really think of any neat solution around it
either...

This doesn't concern me, as - as I mention - I use VS7...

Steve

-Original Message-
From: Drew, Stephen 
Sent: 19 September 2005 17:13
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] problems compiling 3.2.6

Mike,

3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can give it a go
in my copy of Visual Studio 6 if you like...

Steve 

-Original Message-
From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
Sent: 19 September 2005 10:14
To: sqlite-users@sqlite.org
Subject: [sqlite] problems compiling 3.2.6

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem, and I
will try to fix locally (although I would like to hear from other people
about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?

RE: [sqlite] problems compiling 3.2.6

[Drew, Stephen]

Mike,

3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can give it a go
in my copy of Visual Studio 6 if you like...

Steve 

-Original Message-
From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] 
Sent: 19 September 2005 10:14
To: sqlite-users@sqlite.org
Subject: [sqlite] problems compiling 3.2.6

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem, and I
will try to fix locally (although I would like to hear from other people
about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?

RE: [sqlite] Column Naming Convention (yet again)

[Drew, Stephen]

I've fixed this in my local copy, but sadly I am completely useless at
CVS etcso here's what I did.  Please let me know if anyone suspects
this is dodgy or plain wrong.

FILE:   select.c,  LINE: 1136

Changed:

if( longNames )

To:

if( longNames || pTabList->nSrc>1 ){ 

Many thanks,
Steve

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 14 September 2005 16:04
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Column Naming Convention (yet again)

Drew, Stephen wrote:

>Hello,
> 
>It's that time of the month again - column naming convention
discussion.
> 
>The documentation states quite clearly:
> 
>Case 4: short_column_names=OFF and full_column_names=OFF
>
>For this case (which was the default prior to version 3.1.0) the result

>is the same as for case 2 for simple queries and is the same as case 5 
>for joins. In other words, for a query with only a single table in the 
>FROM clause, the name of the result set column matches the text of the 
>expression that defined the column as in case 2. For a join, the column

>name is of the form "TABLE.COLUMN" where TABLE and COLUMN are the names

>of the table and column from which the data comes, as defined in the 
>original CREATE TABLE statement.
>
>Now this is great, but (and this isn't the first time I've mentioned 
>it)
>
>CREATE TABLE TEST1  (a INTEGER, b TEXT, PRIMARY KEY (a))
>
>CREATE TABLE TEST2  (a INTEGER, b TEXT, PRIMARY KEY (a))
>
>SELECT T1.a, T1.b, T2.a, T2.b FROM TEST1 T1, TEST2 T2 WHERE T1.a = T2.a
>
>---> this is fine and returns the column names as written
>
>SELECT T1.*, T2.* FROM TEST1 T1, TEST2 T2 WHERE T1.a = T2.a
>
>---> this STILL returns without the table-alias prefix.
>
>In my mind these statements both fall into the category mentioned
above.
>
> 
>
>Can I ask what the logic is here?  (this page doesn't seem to cover it:
>http://www.sqlite.org/cvstrac/wiki?p=ColumnNames)
>
>Following the upgrade to 3.2.5 (on the basis this had been fixed), I do

>not want to tell my users to expand their  T1.*  to include all the 
>column names, as this is a pain when the tables have hundreds of 
>columns.
>
>Any thoughts appreciated.
>
>Regards,
>
>Steve
>
>
>  
>
Steve,

You are correct. Something is still broken, either the code or the docs.

I think it's the code. These two statements should produce the same
column headings, and since they are joining two tables, they should both
have full (table.column) column names.

Dennis Cote

[sqlite] Column Naming Convention (yet again)

[Drew, Stephen]

Hello,
 
It's that time of the month again - column naming convention discussion.
 
The documentation states quite clearly:
 
Case 4: short_column_names=OFF and full_column_names=OFF 

For this case (which was the default prior to version 3.1.0) the result
is the same as for case 2 for simple queries and is the same as case 5
for joins. In other words, for a query with only a single table in the
FROM clause, the name of the result set column matches the text of the
expression that defined the column as in case 2. For a join, the column
name is of the form "TABLE.COLUMN" where TABLE and COLUMN are the names
of the table and column from which the data comes, as defined in the
original CREATE TABLE statement. 

Now this is great, but (and this isn't the first time I've mentioned
it)

CREATE TABLE TEST1  (a INTEGER, b TEXT, PRIMARY KEY (a))

CREATE TABLE TEST2  (a INTEGER, b TEXT, PRIMARY KEY (a))

SELECT T1.a, T1.b, T2.a, T2.b FROM TEST1 T1, TEST2 T2 WHERE T1.a = T2.a

---> this is fine and returns the column names as written

SELECT T1.*, T2.* FROM TEST1 T1, TEST2 T2 WHERE T1.a = T2.a

---> this STILL returns without the table-alias prefix.

In my mind these statements both fall into the category mentioned above.

 

Can I ask what the logic is here?  (this page doesn't seem to cover it:
http://www.sqlite.org/cvstrac/wiki?p=ColumnNames)  

Following the upgrade to 3.2.5 (on the basis this had been fixed), I do
not want to tell my users to expand their  T1.*  to include all the
column names, as this is a pain when the tables have hundreds of
columns.

Any thoughts appreciated.

Regards,

Steve

RE: [sqlite] Why can i open a textfile?

[Drew, Stephen]

Point taken about viruses perhaps, but there are other reasons one might want 
to encrypt data - which by its very nature could be related to anything.  
 
For example, in a commercially competitive environment, it might be easy for a 
competitor to gain access to files, or even colleagues within the same 
organisation who do not have the privilege to see such data. A file that can be 
easily read, often will be - whereas an encrypted file, of any reasonable 
level, will often be enough to deter prying eyes.
 
If one needs to know whether a file is a database or not immediately after 
opening it, surely a user-written wrapper function around sqlite(3)_open which 
does a simple select on sqlite_master is the way to go?  Keeps everybody 
happy Personally I'd wrap the calls to sqlite anyway but C++ sort of 
suggests doing this regardless
 
Steve

-Original Message- 
From: Edwin Knoppert [mailto:[EMAIL PROTECTED] 
Sent: Mon 22/08/2005 23:20 
To: sqlite-users@sqlite.org 
Cc: 
Subject: Re: [sqlite] Why can i open a textfile?



At the other hand, this is database stuff, what on earth would you 
encrypt
in real life business databases.
No one cares, except for a few purposes.
(Now i done it :) )
Encrypting a header, like if any virus writer is busy with a tool like
sqlite..
pfffttt.


- Original Message -
From: "Dennis Jenkins" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, August 23, 2005 12:07 AM
Subject: Re: [sqlite] Why can i open a textfile?


> Mike Shaver wrote:
>
>>On 8/22/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>>
>>>I very much disagree.  I want the entire file, header included, to be
>>>encrypted.  Sometimes you don't want anyone to know what the file 
type
>>>is.  Security through obscurity is not secure.  However, you don't 
want
>>>to give the bad guys a road map either...
>>>
>>
>>Finding out that it's a sqlite file is not a hard problem for an
>>attacker who has any interesting access to your machine, since your
>>programs must find that file somehow.  Once they find it, are you not
>>concerned about lightening their cryptanalysis burden through
>>known-plaintext attacks?
>>
>>Mike
>>
> No, not really.  The sqlite crypto engine consumes the first several
> hundred bytes of the rc4 random number generator output.  It is my
> understanding that this would significantly complicate the plain-text
> attack.  But I'm not a crytologist.  I do find it facinating though.
>
> I do not understand how "finding the file" would give the attackers 
any
> clue to what kind of file it is (unless I make the filename something 
like
> "sqlite3.db3").  If the file were named "jimbob.dat", and the contents
> looked like gibberish, then what do they know?  They must analyze the
> program that accesses the file.
>
> I once thought that I could remove all text strings from the sqlite 
code
> that would give the attacker any clues.  I then realized that the 
strings
> are important to the proper functioning.  The ones that need to be 
left
> behind are significant enough to be good clues that the program uses
> sqlite technology.  So, I do agree with you, that it is not too 
difficult
> to determine if a data file _might_ be an sqlite database, even if it 
in
> encrypted.
>
> That being said, I still like having the header encrypted as it is.  
Maybe
> it just makes me feel warm and fuzzy on the inside :)
>
> In the end, I feel that our software is much more vulnerable to 
someone
> attacking it with a debugger than with crypto analytic attacks.  At 
some
> point, you must call "sqlite3_key()" and pass it three things: the 
sqlite
> handle, a void* to the key initializer and an "int" (# of bytes in the
> key).  All the attacker has to do is locate that code and determine 
what
> those last two arguments are.  Personally, I find this to be an easier
> approach.  But then, I've been coding in assembly since I was 8 and C 
for
> the last 10 years.  I'm not much of a mathematician or code breaker.
>
> I have often wondered how difficult it would be to derive the rc4
> initialization key given a known plain text and a known cipher text
> generated from the unknown key and known plain text.  I imagine it as 
a
> breadth-first search of the key space.
> Lets say that it is computationally feasible to do just that.  The 
sqlite
> header string is.. um, heck, I 

[sqlite] Integrity Check Pragma

[Drew, Stephen]

Hi,
 
Sorry to ask a stupid question, but how exactly do I get the return
value from this pragma?
 
Thanks in advance,
Steve

RE: [sqlite] database disk image is malformed

[Drew, Stephen]

Sorry for the lack of information.

I'm using Windows 2000, the file is on a local drive with full access
permissions. I've done some checking on memory over the last few weeks,
but nothing conclusive so I will give this a go.

The odd thing is it happens during a piece of functionality that is used
continuously, yet the error only surface occasionally, and the disk
image is no longer malformed when I restart the application.

Thanks.
Steve 

-Original Message-
From: Christian Smith [mailto:[EMAIL PROTECTED] 
Sent: 26 May 2005 17:34
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database disk image is malformed

On Thu, 26 May 2005, Drew, Stephen wrote:

>Hello,
>
>Assuming there is no external interference, how could one cause this 
>error to occur through embedded use of SQLite?  And why, following a 
>restart of my application, does it not happen again immediately?  It 
>seems most odd...
>
>Any clues would be most appreciated. This is SQLite 2.8.15.


Without details, it's difficult to say. What platform you running on? Is
the file on a network drive? Have you checked your application against
buffer overruns and stray pointers?


>
>Regards,
>Steve
>

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

[sqlite] database disk image is malformed

[Drew, Stephen]

Hello, 

Assuming there is no external interference, how could one cause this
error to occur through embedded use of SQLite?  And why, following a
restart of my application, does it not happen again immediately?  It
seems most odd... 

Any clues would be most appreciated. This is SQLite 2.8.15. 

Regards, 
Steve 

RE: [sqlite] Pragmas

[Drew, Stephen]

Attachment included:


#include "sqlite3.h"
#include 
#include 
#include 
#include 

#define CHECK_CODE(db, rc, msg) \
if (rc!=SQLITE_OK)  \
{ do_exit(db, rc, msg); }   

char* test_pragmas[][2] = { { "PRAGMA short_column_names=OFF;", "PRAGMA
full_column_names=OFF;" }
  , { "PRAGMA
short_column_names=ON;", "PRAGMA full_column_names=OFF;" }
  , { "PRAGMA
short_column_names=OFF;", "PRAGMA full_column_names=ON;" }
};

void do_exit(sqlite3* db, int rc, char* msg=NULL)
{
printf("Exiting with error code %d\n", rc);
if (msg)
printf("%s\n", msg);
if (db)
printf("%s\n", sqlite3_errmsg(db));
exit(0);
}

void execute(sqlite3* db, char* sql)
{
char* zErrMsg=NULL;
int rc = sqlite3_exec(db, sql, 0, 0, );
CHECK_CODE(db, rc, zErrMsg);
}

void query(sqlite3* db, char* sql)
{
const char* zTail=NULL;
sqlite3_stmt*   vm=NULL;
int rc=sqlite3_prepare(db, sql, (int) strlen(sql), , );
CHECK_CODE(db, rc, "Error preparing SQL query");
if (vm==NULL)
CHECK_CODE(db, 1, "Error prerparing SQL query 2");

int col_qty = sqlite3_column_count(vm);

// TODO: add data type and flags
for (int i=0; i<col_qty; ++i) 
printf("Column %d: %s\n", i, sqlite3_column_name(vm,i));
}

int main(int argc, char** argv)
{
for (int i=0; i<3; ++i)
{
sqlite3* db=NULL;
char db_name[30];
sprintf(db_name, "c:\\test%d.db", i);

int rc = sqlite3_open(db_name, );
CHECK_CODE(db, rc, "DB failed to open");

execute(db, test_pragmas[i][0]);
execute(db, test_pragmas[i][1]);
execute(db, "CREATE TABLE TEST1 (id NUMERIC, data
VARCHAR2);");
execute(db, "CREATE TABLE TEST2 (id NUMERIC, data2
VARCHAR2);");

//query(db, "SELECT * FROM TEST1;");
query(db, "SELECT T1.*, T2.* FROM TEST1 T1, TEST2 T2
WHERE T1.id = T2.id;");

sqlite3_close(db);
}

while(true)
Sleep(100);
} 

-Original Message-
From: Drew, Stephen 
Sent: 04 May 2005 18:13
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Pragmas

Hello,

I have included a C++ file which will compile against SQLite 3.2.1 under
Visual C++ 7 in Windows (it is almost C, and only has one Win32 Api call
- to sleep at the end).  I would be grateful if somebody who has
experience using these column name pragmas could take a look and see if
either:

(a) This documentation is incorrect:
http://www.sqlite.org/cvstrac/wiki?p=ColumnNames

(b) I am not using SQLite correctly.

Here are my results:

CREATE TABLE TEST1 (id NUMERIC, data VARCHAR2); CREATE TABLE TEST2 (id
NUMERIC, data2 VARCHAR2);

Query = "SELECT T1.*, T2.* FROM TEST1 T1, TEST2 T2 WHERE T1.id = T2.id;"

Short=OFF, Full=OFF
Column 0: id
Column 1: data
Column 2: id
Column 3: data2
This should be the same as the third scenario, as my query is a join
involving two tables.

Short=ON, Full=OFF (default, but set anyway) Column 0: id Column 1: data
Column 2: id Column 3: data2 This is as expected from the docs.

Short=OFF, Full=ON
Column 0: id- should be T1.id
Column 1: data  - should be T1.data
Column 2: id- should be T2.id
Column 3: data2 - should be T2.data2
This is wrong, according to the documentation. These strike me as more
of what I would expect had I run the query: SELECT * FROM TEST1 T1,
TEST2 T2 WHERE T1.id=T2.id.

These clearly do not match the documentation. Also, the documentation
example does not provide the core comparison case of a query joining two
or more tables.

I would really appreciate help on this if anyone can spare the time. I
need to either move to SQLite 3 or find an alternative.  I cannot move
to SQLite 3 if I cannot replicate the behaviour of SQLite 2, even with
pragmas.

Many thanks,
Steve



-Original Message-
From: Drew, Stephen
Sent: 03 May 2005 15:49
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Pragmas

Understood.

I fall into case 5, and this is definitely not the way they're
represented. So assuming my code was correct, and the documentation
refers to 3.2.1, the documentation is STILL incorrect?

Regards,
Steve 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 03 May 2005 15:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Pragmas

On Tue, 2005-05-03 at 15:09 +0100, Drew, Stephen wrote:
> This is not the case for the version of SQLite 3.2.1 I am using.   Is
> the documentation incorrect, or am I doing anything wrong?
>

Documentation is wrong.  See http://www.sqlite.org/cvstrac/wiki?
p=ColumnNames
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Pragmas

[Drew, Stephen]

Hello,

I have included a C++ file which will compile against SQLite 3.2.1 under
Visual C++ 7 in Windows (it is almost C, and only has one Win32 Api call
- to sleep at the end).  I would be grateful if somebody who has
experience using these column name pragmas could take a look and see if
either:

(a) This documentation is incorrect:
http://www.sqlite.org/cvstrac/wiki?p=ColumnNames

(b) I am not using SQLite correctly.

Here are my results:

CREATE TABLE TEST1 (id NUMERIC, data VARCHAR2);
CREATE TABLE TEST2 (id NUMERIC, data2 VARCHAR2);

Query = "SELECT T1.*, T2.* FROM TEST1 T1, TEST2 T2 WHERE T1.id = T2.id;"

Short=OFF, Full=OFF
Column 0: id
Column 1: data
Column 2: id
Column 3: data2
This should be the same as the third scenario, as my query is a join
involving two tables.

Short=ON, Full=OFF (default, but set anyway)
Column 0: id
Column 1: data
Column 2: id
Column 3: data2
This is as expected from the docs.

Short=OFF, Full=ON
Column 0: id- should be T1.id
Column 1: data  - should be T1.data
Column 2: id- should be T2.id
Column 3: data2 - should be T2.data2
This is wrong, according to the documentation. These strike me as more
of what I would expect had I run the query: SELECT * FROM TEST1 T1,
TEST2 T2 WHERE T1.id=T2.id.

These clearly do not match the documentation. Also, the documentation
example does not provide the core comparison case of a query joining two
or more tables.

I would really appreciate help on this if anyone can spare the time. I
need to either move to SQLite 3 or find an alternative.  I cannot move
to SQLite 3 if I cannot replicate the behaviour of SQLite 2, even with
pragmas.

Many thanks,
Steve



-Original Message-----
From: Drew, Stephen 
Sent: 03 May 2005 15:49
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Pragmas

Understood.

I fall into case 5, and this is definitely not the way they're
represented. So assuming my code was correct, and the documentation
refers to 3.2.1, the documentation is STILL incorrect?

Regards,
Steve 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 03 May 2005 15:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Pragmas

On Tue, 2005-05-03 at 15:09 +0100, Drew, Stephen wrote:
> This is not the case for the version of SQLite 3.2.1 I am using.   Is
> the documentation incorrect, or am I doing anything wrong?
>

Documentation is wrong.  See http://www.sqlite.org/cvstrac/wiki?
p=ColumnNames
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Pragmas

[Drew, Stephen]

Understood.

I fall into case 5, and this is definitely not the way they're
represented. So assuming my code was correct, and the documentation
refers to 3.2.1, the documentation is STILL incorrect?

Regards,
Steve 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: 03 May 2005 15:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Pragmas

On Tue, 2005-05-03 at 15:09 +0100, Drew, Stephen wrote:
> This is not the case for the version of SQLite 3.2.1 I am using.   Is
> the documentation incorrect, or am I doing anything wrong?
>

Documentation is wrong.  See http://www.sqlite.org/cvstrac/wiki?
p=ColumnNames
--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Pragmas

[Drew, Stephen]

Hello,
 
Sorry to keep on harping on about this, but the documentation for the
full_column_names pragma states clearly:
 
"...Normally, such result columns are named
 if the SELECT statement joins two or
more tables together, or simply  if the SELECT statement
queries a single table."
 
This is not the case for the version of SQLite 3.2.1 I am using.   Is
the documentation incorrect, or am I doing anything wrong?
 
The sequence I take is (somewhat simplified):
 

   sqlite3* db;
   int rc = sqlite3_open(":memory:", );
 

   char* zErrMsg=NULL;
 
   // Location 1
 
   rc = sqlite3_exec(db, "CREATE TABLE TEST1 (id INTEGER, data
VARCHAR2);", 0, 0, );
   rc = sqlite3_exec(db, "CREATE TABLE TEST2 (id INTEGER, data2
VARCHAR2);", 0, 0, );
 
   // Then this query is executed (sqlite3_prepare,
sqlite3_step, sqlite3_finalize, )
   // SELECT T1.*, T2.* FROM TEST1 T1, TEST2 T2 WHERE T1.id =
T2.id
 
   // Column names are then retrieved used sqilte3_column_count,
sqlite3_column_name
 

Now no column name pragmas have been executed, and yet the column names
are returned as  despite the fact there is clearly a join
between two tables.
 
Executing either the short_column_names or full_column_names pragmas at
"Location 1" appears to make no difference whatsoever to the returned
names.
 
I have changed my rowset class to accept multiple columns with the same
name, but our legacy code (which used SQLite 2.8.15) has literally
hundreds of queries which depended on the old naming convention.
 
Any more help would be greatly appreciated - especially explaining what
I may have done wrong about the pragma calls.
 
Many thanks in advance,
Steve
   

RE: [sqlite] RDBMS handling of column names (was: Trouble with column names)

[Drew, Stephen]

This is fine for me too.  However, in 3.1.0 neither of these pragmas
appeared to work. Are they fixed in a later release or check-in?  Sorry,
but I couldn't find this info on the website.



-Original Message-
From: George Ionescu [mailto:[EMAIL PROTECTED] 
Sent: 27 April 2005 14:18
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RDBMS handling of column names (was: Trouble with
column names)

Hello Dr. Hipp,
Hello dear sqlite users,

> > And I don't really think I'm asking much: all I want is that when I 
> > ask
for
> > Field1 column, the database engine to report Field1 and not FIELD1.
> >
>
> Set PRAGMA short_column_names=OFF; and that will happen.
> Why doesn't that solution work for you?

Hmmm, too much caffeine and less sleep! I didn't notice, neither by
reading the docs nor by experimenting with command line tool, that
setting short_column_names=OFF is just what I need and think is the
correct way of handling column names.

Sorry for insisting so much. I didn't want to start a riot...

Thanks again.
George.

RE: [sqlite] Column Names (revisited)

[Drew, Stephen]

Hello,

Has anyone else had any problems getting the column names pragmas
working in SQLite 3.x ?

I can't seem to get them to work, and can't find anything relating to
the latest version on the website.

Thanks,
Steve 

-Original Message-
From: Drew, Stephen 
Sent: 06 April 2005 11:57
To: sqlite-users@sqlite.org
Subject: [sqlite] Column Names (revisited)

Hello,
 
I've been looking into upgrading to 3.2.1 from 2.8.15 and am still
running into problems with returned column names.
 
I've written a piece of code to test four scenarios with 2.8.15 and
3.2.1 and these are the results:
 
Two tables:
CREATE TABLE A_TABLE (col1 INTEGER, col2 INTEGER) CREATE TABLE B_TABLE
(col1 INTEGER, col2 INTEGER)

Two queries:
1. SELECT A.*, B.* FROM A_TABLE, B_TABLE WHERE A.col1 = B.col1 2. SELECT
* FROM A_TABLE
 
The column names are returned as follows (with the relevant column names
pragmas).


---
TEST PRAGMAS2.8.15
3.2.1

--- 
1 (short=0, full=0) A.col1, B.col1, ...
col1, col1, ... 
2 (short=0, full=0) col1, col2, ...
col1, col2, ...

1 (short=1, full=0) col1, col1, ...
col1, col1, ... 
2 (short=1, full=0) col1, col2, ...
col1, col2, ...

1 (short=0, full=1) A_TABLE.col1, B_TABLE.col1, ...
col1, col1, ... 
2 (short=0, full=1) A_TABLE.col1, A_TABLE.col2, ...
col1, col2, ...

---

In other words, the pragmas seem to have NO effect when the same piece
of code compiled against 2.8.15 is compiled against 3.2.1.  I ALWAYS get
returned just the column names.

All I need to do is guarantee that the column names will turn out like
the first two rows of the table !!

Can anyone offer any help?  I'm using standard sqlite_step
functionality, which is working as expected in 2.8.15.  

Regards,
Steve

[sqlite] Column Names (revisited)

[Drew, Stephen]

Hello,
 
I've been looking into upgrading to 3.2.1 from 2.8.15 and am still
running into problems with returned column names.
 
I've written a piece of code to test four scenarios with 2.8.15 and
3.2.1 and these are the results:
 
Two tables:
CREATE TABLE A_TABLE (col1 INTEGER, col2 INTEGER)
CREATE TABLE B_TABLE (col1 INTEGER, col2 INTEGER)

Two queries:
1. SELECT A.*, B.* FROM A_TABLE, B_TABLE WHERE A.col1 = B.col1
2. SELECT * FROM A_TABLE
 
The column names are returned as follows (with the relevant column names
pragmas).


---
TEST PRAGMAS2.8.15
3.2.1

--- 
1 (short=0, full=0) A.col1, B.col1, ...
col1, col1, ... 
2 (short=0, full=0) col1, col2, ...
col1, col2, ...

1 (short=1, full=0) col1, col1, ...
col1, col1, ... 
2 (short=1, full=0) col1, col2, ...
col1, col2, ...

1 (short=0, full=1) A_TABLE.col1, B_TABLE.col1, ...
col1, col1, ... 
2 (short=0, full=1) A_TABLE.col1, A_TABLE.col2, ...
col1, col2, ...

---

In other words, the pragmas seem to have NO effect when the same piece
of code compiled against 2.8.15 is compiled against 3.2.1.  I ALWAYS get
returned just the column names.

All I need to do is guarantee that the column names will turn out like
the first two rows of the table !!

Can anyone offer any help?  I'm using standard sqlite_step
functionality, which is working as expected in 2.8.15.  

Regards,
Steve

RE: [sqlite] SQLite Advocacy

[Drew, Stephen]

Shawn,

"If SQLite is as good as you say, then why is it free?"

Things don't have to cost the earth (or anything at all) to be good! 

"If anyone can see the source code, then won't we be venerable to
hackers?"

There is a encryption package available, at a cost.
http://www.hwaci.com/sw/sqlite/prosupport.html

Regards,
Steve 

-Original Message-
From: Downey, Shawn [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 31, 2005 3:58 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite Advocacy

Thank you for your replies (both yours and Jalil's).  I understand that
SQLite is not universally applicable.  Let me give you some background
to better explain my request.

We currently have a product (which we inherited the responsibility from
another company).  This product was originally written to use SQLite.
We have expanded the this product (continuing to use SQLite).

Now our organization is partnered with another company which has a
related product which uses SQL Server 7.  This new partner is pushing my
management to convert our product over to use SQL Server 7 instead of
SQLite.  The intent is that we can use a single database format at some
point in the future.

I can see no earthy reason to move in that direction.  If a single
database is needed, I would like to see it be SQLite.  I am convinced
that SQLite could fill both companies our needs. 

I am sorry I can not disclose more about the products in question.  What
I am looking for are some talking points which can convince my own
management to push back against converting all our good work into SQL
Server 7.  I am trying to overcome the attitude of:

"If SQLite is as good as you say, then why is it free?"

and 

"If anyone can see the source code, then won't we be venerable to
hackers?"

etc.

Thanks again.

Shawn M. Downey
MPR Associates
632 Plank Road, Suite 110
Clifton Park, NY 12065
518-371-3983 x3 (work)
860-508-5015 (cell)


-Original Message-
From: Fred Williams [mailto:[EMAIL PROTECTED]
Sent: Monday, January 31, 2005 10:32 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite Advocacy

I think if your organization is truly looking at SQLite vs SQL Server,
you need to reconsider your project specifications.  You are basically
comparing a Corvette to a  Greyhound Bus.

Fred

-Original Message-
From: Downey, Shawn [mailto:[EMAIL PROTECTED]
Sent: Monday, January 31, 2005 8:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite Advocacy


In our organization, my management is debating the use of SQLite vs. MS
SQL Server 7 for an upcoming MS Windows project.  Does anyone have any
SQLite advocacy info I can use?  Perhaps a link to an article?  I am
hoping someone had written something eloquent about SQLite's advantages
such as its size, speed, zero-installation, etc.

...

RE: [sqlite] Unable to open database file

[Drew, Stephen]

Thanks Clay,

It's definitely Starteam - I used FileMonitor from SysInternals to
identify it.

My database is running on a removable drive, which may be the cause I
guess. If it gets temporarily disconnected or something (or this is
usually displayed on the screen). 

You are right in the last paragraph, that as Starteam is not even
interested in the file, it is unlikely to be the cause, although
empirical evidence suggests otherwise.

I shall keep investigating. Thanks for the help.

Regards,
Steve

-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 9:42 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Unable to open database file


Drew, Stephen said:
> Yet Starteam (version control system) is still interfering with the 
> file
> - causing unexpected errors while the application is running.  Is this

> because the locking in Windows is below-par in comparison with the 
> Unix locking?  The thing is that Starteam is not even interested in 
> the file, only the directory (I guess it is scanning the file to check

> it's not been added to version control).

First, verify that it's really StarTeam touching the process and fouling
it up.  You might also want to make sure that you're only using
timestamps and not md5 sums to verify file integrity.  Trust me, as a
fellow StarTeam victim, that using md5 sums in StarTeam is not a
reliable way to determine file changes (having nothing to do with md5
and everything to do with StarTeam).

Second, check if this thing is running on a local or network drive. 
SQLite isn't recommended for network drives, and StarTeam most assuredly
isn't suitable for network drives.

The truth is that StarTeam probably isn't causing the problem, since it
isn't interested in writing to the file.  If it is causing the problem,
consider one of the free source control systems.  I've found CVS at
least to be a lot less problematic than StarTeam.

Clay
--
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web
development

RE: [sqlite] sqlite search by "DATE" range ?

[Drew, Stephen]

Have you considered using the date functionality supported by SQLite?

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions 

-Original Message-
From: teoh [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 27, 2005 4:22 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite search by "DATE" range ?

 hi,  i created  

date_year smallint,date_month smallint, date_day smallint, time text  in
sqlite database.

does anyone knows algorithm use to quote date range?  
let say, user wants to quote for result from
20-12-2004 until 14-1-2005 ?(dd-mm-)

thank you for clarifying. 



__
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

RE: [sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

I must confess that for my purposes I'd prefer the columns to have
prefixes if prefixes were used in the query, or there is more than one
table in the query. In other words:

SELECT A.* FROM A
A.a_col, A.b_col

SELECT * FROM A
a_col, b_col

SELECT * FROM A, B WHERE A.a_col = B.b_col
A.a_col, A.b_col, B.a_col

SELECT A.*, B.* FROM A, B WHERE A.a_col = B.b_col
A.a_col, A.b_col, B.a_col

Obviously this can be overridden by using aliases:

SELECT A.a_col aa_col, A.b_col ab_col, B.a_col ba_col
aa_col, ab_col, ba_col

Regards,
Steve

-Original Message-
From: CARIOTOGLOU MIKE [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 26, 2005 10:07 AM
To: 'sqlite-users@sqlite.org'
Subject: RE: [sqlite] SQLite 3.1.0 Column Names

Ok, since this issue with column names has gone on long enough, and we
seem to be getting nowhere, I would like to propose an API change.

Here is the rationale :

Normally, a query does not need or can use "qualified" names (like
something.something). An explicit, hard-coded query knows the structure
of The result set, so it should/would not rely on column names as such.
It is only generic code, and tool-level code that worries about these
things. For example, an ODBC or OLEDB driver that uses sqlite
underneath, needs to know the column *origin*. 
Also, the semantics and current buggy implementation of the pragmas
relating to this issue, have been very frustrating to tool writers, I
believe.
So, what about this :

A. Drop the pragmas (short_column and full_column) completely. They are
stateful, which implies an extra headache for driver-level code.

B. always name the columns using short names (ie just a column name, no
qualification). If duplicate column names result in this way, either
de-dupe them by adding a numeric suffix (ID, ID_1 etc) or just leave the
duplicates alone. Higher-level code can handle this anyway.

C. Add a new API: sqlite3_column_table(idx). It should return the table
associated with a column, if it can be defined (and, by the way, it
should probably NOT return the table ALIAS, as the current
implementation of short_column_names does, but the real table name. who
cares about the alias,anyway). If not known, return NIL. 

D. nice to have : this should work for views as well, either when a view
is queried by itself, or if it is part of a join. 
In other words :

If :
Create table t1 (id,name)
Create table t2(id,name)
Create view v1 as select * from t1

Then:

Select * from v1

Gives sqlite3_column_table(0)="t1", not "v1" (and perhaps a flag leting
the user know that this came from a view)

And,

Select t2.*,v1.* from t2,v1 where t2.id=v1.id

Also returns table name of "t1" for all columns coming from v1.

I am not sure if this is doable, but it would be nice to have. Why ?
Because a resolver engine, that generates sql, can effectively make
views updateable.

This proposed change would clean up the issue with the pragmas, give
default column names that are compatible with all other sql engines I
know of,
*and* it would still give the ability to the interested user to find out
the
*origin* of a result set column.

The idea can be expanded: we could also have this api function:

Int sqlite3_column_origin(idx)

Result:
 0 = comes from table
 1 = comes from view
 2 = comes from expression

Etc etc

Why don't we vote on this issue, and if we can find enough support, try
to convince DRH to implement it ?

I, for one, am strongly in favor, since I am developing both an OLEDB
driver (which I intend to make public), and a utility (SqliteExplorer)
which would benefit a lot by such APIs, and I am sure I am not the only
one.

RE: [sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

Kurt,

Apologies.  I believe I have also tried:

Exec("PRAGMA full_column_names=1;")

Steve 

-Original Message-----
From: Drew, Stephen 
Sent: Tuesday, January 25, 2005 4:33 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite 3.1.0 Column Names

Kurt,

Thanks for the reply. Assume my pseudo-calls below do the following:

Open:   calls sqlite3_open
Exec:   calls sqlite3_exec
Query:  calls sqlite3_prepare, sqlite3_step, ..., sqlite3_finalize
Close:  calls sqlite3_close

And check return values of these functions.

I am doing the following:

Open();
Exec("BEGIN");
Exec("PRAGMA full_column_names;");
Exec("CREATE TABLE A (a_col TEXT, PRIMARY KEY (a_col));") Exec("CREATE
TABLE B (b_col TEXT, a_col TEXT, PRIMARY KEY (b_col));") Exec("COMMIT");
Exec("SELECT A.*, B.* FROM A, B WHERE A.a_col = B.b_col");

Now these tables are empty, but the column names are still retrievable.

In SQLite 2.8.15, the following names are returned:

A.a_col, B.b_col, B.a_col

In SQLite 3.1.0, regardless of whether either pragma (full_column_names
or short_column_names) is executed at the location above, the following
names are returned:

a_col, b_col, a_col.

I need these to be unique!  Am I missing something?  Are they
compile-time pragmas?

Thanks,
Steve


-Original Message-
From: Kurt Welgehausen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 25, 2005 4:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite 3.1.0 Column Names

> PRAGMA full_column_names;
>
> PRAGMA short_column_names;

Did you really set them, or just query them?

<http://www.sqlite.org/pragma.html>:

 PRAGMA full_column_names;
 PRAGMA full_column_names = 0|1;

 Query or change the short-column-names flag. ...

Regards

RE: [sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

Kurt,

Thanks for the reply. Assume my pseudo-calls below do the following:

Open:   calls sqlite3_open
Exec:   calls sqlite3_exec
Query:  calls sqlite3_prepare, sqlite3_step, ..., sqlite3_finalize
Close:  calls sqlite3_close

And check return values of these functions.

I am doing the following:

Open();
Exec("BEGIN");
Exec("PRAGMA full_column_names;");
Exec("CREATE TABLE A (a_col TEXT, PRIMARY KEY (a_col));")
Exec("CREATE TABLE B (b_col TEXT, a_col TEXT, PRIMARY KEY (b_col));")
Exec("COMMIT");
Exec("SELECT A.*, B.* FROM A, B WHERE A.a_col = B.b_col");

Now these tables are empty, but the column names are still retrievable.

In SQLite 2.8.15, the following names are returned:

A.a_col, B.b_col, B.a_col

In SQLite 3.1.0, regardless of whether either pragma (full_column_names
or short_column_names) is executed at the location above, the following
names are returned:

a_col, b_col, a_col.

I need these to be unique!  Am I missing something?  Are they
compile-time pragmas?

Thanks,
Steve


-Original Message-
From: Kurt Welgehausen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 25, 2005 4:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite 3.1.0 Column Names

> PRAGMA full_column_names;
>
> PRAGMA short_column_names;

Did you really set them, or just query them?

:

 PRAGMA full_column_names;
 PRAGMA full_column_names = 0|1;

 Query or change the short-column-names flag. ...

Regards

[sqlite] SQLite 3.1.0 Column Names

[Drew, Stephen]

Hello,
 
I have two 
tables:
 
"CREATE TABLE A (a_col 
TEXT, PRIMARY KEY (a_col))"

"CREATE TABLE B 
(b_col TEXT, a_col TEXT, PRIMARY KEY (b_col))"
I am trying to test the column names returned by the following 
query:
SELECT A.*, B.* FROM A, B WHERE A.a_col = 
B.a_col
Now whether or not I have one of these pragmas set after opening 
the transaction:
PRAGMA full_column_names;
PRAGMA short_column_names;
, the resulting column names are always:
a_col, b_col, a_col
breaking the unique 
constraint on column names in my column set.  Can anyone spot anything 
wrong with this?
 
Regards,Stephen Drew 

RE: [sqlite] v2 -> v3 upgrade

[Drew, Stephen]

Will,

Thanks for the reply. Yes in version 2 I would do something like:

CREATE TABLE TEST (d DATE);

And use the DATE returned in the column names (using the show datatypes
pragma).

>From the documentation, it appears that sqlite3_column_type returns one
of five defined types, none of which is a date (obviously, as SQLite
doesn't distinguish dates). Is there a way I can continue using the
text-based data type (in addition to the new data type functionality)?

Regards,
Steve 

-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 21, 2005 3:47 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] v2 -> v3 upgrade

On Fri, 21 Jan 2005 14:30:01 -, Drew, Stephen <[EMAIL PROTECTED]>
wrote:

> However, with version 3 and its new data types, I can't seem to be 
> able to replicate this.  It seems as though it will just be treated as

> a numeric data type. Does anyone know how I can achieve the same
functionality?

I believe the "data type string" you are referring to in v2 is the
user-defined column type, not the intrinsic type of the data itself.
There is an API call for fetching the user-defined column type:

int sqlite3_column_type(sqlite3_stmt*, int iCol);

[sqlite] v2 -> v3 upgrade

[Drew, Stephen]

Hello,
 
I'm trying to upgrade my 
wrapper library for SQLite to use version 3.  Everything is going well so 
far, with one exception.
 
In Version 2.x, I would 
use the data type string which is returned after the column names in call 
to sqlite_step().  This allowed me to distinguish dates from text by using 
DATE as the type.  
 
However, with version 3 
and its new data types, I can't seem to be able to replicate this.  It 
seems as though it will just be treated as a numeric data type. Does anyone know 
how I can achieve the same functionality?
 
Regards,Stephen Drew 

RE: [sqlite] $ in table and column names

[Drew, Stephen]

My viewpoint on this is that $ should be allowed in both table names and
column names and that:

 CREATE TABLE ex$1( col$abc INTEGER );

Should work as-is. It does in Oracle SQL. 

If other languages/systems use the $ for other purposes, surely they can
(or will be forced to) just avoid using the $ in these names?

-Original Message-
From: Mrs. Brisby [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 18, 2005 3:42 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] $ in table and column names

On Mon, 2005-01-10 at 18:00 -0500, D. Richard Hipp wrote:
> A user has reported a bug saying that SQLite does not allow the '$' in

> the middle of indentifiers (without quoting).  The bug reports says 
> that statements like this:
> 
> CREATE TABLE ex$1( col$abc INTEGER );
> 
> are legal and work fine in other database engines.
> This seems very odd to me.  Can anybody else confirm the use of '$' in

> the middle of identifier names in other database engines?

I find quoting to be satisfactory, and use it whenever attributes are
called for that aren't of the set [a-zA-Z]+

Nevertheless, I think it should be useful to understand why someone
would want this. I presume most people on this list are familiar with $-
expansion in most modern interpretive languages (esp. considering the
strong ties to php, tcl, and perl the list members seem to have).

Fortran allows the dollar-sign in variable names, and it's common! Many
Fortran programmers use the dollar-sign in the absence of real
structures and classes- I can imagine this making porting Fortran code
easier.

VMS uses the dollar-sign in a similar way- to separate classes (think:
SYS$CURRENCY or COB$CARDREADER :) )

Many REXX programmers use the dollar-sign as part of an identifier to
represent a kind of "global" scope (e.g. $.foo and EXPOSE $.) - some
REXX programmers end up "thinking" SQL in the same way.

Now a dissent: Some SQL interfaces (Informix comes to mind) use the
dollar-sign BECAUSE it's not allowed in SQL to mean "this follows is
SQL". I cannot imagine allowing the dollar-sign _in_ identifiers would
mess this up, but it might screw up Really Stupid Parsers[tm].

I don't think it would make many TCL or PERL programmers happy to see $
being what (in their mind) could be overloading. It might introduce
confusion. Simply requiring it be quoted could help alleviate it. Then
again, those programmers aren't likely to take advantage of it.

Again: I'm fine with quoting. I don't particularly like the idea of
barewords changing meanings, but I confess to taking advantage of it.

RE: [sqlite] Strange UPPER() behaviour

[Drew, Stephen]

OK, well I guess I should be moving to version 3 soon anyway! Another
reason to give to my boss...

Thanks for the help.

-Original Message-
From: Simon Middleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 18, 2005 1:11 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Strange UPPER() behaviour

Drew, Stephen wrote:
> Simon, Thanks for the response.  Two points:
> 
> 1) The code is in the SQLite library, not my application.

Oh yes - so it is. And in the copy I have here (v3) it does in fact use
"unsigned char *".

> 2) Surely if chars are signed by default in MSVC, then islower() 
> should work with this default?

You'd have thought so wouldn't you? But that's not been the case in my
experience.

> PS - I have never had a problem with this before...

Possibly there is something else that would prevent from this showing up
as a problem - but I'm not aware of one.

-- 

Simon Middleton, Senior Software Engineer

ANT Limited, Cambridge Business Park, Cowley Road, Cambridge CB4 0WZ, UK
Tel +44 (0)1223 716413 Switch +44 (0)1223 716400  Fax +44 (0)1223 716401
<[EMAIL PROTECTED]>   http://www.antlimited.com

TI integrates ANT Galio for advanced digital TV applications
www.antlimited.com/news/2005-01-05-ti-ces.htm

* ANT LIMITED LEGAL DISCLAIMER *
This message (which includes any files transmitted with it) is
confidential and contains information which may be legally privileged.
It is intended for the stated addressee(s) only.
Access to this email by anyone else is unauthorised. If you are not the
intended addressee, any disclosure, copying or storage of the contents
of this email, or any action taken (or not taken) in reliance on it, is
unauthorised and is unlawful.
If you are not the addressee, please delete the original and any copies
and inform the sender immediately.

RE: [sqlite] Strange UPPER() behaviour

[Drew, Stephen]

Simon,
Thanks for the response.  Two points:

1) The code is in the SQLite library, not my application. 

2) Surely if chars are signed by default in MSVC, then islower() should work 
with this default?

PS - I have never had a problem with this before...

Regards,
Steve

-Original Message-
From: Simon Middleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 18, 2005 12:57 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Strange UPPER() behaviour

Drew, Stephen wrote:
> Hello,
> I was running my application which uses SQLite in debug mode, and I 
> noticed some very bizarre behaviour in this function:
>  
> The character in question was the pound sign ('£') whose value the 
> debugger said was -93 (the watch window however said 163).  It seems 
> that inside my implementation of islower, it does the following assertion:

Sounds like chars in your environment are signed by default. This means that 
any char that you expect to be between 128 and 255 reads as -128 to
-1 instead. This often doesn't matter if you are just writing the char back to 
memory but if you try and pass it to an OS routine like
toupper() or cast it to unsigned as you do then you'll get incorrect results.

Your choices are to either change your compile settings so that chars are 
unsigned by default (probably safest) or to change your code to explicitly use 
unsigned chars, declare z as 'unsigned char *z'.

Hope this helps.

-- 

Simon Middleton, Senior Software Engineer

ANT Limited, Cambridge Business Park, Cowley Road, Cambridge CB4 0WZ, UK Tel 
+44 (0)1223 716413 Switch +44 (0)1223 716400  Fax +44 (0)1223 716401
<[EMAIL PROTECTED]>   http://www.antlimited.com

TI integrates ANT Galio for advanced digital TV applications 
www.antlimited.com/news/2005-01-05-ti-ces.htm

* ANT LIMITED LEGAL DISCLAIMER *
This message (which includes any files transmitted with it) is confidential and 
contains information which may be legally privileged. It is intended for the 
stated addressee(s) only.
Access to this email by anyone else is unauthorised. If you are not the 
intended addressee, any disclosure, copying or storage of the contents of this 
email, or any action taken (or not taken) in reliance on it, is unauthorised 
and is unlawful.
If you are not the addressee, please delete the original and any copies and 
inform the sender immediately.

[sqlite] Strange UPPER() behaviour

[Drew, Stephen]

Hello,
I was running my 
application which uses SQLite in debug mode, and I noticed some very bizarre 
behaviour in this function:
 

static void upperFunc(sqlite_func *context, 
int argc, const char **argv){
char *z;
int i;
if( argc<1 || argv[0]==0 ) return;
z = sqlite_set_result_string(context, argv[0], -1);
if( z==0 ) return;
for(i=0; z[i]; i++){
if( islower(z[i]) ) z[i] = toupper(z[i]);
}
}
The character in question 
was the pound sign ('£') whose value the debugger said was -93 (the watch window 
however said 163).  It seems that inside my implementation of islower, it 
does the following assertion:
 
_ASSERTE((unsigned)(c + 1) <= 256);
 
Now as the 
character is passed in as an integer, this gets converted from -93 to 4 million 
something, and fails.  Is this expected / acceptable?  It blows up my 
application!
 
As an aside, it is more 
performant to call islower() and then decide whether to call toupper()?  
Surely this results in n calls to islower() and some calls to 
toupper(), whereas simply calling toupper() would result in n calls, 
and it obviously does the same checking inside this function?
 
Regards,
Steve
 

RE: [sqlite] coding style

[Drew, Stephen]

Also, the data types (in 2.8.15) do not appear to copy over into the newly 
created table...

I had to manually create the table with SQL, then insert into it from the query.

-Original Message-
From: Jakub Adámek [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 13, 2004 3:08 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] coding style

Yes but then I must repeat the column names in every query in which I use the 
view. It is a bug in SQLite, isn't it?

Jakub

Brass Tilde ([EMAIL PROTECTED]) wrote*:
>
> > create view myview as select t1.a a from t1 inner join t2 on 
> > t1.a=t2.a; create table problem as select * from myview;
>
> Change this last line to:
>
> create table problem as select a as a from myview;
>
> That creates the problem table with just "a" as the field name.
>

[sqlite] Temp Store

[Drew, Stephen]

Hi 
there,
 
Sorry to bother you with 
a simple question, but where does this reside by default (i.e. 
file)?
 
I have changed my project 
to use in-memory temp-store, but the program won't start as the temp store 
already exists on disk.  I can't locate the file however - I have deleted 
the db file and the journal, but still get the following:
 
"The temporary database 
already exists - its location cannot now be changed"
 
Many 
thanks.
Steve
 

RE: [sqlite] In-Memory Performance Comparisons

[Drew, Stephen]

Many thanks. 

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 23, 2004 1:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] In-Memory Performance Comparisons

Drew, Stephen wrote:
>  
> Can anyone tell me (or point me to any documentation) about the 
> differences in speed between using an on-disk DB and an in-memory one?
>  

Try it for yourself.  Like this:

drh$ rm test.db
drh$ time sqlite3 test.db /dev/null
real0m2.908s
user0m1.683s
sys 0m0.291s
drh$ time sqlite3 :memory: /dev/null
real0m1.697s
user0m1.635s
sys 0m0.063s

Your mileage will vary according to the content of workload.sql, of
course.  As a rule of thumb, the user time (the amount of time the
process spends in user mode) will be similar.  But for an in-memory
database, the sys time (the amount of time used by the
OS) will be much less, since there is no disk I/O.  And realtime (a.k.a.
wallclock time) will also typically be less since there are no waits for
disk controllers.

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

[sqlite] In-Memory Performance Comparisons

[Drew, Stephen]

Hello 
all,
 
Can anyone tell me (or 
point me to any documentation) about the differences in speed between using an 
on-disk DB and an in-memory one?
 
Regards,
Steve

RE: [sqlite] Problems with the C interface

[Drew, Stephen]

Ah sorry, my mistake.
I think you need to actually call sqlite_step for the DELETE to take
effect though. 

-Original Message-
From: Holger Brunck [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 24, 2004 5:59 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Problems with the C interface

> You need a call to sqlite_exec() too.
Why do I need this ? I am using the second possibility to execute sql
commands instead of the sql_exec() command and in other cases it works
very well:
sqlite_compile();
sqlite_step();
sqlite_finalize();

In my case I skip the sqlite_step() command, but I assume that the
sqlite_compile() command is the important one.

> -Original Message-
> From: Holger Brunck [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 24, 2004 5:44 PM
> To: [EMAIL PROTECTED]
> Subject: [sqlite] Problems with the C interface
>
> Hi,
> I'm using sqlite 2.8.12 and have a problem in deleting rows from a 
> database.
> The sql statement which don't work in my C++ code was:
> C++ Code Segment:
> sql = "DELETE FROM table_1 WHERE 
> path='/daten2/documents/my_file.txt';";
> if(SQLITE_OK != sqlite_compile(db, sql, , , ))
{
> cout << "Error:" << dberr << endl;
> free (dberr);
> }
> else
> {
>cout << "Success:" << endl;
>sqlite_finalize(dbcursor, NULL); }
>
> The code segment returns a Success, but the matching file won't be 
> deleted from the database. Other sql statements are working verry
well.
> When I try to use the sqlite program instead of invoking the command 
> via the C interface and enters the command per command line it was 
> removed out of the
> database:
> sqlite> DELETE FROM table_1 WHERE 
> sqlite> path='/daten2/documents/my_file.txt';
>
> Know the file was removed out of the database.
> Any hints are welcome.
>
> Kind Regards
> Holger

RE: [sqlite] .db file size is not changing

[Drew, Stephen]

Unni,

Try using the VACUUM command:

http://sqlite.org/lang.html#vacuum

Steve

-Original Message-
From: Unnikrishnan Nair [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 17, 2004 3:57 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] .db file size is not changing

Hi all,

I have a sqlite database. I had thousands of rows in it. I did a test
with the database and everything was good. So before I ship it to
production, I went to the database and I did drop all the index and
'deleted'
all the rows from the table. I thought the size of the database will
come down so that I can ship the model database for production. But the
size didn't change.
How can I reduce the size or is it possible at all?

Thanks.
Unni



__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

RE: [sqlite] Row Count

[Drew, Stephen]

Yes, I know it sounds a little optimistic :)

It's not a problem if I read the whole rowset into memory and then start
sending it, but this can cause massive memory usage and time delays. It's
not such a big deal if it's not possible, I'll just have to use a Microsoft
style progress bar (i.e. one that keeps resetting). 

 

-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 11, 2004 4:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Row Count


On Aug 11, 2004, at 8:41 AM, Drew, Stephen wrote:

> This will be a big overhead on really small queries
>
> I need to be able to say roughly how many rows, but obviously the 
> exact number would be good.  Some queries are very time critical and 
> complex so I don't want to have to do a COUNT(*) on them.
>

So you would like a way to get a count of rows a SELECT will return 
without actually processing the SELECT?

RE: [sqlite] Row Count

[Drew, Stephen]

This will be a big overhead on really small queries

I need to be able to say roughly how many rows, but obviously the exact
number would be good.  Some queries are very time critical and complex so I
don't want to have to do a COUNT(*) on them.

-Original Message-
From: Scott Baker [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 11, 2004 4:10 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Row Count

SELECT count(*) FROM Table WHERE Foo = 'bar';



Drew, Stephen wrote:
> Hello,
>  
> Is there any way to obtain the number of rows returned by a SELECT 
> statement, before receiving all the rows returned?  I am using the
> sqlite_exec() / sqlite_step() functions to return the data, but need 
> to populate a progress bar.
>  
> Regards,
> Stephen Drew

[sqlite] Row Count

[Drew, Stephen]

Hello,
 
Is there any way to 
obtain the number of rows returned by a SELECT statement, before receiving all 
the rows returned?  I am using the sqlite_exec() / sqlite_step() functions 
to return the data, but need to populate a progress bar.
 
Regards,Stephen Drew 
DISCLAIMER: This e-mail and any files transmitted with it 
contain confidential and proprietary information and is intended solely for the 
use of the intended recipient. If you are not the intended recipient, please 
return the e-mail to the sender and delete it from your computer and you must 
not use, disclose, distribute, copy, print or rely on this e-mail. The contents 
of this e-mail and any files transmitted with it may have been changed or 
altered without the consent of the author. Email transmission cannot be 
guaranteed to be secure or error-free. 
 

[sqlite] Sqlite Analyzer

[Drew, Stephen]

I attempted to open a 
2.8.13 database (double the standard page size) with the Sqlite Analyzer.  
I now get the following error upon opening the DB in the standard 
fashion:
 
malformed database schema 
- near "("
 
Surely if the DB file is 
unreadable, it shouldn't actually alter it in anyway?!?
 
Regards,Stephen Drew 


Stephen Drew 

Stephen Drew 

Stephen Drew 

Software Developer 

Software Developer 

Software Developer 

Front Office Development 

Front Office Development 

Front Office Development 


Beauchamp Financial Technology 
Limited 
tel: 
+44 (0)20 7469 
8636 
email: 
[EMAIL PROTECTED] 
web: 
www.bftl.com 
85 
Gracechurch Street, London, EC3V 0AA, United Kingdom 
DISCLAIMER: This e-mail and any files 
transmitted with it contain confidential and proprietary information and is 
intended solely for the use of the intended recipient. If you are not the 
intended recipient, please return the e-mail to the sender and delete it from 
your computer and you must not use, disclose, distribute, copy, print or rely on 
this e-mail. The contents of this e-mail and any files transmitted with it may 
have been changed or altered without the consent of the author. Email 
transmission cannot be guaranteed to be secure or error-free. 
 

[sqlite] RE: Data Types

[Drew, Stephen]

Hello again,
 
Can anyone answer these simple questions for 
me?

  
  Why is the 
  SHOW_DATATYPES pragma not mentioned on the SQL syntax page under the PRAGMA 
  keyword section?
  
  Do I have to execute the pragma per 
  transaction?
  
  If not, why I am intermittently not getting the data 
  types?
Thanks,
Steve


From: Drew, Stephen Sent: Monday, July 19, 
2004 7:09 PMTo: '[EMAIL PROTECTED]'Subject: Data 
Types

Hi 
there,
 
I am using the PRAGMA 
command "SHOW_DATATYPES=on".  However, not all of my queries return the 
datatypes.  Has anyone else experienced this 
problem?
 
Regards,Steve

[sqlite] Data Types

[Drew, Stephen]

Hi 
there,
 
I am using the PRAGMA 
command "SHOW_DATATYPES=on".  However, not all of my queries return the 
datatypes.  Has anyone else experienced this 
problem?
 
Regards,Steve

RE: [sqlite] Variable sub-queries?

[Drew, Stephen]

I agree that minimising the SQL syntax that SQLite doesn't support would be
a good addition to the new release.

Steve

-Original Message-
From: J.W. Janssen [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 18, 2004 2:03 PM
To: [EMAIL PROTECTED]
Subject: [sqlite] Variable sub-queries?


Hi,

The documentation on SQLite states that variable subqueries aren't
supported. I whether SQLite will support this in the (near) future, and if
not, why not.

TiA,

  Jan Willem

--
  `
  | J.W. Janssen,  Groesbeekseweg 191 ,  (o<
  | 6523 NS Nijmegen, The Netherlands |  /\
  | mail:  j dot w dot janssen at lxtreme dot nl  | v_/_
  | http:  www.lxtreme.nl '
  ` --

"Statistics are like bikinis.
 What they reveal is suggestive, but what they hide is vital."
  -- anonymous /.'er


-
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] Can't Open Database File

[Drew, Stephen]

Tito,

Thanks for that. I've had a look but it doesn't really help. The two main
questions are:

1.) Why do I need a separate sqlite pointer for each thread, if I am
protecting access to it?
2.) Even aside from this, I am using multiple threads to access the
database, each opens it when it needs it, and this is protected by a
critical section, so I have a separate pointer per thread and only one
thread is accessing the DB at a time. Why do I get "Can't open database"
error? Is this something to do with the filesystem locking? Perhaps sleeping
for a few milliseconds might help?

Regards,
Steve

-Original Message-
From: Tito Ciuro [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 10, 2004 4:13 PM
To: Drew, Stephen
Cc: '[EMAIL PROTECTED]'
Subject: Re: [sqlite] Can't Open Database File

Hi Stephen,

Check this: http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

Regards,

-- Tito

On 10 jun 2004, at 17:05, Drew, Stephen wrote:

> From the SQLite FAQ:
>
> "Threadsafe" in the previous paragraph means that two or more threads 
> can run SQLite at the same time on different "sqlite" structures 
> returned from separate calls to sqlite_open(). It is never safe to use 
> the same sqlite structure pointer simultaneously in two or more 
> threads.
>
> Is it still not safe to use the same SQLite structure pointer in two 
> or more threads if they are protecting against concurrent use?
>
> Regards,
> Steve
>
> -Original Message-
> From: Drew, Stephen [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 10, 2004 3:38 PM
> To: '[EMAIL PROTECTED]'
> Subject: [sqlite] Can't Open Database File
>
> Hi there,
>
> I am having a problem with accessing my SQLite database.
>
> I have a certain number of worker threads that access the SQLite 
> database, protected by a critical section.
>
> The database is already open prior to this.  Each thread attempts to 
> insert a row protected by the critical section.  This works fine for 
> about thirty rows, but then I get a "Can't open database file" error.
>
> Does anyone have any clues to what might be going on?
>
> Many thanks,
> Steve
>
> -
> 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] Can't Open Database File

[Drew, Stephen]

>From the SQLite FAQ:

"Threadsafe" in the previous paragraph means that two or more threads can
run SQLite at the same time on different "sqlite" structures returned from
separate calls to sqlite_open(). It is never safe to use the same sqlite
structure pointer simultaneously in two or more threads. 

Is it still not safe to use the same SQLite structure pointer in two or more
threads if they are protecting against concurrent use?

Regards,
Steve

-Original Message-----
From: Drew, Stephen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 10, 2004 3:38 PM
To: '[EMAIL PROTECTED]'
Subject: [sqlite] Can't Open Database File

Hi there,
 
I am having a problem with accessing my SQLite database.
 
I have a certain number of worker threads that access the SQLite database,
protected by a critical section.
 
The database is already open prior to this.  Each thread attempts to insert
a row protected by the critical section.  This works fine for about thirty
rows, but then I get a "Can't open database file" error.
 
Does anyone have any clues to what might be going on?
 
Many thanks,
Steve

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

RE: [sqlite] Borland C++ Builder 6

[Drew, Stephen]

Further to my original question, does anyone know why the SQLite library
compiles when (and only when, as far as I can tell) the following lines in
SQLiteInt.h :

#include 
#include 
#include 
#include 

are replaced with the original C equivalents:

#include 
#include 
#include 
#include 

Thanks,
Steve

-Original Message-
From: Drew, Stephen [mailto:[EMAIL PROTECTED]
Sent: Monday, March 15, 2004 11:36 AM
To: '[EMAIL PROTECTED]'
Subject: [sqlite] Borland C++ Builder 6


Has anyone any experience of compiling the SQLite library with Borland C++
Builder 6 and it's shipped version of STLPort?
 
Regards,
Steve

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

[sqlite] Borland C++ Builder 6

[Drew, Stephen]

Has anyone any experience of compiling the SQLite library with Borland C++
Builder 6 and it's shipped version of STLPort?
 
Regards,
Steve

RE: [sqlite] Re: Julian Date

[Drew, Stephen]

According to the excellent "Calendrical Calculations" by Reingold &
Dershowitz, 

Julian Day 0 = Noon, Monday 1st January 4713 BC (Julian Calendar)
  Noon, Monday 24th November -4713 (Proleptic Gregorian
Calendar)


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 3:01 PM
To: Andrzej Kukula; [EMAIL PROTECTED]
Subject: [sqlite] Re: Julian Date


Andrzej Kukula wrote:
> 
> Could you please point me to some material describing Julian Date that
> SQLite uses? Googling through the Net reveals that there are many
> definitions that differ in respect to start date, ranging from January 1st
> 4714 B.C. to January 1st 4712 B.C.
> 

References and explanations are contained in code comments.
See

   http://www.sqlite.org/cvstrac/getfile/sqlite/src/date.c


-- 
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] escaping " and % in sql statements

[Drew, Stephen]

So how does SQLite distinguish between the built-in:

{ "like",   2, SQLITE_NUMERIC, likeFunc   },

and a user-defined function such as 

{ "like",   2, SQLITE_NUMERIC, my_like_function   },

then?

-Original Message-
From: Mrs. Brisby [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 27, 2004 12:27 PM
To: [EMAIL PROTECTED]
Cc: D. Richard Hipp; [EMAIL PROTECTED]
Subject: Re: [sqlite] escaping " and % in sql statements


On Mon, 2004-01-26 at 09:08, [EMAIL PROTECTED] wrote:
> "D. Richard Hipp" <[EMAIL PROTECTED]> writes:
> 
> > As an interim workaround, you could defined your own
> > "like()" function using the sqlite_create_function() API
> > that implemented built-in escapes.  Any user-defined
> > function named "like()" is used to implement the LIKE
> > keyword of SQL.
> 
> That seems pretty dangerous, since no one would expect that function name
to
> exist as a public symbol in a library.  I would suggest changing the name
of
> the internal like() function to sqlite_like() or some equivalent, to avoid
> potential problems.  Being able to overload internal functions could be a
nice
> feature (which should be documented) but it seems the names should be
clearly
> associated with sqlite.

I do not think you understand.

SQLite has no "deep magic" allowing it to figure out what you call your
own symbols: The built-in like() function is a static symbol in func.c
called "likeFunc", and your code has nothing at all to do with this.

You make a call like this:

sqlite_create_function(p, "like", 2, my_like_func, 0);

after defining your own function; in this example it's called
"my_like_function" - but you can certainly call it whatever you like,
including sqlite_like, if you're so inclined.


-
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]

[sqlite] Functions & Keywords

[Drew, Stephen]

Hello,
 
It is 
straightforward to define a function in SQLite...such as MY_FUNCTION( 
).
 
Is there an equally 
simple way to define a keyword, i.e. exactly like the function above but without 
the parentheses?
 
I am trying to 
expand SQLite, using user-defined functions, to more closely resemble Oracle SQL 
features.
 
Thanks in 
advance,
Steve
 
 


Stephen Drew 

Stephen Drew 

Stephen Drew 

Front Office Development Team 

Front Office Development Team 

Front Office Development Team 


Beauchamp Financial Technology 
Limited 
tel: 
+44 (0)20 7469 
8636 
email: 
[EMAIL PROTECTED] 
web: 
www.bftl.com 
85 
Gracechurch Street, London, EC3V 0AA, United Kingdom 

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

[sqlite] Tables involved in queries

[Drew, Stephen]

Hello,
It would be very useful to be able to determine which tables are being used
in a given query.  
The background is that we are performing a local caching of queries by
caching the underlying tables from an Oracle database in a local SQLite
database.  
 
Given this query:
 
SELECTA.col_1, B.col_2
FROM   A
,   ( SELECT C.col_1 col_1, C.col_2 col_2 FROM C, D WHERE
C.col_2 = D.col_2) B
WHEREA.col_1 = B.col_1
 
[ Note that I have to alias the column C.col_1 in the subquery to get this
to work! ]
 
...I would like to be able to determine that the tables A, C, D are used as
the underlying tables.
 
I had thought of using the EXPLAIN command to examine the vm and check all
the OpenRead, OpenTemp, etc... opcodes.
 
Is there an easier way?
 
Regards,
Steve

[sqlite] Decodes

[Drew, Stephen]

Hi,
Are there any plans to include a DECODE as a standard built-in function in
SQLite?  I have written one myself very easily, and it seems to be a nice,
quick feature to add.
Regards,
Steve