Re: [sqlite] sqlite on VPS configurations

[drh]

Dave Dyer <[EMAIL PROTECTED]> wrote:
> Is there any suspicion that sqlite might fail on virtual
> unix server configurations?  

The http://www.sqlite.org/ website is on a virtual unix server.
It uses SQLite extensively without any problems.  But I guess
it really depends on what virtual unix server you are using.
(www.sqlite.org runs under User-Mode Linux.  http://www.linode.com/)

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


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

[sqlite] Regexp

[T]

Hi,

I noticed in recent versions of SQLite the addition of some functions  
such as Trim(). So I am hopeful that at least one more function,  
Regexp(), can be included since it is such a central and common need.  
Time and time again I come up with needing a Regexp function, and I  
notice that the SQLite expression syntax even caters for it, but  
doesn't actually implement it.


I need full PCRE (ie Perl Compatible Regular Expression) support, for  
things such as grouping, replacing, "g i s" options.


For instance, Trim() could have been accomplished via Regexp, such as:

Trim = '   text between spaces   ' Regexp '/[:space:]*(.*?)[:space:]*/s'

I'm not sure how the SQLite syntax should cater for extracting a  
particular group from the match (ie the text match between the  
brackets above).


I know there are hooks for adding a Regexp function/operator  
ourselves, but I need to know it's available on other machines with  
standard install. It's the same reasoning, I guess, as why Trim() was  
added, but Regexp seems to serve a wider need.


Please and thankyou,
Tom

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

Re: [sqlite] database is locked error

[T]

Is there a timetable for including the fix for opening SQLite files  
on a shared volume? Since it's fairly trivial, is there a reason why  
it hasn't been included yet?


Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 29 April 2007 3:35:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Following up an old thread:

The SQLite sources include an (Apple-supplied) patch to work around  
the problem. Recompile

with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are  
not quite there yet.


I compiled and ran SQLite 3.3.17 and got the old error again when  
accessing a database file on a server volume, with SQLite saying it  
is locked.


Does this mean that we are still "not quite there yet" with a default  
fix? Any time frame?


Thanks,
Tom




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

[sqlite] sqlite on VPS configurations

[Dave Dyer]

Is there any suspicion that sqlite might fail on virtual
unix server configurations?   Among other things that might
be odd in this kind of environment, perhaps the relationship
between disk locks and physical activity might be tenuous,
and there might be genuine multiprocessors.


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

Re: [sqlite] indexing large databases

[drh]

Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote:
> 
> Having said that, I can understand that sorting and disk cache and so  
> on factors in - but my initial database is already very large (1.3GB  
> - 145.000.000 milion rows), and surely that disk cache would already  
> factor in at that stage?
> 

As long as your table does not contain out-of-order indices,
each new row will be inserted at the end.  So the last few
disk pages will be in cache and everything will go quickly.
It is when you have to start inserting at random places in
the middle of the database - on disk pages that are not
cached - that things slow down.

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


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

[sqlite] Re: Longest "real" SQL statement

[Charles Cazabon]

Sorry for the lateness of this reply.

I don't have the query anymore (wrote it at my previous job), and it wasn't
specifically for sqlite, but here goes.

A company I worked for built a system to calculate and report various health
statistics from a huge database which was itself a composite of two dozen or
more other databases, collected by various authorities and organizations.  As
part of demonstration, I ended up writing a query that was roughly 100kbytes /
2500 lines.

It had several nested subqueries, and did joins across dozens of tables and
many dozens of columns.  I think it calculated the teen pregnancy rate for a
given community in a given year, IIRC :).

Charles
-- 
---
Charles Cazabon   <[EMAIL PROTECTED]>
---

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

Re: [sqlite] Database locked version 3.3.17

[Ken]

Resolved the issue. My issue on the app side. 
 
 I think the newer version is much faster. So much so that it changed timing 
characteristics and reveald a logic bug in my code.
 
 Appologies,
 Ken
  
 
 
  

Ken <[EMAIL PROTECTED]> wrote: Hi all,
 
 Think I may have hit some form of a bug in 3.3.17:
 At an insert statement I'm getting a rc=[5] msg=[database is locked] 
 
 When I recompile the sqlite using version 3.3.13 and re-run the appliation. No 
problems.
 
 Sorry this is pretty vague.. I'll try digging in more and finding more details 
to reproduce a simple test case.
 
 Ken
 
 

Re: [sqlite] indexing large databases

[Kasper Daniel Hansen]

On May 10, 2007, at 3:04 PM, [EMAIL PROTECTED] wrote:


Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote:

Hi

We are using SQLite for a fairly big (but simple) calculation, and
have some problems when creating an index on a database with
600.000.000 rows. Specifically it has not ended even after 5 days of
running. We have done it successfully on 25% of the full data base,
and are now wondering what we can do.



This is likely a locality of reference problem.  It comes up
about every two weeks on this mailing list.  You can read more
about it by looking at the mailing list archives.

I think I know how to fix the problem.  But I have not yet had
an opportunity to implement the fix.


Thank you for that pointer. I have now read up a bit on the  
references. From what I understand it has to do with the ordering of  
the data and the disk cache and so on.


I could without too much trouble (although it will take some time)  
sort the data in a way such that it is sorted in the right way for  
the index (by right I mean if I create a (VAR1, VAR2) index I want  
VAR2 to be sorted within VAR1 in increasing order). Would that help?  
Or does the UNIQUE keyword make sqlite do some checking that will  
slow down everything.


Having said that, I can understand that sorting and disk cache and so  
on factors in - but my initial database is already very large (1.3GB  
- 145.000.000 milion rows), and surely that disk cache would already  
factor in at that stage?


Thanks, Kasper



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

Re: [sqlite] indexing large databases

[drh]

Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote:
> Hi
> 
> We are using SQLite for a fairly big (but simple) calculation, and  
> have some problems when creating an index on a database with  
> 600.000.000 rows. Specifically it has not ended even after 5 days of  
> running. We have done it successfully on 25% of the full data base,  
> and are now wondering what we can do.
> 

This is likely a locality of reference problem.  It comes up
about every two weeks on this mailing list.  You can read more
about it by looking at the mailing list archives.

I think I know how to fix the problem.  But I have not yet had
an opportunity to implement the fix.

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


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

[sqlite] indexing large databases

[Kasper Daniel Hansen]

Hi

We are using SQLite for a fairly big (but simple) calculation, and  
have some problems when creating an index on a database with  
600.000.000 rows. Specifically it has not ended even after 5 days of  
running. We have done it successfully on 25% of the full data base,  
and are now wondering what we can do.


