Re: [sqlite] SQLite v/s SQLite3 Performance Assay

[Dan Kennedy]

> My Intentions are towards exploring the reason behind these differences; and
> what can be
> done to counter these performance differences. I'm seeking some pointers
> from the Community.

Version 3 has a different default safety-level (default FULL) to 
version 3 (default NORMAL). So if you didn't explicitly set the
safety-level during the tests, then version 3 was syncing the
disk more often than version 2. I think this might be why version 3
appears slower in Test Case I (Inserts).

The results of cases II to IV seem odd. Can you post the test
code to the list?

Dan.

> Many Thanks In Advance.
> 
> Test Setups:
> +--+--+-+-+-+
> | CPU   |(x86) |  (x86_64)| |
> | |
> |---|   P4 2.4 GHz |  Core2Duo| |   Athlon|
> Davinci |
> | OS|  | 1.86GHz x 2  |BSP15|   X2 (64)
> | |
> +---+--+--+-+-+-+
> | WinXP(32) |  X   |  X   | |
> | |
> +---+--+--+-+-+-+
> | WinXP(64) |  |  X   | |  X
> | |
> +---+--+--+-+-+-+
> | Linux(32) |  X   |  X   |  X  |
> |X|
> +---+--+--+-+-+-+
> | Linux(64) |  |  X   | |  X
> | |
> +---+--+--+-+-+-+
> 
> DB Schema:
>  It Consists of 4 Identical Tables
> tbl01{ code integer primary key
>   ,code01
>   ,code02
>   ,code03
>   ,code04
>   ,orderField
>   ,field01 }
> 
> Implementation:
> Application were written in C using SQLite & SQLite3's C API sets.
> 
>   Case I:
> SQL Insert Queries where fired in Sequential Progression; making 10
> Entries
> in First Table; 100 Entries in 2nd Table; 1000 Entries in 3rd Table and
> finally 1 Entries in 4th Table; Data below is Collective Time Taken
> to make Inserts in all 4 tables, expressed in millisecs.
> 
>  Insert |   SQLite|   SQLite3-> 0 Entries
> +-+-
>  Win32 x86  |78896| 97800
>  Win32 x86_64   |82100| 85000
>  Win64 x86_64   |  -  |   -
>  Linux32 x86|76900|100016
>  Linux32 x86_64 |87728| 99004
>  Linux64 x86_64 |79200| 99102
>  Linux64 x64|79788| 98794
>  Linux BSP15|37888| 37566
>  Linux Davinci  |  -  |   -
> --+-
> 
>Case II:
>  SQL Select with simple query on a single table fetching all records.
> 
>  Select on Simple Qry|   SQLite|   SQLite3   -> 1 (x 8 Cols)
> Entries
> -+-+-
>  Win32 x86   | 125 |   578
>  Win32 x86_64| |
>  Win64 x86_64|  -  |   -
>  Linux32 x86 |   8 |   297
>  Linux32 x86_64  |   6 |   251
>  Linux64 x86_64  |   6 |   149
>  Linux64 x64 |   7 |   144
>  Linux BSP15 | 287 | 22069
>  Linux Davinci   |  -  |   -
> ---+-
> 
>Case III:
>  SQL Select with Join of 2 Tables fetching all records.
> 
>  Select on Moderate Qry|   SQLite|   SQLite3 -> 1 (x 15
> Cols) [2 Table Join]
> ---+-+-
>  Win32 x86 | 5532|  1172
>  Win32 x86_64  | |
>  Win64 x86_64  |  -  |   -
>  Linux32 x86   |  439|   669
>  Linux32 x86_64|  251|  1108
>  Linux64 x86_64|  272|  1120
>  Linux64 x64   |  259|  1090
>  Linux BSP15   | 9258| 49773
>  Linux Davinci |  -  |   -
> ---+-+-
> 
>Case IV:
>  SQL Select with Join of 3 Tables fetching redundant records.
> 
>  Select on Complex Qry|   SQLite|   SQLite3  -> 9 (x 22
> Cols) [3 Table Join with redundant entries]
> --+-+-
>  Win32 x86| 6593|110157
>  Win32 x86_64 | |
>  Win64 x86_64 |  -  |  

[sqlite] SQLite v/s SQLite3 Performance Assay

[Nitin Kashyap]

Hi All,

I Carried out a small assay comparing performance between SQLite-2.8.16 &
SQLite-3.3.9;
Over Multiple platforms & Operating Environments.

Please consider the observations of the assay below. There seems to be a
very clear
and visible difference in performance of SQLite & SQLite3; for which
performance
acceptability seems questionable on some embedded platforms: -
 - SQLite & SQLite3 on the same platform.
 - SQLite on 2 different OS on same platform
 - SQLite3 on 2 differnt OS on same platform

My Intentions are towards exploring the reason behind these differences; and
what can be
done to counter these performance differences. I'm seeking some pointers
from the Community.

Many Thanks In Advance.

Test Setups:
+--+--+-+-+-+
| CPU   |(x86) |  (x86_64)| |
| |
|---|   P4 2.4 GHz |  Core2Duo| |   Athlon|
Davinci |
| OS|  | 1.86GHz x 2  |BSP15|   X2 (64)
| |
+---+--+--+-+-+-+
| WinXP(32) |  X   |  X   | |
| |
+---+--+--+-+-+-+
| WinXP(64) |  |  X   | |  X
| |
+---+--+--+-+-+-+
| Linux(32) |  X   |  X   |  X  |
|X|
+---+--+--+-+-+-+
| Linux(64) |  |  X   | |  X
| |
+---+--+--+-+-+-+

DB Schema:
It Consists of 4 Identical Tables
   tbl01{ code integer primary key
 ,code01
 ,code02
 ,code03
 ,code04
 ,orderField
 ,field01 }

Implementation:
   Application were written in C using SQLite & SQLite3's C API sets.

 Case I:
   SQL Insert Queries where fired in Sequential Progression; making 10
Entries
   in First Table; 100 Entries in 2nd Table; 1000 Entries in 3rd Table and
   finally 1 Entries in 4th Table; Data below is Collective Time Taken
   to make Inserts in all 4 tables, expressed in millisecs.

Insert |   SQLite|   SQLite3-> 0 Entries
   +-+-
Win32 x86  |78896| 97800
Win32 x86_64   |82100| 85000
Win64 x86_64   |  -  |   -
Linux32 x86|76900|100016
Linux32 x86_64 |87728| 99004
Linux64 x86_64 |79200| 99102
Linux64 x64|79788| 98794
Linux BSP15|37888| 37566
Linux Davinci  |  -  |   -
   --+-

  Case II:
