Re: [sqlite] Update A table

[Jim Dodgen]

the way I do it is:


update table1
set table1.f1 = 
  (select table2.f1 from table2 WHERE table1.f0 = table2.f0)
where exists  
  (select * from table2 WHERE table1.f0 = table2.f0);





Francisco Tapia wrote:

I want to update data from table1 to table2, i seems I cannot get this
to work right, perhaps my syntax is wrong?


update table1
set table1.f1 = table2.f1
FROM table1, table2
WHERE table1.f0 = table2.f0


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...


  

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[Dennis Cote]

m christensen wrote:




[EMAIL PROTECTED] wrote:


m christensen <[EMAIL PROTECTED]> wrote:
 


Ok, more info

The SQLite.so library in the perl site install directory is version
3.2.7 from looking at the strings in the binary itself.

If I rename that file so as to 'hide' it the perl code falls over 
dead as it can't find it.


SO I have a library that I NOW it is using which:

Claims to be 3.2.7 according to the source code.
Claims to be 3.2.7 according to the compiled lib strings.
BUT It Returns 3.2.2 from the $dbh ->{sqlite_version} function.

  



You are not loading the library you think you are.  If the
source code says 3.2.7 then $dbh->{sqlite_version} will too.

 


That is why this is SOOO frustrating.

There is absolutely no question it's using the library in question (at 
least in some way).
If I MOVE the SQLite.so file (Which contains 3.2.7) the script fails, 
stating the SQLite module

is not installed correctly.
When I put it back, it works, and internally claims it's version 3.2.2



Are you sure that $dbh0>(sqlite_version) returns the same thing as 
executing the following SQL:


   select sqlite_version();

I don't use perl, so I'm not sure how you actually execute the query.

One other possibility, you may have a wrapper that is statically linked 
to an older library and the SQLite.so dynamic library may not be used.


Finally, shouldn't the library name be sqlite3.so or libsqlite3.so?

Dennis Cote

Re: [sqlite] Update A table

[Francisco Tapia]

very cool. thanks.

On 4/5/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Francisco,
>
> You might want to add a coalesce() call so the f1 value is only changed
> if there is a corresponding value in table2.
>
> update table1
> set f1 = coalesce((select f1 from table2 where table2.f0 = table1.f0),  f1)
>
> Dennis Cote
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...

Re: [sqlite] Update A table

[Francisco Tapia]

Dennis,
   That seems to be the correct syntax... very cool..

many thanks!!!
--Francisco

On 4/5/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Francisco Tapia wrote:
>
> >I want to update data from table1 to table2, i seems I cannot get this
> >to work right, perhaps my syntax is wrong?
> >
> >
> >update table1
> >set table1.f1 = table2.f1
> >FROM table1, table2
> >WHERE table1.f0 = table2.f0
> >
> >
> >--
> >-Francisco
> >http://pcthis.blogspot.com |PC news with out the jargon!
> >http://sqlthis.blogspot.com | Tsql and More...
> >
> >
> >
> Francisco,
>
> Try this instead.
>
> update table1
> set f1 = (select f1 from table2 where table2.f0 = table1.f0)
>
> HTH
> Dennis Cote
>

Re: [sqlite] Update A table

[Dennis Cote]

Francisco,

You might want to add a coalesce() call so the f1 value is only changed 
if there is a corresponding value in table2.


update table1
set f1 = coalesce((select f1 from table2 where table2.f0 = table1.f0),  f1)

Dennis Cote

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[m christensen]

[EMAIL PROTECTED] wrote:


m christensen <[EMAIL PROTECTED]> wrote:
 


Ok, more info

The SQLite.so library in the perl site install directory is version
3.2.7 from looking at the strings in the binary itself.

If I rename that file so as to 'hide' it the perl code falls over dead 
as it can't find it.


SO I have a library that I NOW it is using which:

Claims to be 3.2.7 according to the source code.
Claims to be 3.2.7 according to the compiled lib strings.
BUT It Returns 3.2.2 from the $dbh ->{sqlite_version} function.

   



You are not loading the library you think you are.  If the
source code says 3.2.7 then $dbh->{sqlite_version} will too.

 


That is why this is SOOO frustrating.

There is absolutely no question it's using the library in question (at 
least in some way).
If I MOVE the SQLite.so file (Which contains 3.2.7) the script fails, 
stating the SQLite module

is not installed correctly.
When I put it back, it works, and internally claims it's version 3.2.2

Re: [sqlite] Update A table

[Dennis Cote]

Francisco Tapia wrote:


I want to update data from table1 to table2, i seems I cannot get this
to work right, perhaps my syntax is wrong?


update table1
set table1.f1 = table2.f1
FROM table1, table2
WHERE table1.f0 = table2.f0


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...

 


Francisco,

Try this instead.

update table1
set f1 = (select f1 from table2 where table2.f0 = table1.f0)

HTH
Dennis Cote

[sqlite] Update A table

[Francisco Tapia]

I want to update data from table1 to table2, i seems I cannot get this
to work right, perhaps my syntax is wrong?


update table1
set table1.f1 = table2.f1
FROM table1, table2
WHERE table1.f0 = table2.f0


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...

Re: AW: [sqlite] primary key and physical data organization

[Dennis Cote]

Martin Pfeifle wrote:


Thanks,
If we look at a B+-tree, all records are stored at the leaf level and form at 
least a logical list.
But what happens, if we insert new records which do not fit on the 
corresponding leaf page any more. Assume this page has to be split. Where is 
the newly created physical page stored? Does the logical list of blocks also 
lead to a list of consecutive physical blocks?
Is the physical ordering of the records in the file independent of the 
insertion ordering?
Our goal is that all records are physically clustered according to their ROWID.
In order to achieve this goal, does it make sense to reorganize a table by for 
instance a command like
"insert into reorganized_table
select * from original_table ordered by rowid"

 


Martin,

The physical order does depend upon the insertion order.

You can reorganize a database as you have suggested or by running the 
vacuum command which does essentially the same thing for every table in 
the database. In either case the source tables are scanned in rowid 
order as the records are copied to the destination table. The inserts 
into the destination table are always appending new pages to the end of 
the new database file, so your tables end up allocated to sequential 
pages in the file.


Dennis Cote

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[drh]

m christensen <[EMAIL PROTECTED]> wrote:
> Ok, more info
> 
> The SQLite.so library in the perl site install directory is version
> 3.2.7 from looking at the strings in the binary itself.
> 
> If I rename that file so as to 'hide' it the perl code falls over dead 
> as it can't find it.
> 
> SO I have a library that I NOW it is using which:
> 
> Claims to be 3.2.7 according to the source code.
> Claims to be 3.2.7 according to the compiled lib strings.
> BUT It Returns 3.2.2 from the $dbh ->{sqlite_version} function.
> 

You are not loading the library you think you are.  If the
source code says 3.2.7 then $dbh->{sqlite_version} will too.

It is issues like this that make me tend to use static linking
instead of shared libraries...
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: AW: [sqlite] primary key and physical data organization

[drh]

Martin Pfeifle <[EMAIL PROTECTED]> wrote:
> Our goal is that all records are physically clustered according to their RO=
> WID.
> In order to achieve this goal, does it make sense to reorganize a table by =
> for instance a command like
> "insert into reorganized_table
> select * from original_table ordered by rowid"
> 

Running VACUUM is an easier way to do this.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[m christensen]

Ok, more info

The SQLite.so library in the perl site install directory is version
3.2.7 from looking at the strings in the binary itself.

If I rename that file so as to 'hide' it the perl code falls over dead 
as it can't find it.


SO I have a library that I NOW it is using which:

Claims to be 3.2.7 according to the source code.
Claims to be 3.2.7 according to the compiled lib strings.
BUT It Returns 3.2.2 from the $dbh ->{sqlite_version} function.

It ACTS like it is older than 3.2.6 in that
count(distinct(expression))   does not work.


Now What?

m christensen wrote:


OK, the DBD 1.11 install creates a SQLite.so Library.

There is ONE in the perl site install directory.
The is another one burried down in /home which perl SHOULD not find

The strings command finds version 3.2.7 in BOTH files.

Where else could perl possibly be finding a SQLite library?


m christensen wrote:


My Perl code reports version 3.2.2from $dbh->{sqlite_version}

My sqlite header file in the sqlite DBD 1.11 install dir clearly 
states 'IT' is 3.2.7.


The DBD 1.11 install built fine, tested perfectly and installed 
without errors.


There must be an older one that came with the OS install or something 
which perl is finding first.


Thanks.



Dennis Cote wrote:


m christensen wrote:


I have a linux machine that saw one-to-many power outages.

I got a backup of everything and reinstalled it.

I have some code that uses Perl DBI and connects to several Oracle 
Databases and builds several local sqlite databases.


It pulls a lot of data from Oracle and builds several hundred Meg 
of sqlite tables before this failure.


I have the following code which HAD worked for months.

select count(distinct(rec_key))
from test_rows
where list = 'A';

Now it suddenly fails with aPrepare failed near 'DISTINCT' : 
syntax error(1) at dbdimp.c line 269  error.


The code runs just fine with the same database file using the 
sqlite command line tool.


I tried upgrading DBI from 1.48 to 1.5.
I tried downgrading the sqlite DBD to 1.09 (Which is what I THINK I 
was running before), nothing helps.


Ideas.

It sounds familiar but I can't find any references for the life of 
me...



SQLite has only supported the count(distinct(expression)) since 
version 3.2.6.
I suspect that you have restored to an earlier version. You can 
check the library version by executing

   select sqlite_version();

HTH
Dennis Cote

AW: [sqlite] primary key and physical data organization

[Martin Pfeifle]

Thanks,
If we look at a B+-tree, all records are stored at the leaf level and form at 
least a logical list.
But what happens, if we insert new records which do not fit on the 
corresponding leaf page any more. Assume this page has to be split. Where is 
the newly created physical page stored? Does the logical list of blocks also 
lead to a list of consecutive physical blocks?
Is the physical ordering of the records in the file independent of the 
insertion ordering?
Our goal is that all records are physically clustered according to their ROWID.
In order to achieve this goal, does it make sense to reorganize a table by for 
instance a command like
"insert into reorganized_table
select * from original_table ordered by rowid"

- Ursprüngliche Mail 
Von: Dennis Cote <[EMAIL PROTECTED]>
An: sqlite-users@sqlite.org
Gesendet: Mittwoch, den 5. April 2006, 23:20:07 Uhr
Betreff: Re: [sqlite] primary key and physical data organization


Martin Pfeifle wrote:

>Hi,
>Assume I have a table containing an integer primary key. As far as I know, 
>this value is identical to the ROWID, right?
>How does SQLite organize the data within the file?
>Does it try to organize the data on the pages according to the primary key 
>(=ROWID) or according to the insertion order of the records?
>Can anyone explain that to me?
>Best Martin
>
>  
>
Martin,

An integer primary key is the rowid which is the key for the b-tree used 
to store the table. The way the b-tree is constructed is explained in 
the comment at the beginning of the source file btree.c which you can 
view here 
http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/btree.c=1.324. 
Basically it is a tree of blocks with similar rowid values and pointers 
to other blocks with larger and smaller rowid values.

HTH
Dennis Cote

Re: [sqlite] primary key and physical data organization

[Dennis Cote]

Martin Pfeifle wrote:


Hi,
Assume I have a table containing an integer primary key. As far as I know, this 
value is identical to the ROWID, right?
How does SQLite organize the data within the file?
Does it try to organize the data on the pages according to the primary key 
(=ROWID) or according to the insertion order of the records?
Can anyone explain that to me?
Best Martin

 


Martin,

An integer primary key is the rowid which is the key for the b-tree used 
to store the table. The way the b-tree is constructed is explained in 
the comment at the beginning of the source file btree.c which you can 
view here 
http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/btree.c=1.324. 
Basically it is a tree of blocks with similar rowid values and pointers 
to other blocks with larger and smaller rowid values.


HTH
Dennis Cote

AW: [sqlite] primary key and physical data organization

[Martin Pfeifle]

That's great thank you very much.

- Ursprüngliche Mail 
Von: [EMAIL PROTECTED]
An: sqlite-users@sqlite.org; Martin Pfeifle <[EMAIL PROTECTED]>
Gesendet: Mittwoch, den 5. April 2006, 23:09:25 Uhr
Betreff: Re: [sqlite] primary key and physical data organization


Martin Pfeifle <[EMAIL PROTECTED]> wrote:
> Hi,
> Assume I have a table containing an integer primary key. As far as I know, 
> this value is identical to the ROWID, right?

Correct

> How does SQLite organize the data within the file?
> Does it try to organize the data on the pages according to the primary key 
> (=ROWID) or according to the insertion order of the records?
> Can anyone explain that to me?

Entries or ordered by ROWID.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[m christensen]

OK, the DBD 1.11 install creates a SQLite.so Library.

There is ONE in the perl site install directory.
The is another one burried down in /home which perl SHOULD not find

The strings command finds version 3.2.7 in BOTH files.

Where else could perl possibly be finding a SQLite library?


m christensen wrote:


My Perl code reports version 3.2.2from $dbh->{sqlite_version}

My sqlite header file in the sqlite DBD 1.11 install dir clearly 
states 'IT' is 3.2.7.


The DBD 1.11 install built fine, tested perfectly and installed 
without errors.


There must be an older one that came with the OS install or something 
which perl is finding first.


Thanks.



Dennis Cote wrote:


m christensen wrote:


I have a linux machine that saw one-to-many power outages.

I got a backup of everything and reinstalled it.

I have some code that uses Perl DBI and connects to several Oracle 
Databases and builds several local sqlite databases.


It pulls a lot of data from Oracle and builds several hundred Meg of 
sqlite tables before this failure.


I have the following code which HAD worked for months.

select count(distinct(rec_key))
from test_rows
where list = 'A';

Now it suddenly fails with aPrepare failed near 'DISTINCT' : 
syntax error(1) at dbdimp.c line 269  error.


The code runs just fine with the same database file using the sqlite 
command line tool.


I tried upgrading DBI from 1.48 to 1.5.
I tried downgrading the sqlite DBD to 1.09 (Which is what I THINK I 
was running before), nothing helps.


Ideas.

It sounds familiar but I can't find any references for the life of 
me...



SQLite has only supported the count(distinct(expression)) since 
version 3.2.6.
I suspect that you have restored to an earlier version. You can check 
the library version by executing

   select sqlite_version();

HTH
Dennis Cote

Re: [sqlite] primary key and physical data organization

[drh]

Martin Pfeifle <[EMAIL PROTECTED]> wrote:
> Hi,
> Assume I have a table containing an integer primary key. As far as I know, 
> this value is identical to the ROWID, right?

Correct

> How does SQLite organize the data within the file?
> Does it try to organize the data on the pages according to the primary key 
> (=ROWID) or according to the insertion order of the records?
> Can anyone explain that to me?

Entries or ordered by ROWID.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[m christensen]

My Perl code reports version 3.2.2 from $dbh->{sqlite_version}

My sqlite header file in the sqlite DBD 1.11 install dir clearly states 
'IT' is 3.2.7.


The DBD 1.11 install built fine, tested perfectly and installed without 
errors.


There must be an older one that came with the OS install or something 
which perl is finding first.


Thanks.



Dennis Cote wrote:


m christensen wrote:


I have a linux machine that saw one-to-many power outages.

I got a backup of everything and reinstalled it.

I have some code that uses Perl DBI and connects to several Oracle 
Databases and builds several local sqlite databases.


It pulls a lot of data from Oracle and builds several hundred Meg of 
sqlite tables before this failure.


I have the following code which HAD worked for months.

select count(distinct(rec_key))
from test_rows
where list = 'A';

Now it suddenly fails with aPrepare failed near 'DISTINCT' : 
syntax error(1) at dbdimp.c line 269  error.