Details:
We have a very simple database, just two columns VAR1 and VAR2 which  
are both integers. Every combination of (VAR1, VAR2) is unique. We  
want to add an index by using the following command

 CREATE UNIQUE INDEX on DATABASE (VAR1, VAR2);
After creating the index we have to do appr. 100.000.000 lookups on  
(VAR1, VAR2). We have benchmarked the results on a small(er) database  
with 145.000.000 rows where the indexing takes around 1 hour and the  
speed of the lookup is _very_ acceptable. However when running the  
indexing command on the full database it does not terminate even  
after 5-6 days (unfortunately our server got rebooted while it was  
running). We are wondering on the rather drastic increase in run time  
when going from 145.000.000 rows to 600.000.000 rows.


?: does the unique keyword matter, ie. does it make lookups faster  
and does it slow down the index creation.
?: does the order of the data matter a lot - right now it is  
partially sorted in that all combinations involving a single value of  
VAR2 (not VAR1) are next to each other.
?: is it possible to enable some kind of logging/debugging mode so we  
can monitor progression and see how it is doing.


We run the program on an 2600mhz Opteron with 16GB of ram and so far  
it seems we are not getting killed by I/O or RAM: using top  
frequently shows around 99% cpu usage and less than 40% memory usage.  
We are using the following program (including all pragmas):


sqlite3 blasthits.db "pragma default_cache_size = 1200;"
sqlite3 blasthits.db "pragma synchronous = off;
  pragma temp_store = 2;
  create unique index probeseqIDX on blasthits (probeidx,prokMSA);"
(here blashits.db is the database name and probeidx, prokMSA are  
VAR1, VAR2).


?: Is there anything we can do to speed it up further?

Thanks a lot for any help, even if it is "it does not seem to be  
possible to improve the speed of this operation".


Jim and Kasper

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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Derrell . Lipman]

Brandon Eley <[EMAIL PROTECTED]> writes:

> No, I can't upgrade to PHP5, yet. We're on PHP4.

I was accessing sqlite 2.8.x databases with PHP4 a number of years ago.  It
worked fine.  The page I referred you to previously _initially_ discusses the
object interface for the sqlite functions.  That, AFAIR, didn't exist in php4.
The sqlite_* functions that are listed further down, however, did exist and
worked fine.

Try this as an alternate starting point:

  http://us.php.net/manual/en/function.sqlite-open.php

Just ignore the Object interface that is described for each function, and
stick with the sqlite_*() functions.

You might try running a script with phpinfo() to ensure that sqlite was
compiled into your build.  I recall it was compiled by default, but it's
conceivable it was left out of your build.

Cheers,

Derrell

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

[sqlite] Database locked version 3.3.17

[Ken]

Hi all,
 
 Think I may have hit some form of a bug in 3.3.17:
 At an insert statement I'm getting a rc=[5] msg=[database is locked] 
 
 When I recompile the sqlite using version 3.3.13 and re-run the appliation. No 
problems.
 
 Sorry this is pretty vague.. I'll try digging in more and finding more details 
to reproduce a simple test case.
 
 Ken
 
 

Re: [sqlite] The need for sqlite3_encode_binary and sqlite3_decode_binary

[Andrew Finkenstadt]

much thanks.

My background is as a very strongly performance-oriented Oracle developer
and DBA, and nearly every example I see for using sqlite3 uses non-optimal
techniques which either blow through memory allocations, or become subject
to SQL-injection attacks.

On a related note, what's the best way to give back changes to CppSQLite3DB,
one of the myriad C++ front-ends to sqlite3 ?  I'm positive I can dedicate
the changes to the public domain anywhere except in Germany, in which case
I'd be happy assigning them to drh's entity of choice.

--andy


On 5/10/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


Andrew Finkenstadt wrote:
> It would appear that by using bind variables instead of '%Q' in the SQL
> string, the need for sqlite3_encode_binary and sqlite3_decode_binary is
> eliminated.  Is that indeed the case?
>
Andy,

Yes that is the case. You can use sqlite3_bind_blob to pass arbitrary
binary data to an SQL statement without worrying about special quoting.

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Derrell . Lipman]

Brandon Eley <[EMAIL PROTECTED]> writes:

> Thank you for this information. How would I access the 2.8.x database  from
> within PHP? is it even possible?

Go to http://www.php.net.  Type "sqlite" into the search box at the top.  The
page it brings you to describes the sqlite 2.8.x interface.  The PECL
extension that you referenced is for accessing the newer 3.x databases.

Derrell

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

Re: [sqlite] Longest "real" SQL statement

[BardzoTajneKonto]

I haven't used SQLite for that yet (I will in the near future) but with mysql 
my longes statement was at least several megabytes.
I create statements automatically. I think so far the longes created 
statement was a PCA trafsformation from table with about 2000 columns 
(would have been more if mysql supported that).
Coefficients were already calculated, so a single column in the result table 
was created from at least 2 * 2000 tokens.
I don't remember exact length, but possibly even longer statements was 
created using a few CASE .. WHEN constructs with lengthy calculations for 
every condition with lots of columns.