SQL Select with simple query on a single table fetching all records.

Select on Simple Qry|   SQLite|   SQLite3   -> 1 (x 8 Cols)
Entries
   -+-+-
Win32 x86   | 125 |   578
Win32 x86_64| |
Win64 x86_64|  -  |   -
Linux32 x86 |   8 |   297
Linux32 x86_64  |   6 |   251
Linux64 x86_64  |   6 |   149
Linux64 x64 |   7 |   144
Linux BSP15 | 287 | 22069
Linux Davinci   |  -  |   -
   ---+-

  Case III:
SQL Select with Join of 2 Tables fetching all records.

Select on Moderate Qry|   SQLite|   SQLite3 -> 1 (x 15
Cols) [2 Table Join]
   ---+-+-
Win32 x86 | 5532|  1172
Win32 x86_64  | |
Win64 x86_64  |  -  |   -
Linux32 x86   |  439|   669
Linux32 x86_64|  251|  1108
Linux64 x86_64|  272|  1120
Linux64 x64   |  259|  1090
Linux BSP15   | 9258| 49773
Linux Davinci |  -  |   -
   ---+-+-

  Case IV:
SQL Select with Join of 3 Tables fetching redundant records.

Select on Complex Qry|   SQLite|   SQLite3  -> 9 (x 22
Cols) [3 Table Join with redundant entries]
   --+-+-
Win32 x86| 6593|110157
Win32 x86_64 | |
Win64 x86_64 |  -  |   -
Linux32 x86  |  484|   1059861
Linux32 x86_64   | |
Linux64 x86_64   | |
Linux64 

[sqlite] SQLITE_CANTOPEN error in sqlite3_exec function.

[Krishnaveni N]

Hello,

I am using some of the functions from C/C++ interface of SQLite3.  
While inserting or updating the database randomly some times it  
returns error code 14 ie SQLITE_CANTOPEN error. To execute SQL query  
I am using  sqlite3_exec function.


ret = sqlite3_exec(mDataBaseConnection, sqlQueryString, NULL, 0,  
);


In my application I am having single database file and single thread  
accessing this database. I am working in Macintosh platform.


Any one of you please let me know what is the problem and solution  
for that?



Thanks and Regards,
Krishnaveni N
[EMAIL PROTECTED]





---
Robosoft Technologies - Come home to Technology

Re: [sqlite] Sqlite 3.3.13; expr.c; analyzeAggregate

[drh]