The code runs just fine with the same database file using the sqlite 
command line tool.


I tried upgrading DBI from 1.48 to 1.5.
I tried downgrading the sqlite DBD to 1.09 (Which is what I THINK I 
was running before), nothing helps.


Ideas.

It sounds familiar but I can't find any references for the life of me...


SQLite has only supported the count(distinct(expression)) since 
version 3.2.6.
I suspect that you have restored to an earlier version. You can check 
the library version by executing

   select sqlite_version();

HTH
Dennis Cote

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[Dennis Cote]

m christensen wrote:


I have a linux machine that saw one-to-many power outages.

I got a backup of everything and reinstalled it.

I have some code that uses Perl DBI and connects to several Oracle 
Databases and builds several local sqlite databases.


It pulls a lot of data from Oracle and builds several hundred Meg of 
sqlite tables before this failure.


I have the following code which HAD worked for months.

select count(distinct(rec_key))
from test_rows
where list = 'A';

Now it suddenly fails with aPrepare failed near 'DISTINCT' : 
syntax error(1) at dbdimp.c line 269  error.


The code runs just fine with the same database file using the sqlite 
command line tool.


I tried upgrading DBI from 1.48 to 1.5.
I tried downgrading the sqlite DBD to 1.09 (Which is what I THINK I 
was running before), nothing helps.


Ideas.

It sounds familiar but I can't find any references for the life of me...


SQLite has only supported the count(distinct(expression)) since version 
3.2.6.
I suspect that you have restored to an earlier version. You can check 
the library version by executing 


   select sqlite_version();

HTH
Dennis Cote

Re: [sqlite] sql question

[Dennis Cote]

Uma Venkataraman wrote:

I want to recycle the table for which I need to be able to delete the 
first 100 records from a table and add 100 new records.


Thanks



Uma,

See my reply to a similar question about FIFO tables in the archives at 
http://article.gmane.org/gmane.comp.db.sqlite.general/16175/match=fifo


HTH
Dennis Cote

Re: [sqlite] Syntax error near "NOT"

[Pam Greene]

What version are you using? The "IF NOT EXISTS" clause only exists in SQLite
3.3.0 and later.

- Pam

On 4/5/06, Lucky Luke <[EMAIL PROTECTED]> wrote:
>
> Ok, a VERY VERY strange error, which I can't solve.
>
> I use the SQLite.NET wrapper from www.phpguru.org to use it with C#.
>
> I have the following query:
>
> sql = "CREATE TABLE IF NOT EXISTS bot_users (" +
> "username VARCHAR(100)," +
> "password VARCHAR(100) " +
> ")";
>
> But when I try to execute this I get the following error:
>
> Sqlite.NET.SQLiteEception: near "NOT": syntax error
>
> There isn't something wrong in my query I thought..
> Greetings,
>
> LuckyLuke
>
>

[sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

[m christensen]

I have a linux machine that saw one-to-many power outages.

I got a backup of everything and reinstalled it.

I have some code that uses Perl DBI and connects to several Oracle 
Databases and builds several local sqlite databases.


It pulls a lot of data from Oracle and builds several hundred Meg of 
sqlite tables before this failure.


I have the following code which HAD worked for months.

select count(distinct(rec_key))
from test_rows
where list = 'A';

Now it suddenly fails with aPrepare failed near 'DISTINCT' : syntax 
error(1) at dbdimp.c line 269  error.


The code runs just fine with the same database file using the sqlite 
command line tool.


I tried upgrading DBI from 1.48 to 1.5.
I tried downgrading the sqlite DBD to 1.09 (Which is what I THINK I was 
running before), nothing helps.


Ideas.

It sounds familiar but I can't find any references for the life of me...

[sqlite] Syntax error near "NOT"

[Lucky Luke]

Ok, a VERY VERY strange error, which I can't solve.

I use the SQLite.NET wrapper from www.phpguru.org to use it with C#.

I have the following query:

sql = "CREATE TABLE IF NOT EXISTS bot_users (" +
"username VARCHAR(100)," +
"password VARCHAR(100) " +
")";

But when I try to execute this I get the following error:

Sqlite.NET.SQLiteEception: near "NOT": syntax error

There isn't something wrong in my query I thought..
Greetings,

LuckyLuke

Re: [sqlite] Windows problem when updating

[Milton Sagen]

I'd assume that something like that is exactly what's happening since  
the problem only happens after a reboot, although I was thinking that  
sqlite was caching it to a temp file or something. I'm running  
Windows XP Pro but I haven't done anything to change the default  
behavior of the os.


On Apr 05, 2006, at 06:19, Jay Sprenkle wrote:


On 4/4/06, Milton Sagen <[EMAIL PROTECTED]> wrote:

After a machine restart the first time I try to change a field in a
record, on Windows, it takes an inordinately long time for the sqlite
code to return, the amount of time is dependent on the size of the
database but even for a 20 Meg one with about 4000 records, the time
is in the order of a minute. Once the change is made I can quite the
program and relaunch it, open the database, and make another change
exactly like the first to another record and the sqlite code returns
almost immediately. On the Mac OS X, I don't see this delay.


Is the operating system, or cache hardware,
reading the database into memory and caching it?

[sqlite] sql question

[Uma Venkataraman]

I want to recycle the table for which I need to be able to delete the first 
100 records from a table and add 100 new records.


Thanks

Re: [sqlite] Speed, Locks and Stability

[Dennis Cote]

Deepak Kaul wrote:

Please rank the following scenarios considering speed, locks and 
stability.  I'm using sqlite in a C++ environment and running in a 
single process and single threaded environment where SQL_BUSY should 
occur minimally.


1.  Calling sqlite_exec within my C++ program
2.  Calling sqlite_prepare, sqlite3_step and sqlite3_finalize within 
my C++ program
3.  Calling ::system with the following string "/usr/bin/sqlite3 
database.db < file.txt"

Where file.txt contains ".read sqlstatements.txt"
Where sqlstatements.txt contains sql statements to be executed.


Rough order of speed and approximate (guessed) execution time factors

1.   using sqlite_prepare etc. will be fastest  

2.   using sqlite_exec will be slower by 10% to 100% (factor 1.1 to 2) 
due to time spent generating and reparsing SQL
   
3.   uses system will be slowest by a larger factor (25% to 200%) due to 
overhead of generating, opening, and reading the SQL from the file.


These factors really depend upon the type of SQL you will be using.

If you are working in C++ you might want to consider using a simple C++ 
wrapper to handle the prepare, step, finalize sequencing for you. I 
would suggest http://www.codeproject.com/database/CppSQLite.asp as a 
good place to start.


HTH
Dennis Cote

[sqlite] Speed, Locks and Stability

[Deepak Kaul]

Please rank the following scenarios considering speed, locks and 
stability.  I'm using sqlite in a C++ environment and running in a 
single process and single threaded environment where SQL_BUSY should 
occur minimally.


1.  Calling sqlite_exec within my C++ program
2.  Calling sqlite_prepare, sqlite3_step and sqlite3_finalize within my 
C++ program
3.  Calling ::system with the following string "/usr/bin/sqlite3 
database.db < file.txt"

Where file.txt contains ".read sqlstatements.txt"
Where sqlstatements.txt contains sql statements to be executed.

Thanks in advance.

--
Software Engineer
[EMAIL PROTECTED]
301.286.7951

Re: [sqlite] Strange Behaviour on Solaris 8 on Sparc

[Martin Jenkins]

Are those directories on the same disk/partition?
What do the results look like if you replace "| sqlite" with "> /dev/null"?

I'm not 100% sure that you're timing the sqlite part here. I think you're
timing the cat, hence my earlier comments.

Try "time sqlite < /export/home/ykphuah/test.sql" (untested, but you get the 
idea)


Martin

- Original Message - 
From: "Phuah Yee Keat" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, April 05, 2006 6:34 AM
Subject: [sqlite] Strange Behaviour on Solaris 8 on Sparc



Hi,

I am currently running some tests to decide whether to use sqlite, and 
bump into some strange behavior. I compiled sqlite 3.3.4 from source and 
installed it on a solaris 8 on sparc without any updates. I run the same 
scripts (which insert 1000 entries without BEGIN/END block), on the same 
machine, but in different directories, getting totally different results:


###
# In the "db1" directory:
###
[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real3m53.708s
user0m0.710s
sys 0m2.140s
[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real3m48.267s
user0m0.590s
sys 0m2.120s
[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real3m48.435s
user0m0.630s
sys 0m2.100s
[EMAIL PROTECTED]:~/db1$

###
# In the "db2" directory:
###
[EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real0m12.523s
user0m0.650s
sys 0m1.960s
[EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real0m6.296s
user0m0.490s
sys 0m1.720s
[EMAIL PROTECTED]:~/db2$ time cat /export/home/ykphuah/test.sql  | sqlite3 
test.db


real0m6.264s
user0m0.470s
sys 0m1.750s
[EMAIL PROTECTED]:~/db2$


I am really puzzled as to why there's so much difference in the time to 
execute the same script on the same box just in different directories, 
thinking that it might be placement of the directories in the physical 
disc itself causing the fsync calls to differ so much?


Is there any way where I can provide more information to help you guys 
help me?


Thanks in advance.

Cheers,
Phuah Yee Keat

RE: [sqlite] UI framework for sqlite

[Fred Williams]

Not sure why you are trying to do something like that.  One technique
which seems to be quite common, and that I use extensively, is to
present a scrollable read only grid of pertinent data fields.  with a
"VCR" button set for control.  The user can press the buttons for
Insert, Delete, or Edit for a selected record.  Double clicking the
highlighted item opens a detailed view window. (The edit form in read
only mode.)

I don't see a whole lot of advantage of presenting a scrollable editable
grid (Very easily done, code wise, in Delphi) as most all records I work
with greatly exceed the capabilities of a single GUI line length.
Actually I think a third party Delphi Grid Component vendor I use may
have the capability to do what you describe, although I have no interest
is finding out how far I can "stretch" their grid in that direction.

Anyway that's enough off topic from here.

Fred

> -Original Message-
> From: David Bicking [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 05, 2006 8:43 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] UI framework for sqlite
>
>
> This is probably off-topic for this list, so let me apologize
> in advance
> if it is. I don't have a probably in using sqlite itslef, but
> in rather
> how to use a UI to present the info to the user. I am not neccesarily
> asking for how to advice here, but rather pointers on where I can find
> such advice.
>
> I have written applications that will grab a record from an sqlite
> table, and insert the values in pre-existing text boxes,
> which the user
> can then change, finally clicking a save button, which sends
> the changed
> data back to sqlite.
>
> I have also written applications that will select multiple
> records, and
> dump that data as a printed report on paper.
>
> What I don't know how to do (without specialized tools) is to grab
> multiple records and present them in editable fashion to the user,
> keeping track of changes so thy can be written back. I am looking for
> something like a datasheet view or continuous form on MS Access.
>
> I have been advised to just place enough edits boxes for five or so
> records, then fill in the first five, then give the user
> "Next Five" and
> "Previous Five" buttons to click. But I would rather a
> solution in which
> the user can scroll down to see all the records, (within reason).
>
> I also want to do this with the least overhead and the most
> portable way
> possible. My target audience is like my brother in laws business: they
> have a few non-networked PC's with a mixture of versions of
> windows. My
> second audience is my own home network of PC's running windows and
> linux.
>
> This, I believe, leaves out any tool that requires KDE or Gnome or
> anything big and bloated. While I consider GTK to be big and
> bloated, I
> guess that is the upper limit that I want to consider.
>
> My language of choice is Basic, but do program in C, and have
> programmed
> in C++.
>
> So I am looking for links to  tutorials or how-tos, or even the right
> string of text to use on google to point me in the right direction.
>
> Any advice is appreciated.
> Thanks,
> David
>

Re: [sqlite] UI framework for sqlite

[Ulrich Schöbel]

Hi David,

if you're looking for a truly portable tool, you should consider
Tcl/Tk. SQLite has a builtin interface to Tcl, Starkits are portable
between many OSes without any code change and the tktable widget
ot tablelist should be what you are looking for to present your data.

Visit http://wiki.tcl.tk/2633 (sqlite), http://wiki.tcl.tk/1877 (tktable)
and http://wiki.tcl.tk/5527 (tablelist) for more info.

For starkits see http://www.equi4.com/starkit.html

Good luck for your project and happy Tcl'ing

Ulrich


On Wednesday 05 April 2006 15:42, David Bicking wrote:
> This is probably off-topic for this list, so let me apologize in advance
> if it is. I don't have a probably in using sqlite itslef, but in rather
> how to use a UI to present the info to the user. I am not neccesarily
> asking for how to advice here, but rather pointers on where I can find
> such advice.
>
> I have written applications that will grab a record from an sqlite
> table, and insert the values in pre-existing text boxes, which the user
> can then change, finally clicking a save button, which sends the changed
> data back to sqlite.
>
> I have also written applications that will select multiple records, and
> dump that data as a printed report on paper.
>
> What I don't know how to do (without specialized tools) is to grab
> multiple records and present them in editable fashion to the user,
> keeping track of changes so thy can be written back. I am looking for
> something like a datasheet view or continuous form on MS Access.
>
> I have been advised to just place enough edits boxes for five or so
> records, then fill in the first five, then give the user "Next Five" and
> "Previous Five" buttons to click. But I would rather a solution in which
> the user can scroll down to see all the records, (within reason).
>
> I also want to do this with the least overhead and the most portable way
> possible. My target audience is like my brother in laws business: they
> have a few non-networked PC's with a mixture of versions of windows. My
> second audience is my own home network of PC's running windows and
> linux.
>
> This, I believe, leaves out any tool that requires KDE or Gnome or
> anything big and bloated. While I consider GTK to be big and bloated, I
> guess that is the upper limit that I want to consider.
>
> My language of choice is Basic, but do program in C, and have programmed
> in C++.
>
> So I am looking for links to  tutorials or how-tos, or even the right
> string of text to use on google to point me in the right direction.
>
> Any advice is appreciated.
> Thanks,
> David

Re: [sqlite] UI framework for sqlite

[Jay Sprenkle]

On 4/5/06, David Bicking <[EMAIL PROTECTED]> wrote:
>
> So I am looking for links to  tutorials or how-tos, or even the right
> string of text to use on google to point me in the right direction.

You might look into these:

Java is very much like C++
The Eclipse IDE is supposed to be very good.
http://www.eclipse.org/

Gambas
A visual basic clone for linux
http://gambas.sourceforge.net/

For simple tasks a visual designer will make your life much easier.

Re: [sqlite] UI framework for sqlite

[Olaf Beckman Lapré]

There's the SQLite Control Center:

http://bobmanc.home.comcast.net/sqlitecc.html

Olaf

- Original Message - 
From: "David Bicking" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, April 05, 2006 3:42 PM
Subject: [sqlite] UI framework for sqlite


> This is probably off-topic for this list, so let me apologize in advance
> if it is. I don't have a probably in using sqlite itslef, but in rather
> how to use a UI to present the info to the user. I am not neccesarily
> asking for how to advice here, but rather pointers on where I can find
> such advice.
> 
> I have written applications that will grab a record from an sqlite
> table, and insert the values in pre-existing text boxes, which the user
> can then change, finally clicking a save button, which sends the changed
> data back to sqlite.
> 
> I have also written applications that will select multiple records, and
> dump that data as a printed report on paper.
> 
> What I don't know how to do (without specialized tools) is to grab
> multiple records and present them in editable fashion to the user,
> keeping track of changes so thy can be written back. I am looking for
> something like a datasheet view or continuous form on MS Access.
> 
> I have been advised to just place enough edits boxes for five or so
> records, then fill in the first five, then give the user "Next Five" and
> "Previous Five" buttons to click. But I would rather a solution in which
> the user can scroll down to see all the records, (within reason).
> 
> I also want to do this with the least overhead and the most portable way
> possible. My target audience is like my brother in laws business: they
> have a few non-networked PC's with a mixture of versions of windows. My
> second audience is my own home network of PC's running windows and
> linux. 
> 
> This, I believe, leaves out any tool that requires KDE or Gnome or
> anything big and bloated. While I consider GTK to be big and bloated, I
> guess that is the upper limit that I want to consider.
> 
> My language of choice is Basic, but do program in C, and have programmed
> in C++.
> 
> So I am looking for links to  tutorials or how-tos, or even the right
> string of text to use on google to point me in the right direction.
> 
> Any advice is appreciated.
> Thanks,
> David
> 
> 

Re: [sqlite] UI framework for sqlite

[drh]

David Bicking <[EMAIL PROTECTED]> wrote:
> 
> I am looking for links to  tutorials or how-tos,...
> to point me in the right direction.
> 

http://wiki.tcl.tk/15631
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] UI framework for sqlite

[Clay Dowling]

David,

What you're looking for is an editable data grid.  They're common
components and fairly easy to find.  They're also typically a pain in the
tail.  If you're comfortable with C++ you might look at wxWidgets and
their built-in datagrid.

Clay

David Bicking said:
> This is probably off-topic for this list, so let me apologize in advance
> if it is. I don't have a probably in using sqlite itslef, but in rather
> how to use a UI to present the info to the user. I am not neccesarily
> asking for how to advice here, but rather pointers on where I can find
> such advice.
>
> I have written applications that will grab a record from an sqlite
> table, and insert the values in pre-existing text boxes, which the user
> can then change, finally clicking a save button, which sends the changed
> data back to sqlite.
>
> I have also written applications that will select multiple records, and
> dump that data as a printed report on paper.
>
> What I don't know how to do (without specialized tools) is to grab
> multiple records and present them in editable fashion to the user,
> keeping track of changes so thy can be written back. I am looking for
> something like a datasheet view or continuous form on MS Access.
>
> I have been advised to just place enough edits boxes for five or so
> records, then fill in the first five, then give the user "Next Five" and
> "Previous Five" buttons to click. But I would rather a solution in which
> the user can scroll down to see all the records, (within reason).
>
> I also want to do this with the least overhead and the most portable way
> possible. My target audience is like my brother in laws business: they
> have a few non-networked PC's with a mixture of versions of windows. My
> second audience is my own home network of PC's running windows and
> linux.
>
> This, I believe, leaves out any tool that requires KDE or Gnome or
> anything big and bloated. While I consider GTK to be big and bloated, I
> guess that is the upper limit that I want to consider.
>
> My language of choice is Basic, but do program in C, and have programmed
> in C++.
>
> So I am looking for links to  tutorials or how-tos, or even the right
> string of text to use on google to point me in the right direction.
>
> Any advice is appreciated.
> Thanks,
> David
>
>


-- 
Simple Content Management
http://www.ceamus.com

[sqlite] UI framework for sqlite

[David Bicking]

This is probably off-topic for this list, so let me apologize in advance
if it is. I don't have a probably in using sqlite itslef, but in rather
how to use a UI to present the info to the user. I am not neccesarily
asking for how to advice here, but rather pointers on where I can find
such advice.

I have written applications that will grab a record from an sqlite
table, and insert the values in pre-existing text boxes, which the user
can then change, finally clicking a save button, which sends the changed
data back to sqlite.

I have also written applications that will select multiple records, and
dump that data as a printed report on paper.

What I don't know how to do (without specialized tools) is to grab
multiple records and present them in editable fashion to the user,
keeping track of changes so thy can be written back. I am looking for
something like a datasheet view or continuous form on MS Access.

I have been advised to just place enough edits boxes for five or so
records, then fill in the first five, then give the user "Next Five" and
"Previous Five" buttons to click. But I would rather a solution in which
the user can scroll down to see all the records, (within reason).

I also want to do this with the least overhead and the most portable way
possible. My target audience is like my brother in laws business: they
have a few non-networked PC's with a mixture of versions of windows. My
second audience is my own home network of PC's running windows and
linux. 

This, I believe, leaves out any tool that requires KDE or Gnome or
anything big and bloated. While I consider GTK to be big and bloated, I
guess that is the upper limit that I want to consider.

My language of choice is Basic, but do program in C, and have programmed
in C++.

So I am looking for links to  tutorials or how-tos, or even the right
string of text to use on google to point me in the right direction.

Any advice is appreciated.
Thanks,
David

Re: [sqlite] Windows problem when updating

[Jay Sprenkle]

On 4/4/06, Milton Sagen <[EMAIL PROTECTED]> wrote:
> After a machine restart the first time I try to change a field in a
> record, on Windows, it takes an inordinately long time for the sqlite
> code to return, the amount of time is dependent on the size of the
> database but even for a 20 Meg one with about 4000 records, the time
> is in the order of a minute. Once the change is made I can quite the
> program and relaunch it, open the database, and make another change
> exactly like the first to another record and the sqlite code returns
> almost immediately. On the Mac OS X, I don't see this delay.

Is the operating system, or cache hardware,
reading the database into memory and caching it?

Re: [sqlite] Question on how to enter blobs in sqlite3

[Jay Sprenkle]

On 4/4/06, Roman <[EMAIL PROTECTED]> wrote:
> Hello,
> I am using sqlite3 on am embedded platform. I am using c++ to enter data. I
> have BLOB field, and I do not know how to enter hex values from a data
> string. I am trying to use update, but how do I enter col_name=' data '
> format?
>
> How does ' escaping work? I could not find help on the sqlite site, and I am
> trying this list.

You don't have to escape the data or dynamically build SQL if you use bind:
http://sqlite.org/capi3ref.html#sqlite3_bind_blob

Re: [sqlite] Question on how to enter blobs in sqlite3

[Thomas Chust]

On Tue, 4 Apr 2006, Roman wrote:


[...]
I have tried

sqlite> select * from loc_configuration;
0|0|18|52|86|4|24|22|51|116|39|15|0|0|0|0|0|0|0|0|0|0|0|0

The blob is the one before the last one, and the length is the very last one
The serials are second, third , fourth and fifth columns.

sqlite> UPDATE loc_configuration SET loc_authorization_code_length = 18,
loc_authorization_code = X'000102030405060708' WHERE loc_serial_0=0 AND
loc_serial_1=18 AND loc_serial_2=52 AND loc_serial_3=86;
sqlite> select * from loc_configuration;

0|0|18|52|86|4|24|22|51|116|39|15|0|0|0|0|0|0|0|0|0|0||0

I get no response or error from sqlite3.
[...]


Hello Roman,

I wonder why your loc_authorization_code_length column still seems to be 0 
after the update, but otherwise this behaviour looks normal and correct. 
Here is what I did to check:


$ sqlite3
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> CREATE TABLE loc_configuration(
   ...>   loc_serial_0 INTEGER, loc_serial_1 INTEGER,
   ...>   loc_serial_2 INTEGER, loc_serial_3 INTEGER,
   ...>   loc_authorization_code BLOB,
   ...>   loc_authorization_code_length INTEGER);
sqlite> INSERT INTO loc_configuration VALUES(0, 18, 52, 86, 0, 0);
sqlite> SELECT * FROM loc_configuration;
0|18|52|86|0|0
sqlite> UPDATE loc_configuration
   ...>SET loc_authorization_code_length = 18,
   ...>loc_authorization_code = X'000102030405060708'
   ...>  WHERE loc_serial_0 = 0 AND loc_serial_1 = 18
   ...>AND loc_serial_2 = 52 AND loc_serial_3 = 86;
sqlite> SELECT * FROM loc_configuration;
0|18|52|86||18
sqlite> SELECT length(loc_authorization_code) FROM loc_configuration;
9
sqlite> SELECT quote(loc_authorization_code) FROM loc_configuration;
X'000102030405060708'

Note that the BLOB column does not contain printable characters and is 
therefore output as an empty string in the normal output mode, but you can 
always get a quoted version with the quote function. Anyway you should 
not have any quoting problems if you are accessing your database from C or 
C++.


cu,
Thomas

[sqlite] sqlite-3.3.4 and extra float decimals

[sqlite]

Hello,

When using sqlite-3.3.4 with windows I get the following strange behaviour.

create table Muppet (Kermit float);
insert into Muppet values (100);
select * from Muppet;
100.0

As you see it returns 100.0 instead of only 100. This happens in windows
xp but not in win ce.

Anyone know how to fix this?

Thanks,
  Floppe

ps. it works with older versions but I don't want to downgrade.

Re: [sqlite] Strange Behaviour on Solaris 8 on Sparc

[Christian Smith]

On Wed, 5 Apr 2006, Phuah Yee Keat wrote:

>Hi,
>
>I am currently running some tests to decide whether to use sqlite, and
>bump into some strange behavior. I compiled sqlite 3.3.4 from source and
>installed it on a solaris 8 on sparc without any updates. I run the same
>scripts (which insert 1000 entries without BEGIN/END block), on the same
>machine, but in different directories, getting totally different results:
>
>###
># In the "db1" directory:
>###
>[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql  | sqlite3
>test.db
> ...


Note, you are not testing the SQLite time here, but the time taken to run
"cat /export/home/ykphuah/test.sql". What you actually need is:
$ cat /export/home/ykphuah/test.sql  | time sqlite3 test.db

As to what is causing the slowdown, check your system log. You may have
hardware problems. Whatever the problem, it is almost certainly a
non-SQLite problem.


>I am really puzzled as to why there's so much difference in the time to
>execute the same script on the same box just in different directories,
>thinking that it might be placement of the directories in the physical
>disc itself causing the fsync calls to differ so much?
>
>Is there any way where I can provide more information to help you guys
>help me?


System utilities. Check prstat (top like util), dmesg (for kernel
messages), vmstat (IO stats) and truss (syscall trace).


>
>Thanks in advance.
>
>Cheers,
>Phuah Yee Keat
>


Christian

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

Re: [sqlite] sqlite3_enable_shared_cache

[Roger Binns]

Changing the setting while a session is active will lead
to serious problems.  That's why I added a check for that
behavior that makes the routine return SQLITE_MISUSE instead.


Ok, then the documentation is wrong as it says calling the
routine when connections exist will cause memory corruption.  
You have implmented it in such a way that no memory corruption

is possible.  You just get SQLITE_MISUSE if called while there
are connections.

Similarly the doc for this and sqlite3_thread_cleanup doesn't
make it clear that if you do close all your connections then
you don't need to call any of them.

Roger