- Original Message -
From: [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Date: Wed, 09 May 2007 23:32:53 +
Subject: [sqlite] Longest "real" SQL statement

> I'm looking for an upper bound on how big legitimate 
> SQL statements handed to SQLite get to be.  I'm not
> interested in contrived examples.  I want to see
> really big SQL statements that are actually used in
> real programs.
> 
> "Big" can be defined in several ways:
> 
> *  Number of bytes of text in the SQL statement.
> *  Number of tokens in the SQL statement
> *  Number of result columns in a SELECT
> *  Number of terms in an expression
> 
> If you are using really big SQL statements, please
> tell me about them.  I'd like to see the actual
> SQL text if possible.  But if your use is proprietary,
> please at least tell me how big your query is in
> bytes or tokens or columns or expression terms.
> 
> Thanks.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> 
-
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> 
-
> 
> 
> 


--
NIE KUPUJ!!!
...zanim nie porownasz cen >> http://link.interia.pl/f1a5e




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

Re: [sqlite] My HPUX Notes

[John Stanton]

Markus Hoenicka wrote:

Quoting km4hr <[EMAIL PROTECTED]>:


I just happened to notice that I may not be executing the sqlite
installation process (configure/make/make install) in a full "bash"
environment. My usual environment is "ksh". Typing in the command
"/bin/OpenSource/bin/bash" I get a bash prompt. But apparently that 
doesn't
put me in a full bash environment. I just noticed that unless I 
specify the

full path to the GNU make command (/opt/OpenSource/bin/make) then the ksh
version (/usr/bin/make) is executed. I wonder what I have to do to get 
into

a true bash environment? Must be some environment viable that has to be
changed. Oh well, one mystery begets another!



There is no such thing as a full "bash" environment. bash is just  
another shell, just like ksh. You may experience some differences  
because the shells may read different startup files, so e.g.  
environment variables may be set differently.


Some systems like FreeBSD call the GNU make binary "gmake" to  
distinguish it from the system's own make binary. You could put a  
symlink called "gmake" into /usr/bin (or /usr/local/bin, depending on  
your local policy) which points to /opt/OpenSource/bin/make. You can  
then run GNU make by using "gmake" instead of "make" in the build  
process from any shell you prefer.


regards,
Markus

Markus has good advice.  We make legacy Unix machines compatible with 
open source software by installing gcc and key GNU utilities like make, 
bison etc in /usr/local/bin and put that in the path.  Where the names 
clash, like with "make" we name the GNU one gmake.


You then have the ./configure, gmake and gmake install sequence working.

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

Re: [sqlite] The need for sqlite3_encode_binary and sqlite3_decode_binary

[Dennis Cote]

Andrew Finkenstadt wrote:

It would appear that by using bind variables instead of '%Q' in the SQL
string, the need for sqlite3_encode_binary and sqlite3_decode_binary is
eliminated.  Is that indeed the case?


Andy,

Yes that is the case. You can use sqlite3_bind_blob to pass arbitrary 
binary data to an SQL statement without worrying about special quoting.


Dennis Cote

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

Re: [sqlite] sql stack using sqlite

[Ken]

 I think i found an ok solution: (note I've updated my own insert statements 
from the OP)
 
 drop table purg;
 create temporary table purg (id integer primary key, tbl);

 -- Use the fact that the Puging tables  C unique ID is always after table A's 
data.
 insert into purg
 select  distinct kd.id, 'a'
   from c, a
 where  c.id = a.id
and  c.cnt  = a.cnt
and  c.ref  = a.ref
and  a.id < c.id ;
 
 -- For ech a record to be purged. Join it to get the ref value and the table B 
id's
 insert into purg
  select distinct b.id, 'b'
   from  purg p,   b,  a
   where p.id   = a.id
 and a.id  = b.id
 and a.ref = b.ref ;
 
 insert into purg
 select  c.id, 'c'
   from c;
 
 
 --- Now purge.
 delete from a where id in (select id from purg where tbl = 'a');
 delete from b where id in (select id from purg where tbl = 'b');
 delete from c where id in (select id from purg where tbl = 'c');
 
 
 Any ideas of a better way?
 
 Thanks,
 Ken
 
 
 
 
Ken <[EMAIL PROTECTED]> wrote: Id like to get your ideas on implementing a 
stack using sql tables.
 
 table a, contains references to b 
 table b contains refernce to a
 
 table c contains delete entries for A (but b must also be purged!)
 
 My processing forces me to load all of a,b and c. 
 There may be cases where table C indicates a complete deletion for table A. As 
a special case there is another table D that indicates a complete delete. In 
this instance I can delete by another unique Id that is contained in all of the 
tables but ommitted for brevity.
 
 
 create table a ( id integer, ref integer, cnt integer );
 create table b ( id integer, ref integer, val text );
 create table c ( id integer, ref,  cnt integer );
 
 insert into a values (1,32,5);
 insert into b values (11,32,'first data item');
 insert into c values (2,32,5) ;
 
 insert into a values (2,33,5);
 insert into b values (12,33,'second data item');
 insert into c values (3,5) ;
 
 insert into a values (4,34,5);
 insert into b values (13,34,'third data item');
 
 After processing, Id like to be left with the following:
 a ( 4, 34,5)
 b (13, 34, 'third data item')
 
 This is easily implemented in a memory stack. but I'm not sure how to 
implement using sql.
 
 thanks for any ideas.
 Ken
 
 
 
 
 
 

Re: [sqlite] DBD::SQLite with FTS

[Tomash Brechko]

On Thu, May 10, 2007 at 08:04:51 -0500, P Kishor wrote:
> are there any guidelines on how to hook a new SQLite lib with the
> DBD package since the CPAN version seems to be running a few
> versions late.

No special actions needed, default build of DBD::SQLite will use
pre-installed shared library instead of the shipped code:

$ ldd /usr/lib/perl5//DBD/SQLite/SQLite.so
linux-gate.so.1 =>  (0xb7fc)
 (!)libsqlite3.so.0 => /usr/local/lib/libsqlite3.so.0 (0xb7f25000)
libc.so.6 => /lib/tls/libc.so.6 (0xb7dfe000)
/lib/ld-linux.so.2 (0x8000)



-- 
   Tomash Brechko

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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[T]

Hi Brandon,


Is there a way to upgrade the module to SQLite 3 on PHP4?


I think so. I recall reading something about it, but since I was able  
to just install PHP5 I went via that route. Try Google:

http://www.google.com.au/search?q=SQLite3+PHP4

Tom


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

Re: [sqlite] Longest "real" SQL statement

[T]

Hi Richard,

I tend to use a series of CREATE VIEW AS SELECT type statements to  
effectively nest my SELECT statements, making them more humanly  
editable and manageable. Consequently, the size of the maximum sized  
single SQL statement is reduced, though the total SQL statement  
effectively executed may be reasonably large. So I don't know if such  
a series would be of use to look at, for your purposes.


In any case, below is a series of nested CREATE VIEW ... AS SELECT  
statements, which if combined into the one that effectively runs when  
the first "Aligned_All_Accept" is run, is quite large (larger than  
the sum of its parts). The longest single SQL statement is probably  
the view called "Structured_Amicroe".


For the curious, this set of views modifies the varied source price  
list data from five supplier price lists into one homogeneous view,  
selectively adding calculated fields (such as image URLs) and  
removing records with dodgy data.


Tom

CREATE VIEW Aligned_All_Accept AS
  SELECT * FROM Aligned_All EXCEPT SELECT * FROM Aligned_All_Reject

CREATE VIEW Aligned_All_Reject AS
  SELECT * FROM Aligned_All WHERE Buy = 0 OR Buy * 1.1 > RRP AND RRP  
IS NOT NULL AND RRP > 0


CREATE VIEW Aligned_All AS
SELECT * FROM Aligned_Amicroe
  UNION ALL
SELECT * FROM Aligned_Apple
  UNION ALL
SELECT * FROM Aligned_BlueChipIT
  UNION ALL
SELECT * FROM Aligned_MacSense
  UNION ALL
SELECT * FROM Aligned_PowerMove
  UNION ALL SELECT * FROM Aligned_Simms

CREATE VIEW Aligned_Amicroe AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, NULL AS Detail, NULL AS Thumbnail, NULL AS Image, NULL  
AS Manufacturer, NULL AS URL, NULL AS Warranty, Buy, NULL AS RRP,  
Sell, NULL AS Stock, NULL AS Ordered, NULL AS Due, Modified FROM  
Calculated_Amicroe


CREATE VIEW Aligned_Apple AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, Detail, NULL AS Thumbnail, Image, Manufacturer, NULL AS  
URL, Warranty, Buy, RRP, Sell, Stock, NULL AS Ordered, NULL AS Due,  
Modified FROM Calculated_Apple


CREATE VIEW Aligned_BlueChipIT AS
  SELECT rowid, Supplier, Category, Category2, NULL AS Category3,  
Code, Description, Detail, Thumbnail, Image, Manufacturer, NULL AS  
URL, NULL AS Warranty, Buy, NULL AS RRP, Sell, Stock, Ordered, Due,  
Modified FROM Calculated_BlueChipIT


CREATE VIEW Aligned_MacSense AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, Detail, NULL AS Thumbnail, Image, NULL AS Manufacturer,  
URL, NULL AS Warranty, Buy, RRP, Sell, NULL AS Stock, NULL AS  
Ordered, NULL AS Due, Modified FROM Calculated_MacSense ORDER BY lower 
(Category), lower(Category2), lower(Category3)


CREATE VIEW Aligned_PowerMove AS
  SELECT rowid, Supplier, Category, Category2, NULL AS Category3,  
Code, Description, Detail, NULL AS Thumbnail, Image, NULL AS  
Manufacturer, NULL AS URL, NULL AS Warranty, Buy, RRP, Sell, Stock,  
NULL AS Ordered, NULL AS Due, Modified FROM Calculated_PowerMove


CREATE VIEW Aligned_Simms AS
  SELECT rowid, Supplier, Category, Category2, Category3, Code,  
Description, Detail, NULL AS Thumbnail, Image, Manufacturer, NULL AS  
URL, Warranty, Buy, RRP, Sell, Stock, NULL AS Ordered, NULL AS Due,  
Modified FROM Calculated_Simms


CREATE VIEW Calculated_Amicroe AS
  SELECT rowid, Category, Category2, Category3, Code,  
Products.Description AS Description, Buy, Products.Modified AS  
Modified, 'Amicroe' AS Supplier, Round(Buy * 1.1 * 1.13 + 0.5) AS  
Sell FROM Structured_Amicroe AS Products JOIN Suppliers ON  
Suppliers.Name='Amicroe' WHERE Buy IS NOT NULL AND Buy > 0.0


CREATE VIEW Calculated_Apple AS
  SELECT rowid, Category, Category2, Category3, Code,  
Products.Description AS Description, Detail, CASE WHEN Image LIKE '% 
CPP00869.JPG' OR Image LIKE '%CPP00011.JPG' OR Image LIKE '% 
CPP00014.JPG' OR Image LIKE '%simmsnoimage.jpg' THEN NULL ELSE  
'http://www.simms.com.au/direct/' || Image END AS Image,  
Manufacturer, Warranty, Buy, RRP, Stock, Products.Modified AS  
Modified, 'Apple' AS Supplier, CASE WHEN RRP NOTNULL AND RRP > 0 AND  
Round(Buy * 1.1 * (1 + Markup) + 0.5) > RRP THEN Round((Buy * 1.1 * 2  
+ RRP * 8) / 10) ELSE Round(Buy * 1.1 * (1 + Markup) + 0.5) END AS  
Sell FROM Structured_Apple AS Products JOIN Suppliers ON  
Suppliers.Name='Apple'


CREATE VIEW Calculated_BlueChipIT AS
  SELECT rowid, Category, Category2, Code, Products.Description AS  
Description, Detail, Manufacturer, Buy, Stock, Ordered, Due, 'http:// 
www.bluechipit.com.au/content/_images/' || Code || '_sm.jpg' AS  
Thumbnail, 'http://www.bluechipit.com.au/content/_images/' || Code ||  
'_lg.jpg' AS Image, Products.Modified AS Modified, 'BlueChipIT' AS  
Supplier, Round(Buy * 1.1 * (1 + Markup) + 0.5) AS Sell FROM  
Structured_BlueChipIT AS Products JOIN Suppliers ON  
Suppliers.Name='BlueChipIT' WHERE Category != 'Services'


CREATE VIEW Calculated_MacSense AS
  SELECT rowid, Category, Category2, Category3, 

RE: [sqlite] Longest "real" SQL statement

[Samuel R. Neff]

We don't do it in SQLite but as an example of how large a legitimate SQL
statement can be, in a previous project we generated a pseudo-cube from the
current database in a single sql statement.  Basically we wanted to
implement a fast complex search routine where users can choose any fields
from any tables and we can filter quickly.  

The sql statement was an INSERT statement with several hundred fields and
40+ joined tables (no where clause).  Initially it took 30 seconds to run on
Sybase ASE but after some optimization (mostly of the OS, not sql) it ran in
under a second for typical production data.  Of course we removed all
indexes during the actual insert operation.

If we did the same thing in our current project we'd have 242 fields in the
select list and 69 tables in the FROM clause.  No where clause or order by.

HTH,

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 09, 2007 7:33 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Longest "real" SQL statement

I'm looking for an upper bound on how big legitimate 
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Brandon Eley]