"Noah Hart" <[EMAIL PROTECTED]> wrote:
> In expr.c, around line 2253, there is a nested loop which reads:
> 
> for(i=0; inSrc; i++, pItem++){
>   if( pExpr->iTable==pItem->iCursor ){
> for(i=0; inSrc; i++, pItem++){
>   if( pCol->iTable==pExpr->iTable &&
>   pCol->iColumn==pExpr->iColumn ){
> break;
>   }
> }
> if( i>=pAggInfo->nColumn ... ){
> }
> break;
>   }
> }
> 
> In this final line, which loop variable does the i refer to?
> the first one: for(i=0; inSrc; i++, pItem++){
> Or then next : for(i=0; inSrc; i++, pItem++){
> 
> are the two "i" the same variable, and it is intended that the
> second loop reset the Outer loop variable each time the inner 
> loop is run, and that the assignment statement affect the outer 
> loop as well?
> 

Once the first "if" statement fires, the outer loop is done, and 
it is ok to reuse the outer loop variable inside the "if".  So the 
code is correct as written. Nevertheless, I have changed the loop 
variable on the inner loop to avoid unnecessary confusion.

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


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

[sqlite] Sqlite 3.3.13; expr.c; analyzeAggregate

[Noah Hart]

In expr.c, around line 2253, there is a nested loop which reads:

 switch( pExpr->op ){
case TK_AGG_COLUMN:
case TK_COLUMN: {
  /* Check to see if the column is in one of the tables in the FROM
  ** clause of the aggregate query */
  if( pSrcList ){
struct SrcList_item *pItem = pSrcList->a;
for(i=0; inSrc; i++, pItem++){
  struct AggInfo_col *pCol;
  if( pExpr->iTable==pItem->iCursor ){
/* If we reach this point, it means that pExpr refers to a
table
** that is in the FROM clause of the aggregate query.  
**
** Make an entry for the column in pAggInfo->aCol[] if there
** is not an entry there already.
*/
pCol = pAggInfo->aCol;
for(i=0; inSrc; i++, pItem++){
  if( pCol->iTable==pExpr->iTable &&
  pCol->iColumn==pExpr->iColumn ){
break;
  }
}
if( i>=pAggInfo->nColumn && (i =
addAggInfoColumn(pAggInfo))>=0 ){


In this final line, which loop variable does the i refer to?
the first one: for(i=0; inSrc; i++, pItem++){
Or then next : for(i=0; inSrc; i++, pItem++){

are the two "i" the same variable, and it is intended that the second
loop reset the 
Outer loop variable each time the inner loop is run, and that the
assignment statement affect the outer loop as well?



Thanks for the clarification,

Noah Hart



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.




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

Re: [sqlite] notice: embedded system and locked database

[Martin Jenkins]

Jakub Ladman wrote:

Problem is, that this is pretty obscure system. Renesas SuperH SH4
CPU Heavily patched 2.4.18 kernel. (patches will be presented on
internet, but not at this time) Gentoo-embedded linux, based on
uclibc 0.9.28 and busybox . Main storage is SD flash card.



I must confess, that i do not know, how to check the nfs version. (it
is binary distributed kernel and i have not the actual .config of it)
Dmesg shows it not.


Hmm. I see your problem. 2.4.18 is quite old but if it's been patched 
about... It's possible to build the .config into the Linux kernel so 
that it appears under /proc (/proc/config.gz?) - I don't suppose they've 
done that?


Martin


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

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

[Martin Jenkins]

Mitchell Vincent wrote:

So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal?


Don't know about "normal" but ISTR version 3 did bring some fairly major 
improvements in file size. Given that you have your data in both SQLite 
formats would it not be fairly easy to dump both databases to text files 
as (say) SQL and diff the text files?


Martin

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

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

[Mitchell Vincent]

On 3/12/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

>
> So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal?
>

3.3.13 typically generates database files that are 30-40% smaller
than 2.8.16.  60% seems excessive, but is not outside the range of
possibility.  What kind of data are you storing?  Floating point
numbers are 8 bytes in 3.3.13 versus 17 bytes in 2.8.16.  Something
like that could account for the difference.


Ahh yes, I bet that's it!

My 60% was a bit inaccurate. Now that I actually do the math it's
closer to 40-55% depending on the types of data (I have do some that
store lots of floating point numbers).

I was impressed with SQLite before, now I'm REALLY impressed. Thank you!!

--
- Mitchell Vincent

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

[sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

[Stef Mientki]

If ask the table sturcture, with pragma table_info()

I get of course the basic fields, like:
  CID,Name,Type,

And also SOME special values, like
 Null, DefaultValue, PrimaryKey

But NOT the following special values (and probably a lot more)
 Unique, AutoIncrement

Is this due to the DLL I use,
or is it (deliberately) left out of the pragma ?

thanks,
Stef Mientki

KvK: 41055629



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

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

[drh]

"Mitchell Vincent" <[EMAIL PROTECTED]> wrote:
> I'm converting a bunch of databases from SQLite 2.8.16 to SQLite
> 3.3.13 and am seeing something pretty amazing space saving. So good
> that it might be too good to be true!
> 
> For example, a 17 meg database is cut down to 7 megs. That's fantastic
> if it's just that SQlite is *that* much better at storing the data,
> but I'm worried I'm missing things. The row counts for all the tables
> match up but it will take more time for me to write something that
> compares every field in every data table (there are more than 300
> fields total in this database).
> 
> So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal?
> 

3.3.13 typically generates database files that are 30-40% smaller
than 2.8.16.  60% seems excessive, but is not outside the range of
possibility.  What kind of data are you storing?  Floating point
numbers are 8 bytes in 3.3.13 versus 17 bytes in 2.8.16.  Something
like that could account for the difference.

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


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

Re: [sqlite] SQlite 2.8.16 -> SQLite 3

[P Kishor]

On 3/12/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote:

I'm converting a bunch of databases from SQLite 2.8.16 to SQLite
3.3.13 and am seeing something pretty amazing space saving. So good
that it might be too good to be true!

For example, a 17 meg database is cut down to 7 megs. That's fantastic
if it's just that SQlite is *that* much better at storing the data,
but I'm worried I'm missing things. The row counts for all the tables
match up but it will take more time for me to write something that
compares every field in every data table (there are more than 300
fields total in this database).

So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal?




did you get all your indexes over? they are the ones that take up a
bunch of space. Make sure you have all the indexes, and then run
ANALYZE. You will get a more final sense of space saving at that
point. There may or may not be any space saving, and unless you are
writing something for a floppy disk, what's the point of getting
excited over 10 Mb? My $25 thumbdrive is 2 Gb!

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] additional functions in C

[Jakub Ladman]

I think i do understand it now.

Thank you


Dne pondělí 12 březen 2007 16:08 Dennis Cote napsal(a):
> Jakub Ladman wrote:
> > Cause i am really blockheaded - is there somebody so good-hearted, who
> > could write simple step by step directions how to add one simple function
> > to SQLite?
> >
> > I have found so many inspirations and documentation seems to be very
> > precise, but i do not understand it at its width.
> >
> > For example.
> >
> > 1) compile sqlite sources with  -DBLAHBLAH
> > 2) write your own functions to myfunctions.c as described here
> > www.blahblah.org
> > 3) to register new functions write this and do that ...
> > 4) use your functions like select * from tb1 where a = blahblah(b,c,d);
>
> Jakub,
>
> See the sample in a previous post at
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg18608.html
>
> You might also want to search the list for "user defined function".
>
> You really would be well served by reading sqlite's func.c source file.
> It offers many sample functions written using the same APIs you will use.
>
> HTH
> Dennis Cote
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

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

[sqlite] SQlite 2.8.16 -> SQLite 3

[Mitchell Vincent]

I'm converting a bunch of databases from SQLite 2.8.16 to SQLite
3.3.13 and am seeing something pretty amazing space saving. So good
that it might be too good to be true!

For example, a 17 meg database is cut down to 7 megs. That's fantastic
if it's just that SQlite is *that* much better at storing the data,
but I'm worried I'm missing things. The row counts for all the tables
match up but it will take more time for me to write something that
compares every field in every data table (there are more than 300
fields total in this database).

So is a 60%ish reduction in DB size from 2.8.16 to 3.3.13 normal?

Thanks!!

--
- Mitchell Vincent

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

Re: [sqlite] UPDATE colomns based on their values

[Dennis Cote]

jose isaias cabrera wrote:


sqlite> select * from LSOpenJobs where id = '166';
166|166|1172158922388||us|MER|3.0 Cards|Technical| 
Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||c 



the last column is the status, which has the value c and the previus 
column is notes, 


which is empty or null.  

This is the heart of your problem, empty is *not* the same a null.

Null is an unknown or unspecified value, an empty string is a very well 
defined string with a length of zero.


The command line shell displays null values as empty strings by default. 
You can have the shell display an arbitrary string for null values to 
make then visible. Try this before your command


   .nullvalue ''

now you can see the difference between null values and empty strings

   select 'test', '', null;
   test||

After you have convinced yourself that your database contains empty 
strings, you can use the same techniques discussed previously to update 
the empty strings with your desired values.


HTH
Dennis Cote




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

Re: [sqlite] cast problems sqlite3

[drh]

Tom Shaw <[EMAIL PROTECTED]> wrote:
> 
> Using sqlite 3.3.5

You really should try to use something more recent.

> 
> UPDATE av_summary SET rank=((det*100.0)/(tot));
> 
> sets first row to an integer (serendipity?) and then all the other 
> rows are real or text which caused problems since I was expecting 
> that column was integer since that is how the table was created. I 
> see when I export that the numbers are real in the text exported. 
> This must have been what confused me.  There is obviously something 
> here that I don't grok.
> 

SQLite, unlike many other SQL database engines, strives to
avoid throwing away information.  If you have a value 1.234
and you store that value in an INTEGER column, SQLite tries
to convert the value to an integer.  But it sees that this
conversion would loss information - specifically the fraction
part 0.234.  So it stores the original floating point value
instead.  Other database engines would silently discard the
fractional part.

If you store 1.0 into a column marked INTEGER, it will convert
the value to 1, since no information is lost.

If you really have to have an integer in your column, then
use CAST() or ROUND() to round of the value to an integer first.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] cast problems sqlite3

[Tom Shaw]

At 1:40 PM + 3/12/07, [EMAIL PROTECTED] wrote:

Tom Shaw <[EMAIL PROTECTED]> wrote:

 Here ya go.

 >Tom Shaw <[EMAIL PROTECTED]> wrote:
 >>  UPDATE av_summary SET  rank=((det*100.0)/(tot)); only sets the
 >>  first row correctly then all the others have bogus data in rank
 >
 >That seems wrong.  Can you post a sample database that demonstrates
 >this behavior?
 >


I tried this on the database you sent me.  The answers
all look right to me.


Using sqlite 3.3.5

UPDATE av_summary SET rank=((det*100.0)/(tot));

sets first row to an integer (serendipity?) and then all the other 
rows are real or text which caused problems since I was expecting 
that column was integer since that is how the table was created. I 
see when I export that the numbers are real in the text exported. 
This must have been what confused me.  There is obviously something 
here that I don't grok.


Thanks,

Tom




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

Re: [sqlite] Segmentation fault using sqlite

[drh]

"Rich Rattanni" <[EMAIL PROTECTED]> wrote:
> I am wondering why my program crashes with a segmentation fault.  This
> has only occured once, but it seems to be something with sqlite.  Is
> this a known issue or is there anything I can do to further track this
> down?
> 

There are no known malloc() issues in SQLite and malloc() is
*very heavily* tested in SQLite.  I suspect that your problem
is that some other part of your program is corrupting the malloc()
heap (perhaps you are writing into space that has been freed)
and it just happens that this problem is discovered by SQLite.
In other words, the problem is not SQLite's fault, SQLite just
happened to be the first library to stumble across it.

Try running valgrind on your program to see where it is going
wrong.

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


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

Re: [sqlite] additional functions in C

[Dennis Cote]

Jakub Ladman wrote:
Cause i am really blockheaded - is there somebody so good-hearted, who could 
write simple step by step directions how to add one simple function to 
SQLite?


I have found so many inspirations and documentation seems to be very precise, 
but i do not understand it at its width.


For example.

1) compile sqlite sources with  -DBLAHBLAH
2) write your own functions to myfunctions.c as described here 
www.blahblah.org

3) to register new functions write this and do that ...
4) use your functions like select * from tb1 where a = blahblah(b,c,d);



  

Jakub,

See the sample in a previous post at 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg18608.html


You might also want to search the list for "user defined function".

You really would be well served by reading sqlite's func.c source file. 
It offers many sample functions written using the same APIs you will use.


HTH
Dennis Cote

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

[sqlite] Segmentation fault using sqlite

[Rich Rattanni]

I am wondering why my program crashes with a segmentation fault.  This
has only occured once, but it seems to be something with sqlite.  Is
this a known issue or is there anything I can do to further track this
down?


Program terminated with signal 11, Segmentation fault.
#0  0x403cdcb4 in _int_malloc () from /lib/libc.so.6
(gdb) bt
#0  0x403cdcb4 in _int_malloc () from /lib/libc.so.6
#1  0x403cedfc in malloc () from /lib/libc.so.6
#2  0x401c4418 in sqlite3MallocRaw () from /usr/lib/libsqlite3.so.0
#3  0x401c450c in sqlite3StrNDup () from /usr/lib/libsqlite3.so.0
#4  0x401cc070 in sqlite3VdbeChangeP3 () from /usr/lib/libsqlite3.so.0
#5  0x401cc0ac in sqlite3VdbeOp3 () from /usr/lib/libsqlite3.so.0
#6  0x401ac010 in sqlite3CodeSubselect () from /usr/lib/libsqlite3.so.0
#7  0x401ab4b0 in sqlite3ExprCode () from /usr/lib/libsqlite3.so.0
#8  0x401abd00 in sqlite3ExprIfFalse () from /usr/lib/libsqlite3.so.0
#9  0x401d02cc in sqlite3WhereBegin () from /usr/lib/libsqlite3.so.0
#10 0x401bf13c in sqlite3Select () from /usr/lib/libsqlite3.so.0
#11 0x401b6978 in sqlite3Parser () from /usr/lib/libsqlite3.so.0
#12 0x401c12e0 in sqlite3RunParser () from /usr/lib/libsqlite3.so.0
#13 0x47de97e0 in ?? ()
Cannot access memory at address 0x30

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

Re: [sqlite] UPDATE colomns based on their values

[P Kishor]

On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:


"P Kishor" wrote,

> On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>>
>> Ok, I have figured out that I do not have null values on the data of my
>> table, even though I do have empty strings.  Here is my schema,
>>
>> sqlite>
>> sqlite> .schema
>> CREATE TABLE LSOpenJobs
>> (
>>  id integer primary key, ProjID integer, parent, children, login,
>> cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang,
>> vendor,
>> vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid,
>> notes, status
>>
>> );
>>
>> And here is a call for a SELECT for a null value:
>>
>> sqlite> select * from LSOpenJobs where notes IS NULL;
>> sqlite>
>>
>> Now, I know for a fact that there are notes empty on some of the records
>> there.  Why is sqlite thinking that I do not have null values?
>
> you know for a fact that some of the notes are empty, but what makes
> you think they are null? Did you create them as null or did you get
> this db from someone?

Well, at the beginning I created them as null values.  However, I have been
adding data to the records, so it may be that the library that I am using
may not be setting those values to the correct null value.  Here is a record
with a few null values:

sqlite> select * from LSOpenJobs where id = '166';
166|166|1172158922388||us|MER|3.0 Cards|Technical|
Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||c

the last column is the status, which has the value c and the previus column
is notes, which is empty or null.  So, my question is what is NULL for
SQLite?  This is a library call ddbi for the D language.

> Here is a simple test --

This test below works on a new created DB, but doing a SELECT on my database
using your syntax for null value is not working.  I can provide you the DB,
if you want and you will see that I am not crazy.  I have been spending two
days on this and I am going to create a new program that shows this bug to
the folks that wrote the library.

I still would like to know what is a null value.  It may have to do with
this.



no, you are not crazy, and I believe you that it doesn't work for you.
You don't have to send your db to me.

What you are seeing is probably because what you _think_ is NULL is
actually an empty string. It is way too tedious and boring to figure
explain what NULL is, let alone what NULL is to SQLite. There are
many, many discussions on this archived in the list. Just search for
them. As far as I am concerned, I think of NULL as the absence of
value.

It is likely that your ddbi library is inserting empty strings instead
of NULL as default values for columns that are not explicitly
specified during INSERTs or UPDATEs. Just do a small test with it and
send the test to the ddbi developers.


>
> Lucknow:~ punkish$ sqlite3
> SQLite version 3.3.8
> Enter ".help" for instructions
> sqlite> CREATE TABLE foo (a, b);
> sqlite> .s
> CREATE TABLE foo (a, b);
> sqlite> INSERT INTO foo (a) VALUES ('blah');
> sqlite> INSERT INTO foo (b) VALUES ('blech');
> sqlite> .headers on
> sqlite> .mode columns
> sqlite> SELECT * FROM foo;
> a   b
> --  --
> blah
>blech
> sqlite> SELECT * FROM foo WHERE a IS NULL;
> a   b
> --  --
>blech
> sqlite> SELECT * FROM foo WHERE b IS NULL;
> a   b
> --  --
> blah
> sqlite> UPDATE foo SET b = '' WHERE a = 'blah';
> sqlite> UPDATE foo SET a = '' WHERE b = 'blech';
> sqlite> SELECT * FROM foo;
> a   b
> --  --
> blah
>blech
> sqlite> SELECT * FROM foo WHERE a IS NULL;
> sqlite> SELECT * FROM foo WHERE b IS NULL;
> sqlite>
>
>
>>
>>
>> - Original Message -
>> From: "jose isaias cabrera" <[EMAIL PROTECTED]>
>> To: 
>> Sent: Saturday, March 10, 2007 11:40 PM
>> Subject: [sqlite] UPDATE colomns based on their values
>>
>>
>> >
>> > Greetings!
>> >
>> > So, I would like to update some columns based on whether or not they
>> > are
>> > null.  Some of you folks have helped me with ifnull and IS NULL, and I
>> > happened to see coalesce, but none of these are working.  If I set the
>> > value, they will work, but with the checks, they do not get UPDATEd.
>> >
>> > Here is the call:
>> >
>> > BEGIN;
>> > UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND
>> > bdate
>> > IS NULL;
>> > UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE
>> > ProjID =
>> > '215';
>> > UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID =
>> > '215';
>> > COMMIT;
>> >
>> > As you can see, I am using 3 different checks and none of these are
>> > working. I know it's something simple, but what it is?
>> >
>> > Any ideas?
>> >
>> > Maybe the other question is, what defines "IS NULL" or "ifnull" or
>> > "coalesce"?
>> >
>> > thanks,
>> >
>> > josé
>> >
>> > 

Re[2]: [sqlite] FTS: Custom Tokenizer / Stop Words

[Ion Silvestru]

But what about:

I am very interested to know if it would be possible to use an FTS indexing 
module to store the inverted index only, but
not the document's text. This would safe disk space if the text to index is 
stored on disk rather than inside the database.


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

[sqlite] Possible to get international collation built in to SQLite main distribution?

[Samuel R. Neff]

Is there any chance of getting an international-aware case-insensitive
collation option put into the core language, at least as a compilation
option?  Seems that if SQLite is so focused on UTF data and so many users
are international, the limitation of available options being English A-Z
comparison is not appropriate.

One of the problems I have with custom collations is that they are only
available in the environment where the custom collation is defined.  We
create our database with the SQLite.NET wrapper and if we provide a custom
collation we can not then open then the database using the command line tool
due to "no such collation sequence" error.

A less useful but possible alternative would be to have an option to ignore
custom collation sequences on a connection or just within the command line
tool.

Thanks,

Sam


---
We’re Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: jp [mailto:[EMAIL PROTECTED] 
Sent: Saturday, March 10, 2007 9:52 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] collate function / international sort on linux

Thanks to several posts in this forum, I now have a
custom collation defined with
sqlite3_create_collation, which uses Window's
CompareStringA.  I managed to add it to main.c and
recompiled sqlite (I finally got my compilation
running on Windows with MingW/Msys).  For my needs
properly does case-insensitive and characters with
diacritics sorts.  For example:

AAA
amigo
ándale
Andalucía
ángel
Azul

Now I need to have this function, CompareStringA or
similar/better, on linux.  Does anybody have it or can
point me where to look for it?

jp


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

Re: [sqlite] cast problems sqlite3

[drh]

Tom Shaw <[EMAIL PROTECTED]> wrote:
> Here ya go.
> 
> >Tom Shaw <[EMAIL PROTECTED]> wrote:
> >>  UPDATE av_summary SET  rank=((det*100.0)/(tot));  only sets the
> >>  first row correctly then all the others have bogus data in rank
> >
> >That seems wrong.  Can you post a sample database that demonstrates
> >this behavior?
> >

I tried this on the database you sent me.  The answers
all look right to me.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

RE: [sqlite] stupid man's manual to sqlite?

[Samuel R. Neff]

Apress does have an online index they call "superindex" which indexes all
Apress books.  But I just did some test searches and found it to be equally
useless.  You can't search a specific book and some searches that obviously
should have returned results didn't. 

In case you have better luck, the URL is here:

http://superindex.apress.com/

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: A.J.Millan [mailto:[EMAIL PROTECTED] 
Sent: Saturday, March 10, 2007 6:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] stupid man's manual to sqlite?

...
On the other hand, besides the mentioned possibility, to put on-line, or in
a CD, the index, I believe that equally, an artifice could be included that
...

Greetings

A. J. Millan
ZATOR Systems.


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

Re: [sqlite] sqlite3_update_hook and transactions

[John Stanton]

Make the hook queue a GUI update transaction and execute those 
transactions following the COMMIT.  Discard the list of GUI update 
transactions on a ROLLBACK.


Jef Driesen wrote:

I was planning to use the sqlite3_update_hook function to notify my GUI
about changes. The idea was that every part of the GUI can update itself
when a database change is detected. But during testing, I encountered
some problems with this approach together with transactions.

When I group some SQL statements inside a transaction (for performance
or because they have to succeed or fail all together), the callback
function is executed for every statement:

BEGIN
statement 1 -> callback function called
statement 2 -> callback function called
COMMIT

But if the COMMIT is replaced with a ROLLBACK (for instance when an
error is detected), the callback functions are still called and the GUI
is updated with data that is not actually written to the database.

Any ideas on how to prevent this from happening?



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] sqlite3_update_hook and transactions

