[sqlite] SQLITE_CORRUPT recover
Hi, Is there any way to programmatically fix a corrupted sqlite database? I am using sqlite version 3.3.8 with C APIs -- Sabyasachi
[sqlite] indexing large databases
For "create index.." in large database : "pragma default_cache_size = 2000;" For "select ..." -- "pragma default_cache_size = 1200;" Juri
Re: [sqlite] sqlite on VPS configurations
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
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
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&B <[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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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, Code,
RE: [sqlite] Longest "real" SQL statement
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
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
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
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&B 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
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
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
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
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
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
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
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
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
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
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?
"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?
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()
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", &db)) { > fprintf(stderr, "Can't open the database in the Flash file > system\n"); > exit(2); > } else if (sqlite3_open("ram.db", &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
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", &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 , &zErrMsg ); 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
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
the following is my test code. just create table. rc = sqlite3_open("zieckey.db", &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 , &zErrMsg ); 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
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
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] -