No, I can't upgrade to PHP5, yet. We're on PHP4.

Brandon Eley
[EMAIL PROTECTED]



On May 10, 2007, at 9:04 AM, Chris Peachment wrote:


I think I have misread your original posting.
Can you upgrade your version of PHP?
I use PHP version 5.2.1.

Running phpinfo() reveals the PDO driver to be:

PECL version: 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.1 2007/01/01  
09:36:05 sebastian Exp $

Sqlite Library: 3.3.7undefined

Since you can run sqlite3 against the database without issue,
it appears that the version embedded in PHP is the problem.

Chris Peachment

--

On Thu, 10 May 2007 08:46:30 -0400, Brandon Eley wrote:


Thank you for this information. How would I access the 2.8.x database
from within PHP? is it even possible?



The database is created from a shopping cart program, so I can't
upgrade it, it has to remain the same version (for now).



Brandon Eley
[EMAIL PROTECTED]





On May 9, 2007, at 10:59 PM, Chris Peachment wrote:



There was a change of file structure with version 3.x.x
and this is described in the documentation under Version 3
Overview.

You are attempting to use Sqlite3 on a version 2.8.x database
and the formats are not compatible.


On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:


I've gone through a few tutorials and can't seem to get this right.
I've had my web host install the PECL extension and the  
following is