[drh]

Jef Driesen <[EMAIL PROTECTED]> wrote:
> 
> I think that approach should work. But the function sqlite3_commit_hook 
> is marked experimental in the documentation. What does that means? Is it 
> safe to rely on it?
> 

"Experimental" means that we reserve the right to change it in
future releases of SQLite.  Most of the API is guaranteed to
continue to be supported in exactly its current form.  So, for
example, when we wanted to enhance the behavior of sqlite3_prepare()
we had to do so by adding sqlite3_prepare_v2(), not by changing
the existing sqlite3_prepare().  And when the reason for existance
of the sqlite3_global_recover() API went away, we still have to 
have an API with that name that is a no-op.

With sqlite3_commit_hook(), we don't want to be bound by that
contract.  If in the future somebody comes up with a great idea
for enhancing the behavior of sqlite3_commit_hook(), we want to
be able to make the change without adding sqlite3_commit_hook_v2().
Or if somebody finds a fatal flaw in the whole sqlite3_commit_hook()
concept, we'd like to be able to remove the API all together.

Sqlite3_commit_hook() will not change without good reason.  But
if a good reason does come up, because it is experimental, it might
change.  Does that mean you can't rely on it?  I suppose the answer
to that question depends on what you mean by "rely on".

Most libraries treat *every* API as if it were experimental.  This
is especially true the open-source world.  SQLite tries to provide
some guarantee of compatibility from one release to the next in order
to preserve backwards compatibility. But we also try to give ourselves
some wiggle room by explicitly declaring certain functions as
experimental and therefore subject to change.

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


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