what is in my phpinfo();



sqlite
SQLite support  enabled
PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
12:25:33 wez Exp $
SQLite Library  2.8.14
SQLite Encoding iso8859



Directive   Local Value Master Value
sqlite.assoc_case   0   0



***



This is the PHP code I'm using:







***



This is the error I'm getting:



Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
is not a database in /home/xxx/public_html/test.php on line 140



***



What am I doing wrong? I can run queries all day long using the
command line sqlite3 but I can't seem to get PHP to read the  
stinkin

database!



Any help would be GREATLY appreciated!



Brandon Eley
[EMAIL PROTECTED]









 
--

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

---




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-- 
---

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





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

Re: [sqlite] My HPUX Notes

[Markus Hoenicka]

Quoting km4hr <[EMAIL PROTECTED]>:


I just happened to notice that I may not be executing the sqlite
installation process (configure/make/make install) in a full "bash"
environment. My usual environment is "ksh". Typing in the command
"/bin/OpenSource/bin/bash" I get a bash prompt. But apparently that doesn't
put me in a full bash environment. I just noticed that unless I specify the
full path to the GNU make command (/opt/OpenSource/bin/make) then the ksh
version (/usr/bin/make) is executed. I wonder what I have to do to get into
a true bash environment? Must be some environment viable that has to be
changed. Oh well, one mystery begets another!



There is no such thing as a full "bash" environment. bash is just  
another shell, just like ksh. You may experience some differences  
because the shells may read different startup files, so e.g.  
environment variables may be set differently.


Some systems like FreeBSD call the GNU make binary "gmake" to  
distinguish it from the system's own make binary. You could put a  
symlink called "gmake" into /usr/bin (or /usr/local/bin, depending on  
your local policy) which points to /opt/OpenSource/bin/make. You can  
then run GNU make by using "gmake" instead of "make" in the build  
process from any shell you prefer.


regards,
Markus

--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de


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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Brandon Eley]

It's ShopSite (www.shopsite.com) and I'm building add-on modules for  
the cart that need to access information in the DB.


Unfortunately, we can't upgrade to PHP5 just yet, as we have quite a  
few sites on the server running open source software and we haven't  
verified they are all compatible with PHP5.


Is there a way to upgrade the module to SQLite 3 on PHP4?

Brandon Eley
[EMAIL PROTECTED]



On May 10, 2007, at 8:58 AM, T wrote:


Hi Brandon,

I've had my web host install the PECL extension and the  
following is what is in my phpinfo();


sqlite
SQLite support  enabled
PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
SQLite Library  2.8.14


How would I access the 2.8.x database from within PHP? is it even  
possible?


I think you have the problem backwards. Your database data file is  
probably already "upgraded" to SQLite version 3.x format. But it's  
the SQLite executable library in PHP that is lagging at 2.8.14. You  
need to upgrade your PHP SQLite library so it can open your newer  
version database file.


The simplest way I found to do this, was to upgrade my PHP to the  
latest 5.2.1, which includes SQLite support for 3.x database files.


The database is created from a shopping cart program, so I can't  
upgrade it, it has to remain the same version (for now).


Out of curiosity, what is the shopping cart software that you're  
using?


Tom


-- 
---

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





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

[sqlite] DBD::SQLite with FTS

[P Kishor]

has anyone created a DBD::SQLite with the full-text search option
turned on? else, are there any guidelines on how to hook a new SQLite
lib with the DBD package since the CPAN version seems to be running a
few versions late.

--
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] Problem with SQLite Pecl extension for PHP4

[Chris Peachment]

I think I have misread your original posting.
Can you upgrade your version of PHP?
I use PHP version 5.2.1.

Running phpinfo() reveals the PDO driver to be:

PECL version: 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.1 2007/01/01 09:36:05 
sebastian Exp $
Sqlite Library: 3.3.7undefined

Since you can run sqlite3 against the database without issue,
it appears that the version embedded in PHP is the problem.

Chris Peachment

--

On Thu, 10 May 2007 08:46:30 -0400, Brandon Eley wrote:

>Thank you for this information. How would I access the 2.8.x database  
>from within PHP? is it even possible?

>The database is created from a shopping cart program, so I can't  
>upgrade it, it has to remain the same version (for now).

>Brandon Eley
>[EMAIL PROTECTED]



>On May 9, 2007, at 10:59 PM, Chris Peachment wrote:

>> There was a change of file structure with version 3.x.x
>> and this is described in the documentation under Version 3
>> Overview.
>>
>> You are attempting to use Sqlite3 on a version 2.8.x database
>> and the formats are not compatible.
>>
>>
>> On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:
>>
>>> I've gone through a few tutorials and can't seem to get this right.
>>> I've had my web host install the PECL extension and the following is
>>> what is in my phpinfo();
>>
>>> sqlite
>>> SQLite support  enabled
>>> PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
>>> 12:25:33 wez Exp $
>>> SQLite Library  2.8.14
>>> SQLite Encoding iso8859
>>
>>> Directive   Local Value Master Value
>>> sqlite.assoc_case   0   0
>>
>>> ***
>>
>>> This is the PHP code I'm using:
>>
>>> >> $db = sqlite_open("/home/xxx/data/xxx.db");
>>> ?>
>>
>>> ***
>>
>>> This is the error I'm getting:
>>
>>> Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
>>> is not a database in /home/xxx/public_html/test.php on line 140
>>
>>> ***
>>
>>> What am I doing wrong? I can run queries all day long using the
>>> command line sqlite3 but I can't seem to get PHP to read the stinkin
>>> database!
>>
>>> Any help would be GREATLY appreciated!
>>
>>> Brandon Eley
>>> [EMAIL PROTECTED]
>>
>>
>>
>>
>>
>>
>>
>>
>> -- 
>> ---
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -- 
>> ---
>>


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





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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Ulrich Schöbel]

It's the other way round: Your database is 3.x, your SQLite library
used by PHP is 2.8.14. Upgrade your PHP.

Ulrich