Re: [sqlite] Different ROWID and PRIMARY KEY values

[drh]

Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [T]here must be a good reason why the 
> constraints are not copied, but I have not seen an explanation.
> 

The statement is of the form:

   CREATE TABLE  AS 

Where would the constraints come from?  If the arbitrary
select statement were of the form:

   SELECT * FROM 

then you might reasonably take the constraints from .
But what if the SELECT where a join?  Or a compound select
involving different tables?  What if there are subqueries?
Or what if you do something like this:

   CREATE TABLE one(a INTEGER CHECK(a>10));
   INSERT INTO one VALUES(11);
   CREATE TABLE two AS SELECT a-5 FROM one;

Do we try to copy the CHECK(a>10) constraint and thus cause
the CREATE TABLE statement to fail?

UNIQUE constraints are implemented by creating implicit
indices.  So if there are UNIQUE constraints on the source
table, does that mean that some indices should be created
automatically on the created table?

Complications like this go and on and on.

The easiest way to deal with all of these questions is
to simply say that constraints are not copied at all.
When you say that, the rules for dealing with constraints
are simple to state and easy to understand.  Any other
constraint copying rule gets really complicated really
fast.

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



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

[sqlite] Re: sqlite3_update_hook and transactions

[Jef Driesen]

Dan Kennedy wrote:

On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote:

I was planning to use the sqlite3_update_hook function to notify my GUI
about changes. The idea was that every part of the GUI can update itself
when a database change is detected. But during testing, I encountered
some problems with this approach together with transactions.

When I group some SQL statements inside a transaction (for performance
or because they have to succeed or fail all together), the callback
function is executed for every statement:

BEGIN
statement 1 -> callback function called
statement 2 -> callback function called
COMMIT

But if the COMMIT is replaced with a ROLLBACK (for instance when an
error is detected), the callback functions are still called and the GUI
is updated with data that is not actually written to the database.

Any ideas on how to prevent this from happening?


Accumulate updates in a custom data structure (list or something) each
time the update_hook() callback is invoked.

Also register callbacks with sqlite3_commit_hook() and
sqlite3_rollback_hook(). When the commit_hook() callback is invoked,
update the GUI. When either the commit or rollback hooks are invoked,
reset the data structure to empty.


I think that approach should work. But the function sqlite3_commit_hook 
is marked experimental in the documentation. What does that means? Is it 
safe to rely on it?




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

Re: [sqlite] cast problems sqlite3

[drh]

Tom Shaw <[EMAIL PROTECTED]> wrote:
> UPDATE av_summary SET  rank=((det*100.0)/(tot));  only sets the 
> first row correctly then all the others have bogus data in rank

That seems wrong.  Can you post a sample database that demonstrates
this behavior?

> [I]s there a "cast" operator in the SQL
> 

CAST( AS )

Example:

   UPDATE av_summary SET rank=CAST(det AS real)/total;


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


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

Re: [sqlite] FTS: Custom Tokenizer / Stop Words

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> I wonder if there is some effort already under way to allow custom tokenizers 
> by SQLite's full text search?
> 

The plan is to eventually have an API that you can call to add
your own custom tokenzizer/stemmer.  But for now, you have to
modify the code to insert your own stemmer.  The code is near
line 2523 of fts2.c:

  /* TODO(shess) For now, add new tokenizers as else if clauses. */
  if( spec->azTokenizer[0]==0 || startsWith(spec->azTokenizer[0], "simple") ){
sqlite3Fts2SimpleTokenizerModule();
  }else if( startsWith(spec->azTokenizer[0], "porter") ){
sqlite3Fts2PorterTokenizerModule();
  }else{
*pzErr = sqlite3_mprintf("unknown tokenizer: %s", spec->azTokenizer[0]);
rc = SQLITE_ERROR;
goto err;
  }

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


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

Re: [sqlite] Different ROWID and PRIMARY KEY values

[Gerry Snyder]

Luca Moratto wrote:


I already created all the indexes after table creation, but I believed 
that the constraint of primary key came copied also by "create table ... 
as ...)...


Glad everything is working now.

SQLite is so well designed that there must be a good reason why the 
constraints are not copied, but I have not seen an explanation.


Anyone?


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

[sqlite] cast problems sqlite3

[Tom Shaw]

Help is appreciated. I have a table with integer columns rank, tot, 
det with values in tot and det and I want to put an integer percent 
(0-100) into rank


UPDATE av_summary SET  rank=(det/tot)*100;	returns 0 I assume 
because the arithmetic is in integer
UPDATE av_summary SET  rank=((det*100.0)/(tot));	only sets the 
first row correctly then all the others have bogus data in rank

UPDATE av_summary SET  rank=ROUND((det*100.0)/(tot));   works over all rows

Could someone explain. Also is there a "cast" operator in the SQL 
that SQLite executes?


TIA,

Tom


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

Re: [sqlite] Re: Argh, this must be a very stupid question ...

[Martin Jenkins]

Stef Mientki wrote:

so it was indeed a stupid question !


No, it was a well phrased question with a simple answer.

A stupid question is when the message is "I CANT OPEN A DATABSE!!! WHY 
[EMAIL PROTECTED] IT WORK? IS IT A BUG!!!" and nothing else. ;)


mj

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

[sqlite] additional functions in C

[Jakub Ladman]

Hi Friends

Cause i am really blockheaded - is there somebody so good-hearted, who could 
write simple step by step directions how to add one simple function to 
SQLite?

I have found so many inspirations and documentation seems to be very precise, 
but i do not understand it at its width.

For example.

1) compile sqlite sources with  -DBLAHBLAH
2) write your own functions to myfunctions.c as described here 
www.blahblah.org
3) to register new functions write this and do that ...
4) use your functions like select * from tb1 where a = blahblah(b,c,d);


Thank you

Jakub

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

[sqlite] FTS: Custom Tokenizer / Stop Words

[Ralf Junker]

I wonder if there is some effort already under way to allow custom tokenizers 
by SQLite's full text search?