On Thursday 10 May 2007 14:46, Brandon Eley wrote:
> Thank you for this information. How would I access the 2.8.x database
> from within PHP? is it even possible?
>
> The database is created from a shopping cart program, so I can't
> upgrade it, it has to remain the same version (for now).
>
> Brandon Eley
> [EMAIL PROTECTED]
>
> On May 9, 2007, at 10:59 PM, Chris Peachment wrote:
> > There was a change of file structure with version 3.x.x
> > and this is described in the documentation under Version 3
> > Overview.
> >
> > You are attempting to use Sqlite3 on a version 2.8.x database
> > and the formats are not compatible.
> >
> > On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:
> >> I've gone through a few tutorials and can't seem to get this right.
> >> I've had my web host install the PECL extension and the following is
> >> what is in my phpinfo();
> >>
> >> sqlite
> >> SQLite support enabled
> >> PECL Module version1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
> >> 12:25:33 wez Exp $
> >> SQLite Library 2.8.14
> >> SQLite Encodingiso8859
> >>
> >> Directive  Local Value Master Value
> >> sqlite.assoc_case  0   0
> >>
> >> ***
> >>
> >> This is the PHP code I'm using:
> >>
> >>  >> $db = sqlite_open("/home/xxx/data/xxx.db");
> >> ?>
> >>
> >> ***
> >>
> >> This is the error I'm getting:
> >>
> >> Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
> >> is not a database in /home/xxx/public_html/test.php on line 140
> >>
> >> ***
> >>
> >> What am I doing wrong? I can run queries all day long using the
> >> command line sqlite3 but I can't seem to get PHP to read the stinkin
> >> database!
> >>
> >> Any help would be GREATLY appreciated!
> >>
> >> Brandon Eley
> >> [EMAIL PROTECTED]
> >
> > --
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[T]

Hi Brandon,

I've had my web host install the PECL extension and the following  
is what is in my phpinfo();


sqlite
SQLite support  enabled
PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
SQLite Library  2.8.14


How would I access the 2.8.x database from within PHP? is it even  
possible?


I think you have the problem backwards. Your database data file is  
probably already "upgraded" to SQLite version 3.x format. But it's  
the SQLite executable library in PHP that is lagging at 2.8.14. You  
need to upgrade your PHP SQLite library so it can open your newer  
version database file.


The simplest way I found to do this, was to upgrade my PHP to the  
latest 5.2.1, which includes SQLite support for 3.x database files.


The database is created from a shopping cart program, so I can't  
upgrade it, it has to remain the same version (for now).


Out of curiosity, what is the shopping cart software that you're using?

Tom


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

Re: [sqlite] My HPUX Notes

[km4hr]

Essien Essien wrote:
> 
> that was really funny you know :)

Always a pleasure to provide entertainment for my friends.

> 
> On POSIX systems you're supposed to do three things to get your
> software installed (usually a standard set of three commands).
> 
> 1. ./configure (some packages leave this out, since they don't use GNU
> autotools)
> 2. make (there's almost always this, or an alternative, CMAKE, Scons, ant,
> etc)
> 3. make install (if there's a Makefile, you'll be able to do a make
> install, if the software is supposed to be used anyways... and all
> replacements have equivalents - ant deploy for instance).
>

I did try your method. These are the same steps given in the sqlite README
file. I've used them before in other installations. But they didn't work for
me in this case. Here's the directions from the sqlite README file. 

tar xzf sqlite.tar.gz;#  Unpack the source tree into "sqlite"
mkdir bld;#  Build will occur in a sibling directory
cd bld   ;#  Change to the build directory
../sqlite/configure  ;#  Run the configure script
make ;#  Run the makefile.
make install ;#  (Optional) Install the build products

I performed the above steps as stated. But they failed on my HPUX 11.0 box.
First, the "make" step fails immediately as I explained earlier. The README
file doesn't explain that HP's "make" won't work and that you have to use
the GNU "make" instead. Many people won't realize this.  I have done them a
service by telling them so.

The "make install" step doesn't work either. It gives error messages saying
the "-d" option is invalid for the "cp" command.  You also get the following
crytic message.  I asked HP about this. They don't recommend it.

>> If you ever happen to want to link against installed libraries
>> in a given directory, LIBDIR, you must either use libtool, and
>> specify the full pathname of the library, or use the `-LLIBDIR'
>> flag during linking and do at least one of the following:
>>- add LIBDIR to the `SHLIB_PATH' environment variable
>> during execution
>>   - use the `-Wl,+b -Wl,LIBDIR' linker flag

>> See any operating system documentation about shared libraries for
>> more information, such as the ld(1) and ld.so(8) manual pages.

I posted my experience on this site hoping it might benefit anyone who wants
to use sqlite but has found that the standard install process isn't working.
Even though my method looks strange, I prefer funny success over sad
failure.

I just happened to notice that I may not be executing the sqlite
installation process (configure/make/make install) in a full "bash"
environment. My usual environment is "ksh". Typing in the command
"/bin/OpenSource/bin/bash" I get a bash prompt. But apparently that doesn't
put me in a full bash environment. I just noticed that unless I specify the
full path to the GNU make command (/opt/OpenSource/bin/make) then the ksh
version (/usr/bin/make) is executed. I wonder what I have to do to get into
a true bash environment? Must be some environment viable that has to be
changed. Oh well, one mystery begets another!

-- 
View this message in context: 
http://www.nabble.com/I%27m-Starving-for-New-User-Information-tf3701471.html#a10412825
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] PHP5 and SQLite3 and SQLite2

[Al Rider]

As I understand it, SQLite2 files are not compatible with SQLite3.  This 
poses a dilemma for me and I would guess many others on shared, virtual 
webhosts.


The docs say to simply have both versions installed and copy from vers 2 
to vers 3.  That's OK for dedicated servers; but, most of us who use 
shared hosting can't get the webhosts to install both versions. It's 
hard enough to get them to upgrade to php5 with SQLite3. 

For example, my host has php5.1.4 /SQLite2.8.  I'd like to design a new 
application; but, am trapped because the host plans to upgrade to 
php5.4.4 in about a month or two.  This means my data files will not be 
compatible when the upgrade is effected. 

I need some suggestions on how to handle this dilemma. 


Thanks



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

Re: [sqlite] A suggestion

[Trey Mack]

Works for me straight out of the box on Windows XP.


That program does have the capability, but may not be implemented that way 
on Windows.  Why not make the change yourself?


A.J.Millan wrote:
As a suggestion, and even in the risk to abuse of Mr Hipp's patience. 
Would

it be possible to include in the command-line program (sqlite3.exe) the
ability to edit, an repeat at least the five or six last commands, as in
Linux?. Is to say with up-arrow and down-arrow.  I believe it would be 
too

helpful.




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

Re: [sqlite] Problem with SQLite Pecl extension for PHP4

[Brandon Eley]

Thank you for this information. How would I access the 2.8.x database  
from within PHP? is it even possible?


The database is created from a shopping cart program, so I can't  
upgrade it, it has to remain the same version (for now).


Brandon Eley
[EMAIL PROTECTED]



On May 9, 2007, at 10:59 PM, Chris Peachment wrote:


There was a change of file structure with version 3.x.x
and this is described in the documentation under Version 3
Overview.

You are attempting to use Sqlite3 on a version 2.8.x database
and the formats are not compatible.


On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:


I've gone through a few tutorials and can't seem to get this right.
I've had my web host install the PECL extension and the following is
what is in my phpinfo();



sqlite
SQLite support  enabled
PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
12:25:33 wez Exp $
SQLite Library  2.8.14
SQLite Encoding iso8859



Directive   Local Value Master Value
sqlite.assoc_case   0   0



***



This is the PHP code I'm using:







***



This is the error I'm getting:



Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
is not a database in /home/xxx/public_html/test.php on line 140



***



What am I doing wrong? I can run queries all day long using the
command line sqlite3 but I can't seem to get PHP to read the stinkin
database!



Any help would be GREATLY appreciated!



Brandon Eley
[EMAIL PROTECTED]









-- 
---

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





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

Re: [sqlite] Longest "real" SQL statement

[Stephen Toney]

Hi Richard,

Our longest select so far this one:

select * from log where ((aut not like '6%' and aut not like '-1%') or
(avm not like '6%' and avm not like '-1%') or (lam not like '6%' and lam
not like '-1%') or (pam not like '6%' and pam not like '-1%') or (pas
not like '6%' and pas not like '-1%') or (clc not like '6%' and clc not
like '-1%') or (fro not like '6%' and fro not like '-1%') or (spw_ifr
not like '6%' and spw_ifr not like '-1%') or (spw_dem not like '6%' and
spw_dem not like '-1%') or (sp_reg not like '6%' and sp_reg not like
'-1%') or (uni_10 not like '6%' and uni_10 not like '-1%') or (aut_dem
not like '6%' and aut_dem not like '-1%') or (uni_11 not like '6%' and
uni_11 not like '-1%')) order by system, id

This returns 29 columns. I claim no credit for such a rotten data model
that requires a query like this -- it was converted from a spreadsheet.

In another app we have an insert into that inserts 60 columns. The query
is generated by a program so I can't just copy it for you, but I can
provide it if will help.


HTH,

Stephen Toney
Systems Planning
[EMAIL PROTECTED]
http://www.systemsplanning.com


On Wed, 2007-05-09 at 23:32 +, [EMAIL PROTECTED] wrote:
> I'm looking for an upper bound on how big legitimate 
> SQL statements handed to SQLite get to be.  I'm not
> interested in contrived examples.  I want to see
> really big SQL statements that are actually used in
> real programs.
> 
> "Big" can be defined in several ways:
> 
> *  Number of bytes of text in the SQL statement.
> *  Number of tokens in the SQL statement
> *  Number of result columns in a SELECT
> *  Number of terms in an expression
> 
> If you are using really big SQL statements, please
> tell me about them.  I'd like to see the actual
> SQL text if possible.  But if your use is proprietary,
> please at least tell me how big your query is in
> bytes or tokens or columns or expression terms.
> 
> Thanks.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
-- 



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

[sqlite] explain query plan/timing

[bash]

 Hello All,

I've tried to find documentation/help about how to read output from
EXPLAIN QUERY PLAN but without success. Can you point me where i can
get it.

And another question about timing/profiling for SQLite.
Patch from this location
http://katastrophos.net/andre/blog/2007/01/04/sqlite-simple-timing-profiler-patch/
don't work with latest SQLite.

-- 
Biomechanical Artificial Sabotage Humanoid

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

Re: [sqlite] sqlite3_last_insert_rowid : what will it return if we have more than one table?

[drh]

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> Assume I have two tables A and B in the databse.
> 
>  
> 
> sqlite3_last_insert_rowid will return the rowid of which table?
> 

The one you last inserted into.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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

[sqlite] sqlite3_last_insert_rowid : what will it return if we have more than one table?

[B V, Phanisekhar]

Assume I have two tables A and B in the databse.

 

sqlite3_last_insert_rowid will return the rowid of which table?

 

Regards,

Phani

Re: [sqlite] ATTACH and sqlite3_open()

[Dan Kennedy]

On Wed, 2007-05-09 at 16:18 -0700, Jon Scully wrote:
> Simpler than that.  I merely want to attach to two databases (files).
> flash.db contains a set of tables that hold non-volatile data; ram.db
> contains a set of tables that is re-built (volatile) on re-boot -- but
> offers fast, read-only access.  No table-name overlaps, of course.
> 
> I want to access both sets of tables, seamlessly, as I should be able
> to do using ATTACH, at the command prompt, but do so using the C API.
> 
> Just wondering how others do this (Using ATTACH?  Using
> sqlite3_open()? Obviously I haven't looked very far into the
> sqlite3_open() code to see how it's put together, etc.).

Execute an ATTACH statement via sqlite3_exec(), or 
sqlite3_prepare/step/finalize.

> sqlite3 *db;
>
> if (sqlite3_open("flash.db", )) {
> fprintf(stderr, "Can't open the database in the Flash file
> system\n");
> exit(2);
> } else if (sqlite3_open("ram.db", )) {
> fprintf(stderr, "Can't open the database in the RAM-disk file
> system\n");
> sqlite3_close(db);
> exit(2);
> }

Don't do this. The second call to sqlite3_open opens a
new database connection to the file "ram.db" and overwrites
variable db with the new handle.

Dan.



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

Re: [sqlite] create table error

[Mohd Radzi Ibrahim]

Could it be that the data where *sql is pointing to is being re-used 
somewhere?



--radzi.

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, May 10, 2007 3:54 PM
Subject: [sqlite] create table error



the following is my test code. just create table.


rc = sqlite3_open("zieckey.db", );
char *sql = " CREATE TABLE SensorData(ID INTEGER PRIMARY KEY,SensorID
INTEGER,SiteNum INTEGER,Time VARCHAR(12),SensorParameter REAL);" ;
sqlite3_exec( db , sql , 0 , 0 ,  );
sqlite3_close(db);



when execute the sqlite3_exec function,

it corrupt at line 189, sqlite3StrICmp(pTab->aCol[j].zName,
pChanges->a[i].zName)==0 )

pChanges->a[i].zName is NULL;

anybody know the reason ?


how to trace the error? there is no clue for me to analyze the error.

thanks a lot

allen.zhang





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

[sqlite] Network share goes down, SQLite keeps trying

[Jan Debis]

Greetings,

I'm working on a small win32 application that periodically writes data
to a SQLite database located on a remote machine, the remote database
file is opened with a UNC file name, e.g.
\\remotemachine\share\path\to\db

Now if the remote server that holds the database file gets shut down
or rebooted while my application still has an open DB handle, then the
next DB operation will cause my app to get stuck in the busy_handler,
apparently thinking the DB is locked.

Is this intended behavior or a bug?

As a workaround I'm now checking if the file exists each time the busy
handler gets called, and aborting the operation if the file is no
longer there. If anyone knows of a better way to handle this
situation, please let me know :)

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

[sqlite] create table error

[allen . zhang]

the following is my test code. just create table.


 rc = sqlite3_open("zieckey.db", ); 
 char *sql = " CREATE TABLE SensorData(ID INTEGER PRIMARY KEY,SensorID 
INTEGER,SiteNum INTEGER,Time VARCHAR(12),SensorParameter REAL);" ;
 sqlite3_exec( db , sql , 0 , 0 ,  );
 sqlite3_close(db); 



when execute the sqlite3_exec function,

it corrupt at line 189, sqlite3StrICmp(pTab->aCol[j].zName, 
pChanges->a[i].zName)==0 )

pChanges->a[i].zName is NULL;

anybody know the reason ?


how to trace the error? there is no clue for me to analyze the error.

thanks a lot

allen.zhang

Re: [sqlite] Longest "real" SQL statement

[miguel manese]

I am the author of the package SQLiteDF for R (a statistical package),
some sort of sqlite backed "data set". It's "raison d'etre" is to deal
with very large datasets, which could be tables with thousands of
columns. I am not much on the infinite length sql statement, but I
need lots of columns in the result. I plan to hack my way into
extending the syntax to something like

select col1 ... col100 from table

which is shorthand for select col1,col2,col3,..., col100. So a
result set with lots of columns would be very nice.

Thanks,
M. Manese


On 5/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I'm looking for an upper bound on how big legitimate
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

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


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




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

RE: [sqlite] Longest "real" SQL statement

[RB Smissaert]

This is one of my biggest and it is part of a number of queries to
transpose a table:

INSERT OR REPLACE INTO A3BP619_J(PATIENT_ID, ENTRY_ID_E1, START_DATE_E1,
ADDED_DATE_E1, SYST_E1, DIAST_E1, ENTRY_ID_E2, START_DATE_E2, ADDED_DATE_E2,
SYST_E2, DIAST_E2, ENTRY_ID_E3, START_DATE_E3, ADDED_DATE_E3, SYST_E3,
DIAST_E3, ENTRY_ID_E4, START_DATE_E4, ADDED_DATE_E4, SYST_E4, DIAST_E4,
ENTRY_ID_E5, START_DATE_E5, ADDED_DATE_E5, SYST_E5, DIAST_E5, ENTRY_ID_E6,
START_DATE_E6, ADDED_DATE_E6, SYST_E6, DIAST_E6, ENTRY_ID_E7, START_DATE_E7,
ADDED_DATE_E7, SYST_E7, DIAST_E7, ENTRY_ID_E8, START_DATE_E8, ADDED_DATE_E8,
SYST_E8, DIAST_E8, ENTRY_ID_E9, START_DATE_E9, ADDED_DATE_E9, SYST_E9,
DIAST_E9, ENTRY_ID_E10, START_DATE_E10, ADDED_DATE_E10, SYST_E10, DIAST_E10,
ENTRY_ID_E11, START_DATE_E11, ADDED_DATE_E11, SYST_E11, DIAST_E11,
ENTRY_ID_E12, START_DATE_E12, ADDED_DATE_E12, SYST_E12, DIAST_E12,
ENTRY_ID_E13, START_DATE_E13, ADDED_DATE_E13, SYST_E13, DIAST_E13,
ENTRY_ID_E14, START_DATE_E14, ADDED_DATE_E14, SYST_E14, DIAST_E14,
ENTRY_ID_E15, START_DATE_E15, ADDED_DATE_E15, SYST_E15, DIAST_E15,
ENTRY_ID_E16, START_DATE_E16, ADDED_DATE_E16, SYST_E16, DIAST_E16,
ENTRY_ID_E17, START_DATE_E17, ADDED_DATE_E17, SYST_E17, DIAST_E17,
ENTRY_ID_E18, START_DATE_E18, ADDED_DATE_E18, SYST_E18, DIAST_E18,
ENTRY_ID_E19, START_DATE_E19, ADDED_DATE_E19, SYST_E19, DIAST_E19,
ENTRY_ID_E20, START_DATE_E20, ADDED_DATE_E20, SYST_E20, DIAST_E20,
ENTRY_ID_E21, START_DATE_E21, ADDED_DATE_E21, SYST_E21, DIAST_E21,
ENTRY_ID_E22, START_DATE_E22, ADDED_DATE_E22, SYST_E22, DIAST_E22,
ENTRY_ID_E23, START_DATE_E23, ADDED_DATE_E23, SYST_E23, DIAST_E23,
ENTRY_ID_E24, START_DATE_E24, ADDED_DATE_E24, SYST_E24, DIAST_E24,
ENTRY_ID_E25, START_DATE_E25, ADDED_DATE_E25, SYST_E25, DIAST_E25,
ENTRY_ID_E26, START_DATE_ <<---etc.--->> T JOIN GROUP_39 g39 ON
(t1.PATIENT_ID = g39.PID) LEFT JOIN GROUP_40 g40 ON (t1.PATIENT_ID =
g40.PID)

It can be a lot longer even in Excel 2007 as that has many more available
columns.

RBS

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 10 May 2007 00:33
To: sqlite-users@sqlite.org
Subject: [sqlite] Longest "real" SQL statement

I'm looking for an upper bound on how big legitimate 
SQL statements handed to SQLite get to be.  I'm not
interested in contrived examples.  I want to see
really big SQL statements that are actually used in
real programs.

"Big" can be defined in several ways:

*  Number of bytes of text in the SQL statement.
*  Number of tokens in the SQL statement
*  Number of result columns in a SELECT
*  Number of terms in an expression

If you are using really big SQL statements, please
tell me about them.  I'd like to see the actual
SQL text if possible.  But if your use is proprietary,
please at least tell me how big your query is in
bytes or tokens or columns or expression terms.

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

Re: [sqlite] My HPUX Notes

[Essien Essien]

guess you missed all the smileys and innuendo in that reply then.

On 5/8/07, Glenn <[EMAIL PROTECTED]> wrote:

Essien Essien wrote:
> that was really funny you know :)

And your response was really obnoxious.  You could have simply said that
the common way to do things on POSIX is "./configure; make; make
install;" which would accomplish the same thing and left it at that.
Rubbing someone's nose in their inexperience is extremely rude.

--
Glenn McAllister <[EMAIL PROTECTED]>  +1 416 348 1594
SOMA Networks, Inc.  http://www.somanetworks.com/  +1 416 977 1414

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




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