I know that custom tokenizers are already on the developer's todo-list, but I 
would be interested to know if some progess has already been made.

Custom tokenizers would be able solve a couple of the current limitations to 
FTS:

* Caseless searching for full Unicode range or characters (currently limited to 
ASCII only).

* Stop Words - the tokenizer would ignore them and the FTS engine could remain 
unchanged.

* Improve reading of meta text formats (OpenOffice, Word, HTML). I can imagine 
that SQLite would quickly see a bunch of user contributed tokenizers for these 
formats.

Not directly related to tokenizers:

I am very interested to know if it would be possible to use an FTS indexing 
module to store the inverted index only, but not the document's text. This 
would safe disk space if the text to index is stored on disk rather than inside 
the database.

Ralf 


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

RE: [sqlite] journal - "Unable to open the database file"

[Allan, Mark]

Yes it would appear that the file is open read only. I cannot open the file in 
any other program to attempt to change the contents or truncate it.  It appears 
that the file is locked, but when using Unlocker to try to unlock it, it states 
that there is no locking handle on the file. Try to delete the file through 
unlocker, explorer or DOS and it will fail saying "It is being used by another 
process". I shutdown my pc and still get the same error. Spookily after a 
random amount of time the file will delete itself automatically. The only way 
to force its removal from the network is for me to ask a member of admin to do 
it for me.

I cannot be sure it is Windows Desktop Search that causes this. But it is the 
most likely candidate. The file is locked by one of two members of staff here, 
both of which run Windows Desktop Search. Noone else uses it and noone else has 
ever been found guilty of having the file open by admin. However I have tested 
this on a PC running Vista (with the db on a local file system however), Vista 
has file indexing built in as standard. The test program ran all weekend 
without exhibiting this same problem on Vista.


> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 09 March 2007 16:36
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] journal - "Unable to open the database file"
> 
> 
> "Allan, Mark" <[EMAIL PROTECTED]> wrote:
> > 3) Ask if anyone can offer any advise us as to what we can do 
> > to get around the problem of a journal file being locked and 
> > SQLite cannot delete it? Because at the moment if this situation 
> > occurs no one can write to that Db until the journal file has 
> > gone. Which is quite a severe problem...
> > 
> 
> While Windows Desktop Search has the journal file open, is the
> journal file read only?  Is it possible to change the content
> of the file or even truncate the file to zero length as long as
> the file is not deleted?
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> 


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

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

Re: [sqlite] sqlite3_update_hook and transactions

[Dan Kennedy]

On Mon, 2007-03-12 at 10:51 +0100, Jef Driesen wrote:
> I was planning to use the sqlite3_update_hook function to notify my GUI
> about changes. The idea was that every part of the GUI can update itself
> when a database change is detected. But during testing, I encountered
> some problems with this approach together with transactions.
> 
> When I group some SQL statements inside a transaction (for performance
> or because they have to succeed or fail all together), the callback
> function is executed for every statement:
> 
> BEGIN
> statement 1 -> callback function called
> statement 2 -> callback function called
> COMMIT
> 
> But if the COMMIT is replaced with a ROLLBACK (for instance when an
> error is detected), the callback functions are still called and the GUI
> is updated with data that is not actually written to the database.
> 
> Any ideas on how to prevent this from happening?


Accumulate updates in a custom data structure (list or something) each
time the update_hook() callback is invoked.

Also register callbacks with sqlite3_commit_hook() and
sqlite3_rollback_hook(). When the commit_hook() callback is invoked,
update the GUI. When either the commit or rollback hooks are invoked,
reset the data structure to empty.

Dan.



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

Re: [sqlite] Different ROWID and PRIMARY KEY values

[Luca Moratto]

Gerry Snyder ha scritto:

Luca Moratto wrote:


Thanks Gerry,

but I can Create my table in memory from an attached db, I can Insert 
new rows and I can Select the new rows and all values are correct, 
except for my key field that is 0.

My Insert statement is INSERT INTO myTable VALUES (?,?,?);
I prepare this statement with sqlite3_prepare
I bind all values with sqlite3_bind_int, sqlite3_bind_text, ...
I bind my key field with sqlite3_bind_null
I exec with sqlite3_step

It works because if I exec e SELECT rowid,* FROM myTable I can find 
all the new rows and all values except for my key field (INTEGER 
PRIMARY KEY, I have tried also AUTOINCREMENT) that is 0, but rowid is 
correct


I need to use a SELECT *, I can't get rowId so how and when my key 
field has the same value of rowId, why it does't works?


Thanks in advanced
Luke


Luke,

Sorry, I should not have even mentioned that part, since it obviously 
worked for you and it detracted from my main point, which you missed.


When you create the table in the :memory: file by using
CREATE TABLE AS, the PRIMARY KEY part of the field description gets 
dropped. Reread the last part of the example I posted. Or run

select sql from sqlite_master
yourself with the rest of your code.

You have to create the :memory: copy of the table with a different 
method. I would suggest using the other format of the CREATE TABLE 
command and populating it with data from a SELECT.


Gerry



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 




Thanks Gerry and excuse me, I had not read your answer well.

I have change my application:
now first I read sql statements from attachedDb.sqlite_master where 
type='table';

then exec that sql to create tables,
then I exec INSERT INTO 'mynewtable' AS SELECT * FROM 
'attachedDb'.'mytable'.

And now primary key works as expected!
I already created all the indexes after table creation, but I believed 
that the constraint of primary key came copied also by "create table ... 
as ...)...


Thanks
Luke


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

[sqlite] sqlite3_update_hook and transactions

[Jef Driesen]

I was planning to use the sqlite3_update_hook function to notify my GUI
about changes. The idea was that every part of the GUI can update itself
when a database change is detected. But during testing, I encountered
some problems with this approach together with transactions.

When I group some SQL statements inside a transaction (for performance
or because they have to succeed or fail all together), the callback
function is executed for every statement:

BEGIN
statement 1 -> callback function called
statement 2 -> callback function called
COMMIT

But if the COMMIT is replaced with a ROLLBACK (for instance when an
error is detected), the callback functions are still called and the GUI
is updated with data that is not actually written to the database.

Any ideas on how to prevent this from happening?



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

Re: [sqlite] UPDATE colomns based on their values

[jose isaias cabrera]

"P Kishor" wrote,


On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:


Ok, I have figured out that I do not have null values on the data of my
table, even though I do have empty strings.  Here is my schema,

sqlite>
sqlite> .schema
CREATE TABLE LSOpenJobs
(
 id integer primary key, ProjID integer, parent, children, login,
cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, 
vendor,

vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid,
notes, status

);

And here is a call for a SELECT for a null value:

sqlite> select * from LSOpenJobs where notes IS NULL;
sqlite>

Now, I know for a fact that there are notes empty on some of the records
there.  Why is sqlite thinking that I do not have null values?


you know for a fact that some of the notes are empty, but what makes
you think they are null? Did you create them as null or did you get
this db from someone?


Well, at the beginning I created them as null values.  However, I have been 
adding data to the records, so it may be that the library that I am using 
may not be setting those values to the correct null value.  Here is a record 
with a few null values:


sqlite> select * from LSOpenJobs where id = '166';
166|166|1172158922388||us|MER|3.0 Cards|Technical| 
Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||c


the last column is the status, which has the value c and the previus column 
is notes, which is empty or null.  So, my question is what is NULL for 
SQLite?  This is a library call ddbi for the D language.



Here is a simple test --


This test below works on a new created DB, but doing a SELECT on my database 
using your syntax for null value is not working.  I can provide you the DB, 
if you want and you will see that I am not crazy.  I have been spending two 
days on this and I am going to create a new program that shows this bug to 
the folks that wrote the library.


I still would like to know what is a null value.  It may have to do with 
this.


thanks for the help.




Lucknow:~ punkish$ sqlite3
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> CREATE TABLE foo (a, b);
sqlite> .s
CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo (a) VALUES ('blah');
sqlite> INSERT INTO foo (b) VALUES ('blech');
sqlite> .headers on
sqlite> .mode columns
sqlite> SELECT * FROM foo;
a   b
--  --
blah
   blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
a   b
--  --
   blech
sqlite> SELECT * FROM foo WHERE b IS NULL;
a   b
--  --
blah
sqlite> UPDATE foo SET b = '' WHERE a = 'blah';
sqlite> UPDATE foo SET a = '' WHERE b = 'blech';
sqlite> SELECT * FROM foo;
a   b
--  --
blah
   blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
sqlite> SELECT * FROM foo WHERE b IS NULL;
sqlite>





- Original Message -
From: "jose isaias cabrera" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, March 10, 2007 11:40 PM
Subject: [sqlite] UPDATE colomns based on their values


>
> Greetings!
>
> So, I would like to update some columns based on whether or not they 
> are

> null.  Some of you folks have helped me with ifnull and IS NULL, and I
> happened to see coalesce, but none of these are working.  If I set the
> value, they will work, but with the checks, they do not get UPDATEd.
>
> Here is the call:
>
> BEGIN;
> UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND 
> bdate

> IS NULL;
> UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE 
> ProjID =

> '215';
> UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID =
> '215';
> COMMIT;
>
> As you can see, I am using 3 different checks and none of these are
> working. I know it's something simple, but what it is?
>
> Any ideas?
>
> Maybe the other question is, what defines "IS NULL" or "ifnull" or
> "coalesce"?
>
> thanks,
>
> josé
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>


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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] UPDATE colomns based on their values

[P Kishor]

On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:


Ok, I have figured out that I do not have null values on the data of my
table, even though I do have empty strings.  Here is my schema,

sqlite>
sqlite> .schema
CREATE TABLE LSOpenJobs
(
 id integer primary key, ProjID integer, parent, children, login,
cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor,
vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid,
notes, status

);

And here is a call for a SELECT for a null value:

sqlite> select * from LSOpenJobs where notes IS NULL;
sqlite>

Now, I know for a fact that there are notes empty on some of the records
there.  Why is sqlite thinking that I do not have null values?


you know for a fact that some of the notes are empty, but what makes
you think they are null? Did you create them as null or did you get
this db from someone?

Here is a simple test --

Lucknow:~ punkish$ sqlite3
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> CREATE TABLE foo (a, b);
sqlite> .s
CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo (a) VALUES ('blah');
sqlite> INSERT INTO foo (b) VALUES ('blech');
sqlite> .headers on
sqlite> .mode columns
sqlite> SELECT * FROM foo;
a   b
--  --
blah
   blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
a   b
--  --
   blech
sqlite> SELECT * FROM foo WHERE b IS NULL;
a   b
--  --
blah
sqlite> UPDATE foo SET b = '' WHERE a = 'blah';
sqlite> UPDATE foo SET a = '' WHERE b = 'blech';
sqlite> SELECT * FROM foo;
a   b
--  --
blah
   blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
sqlite> SELECT * FROM foo WHERE b IS NULL;
sqlite>





- Original Message -
From: "jose isaias cabrera" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, March 10, 2007 11:40 PM
Subject: [sqlite] UPDATE colomns based on their values


>
> Greetings!
>
> So, I would like to update some columns based on whether or not they are
> null.  Some of you folks have helped me with ifnull and IS NULL, and I
> happened to see coalesce, but none of these are working.  If I set the
> value, they will work, but with the checks, they do not get UPDATEd.
>
> Here is the call:
>
> BEGIN;
> UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate
> IS NULL;
> UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID =
> '215';
> UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID =
> '215';
> COMMIT;
>
> As you can see, I am using 3 different checks and none of these are
> working. I know it's something simple, but what it is?
>
> Any ideas?
>
> Maybe the other question is, what defines "IS NULL" or "ifnull" or
> "coalesce"?
>
> thanks,
>
> josé
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>


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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

Re: [sqlite] UPDATE colomns based on their values

[jose isaias cabrera]

Ok, I have figured out that I do not have null values on the data of my 
table, even though I do have empty strings.  Here is my schema,


sqlite>
sqlite> .schema
CREATE TABLE LSOpenJobs
   (
id integer primary key, ProjID integer, parent, children, login, 
cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, 
vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid, 
notes, status


   );

And here is a call for a SELECT for a null value:

sqlite> select * from LSOpenJobs where notes IS NULL;
sqlite>

Now, I know for a fact that there are notes empty on some of the records 
there.  Why is sqlite thinking that I do not have null values?



- Original Message - 
From: "jose isaias cabrera" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, March 10, 2007 11:40 PM
Subject: [sqlite] UPDATE colomns based on their values




Greetings!

So, I would like to update some columns based on whether or not they are 
null.  Some of you folks have helped me with ifnull and IS NULL, and I 
happened to see coalesce, but none of these are working.  If I set the 
value, they will work, but with the checks, they do not get UPDATEd.


Here is the call:

BEGIN;
UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate 
IS NULL;
UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID = 
'215';
UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID = 
'215';

COMMIT;

As you can see, I am using 3 different checks and none of these are 
working. I know it's something simple, but what it is?


Any ideas?

Maybe the other question is, what defines "IS NULL" or "ifnull" or 
"coalesce"?


thanks,

josé

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




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