[sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
I have just noticed this syntax which will simplify some table creation for me. However in some instances where I want to use it, I have a handy SELECT available, but I don't want to actually insert a row at that time. Testing with the shell, the following appears to work: attach database ':memory:' as mem; create table mem.messages as select * from main.messages limit 0; and I get a new, empty, table with the same columns. Is this supposed to work in this way? The info at http://www.sqlite.org/lang_createtable.html doesn't say one way or the other. I'd like to take advantage of this, but not if it is undefined behaviour that just happens to work. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On 07/10/2014 04:45 PM, Tim Streater wrote: I have just noticed this syntax which will simplify some table creation for me. However in some instances where I want to use it, I have a handy SELECT available, but I don't want to actually insert a row at that time. Testing with the shell, the following appears to work: attach database ':memory:' as mem; create table mem.messages as select * from main.messages limit 0; and I get a new, empty, table with the same columns. Is this supposed to work in this way? The info at http://www.sqlite.org/lang_createtable.html doesn't say one way or the other. I'd like to take advantage of this, but not if it is undefined behaviour that just happens to work. I don't see why that would be undefined behaviour. Adding WHERE 0 to the SELECT would also work. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On 10 Jul 2014 at 11:20, Dan Kennedy danielk1...@gmail.com wrote: On 07/10/2014 04:45 PM, Tim Streater wrote: I have just noticed this syntax which will simplify some table creation for me. However in some instances where I want to use it, I have a handy SELECT available, but I don't want to actually insert a row at that time. Testing with the shell, the following appears to work: attach database ':memory:' as mem; create table mem.messages as select * from main.messages limit 0; and I get a new, empty, table with the same columns. Is this supposed to work in this way? The info at http://www.sqlite.org/lang_createtable.html doesn't say one way or the other. I'd like to take advantage of this, but not if it is undefined behaviour that just happens to work. I don't see why that would be undefined behaviour. Adding WHERE 0 to the SELECT would also work. I don't mean that the LIMIT 0 might be undefined behaviour, but that a SELECT returning no rows used with CREATE TABLE, which then happens to do what I want, might be. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
Tim Streater wrote: I have a handy SELECT available, but I don't want to actually insert a row at that time. Testing with the shell, the following appears to work: attach database ':memory:' as mem; create table mem.messages as select * from main.messages limit 0; and I get a new, empty, table with the same columns. Is this supposed to work in this way? The documentation says: | The table has the same number of columns as the rows returned by the | SELECT statement. This is indeed misleading. The result set actually has columns and column names even when there are now rows in it, so this is guaranteed to work even for empty result sets. (The sqlite3 shell avoids displaying column names for empty result sets on purpose, whatever that purpose might be.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On 10 Jul 2014, at 11:47am, Clemens Ladisch clem...@ladisch.de wrote: This is indeed misleading. The result set actually has columns and column names even when there are now rows in it, so this is guaranteed to work even for empty result sets. However, all is not as you might expect: SQLite version 3.7.13 2012-07-17 17:46:21 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE TestA (a TEXT COLLATE NOCASE, b INTEGER, c REAL NOT NULL); sqlite .schema CREATE TABLE TestA (a TEXT COLLATE NOCASE, b INTEGER, c REAL NOT NULL); sqlite CREATE TABLE TestB AS SELECT *,35+5 FROM TestA; sqlite .schema CREATE TABLE TestA (a TEXT COLLATE NOCASE, b INTEGER, c REAL NOT NULL); CREATE TABLE TestB(a TEXT,b INT,c REAL,35+5); sqlite PRAGMA table_info(TestA); 0|a|TEXT|0||0 1|b|INTEGER|0||0 2|c|REAL|1||0 sqlite PRAGMA table_info(TestB); 0|a|TEXT|0||0 1|b|INT|0||0 2|c|REAL|0||0 3|35+5||0||0 sqlite INSERT INTO TestB (a,b,c) VALUES (8,8,8); sqlite INSERT INTO TestB VALUES (9,9,9,9); sqlite SELECT * FROM TestB; 8|8|8.0| 9|9|9.0|9 Note that although affinities are preserved when doing things this way, you lose COLLATE and constraint information, and that you can get some strange results if your SELECT is anything except SELECT *. In other words, this is good for preserving values, but not good for preserving other elements of a table definition. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
Hello Simon, You've just listed a lot of the concerns involved in the use of scroll-page-by-page. And there are no good answers to them. Thanks for the above. It means that I did my homework right. :-) Nor is it possible to tell SQLite to ... Sorry about that. I already got that feeling, but had to make sure (novice and all that). And nothing to be sorry about (although it would have been usefull in this case), just something I have to learn to work with (or rather, without). However, the whole question is almost obsolete. Users now scroll up and down displays so quickly and frequently that grabbing just one screen worth of data from a database is pointless. Yes, that was also a concern of mine. But although I already had several possible approaches to it (like a bit of caching and buttons scrolling more than a single page, possibly related to the size of the database) I did not want to concern myself and this forum with all of that at the same time. One step at a time keeps things simple. Similarly, users will frequently start a query with a small window, then make the window larger (fullscreen ?) which means it shows more rows. Already considered that, and found LVM_GETCOUNTPERPAGE to work quite well for it. So rather than the old-style page-by-page listing, I was also thinking in that direction. Get all rowIDs first and use them. In that regard, thanks for the rowid IN (line1rid,line2rid,line3rid,...) hint, that takes care of one of my concerns of having to send a query for each-and-every record in a page. But it might cause another problem: the database could get/be so large that the ammount of memory needed to store all the rowIDs in could well exeede the ammount of memory available to the program. I could ofcourse use (or at that moment switch over to) a local file (database!) to store them in, but somehow that feels a bit odd. At this point you care only about column values and you never need to use SQL to scroll around in a table, It also restores the use of the listviews own slider to move about in the list (do away with the page up, page down buttons). I must say I like that. Lastly, the whole of the above ignores systems where the user (or another user !) may insert or delete a row that was in your foundset in another window, while it's being shown I also thought of that, but wasn't prepared to think about the consequences (good or bad) before the preceeding problems where solved. My intended approach to it was to add a reload button/key (F5) for it. Thanks for the full (explanation, hints) reply. Remark: I've not seen any reference in your reply to my first question where I wondered if it would be possible to refer (in queries) to columns other than by their full names. Must I assume its not possible ? Regards, Rudy Wieser - Original Message - From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Wednesday, July 09, 2014 4:07 PM Subject: Re: [sqlite] Questions from a novice - basic browsing of records ina listview. On 9 Jul 2014, at 2:03pm, - mz2n6u7c.temp...@xs4all.nl wrote: 1) Is it possible to refer to the columns in a kind of shorthand (index perhaps) ? 2) Is it possible to have the SQLite engine initialize and remember certain WHERE and ORDER clauses (without creating another database please :-) ), so they can be used again-and-again (for the duration of a connection). 3) Is it possible, for the above 'scrolling cursor' method, to refer to a starting record other than by sending the exact data of such a record back to the SQLite engine ? You've just listed a lot of the concerns involved in the use of scroll-page-by-page. And there are no good answers to them. The convenience functions which would give you Where am I currently in this index ? don't exist. If you want to do it you have to roll your own. Nor is it possible to tell SQLite to preserve the temporary index it made up from your query terms (WHERE and ORDER) so you can reuse it. Sorry about that. However, the whole question is almost obsolete. Users now scroll up and down displays so quickly and frequently that grabbing just one screen worth of data from a database is pointless. Similarly, users will frequently start a query with a small window, then make the window larger (fullscreen ?) which means it shows more rows. So rather than the old-style page-by-page listing, with the programming which goes into scrolling, modern systems tend to use a different style which doesn't have some of the concerns you list. This involves storing and refetching different things as follows. For my example I will use the following example SELECT name,phonenumber FROM people WHERE phonenumber LIKE '01707%' ORDER BY name 1) When you need to open the window, collect which rows are returned. Execute SELECT rowid FROM people WHERE phonenumber LIKE '01707%' ORDER BY name and store the array
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
Hello Philip, How about using prepared statements in conjunction with bind? I also considered dat, and although it would certainly make the ammount of data send after the preparation smaller, it could still mean shi(t/p)loads of data being shutteled to-and-fro. And although I find the to quite acceptable (I need it to be able to display something :-) ), I do not think the same about the fro part (having to send the just-received data back again) Regards, Rudy Wieser - Original Message - From: Philip Bennefall phi...@blastbay.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Wednesday, July 09, 2014 3:11 PM Subject: Re: [sqlite] Questions from a novice - basic browsing of records ina listview. How about using prepared statements in conjunction with bind? http://www.sqlite.org/c3ref/bind_blob.html Kind regards, Philip Bennefall On 2014-07-09 15:03, - wrote: Hello all, I'm quite new at SQLite3, and have a bit of a problem with grasping the handling of a database. After having used the OFFSET and LIMIT 1 method (in conjuction with a userdata listview) and finding a past post into this forum describing it as a rookie mistake I'm now trying to implement the scrolling cursor method in that same post. It leads to a few questions though. For the above method to work for any database it means I need, for each-and-every next/previous page request, to send *all* the bottom/top records data back to the SQLite engine so it knows where to continue. Even when assuming the default maximum of columns the accumulated column names and related data for the WHERE clause could get quite big. Add to that a possible the SORT BY clause and I'm looking at quite a large query, which has to be created and transferred for every scroll. Which is something I do not really like ... 1) Is it possible to refer to the columns in a kind of shorthand (index perhaps) ? 2) Is it possible to have the SQLite engine initialize and remember certain WHERE and ORDER clauses (without creating another database please :-) ), so they can be used again-and-again (for the duration of a connection). 3) Is it possible, for the above 'scrolling cursor' method, to refer to a starting record other than by sending the exact data of such a record back to the SQLite engine ? Ofcourse, feel (very) free to include other things that I've not thought about and could be usefull. :-) Regards, Rudy Wieser ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
On 10 Jul 2014, at 12:54pm, - mz2n6u7c.temp...@xs4all.nl wrote: But it might cause another problem: the database could get/be so large that the ammount of memory needed to store all the rowIDs in could well exeede the ammount of memory available to the program. I could ofcourse use (or at that moment switch over to) a local file (database!) to store them in, but somehow that feels a bit odd. You could set a very big maximum (e.g. 5000 rows) on the assumption that users will never actually read or scroll through that many rows. Use LIMIT 5000 and if you actually get 5000 rows returned put up a messages telling them if the row they want doesn't appear they should be more specific in their query. Or some other cop-out. At this point you care only about column values and you never need to use SQL to scroll around in a table, It also restores the use of the listviews own slider to move about in the list (do away with the page up, page down buttons). I must say I like that. We're all still adapting to the changes that the excellent GUIs and ridiculous speed of today's computers require. I now have trivial little systems which reflect in realtime changes made by other users and in other windows, just because I needed to write those library routines for my 'big' systems. Lastly, the whole of the above ignores systems where the user (or another user !) may insert or delete a row that was in your foundset in another window, while it's being shown I also thought of that, but wasn't prepared to think about the consequences (good or bad) before the preceeding problems where solved. My intended approach to it was to add a reload button/key (F5) for it. You can deal with cases where the rowid no longer exists (as long as you do correctly test for it). But yes, spotting new rows is harder. Thanks for the full (explanation, hints) reply. The clarity of your question suggested that a long answer would be read and understood. Remark: I've not seen any reference in your reply to my first question where I wondered if it would be possible to refer (in queries) to columns other than by their full names. Must I assume its not possible ? Just that I didn't understand the question well enough to answer it and was hoping someone else did. It depends on what interface or shim you're using to access your database. Although several things about SQL syntax betray the fact that columns have an order (for example, you can do INSERT without specifying columns and the third value gets put in the third column) there's no language in SQL to say things like 'column 3' and most APIs don't supply it. On the other hand, if you were referring to the results of a SELECT, then results are always returned in the order you asked for them (apart from *), and you have to go to extra effort to find the names of the columns of the values that were returned. So all you have to do is remember what you asked for. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Basic SQLite/EF6 question
Steven Davisworth wrote: Hi Joe I've just upgraded PC to 64bit (new PC) and installed VS2013. I've followed standard install instructions as outlined in web posts I've come across for EF6. Is there an official, or at least known good, set of instructions somewhere that we can use rather than relying on various web posts (which may or may not be complete/valid/accurate, etc)? -Bill CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On 10 Jul 2014 at 12:45, Simon Slavin slav...@bigfraud.org wrote: On 10 Jul 2014, at 11:47am, Clemens Ladisch clem...@ladisch.de wrote: This is indeed misleading. The result set actually has columns and column names even when there are now rows in it, so this is guaranteed to work even for empty result sets. Thanks. I'll work along those lines then. However, all is not as you might expect: [snip] Note that although affinities are preserved when doing things this way, you lose COLLATE and constraint information, and that you can get some strange results if your SELECT is anything except SELECT *. In other words, this is good for preserving values, but not good for preserving other elements of a table definition. Righto - I'll keep it simple :-) What I'm actually doing is moving/copying a row from one database to another, where the two databases have identical schemas. At present the sequence will be something like (absid is integer primary key): attach database ':memory:' as mem; create table mem.messages as select * from main.messages where absid=some_value; update mem.messages set absid=null; attach database 'dest_db' as dst; insert into dst.messages select * from mem.messages; do something here to get the last insert id in dst.messages So there are two copy steps. What I'd like to do is: attach database 'dest_db' as dst; insert into dst.messages select * from main.messages; do something here to get the last insert id in dst.messages but unfortunately there could easily be a conflict in the absid values, so I have to be able to set it to null to get a new value generated. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
OP is in windows. Windows can send you display cache hints that tells you what page it intends to display next. I use these notification to load up a page worth of data at a time. Keep it in an internal cache. the ammount of memory needed to store all the rowIDs in could well exeede the ammount of memory available to the program. You're talking about 2 gigs in 32 bit windows and virtually unlimited in 64 bit windows. I'd say this is a non-issue. You get the list of RowID's then SetItemCount to tell the list control how big it is. Then the user just scrolls/pages up/down resizes as he will. Windows handles the rest. Windows sends you notifications about what data it needs to display and cache hints telling you what data it intends to display in the future. You can use the same technique in non-windows environments like Android. The details change but, the technique works there too. SS You could set a very big maximum (e.g. 5000 rows) Using the technique discussed here, keeping a list of rowid's to seed the virtual control, I've had virtual list controls with millions of lines of records. It's slowish when I get over about 500K but, functional. It's not really practical to scroll this data but, it works and the RAM usage isn't really over the top. I don't consider using a couple hundred megs for a list control to be unreasonable if that's what the application calls for. Most PC's have more RAM then they can ever use. I think you hit it on head when you suggest that most programmers are mired in the memory limited days. Even android phones can handle 1000's of records in a list control. If it's a list control with only 5000 records, you might be better off loading the entire thing into memory. For my usage, that's a really small list. SS On 10 Jul 2014, at 12:54pm, - mz2n6u7c.temp...@xs4all.nl wrote: But it might cause another problem: the database could get/be so large that the ammount of memory needed to store all the rowIDs in could well exeede the ammount of memory available to the program. I could ofcourse use (or at that moment switch over to) a local file (database!) to store them in, but somehow that feels a bit odd. SS You could set a very big maximum (e.g. 5000 rows) on the SS assumption that users will never actually read or scroll through SS that many rows. Use LIMIT 5000 and if you actually get 5000 rows SS returned put up a messages telling them if the row they want SS doesn't appear they should be more specific in their query. Or some other cop-out. At this point you care only about column values and you never need to use SQL to scroll around in a table, It also restores the use of the listviews own slider to move about in the list (do away with the page up, page down buttons). I must say I like that. SS We're all still adapting to the changes that the excellent GUIs SS and ridiculous speed of today's computers require. I now have SS trivial little systems which reflect in realtime changes made by SS other users and in other windows, just because I needed to write SS those library routines for my 'big' systems. Lastly, the whole of the above ignores systems where the user (or another user !) may insert or delete a row that was in your foundset in another window, while it's being shown I also thought of that, but wasn't prepared to think about the consequences (good or bad) before the preceeding problems where solved. My intended approach to it was to add a reload button/key (F5) for it. SS You can deal with cases where the rowid no longer exists (as long SS as you do correctly test for it). But yes, spotting new rows is harder. Thanks for the full (explanation, hints) reply. SS The clarity of your question suggested that a long answer would be read and understood. Remark: I've not seen any reference in your reply to my first question where I wondered if it would be possible to refer (in queries) to columns other than by their full names. Must I assume its not possible ? SS Just that I didn't understand the question well enough to answer SS it and was hoping someone else did. SS It depends on what interface or shim you're using to access your SS database. Although several things about SQL syntax betray the SS fact that columns have an order (for example, you can do INSERT SS without specifying columns and the third value gets put in the SS third column) there's no language in SQL to say things like SS 'column 3' and most APIs don't supply it. SS On the other hand, if you were referring to the results of a SS SELECT, then results are always returned in the order you asked SS for them (apart from *), and you have to go to extra effort to SS find the names of the columns of the values that were returned. SS So all you have to do is remember what you asked for. SS Simon. SS ___ SS sqlite-users mailing list SS sqlite-users@sqlite.org SS
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On Thu Jul 10, 2014 at 01:52:00PM +0100, Tim Streater wrote: What I'm actually doing is moving/copying a row from one database to another, where the two databases have identical schemas. ... So there are two copy steps. What I'd like to do is: ... but unfortunately there could easily be a conflict in the absid values, so I have to be able to set it to null to get a new value generated. You were almost there. You can do it in one go if you specify the columns exactly: INSERT INTO dst.messages( col1, col2, col3 ) SELECT col1, col2, col3 FROM main.messages WHERE absid = some_value ; And then retrieve the last rowid: SELECT last_insert_rowid(); Mark. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
Hello Clemens, Are you using a list view, or paging? The scrolling cursor method is appropriate only for the latter. I'm using a report-style listview. And to be honest, I have no idea what a paging component looks like (I'm using standard windows components). The easiest way to handle a list view would be to read the primary key of _all_ records in the correct order into your application. True. But as its my intention to create a generic SQLite3 viewer I have no idea if the computer its used on will have enough memory to store such a list, as the size of the database is unknown Only data in those columns that you are using for sorting. (But those must be a unique key for the records.) :-) As for a generic viewer I have no control over that it means I need to send *all* columns back, *in full*. (and yes, I see blobs creating a problem there). :-\ Otherwise I either can get stuck (=) or skip records () when the WHERE field contains more than a pages worth of the same data. Already ran into that 1) Is it possible to refer to the columns in a kind of shorthand (index perhaps) ? No. Thats (too) bad. Compiled statements can be reused (and the SQLite database drivers of many languages have a statement cache). How do I refer to a previously executed (and terminated) statement ? If that is not possible, how is that cache of use to whomever needs to repeat a query ? However, this is unlikely to be a bottleneck. Its not a bottleneck I'm worried about, it is having to cope with a system/method/environment which demands me to do/send the same thing every time I need something from it, or having to return data I just got from it. It just bellows inefficiency to me. No. But SQLite has no client/server communication overhead. I'm sorry, but I have no idea why you mention that overhead. The overhead I was thinking of is the one where the database has to re-find a record it has just found and send me the contents of. Again, inefficiency. Another overhead is my program having to keep track of (possibly large ammounts of) data, only so I can send it back (a standard listview only accepts upto, IIRC, 260 chars and discards the rest). What I was thinking about was something in the line of continue/start from rowID {ID}. Regards, Rudy Wieser - Original Message - From: Clemens Ladisch clem...@ladisch.de To: sqlite-users@sqlite.org Sent: Wednesday, July 09, 2014 4:15 PM Subject: Re: [sqlite] Questions from a novice - basic browsing of records ina listview. - wrote: After having used the OFFSET and LIMIT 1 method (in conjuction with a userdata listview) and finding a past post into this forum describing it as a rookie mistake I'm now trying to implement the scrolling cursor method in that same post. Are you using a list view, or paging? The scrolling cursor method is appropriate only for the latter. The easiest way to handle a list view would be to read the primary key of _all_ records in the correct order into your application. If the amount of data isn't too large, OFFSET/LIMIT works just fine. For the above method to work for any database it means I need, for each-and-every next/previous page request, to send *all* the bottom/top records data back to the SQLite engine so it knows where to continue. Only data in those columns that you are using for sorting. (But those must be a unique key for the records.) 1) Is it possible to refer to the columns in a kind of shorthand (index perhaps) ? No. 2) Is it possible to have the SQLite engine initialize and remember certain WHERE and ORDER clauses (without creating another database please :-) ), so they can be used again-and-again (for the duration of a connection). Compiled statements can be reused (and the SQLite database drivers of many languages have a statement cache). However, this is unlikely to be a bottleneck. 3) Is it possible, for the above 'scrolling cursor' method, to refer to a starting record other than by sending the exact data of such a record back to the SQLite engine ? No. But SQLite has no client/server communication overhead. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] pragma and prepare statement
Hi, We are using sqlite intensively in out developement and we discovered that apparently we cannot create a statement with a pragma Is there a reason why it is not possible? is it a bug or a per design regards, Bapt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I want to use CREATE TABLE ... AS SELECT ... - but what if the SELECT returns no rows?
On 10 Jul 2014 at 14:17, Mark Lawrence no...@null.net wrote: On Thu Jul 10, 2014 at 01:52:00PM +0100, Tim Streater wrote: What I'm actually doing is moving/copying a row from one database to another, where the two databases have identical schemas. ... So there are two copy steps. What I'd like to do is: ... but unfortunately there could easily be a conflict in the absid values, so I have to be able to set it to null to get a new value generated. You were almost there. You can do it in one go if you specify the columns exactly: INSERT INTO dst.messages (col1,col2,col3) SELECT col1,col2,col3 FROM main.messages WHERE absid = some_value; Sure, but then it's another place in the code where I have to enumerate the columns explicitly, a maintenance problem. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma and prepare statement
Baptiste Daroussin wrote: apparently we cannot create a statement with a pragma Is there a reason why it is not possible? This is likely to be a bug in your code (which you have not shown). (Unless you have compiled the library with SQLITE_OMIT_PRAGMA, but not knowing this would be bug in your development process.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
Hello Simon, You could set a very big maximum (e.g. 5000 rows) on the assumption that users will never actually read or scroll through that many rows. :-) In that case I would simply use a simple listview (a listview can handle upto 32000 records), and see if I can use its lazy data retrieval method (never used that method, but there is a first time for everything). Apart from the problems I would need to solve by *not* imposing some abitrary limit (and learn from that ofcourse), it was/is my intention to be able to fully browse a table no matter how long (upto the limits set by SQLite3, although those seem to be quite vague). It depends on what interface or shim you're using to access your database. I'm programming directly against the SQLite3 DLL, mostly using sqlite3_prepare. ... there's no language in SQL to say things like 'column 3' and most APIs don't supply it. Shucks! That means that I need to use the column names in their full glory, no matter how long they are. And as far as I can tell they can be *long* (upto 2 or 4 gigs?), and could, with a few columns easily exhaust the specified buffer size for a query (IIRC, 110 KByte). On the other hand, if you were referring to the results of a SELECT, then results are always returned in the order you asked for them Yeah, thats another funny thing. To be *sure* about the order of the columns, how the full record is sorted and from which record the rolling cursor should continue you're sending the same order of columns 3 times in one query ... (apart from *) Guess what: for a simple table query thats probably the most-used selection. :-( Currently I'm assuming that querying a table will keep the results in order of the columns in the table. Up until now that seems to be true. Man, trying to understand reasons the designers of the SQL language did certain things in a certain way gives me a headache. :-\ Regards, Rudy Wieser - Original Message - From: Simon Slavin slav...@bigfraud.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, July 10, 2014 2:17 PM Subject: Re: [sqlite] Questions from a novice - basic browsing of records ina listview. On 10 Jul 2014, at 12:54pm, - mz2n6u7c.temp...@xs4all.nl wrote: But it might cause another problem: the database could get/be so large that the ammount of memory needed to store all the rowIDs in could well exeede the ammount of memory available to the program. I could ofcourse use (or at that moment switch over to) a local file (database!) to store them in, but somehow that feels a bit odd. You could set a very big maximum (e.g. 5000 rows) on the assumption that users will never actually read or scroll through that many rows. Use LIMIT 5000 and if you actually get 5000 rows returned put up a messages telling them if the row they want doesn't appear they should be more specific in their query. Or some other cop-out. At this point you care only about column values and you never need to use SQL to scroll around in a table, It also restores the use of the listviews own slider to move about in the list (do away with the page up, page down buttons). I must say I like that. We're all still adapting to the changes that the excellent GUIs and ridiculous speed of today's computers require. I now have trivial little systems which reflect in realtime changes made by other users and in other windows, just because I needed to write those library routines for my 'big' systems. Lastly, the whole of the above ignores systems where the user (or another user !) may insert or delete a row that was in your foundset in another window, while it's being shown I also thought of that, but wasn't prepared to think about the consequences (good or bad) before the preceeding problems where solved. My intended approach to it was to add a reload button/key (F5) for it. You can deal with cases where the rowid no longer exists (as long as you do correctly test for it). But yes, spotting new rows is harder. Thanks for the full (explanation, hints) reply. The clarity of your question suggested that a long answer would be read and understood. Remark: I've not seen any reference in your reply to my first question where I wondered if it would be possible to refer (in queries) to columns other than by their full names. Must I assume its not possible ? Just that I didn't understand the question well enough to answer it and was hoping someone else did. It depends on what interface or shim you're using to access your database. Although several things about SQL syntax betray the fact that columns have an order (for example, you can do INSERT without specifying columns and the third value gets put in the third column) there's no language in SQL to say things like 'column 3' and most APIs don't supply it. On the other hand, if you were referring to the results of a SELECT, then results are always returned in the order
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
On 2014/07/10 16:04, - wrote: You could set a very big maximum (e.g. 5000 rows) on the assumption that users will never actually read or scroll through that many rows. :-) In that case I would simply use a simple listview (a listview can handle upto 32000 records), and see if I can use its lazy data retrieval method (never used that method, but there is a first time for everything). Apart from the problems I would need to solve by *not* imposing some abitrary limit (and learn from that ofcourse), it was/is my intention to be able to fully browse a table no matter how long (upto the limits set by SQLite3, although those seem to be quite vague). Hi Rudy, Firstly, while I understand the notion of not wanting arbitrary limits - this is just a silly notion when placed under scrutiny. What is enough? How long is a piece of string? The notion of no matter how long is just not feasible. What if the table has 10^16 or more items? (This is more than the amount of stars in the known universe, so it's probably not likely, but what if it is?) You would need a cray or something to even access a list like that. Limits are inherent and the best practice is to start out with a very specific limit-universe in mind. Further to this - the limits are very specific in SQLIte and not vague or fuzzy at all - though most all of them can be adjusted to suit folks like yourself who wish to push the limits. Lastly, more pertinent to the question - yes, if you have any kind of dataset/table which is larger than what is comfortable for the intended devices in a full-load scenario, the best (but not only) other solution is lazy-retrieval, which you already seem familiar with so I would advise to go with that. I have an example system if you like to see which can display insane amounts of data at lightning speed using just this sort of convention, but it still is limited to 2^63-1 items, a limit which approaches the total number of atoms on earth (~ 1.3 x 10^51) and as such is unlikely to ever span a dataset small enough to fit in physical memory of any size which are made from atoms available on earth - but it still is a limit. It still gets a bit sticky after about a billion items and more importantly, as Simon alluded to, it is silly to display any list which is so long that it cannot possibly be read by a human - what would be the purpose of that? And as other posters alluded to, human readability diminishes very long before the listing abilities of even mediocre modern systems. Shucks! That means that I need to use the column names in their full glory, no matter how long they are. And as far as I can tell they can be *long* (upto 2 or 4 gigs?), and could, with a few columns easily exhaust the specified buffer size for a query (IIRC, 110 KByte). It's ~2 giga-characters for a Unicode-enabled string. Anyone who makes column names that long has issues that can only be solved by a medical doctor, not an SQL engine. (and as such SQLite or any other engine does not really go to lengths to specifically cater for handling those length column names efficiently - the same reason they do not care to cater for saving 256-bit integers natively, even though those numbers are sometimes used by people). Do not confuse system design allowances with actual usage conventions. If you make an app that counts how many hamburgers one eats in a day, you do not need to make the display counter wide enough to fit 2^32 digits, even if that is the system design limit, I think we can safely assume no physical human of the kind that traverse the Earth will top a 4-digit number, even in America. Moral of the story: Pick a limit and build the system according to that. Yeah, thats another funny thing. To be *sure* about the order of the columns, how the full record is sorted and from which record the rolling cursor should continue you're sending the same order of columns 3 times in one query ... How is that a funny thing? The SQL Engine is under obligation to return EXACTLY what you ASK for, or return all results when you do not ask for a specific set. it does this, everytime without fail. It is under no obligation to return a specific form or format when you don't ask for any, and more importantly, this behaviour is not a silly design quirk or oversight of the SQL engine specification or the designers of any SQL engine, it is specifically required to avoid spending system resources on nonsense ordering and formatting and the like when in 99+ % of cases it is not needed at all by the reading construct. It has to tie up though, if you specified the columns in a specific order. This is WHY you specify the columns, and don't worry, it is all cached very nicely inside SQLite, there is negligible overhead for it. Guess what: for a simple table query thats probably the most-used selection. :-( Currently I'm assuming that querying a table will keep the results in order of the columns in the table. Up until now that
Re: [sqlite] pragma and prepare statement
On 07/10/2014 08:44 PM, Baptiste Daroussin wrote: Hi, We are using sqlite intensively in out developement and we discovered that apparently we cannot create a statement with a pragma Is there a reason why it is not possible? is it a bug or a per design The docs feature the following two points: * No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact. * Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language sqlite3_prepare() https://www.sqlite.org/c3ref/prepare.html, sqlite3_step() https://www.sqlite.org/c3ref/step.html, sqlite3_finalize() https://www.sqlite.org/c3ref/finalize.html API (or similar in a wrapper interface), the pragma may run during the sqlite3_prepare() https://www.sqlite.org/c3ref/prepare.html call, not during the sqlite3_step() https://www.sqlite.org/c3ref/step.html call as normal SQL statements do. Or the pragma might run during sqlite3_step() just like normal SQL statements. Whether or not the pragma runs during sqlite3_prepare() or sqlite3_step() depends on the pragma and on the specific release of SQLite. Do either of them explain what you are seeing? https://www.sqlite.org/pragma.html Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
Quick typo/fact check: ...// What if the table has 10^16 or more items? (This is more than the amount of stars in the known universe//... should of course read: ...// What if the table has 10^24 or more items? (This is more than the amount of stars in the observable universe//... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] v3.8.4.3 download
Dave Wellman wrote: Where can I download older versions of sqlite3, specifically v3.8.4.3 (or at least v3.8.4.x). I don't need the source code, just the windows binaries. It looks like we can hack the download URIs to get the version you want. Noting from the download page that the windows x86 binary is at http://www.sqlite.org/2014/sqlite-shell-win32-x86-3080500.zip we can try just altering the last few characters of that and get: http://www.sqlite.org/2014/sqlite-shell-win32-x86-3080403.zip which seems to work. An alternative is to download the source for your version right out of the version control system. Here is the version change history page: http://www.sqlite.org/changes.html You can use the SQLITE_SOURCE_ID from that history page and stick it into a URL like this: http://www.sqlite.org/src/info/SQLITE_SOURCE_ID e.g. in your case http://www.sqlite.org/src/info/a611fa96c4a848614efe899130359c9f6fb889c3 From there, you can click the ZIP Archive link to get the source code. I'll also need the docs. The docs don't get versioned like that AFAICT. There's just one central set of docs. If behavior changes from one version to the next, that is called out with text inside the documentation and in the change logs. Some of the more major changes have their own special pages in the docs. -- Eric A. Rubin-Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] v3.8.4.3 download
Eric Rubin-Smith wrote: The docs don't get versioned like that AFAICT. There's just one central set of docs. If behavior changes from one version to the next, that is called out with text inside the documentation and in the change logs. Some of the more major changes have their own special pages in the docs. Follow-up. Looks like I may have been wrong about that? Look at this link: http://www.sqlite.org/docsrc/taglist Poke around there and see if you get what you want. Don't know if the maintainers recommend using this or what, but in any case you can see the gory change history of the docs there if you want, including what they did along the path to releasing version 3.8.4.3. -- Eric A. Rubin-Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Basic SQLite/EF6 question
Steven Davisworth wrote: I've just upgraded PC to 64bit (new PC) and installed VS2013. I've followed standard install instructions as outlined in web posts I've come across for EF6. I've used Syatem.Data.SQLite.EF6 1.0.93.0 Windows (.NET Framework 4.5.1) and seem to be getting the same sorts of errors listed above. What are the specific errors? I did not see them when I did the testing of this scenario. Can you provide the System.Data.SQLite setup package logs? The log files should be located in the %TEMP% directory on the machine. They will have names similar to Setup Log 2012-12-18 #001.txt and Installer.exe.trace.tmp29.log. All posts I've come across seem to have the same problem. A heap load of suggestions that don't work. I've seen some of those same posts as well. However, it's important to note that people may not always report when they succeed at making it work... :) I've tried the GACing thing as well (contrary to what some posts say) Is this a common issue and will it be addressed soon? (Hope my question is not redundant but as I said loads of advice out there but non seem to work) In the official documentation for Entity Framework 6, it is unclear to me whether or not their providers need to be registered machine-wide. Clearly, the design-time components in Visual Studio need various kinds of machine-wide configuration, hence the confusion here (i.e. especially since the components are in completely different assemblies). -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Basic SQLite/EF6 question
Drago, William @ MWG - NARDAEAST wrote: Is there an official, or at least known good, set of instructions somewhere that we can use rather than relying on various web posts (which may or may not be complete/valid/accurate, etc)? I would have written such a document if I would have known about the potential for complications; however, it was supposed to be point, click, and install: 1. First install the appropriate System.Data.SQLite setup package to get the design-time components for the version of Visual Studio in use. 2. Then install the Entity Framework 6 package (via NuGet) into the target project). I am not aware of any other required steps. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Working with SQLite-Net in Framework 2.0
I have started some SQLite-Net development and it is progressing beautifully. I am using the version for .NET 4.0 installed with: sqlite-netFx40-setup-bundle-x86-2010-1.0.93.0.exe I now learn that one (and only one) of my deployment targets will be a machine using Windows 2000 as a dedicated controller for an industrial machine. I cannot install .NET 4.0 on that machine, but I can manage .NET 2.0 on Windows 2000. There is a possibility that I could upgrade the OS on the industrial machine to Win 7, but that is problematic because the manufacturers of the industrial machine want to sell a new computer and OS rather that to help their users convert from Win2000 to Win7 and they are not being all that helpful. I note that there are also a version of SQLite-Net for .NET 2.0 that can be installed with: sqlite-netFx20-setup-bundle-x86-2005-1.0.93.0.exe On my system, the .NET 40 version of the SQLite-Net installation installed on Windows 7 in: C:\Program Files (x86)\System.Data.SQLite\2010\bin My plan is to compile a special stripped-down version of my database application for that odd Windows 2000 installation (which only requires a very few parts of the entire application). That application will need to be compiled for a .NET 2.0 framework target. The majority of the application will continue to be developed using .NET 4.0. If I install the .NET 2.0 version of SQLite-Net on top of the .NET 4.0 version will that version install in different locations so that I can manage which version of the components I need to use? Can the two versions co-exist, or will there be problems? I really don't relish losing all the .NET 4.0 development and the loss of certain more modern features of C# by maintaining one common application compiled for .NET 2.0. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
Hello Ryan, What if the table has 10^16 or more items? Is that number within the limits as set by SQLite3 ? Than its my intention to handle it. *How* I would handle it is a whole other matter. The scrolling cursor method would be good for that, as it does only works with (very) small parts of the total database (just enough so a windows worth of data can be displayed). Ofcourse, that method has got it drawbacks too. Why do you think I asked my question in the first place ? :-) Limits are inherent and the best practice is to start out with a very specific limit-universe in mind. True. So, where can I find those limits in regard to sqlite3 ? The http://www.sqlite.org/limits.html page mentiones a few, but its very vague about *actual* limits. Further to this - the limits are very specific in SQLIte and not vague or fuzzy at all - Oh, there are some there, like a SQLITE_MAX_SQL_LENGTH of (max) a gig, and a SQLITE_MAX_COLUMN of (max) 32767. Together that would mean a maximum column-name length of about 32768 chars, but *that* limit is not mentioned anywhere, but probably is much larger. How much ? No idea, but I took the assumption that coulumn names can be as large as the data in such columns, which is, according to the above document, 2 gig. Mind you, just one (crazy long, but legal) column name would not even fit in a query. Also, the above SQLITE_MAX_COLUMN can be changed at *compile* time. What am I supposed to do with my regular API (max 2000 columns) when encountering a database made with such an altered API ? Will it even cause troubles at all ? *That* is what I mean with vague or fuzzy. most all of them can be adjusted to suit folks like yourself who wish to push the limits. I'm afraid you misunderstood. Its not my intention to push those limits, but I'll be damned if I let my intended basic database-browser refuse to display a legal table because *I* could not find a way to handle it. Maybe in the end I still have to conceede defeat and use a few arbitrary limits, taking the risk the program cannot handle every table. But not before I tried my d*mn best not to do let that happen. :-) yes, if you have any kind of dataset/table which is larger than what is comfortable for the intended devices in a full-load scenario, the best (but not only) other solution is lazy-retrieval, which you already seem familiar with Well, a kind of lazy retrieval is what I tried at first, using a virtual listview. Alas, the LIMIT/OFFSET wasn't the correct way. The rolling cursor method looks a *lot* better (no rowcount limit, very little actual data stored), but as you might have noticed, I'm a peeved off on the ammount of data I would need to shuttle to-and-fro (for the continue from this record clause). The full-load scenario sounds nice, but severely limits the size of the table that can be handled (assuming the rowID table will be stored in memory). and more importantly, as Simon alluded to, it is silly to display any list which is so long that it cannot possibly be read by a human Well, I wanted to start with browsing. Adding selections to limit the output (possibly also hiding interresting entries!) could come later. The idea behind that is that while browsing you might find stuff that looks interresting, something that could easily get hidden when limiting the output (using a LIKE clause) It's ~2 giga-characters for a Unicode-enabled string. Anyone who makes column names that long has issues that can only be solved by a medical doctor, not an SQL engine. Agreed. But it *is* possible, so a generic browser should be able to handle it (why did you think I was asking if there was a short-hand for colum-names is available). Do not confuse system design allowances with actual usage conventions. Is anyone bound to stay within those actual usage conventions ? If not than its meaningless to me, sorry. Moral of the story: Pick a limit and build the system according to that. And when a fully legal table gets rejected because of such arbitrary limits I would not really be content with myself (to put it lightly). Yeah, thats another funny thing. To be *sure* about the order of the columns, How is that a funny thing? Well, almost the first thing I learned (way back when) about databases is that duplicate data is *bad*. And now SQL queries look to be promoting it ... Man, trying to understand reasons the designers of the SQL language did certain things in a certain way gives me a headache. :-\ If the reasons are hard to comprehend, maybe it is a good thing that you are not tasked with making them. (smily face) Wholeheartedly agreed. The point the devs always make is that SQLite (or any other engine) is under no obligation to do it exactly like that in a next version, Which is why I'm attemting to do it the right way. ... Which than brought me in collision with vague limits. I hope this helps to alleviate your headaches slightly. Not
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
- wrote: Are you using a list view, or paging? The scrolling cursor method is appropriate only for the latter. I'm using a report-style listview. And to be honest, I have no idea what a paging component looks like (I'm using standard windows components). This would be a list without scroll bar but with previous/next page buttons; mostly used in HTML pages. (Without prev/next, you do not have the previous row whose values you could use as basis for fetching the next rows.) The easiest way to handle a list view would be to read the primary key of _all_ records in the correct order into your application. True. But as its my intention to create a generic SQLite3 viewer I have no idea if the computer its used on will have enough memory to store such a list, as the size of the database is unknown When you have a table with millions of rows, and the user happens to scroll to the 1234567th row, how do you get that row without knowing its rowid or primary key? And for huge tables, browsing does not make sense. Even if there were some interesting value in some row, you wouldn't be able to find it. What I was thinking about was something in the line of continue/start from rowID {ID}. This is possible (if the table was not declared with WITHOUT ROWID, and if there are no other columns named rowid, _rowid_, or oid). However, in a list view, you are not guaranteed to know the rowid to start from. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] trying to get things to work from the command line on windows 7 x64
Hey folks, I'm fresh off the boat with sqlite, but I'm an old-timer C programmer on Unix. I have to work on a window's 7 x64 for this project, and I also for the first time have to use SQL and I want to use SQL using C, gcc, and SQLITE. So I went to the website and downloaded: Directory of C:\jon\txt\sceptre\SQLite Download Page_files 140703 03:40 PM 698,103 sqlite-analyzer-win32-x86-3080500.zip 140703 03:40 PM 533,745 sqlite-dll-win64-x64-3080500.zip 140709 11:18 AM 4,446,454 sqlite-doc-3080500.zip 140703 03:41 PM 301,951 sqlite-shell-win32-x86-3080500.zip 140703 03:41 PM 5,452 sqlite370_banner.gif 5 File(s) 5,985,705 bytes 0 Dir(s) 27,665,055,744 bytes free these files, and ended up making a directory C:\sqlite on my c drive like so: Directory of C:\sqlite 140710 02:43 PM DIR . 140710 02:43 PM DIR .. 140710 02:43 PM 91,786 a.exe 140710 02:39 PM 162,722 libsqlite3.a 140710 02:38 PM 56 session.txt 140604 09:21 PM 124,070 shell.c 140710 02:01 PM DIR sqlite-amalgamation-3080500 140710 01:40 PM DIR sqlite-doc-3080500 140604 09:21 PM 5,239,373 sqlite3.c 140630 12:19 PM 4,721 sqlite3.def 140630 12:19 PM 1,238,016 sqlite3.dll 140604 09:22 PM 547,840 sqlite3.exe 140604 09:21 PM 360,297 sqlite3.h 140604 09:21 PM 26,110 sqlite3ext.h 140710 02:40 PM 682 t.lis 140710 02:42 PM 885 test.c 12 File(s) 7,796,558 bytes 4 Dir(s) 27,661,144,064 bytes free following the instructions here: http://www.tutorialspoint.com/sqlite/sqlite_installation.htm and then the sample project here: http://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm well I had all kinds of trouble getting through the linker, spend hours searching the web and finally found the command: dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3.a which seemed to make the library I needed which seems to be some big secret. anyway here is the test.c program I tried to run: //bof #include stdio.h #include sqlite3.h /\ Now, let's compile and run above program to create our database test.db in the current directory. You can change your path as per your requirement. $gcc test.c -l sqlite3 $./a.out Opened database successfully doskey \jon\bat\local dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3.a gcc test.c -L/sqlite -l sqlite3 doskey/history \/ int main(int argc, char* argv[]) { sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open(test.db, db); if( rc ){ fprintf(stderr, Can't open database: %s\n, sqlite3_errmsg(db)); return (0); }else{ fprintf(stderr, Opened database successfully\n); } sqlite3_close(db); } //main //eof Now, when I run a.exe, it crashes with an application was unable to start correctly (0xc07b) error. question 1) what am I doing wrong? so I keep searching the web and I find: http://www.sqlite.org/quickstart.html and they show this really nice program: 01 #include stdio.h 02 #include sqlite3.h 03 04 static int callback(void *NotUsed, int argc, char **argv, char **azColName){ 05int i; 06for(i=0; iargc; i++){ 07 printf(%s = %s\n, azColName[i], argv[i] ? argv[i] : NULL); 08} 09printf(\n); 10return 0; 11 } 12 13 int main(int argc, char **argv){ 14 sqlite3 *db; 15char *zErrMsg = 0; 16int rc; 17 18if( argc!=3 ){ 19 fprintf(stderr, Usage: %s DATABASE SQL-STATEMENT\n, argv[0]); 20 return(1); 21} 22rc = sqlite3_open(argv[1], db); 23if( rc ){ 24 fprintf(stderr, Can't open database: %s\n, sqlite3_errmsg(db)); 25 sqlite3_close(db); 26 return(1); 27} 28rc = sqlite3_exec(db, argv[2], callback, 0, zErrMsg); 29if( rc!=SQLITE_OK ){ 30 fprintf(stderr, SQL error: %s\n, zErrMsg); 31 sqlite3_free(zErrMsg); 32} 33 sqlite3_close(db); 34return 0; 35 } but don't follow through with how to compile and link it. I tried: C:\sqlitegcc test02.c -otest02.exe -L/sqlite -l sqlite3 test02.c:2:23: fatal error: sqlite3.h: No such file or directory #include sqlite3.h ^ compilation terminated. C:\sqlitewinelvis test02.c C:\sqlitegcc test02.c -otest02.exe -L/sqlite -l sqlite3 C:\sqlitetest02 C:\sqlite but it crashed just like a.exe. question 2) any help on getting this program to run? TIA, Jon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trying to get things to work from the command line on windows 7 x64
Update: ok this is working in cygwin, so maybe my GCC compiler on the windows side cmd.exe side is all higgly piggly? on windows cmd.exe whereis gcc shows gcc is at: \mingw\bin\gcc.exe whereis isn't in my current cygwin install. On Thursday, July 10, 2014 4:17 PM, Jonathan Leslie jlesli...@yahoo.com wrote: Hey folks, I'm fresh off the boat with sqlite, but I'm an old-timer C programmer on Unix. I have to work on a window's 7 x64 for this project, and I also for the first time have to use SQL and I want to use SQL using C, gcc, and SQLITE. So I went to the website and downloaded: Directory of C:\jon\txt\sceptre\SQLite Download Page_files 140703 03:40 PM 698,103 sqlite-analyzer-win32-x86-3080500.zip 140703 03:40 PM 533,745 sqlite-dll-win64-x64-3080500.zip 140709 11:18 AM 4,446,454 sqlite-doc-3080500.zip 140703 03:41 PM 301,951 sqlite-shell-win32-x86-3080500.zip 140703 03:41 PM 5,452 sqlite370_banner.gif 5 File(s) 5,985,705 bytes 0 Dir(s) 27,665,055,744 bytes free these files, and ended up making a directory C:\sqlite on my c drive like so: Directory of C:\sqlite 140710 02:43 PM DIR . 140710 02:43 PM DIR .. 140710 02:43 PM 91,786 a.exe 140710 02:39 PM 162,722 libsqlite3.a 140710 02:38 PM 56 session.txt 140604 09:21 PM 124,070 shell.c 140710 02:01 PM DIR sqlite-amalgamation-3080500 140710 01:40 PM DIR sqlite-doc-3080500 140604 09:21 PM 5,239,373 sqlite3.c 140630 12:19 PM 4,721 sqlite3.def 140630 12:19 PM 1,238,016 sqlite3.dll 140604 09:22 PM 547,840 sqlite3.exe 140604 09:21 PM 360,297 sqlite3.h 140604 09:21 PM 26,110 sqlite3ext.h 140710 02:40 PM 682 t.lis 140710 02:42 PM 885 test.c 12 File(s) 7,796,558 bytes 4 Dir(s) 27,661,144,064 bytes free following the instructions here: http://www.tutorialspoint.com/sqlite/sqlite_installation.htm and then the sample project here: http://www.tutorialspoint.com/sqlite/sqlite_c_cpp.htm well I had all kinds of trouble getting through the linker, spend hours searching the web and finally found the command: dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3.a which seemed to make the library I needed which seems to be some big secret. anyway here is the test.c program I tried to run: //bof #include stdio.h #include sqlite3.h /\ Now, let's compile and run above program to create our database test.db in the current directory. You can change your path as per your requirement. $gcc test.c -l sqlite3 $./a.out Opened database successfully doskey \jon\bat\local dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3.a gcc test.c -L/sqlite -l sqlite3 doskey/history \/ int main(int argc, char* argv[]) { sqlite3 *db; char *zErrMsg = 0; int rc; rc = sqlite3_open(test.db, db); if( rc ){ fprintf(stderr, Can't open database: %s\n, sqlite3_errmsg(db)); return (0); }else{ fprintf(stderr, Opened database successfully\n); } sqlite3_close(db); } //main //eof Now, when I run a.exe, it crashes with an application was unable to start correctly (0xc07b) error. question 1) what am I doing wrong? so I keep searching the web and I find: http://www.sqlite.org/quickstart.html and they show this really nice program: 01 #include stdio.h 02 #include sqlite3.h 03 04 static int callback(void *NotUsed, int argc, char **argv, char **azColName){ 05int i; 06for(i=0; iargc; i++){ 07 printf(%s = %s\n, azColName[i], argv[i] ? argv[i] : NULL); 08} 09printf(\n); 10return 0; 11 } 12 13 int main(int argc, char **argv){ 14 sqlite3 *db; 15char *zErrMsg = 0; 16int rc; 17 18if( argc!=3 ){ 19 fprintf(stderr, Usage: %s DATABASE SQL-STATEMENT\n, argv[0]); 20 return(1); 21} 22rc = sqlite3_open(argv[1], db); 23if( rc ){ 24 fprintf(stderr, Can't open database: %s\n, sqlite3_errmsg(db)); 25 sqlite3_close(db); 26 return(1); 27} 28rc = sqlite3_exec(db, argv[2], callback, 0, zErrMsg); 29if( rc!=SQLITE_OK ){ 30 fprintf(stderr, SQL error: %s\n, zErrMsg); 31 sqlite3_free(zErrMsg); 32} 33 sqlite3_close(db); 34return 0; 35 } but don't follow through with how to compile and link it. I tried: C:\sqlitegcc test02.c -otest02.exe -L/sqlite -l sqlite3 test02.c:2:23: fatal error: sqlite3.h: No such file or directory #include sqlite3.h ^ compilation terminated. C:\sqlitewinelvis test02.c C:\sqlitegcc test02.c -otest02.exe -L/sqlite -l sqlite3 C:\sqlitetest02 C:\sqlite but it crashed just like a.exe. question 2) any help on getting this program to run?
Re: [sqlite] trying to get things to work from the command line on windows 7 x64
On 7/10/2014 4:17 PM, Jonathan Leslie wrote: Now, when I run a.exe, it crashes with an application was unable to start correctly (0xc07b) error. sqlite3.dll must be in your PATH, or else in the same directory with the EXE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trying to get things to work from the command line on windows 7 x64
Igor, it is: C:\sqlitepath PATH=C:\Program Files\PlasticSCM\server;C:\Program Files\PlasticSCM\client;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\Wind owsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Micr osoft SQL Server\100\DTS\Binn\;C:\QNX650\host\win32\x86\usr\bin;C:\Program Files (x86)\QNX Software Systems\bin;C:\Program Files\TortoiseSVN\bin;C:\Program Files (x86)\Java\jre6\bin;C:\Program Files (x86)\Common Files\Roxio Shared\DLLShared\;C:\Program Files (x86)\Common Files\Roxio Shared\10.0\DLLShared\;C:\ Program Files (x86)\Pico Technology\PicoScope6\;C:\PROGRA~2\XRay;\sqlite;\mingw\bin;\jon\bat;C:\jon\programs\winelvis\;C:\Borland\BCC55\Bin;C:\cygwin\bin;C :\jon\programs\ctags58;c:\python27 and its in my current directory: Directory of C:\sqlite 140710 02:43 PM DIR . 140710 02:43 PM DIR .. 140710 02:43 PM 91,786 a.exe 140710 02:39 PM 162,722 libsqlite3.a 140710 02:38 PM 56 session.txt 140604 09:21 PM 124,070 shell.c 140710 02:01 PM DIR sqlite-amalgamation-3080500 140710 01:40 PM DIR sqlite-doc-3080500 140604 09:21 PM 5,239,373 sqlite3.c 140630 12:19 PM 4,721 sqlite3.def 140630 12:19 PM 1,238,016 sqlite3.dll 140604 09:22 PM 547,840 sqlite3.exe 140604 09:21 PM 360,297 sqlite3.h 140604 09:21 PM 26,110 sqlite3ext.h 140710 02:40 PM 682 t.lis 140710 02:42 PM 885 test.c 12 File(s) 7,796,558 bytes 4 Dir(s) 27,661,144,064 bytes free On Thursday, July 10, 2014 4:38 PM, Igor Tandetnik i...@tandetnik.org wrote: On 7/10/2014 4:17 PM, Jonathan Leslie wrote: Now, when I run a.exe, it crashes with an application was unable to start correctly (0xc07b) error. sqlite3.dll must be in your PATH, or else in the same directory with the EXE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trying to get things to work from the command line on windows 7 x64
2014-07-10 22:17 GMT+02:00 Jonathan Leslie jlesli...@yahoo.com: question 1) what am I doing wrong? Your compiler is 32-bit MinGW, but you unpacked the 64-bit dll in your current directory. Regards, Jan Nijtmans ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite memory testing
As promised last week, I set out to do testing of memory usage on SQLite DBs. A quick overview of the methods used follows (in short mostly as to avoid the boring bits): Create three tables, one main table storing 1 Int PK, 2 columns of random string data along with 2 Int keys that link the two sub tables. Each sub table has 1 Int PK index for lookup and 1 column of random string data. CREATE TABLE mt_main (id INTEGER PRIMARY KEY, fA TEXT, fB TEXT, iS1 INT, iS2 INT); CREATE TABLE mt_sub1 (id INTEGER PRIMARY KEY, fSA TEXT); CREATE TABLE mt_sub2 (id INTEGER PRIMARY KEY, fSB TEXT); Set the Page size to 4096 and Cache pages to 1,600,000. This would allow a theoretical cache limit of something like 6.4GB Next fill the two sub-tables each with 1-million entries of random strings (simply constructed from the hex(randomblob(1536)) notation of random byte-streams ~3KB in length (simply to stay under the 4KB Page size) Next fill the main table with a recurring set of product codes (just some 49,000 list of codes in varchar(32) format I had from another DB to facilitate some query filtering) in column fA and again random ~3KB strings as above for column fB, all of which iterated until the main db had 1 million entries too. (this means all 3 tables have integer id columns with matching spans, which allows some weird queries). This all took some time to populate and the resulting DB weighed in at just over 12GB, a bit more than I estimated (was thinking ~3GB for the main table and ~2.8GB for the sub tables from armchair calculations). While populating the tables memory usage climbed steadily topping out at about 1.1GB over the baseline. Now I set out to devise queries that would use/abuse maximum amount of system resources with an eye on determining the maximum amount of memory that can be put to use. I used as a first testbed (to find a common denominator) the 32-bit DLL for 3.8.5 downloadable from the sqlite site. (i.e. no fancy compiler switches) Some queries produced insanely big output files and took very long to run, others cranked up the memory usage somewhat, but I failed at first to devise any query that would push the memory profiler or the system resource monitor over about 850MB. Having Distinct queries run against multiple cross joins seems to provide the best memory-abuse. An order-by clause made a difference on non-distinct queries but did not really escalate the memory of distinct queries, small or large scale. It is interesting to note that GROUP BY made it both faster and using less memory, the latter seems intuitive but the former not so much - maybe someone else knows the reason for this. Some experimentation later, the following query seemed to do the trick and pushed the memory usage right to its limits: SELECT DISTINCT M.fA, S3.fA, substr(S1.fSA,1,32) AS SS, S2.id FROM mt_main AS M JOIN mt_sub1 AS S1 ON S1.id=M.id JOIN mt_sub2 AS S2 ON S2.id=M.id+1 JOIN mt_main AS S3 ON S3.id=S2.id ORDER BY SS, M.fA LIMIT 1000; (The limit clause doesn't matter in this type of query, the entire query has to be evaluated anyway, the limit simply inhibits large amounts of output processing which might influence running time) Baseline System-Memory usage before query is run: 2.77GB (which already included some usage from the test system). Memory Ramp: +/-15MB/sec flat-lining after a couple of minutes at: 4.64GB - That's just shy of 2.0GB worth of memory escalation, at which point the out-of-memory error popped up. Interestingly when I dialed the cache pages down to 800K (as opposed to 1.6M), the query took slightly longer (indicated by ramp) and created visible steps in the memory graph, and finished execution without memory errors being reported - and the results were correct. Also to note - the error was due to a return value from either sqlite_prepareV2() or the very first sqlite_step() call (I'd get more clarity but it was not important to me at the time which returned it, just that it did) I can only assume either sqlite itself was not able to index the cache or an internal api call to getmem() failed or such. It is worth noting that the error was specifically the graceful memory error and not a random exception, segfault or unknown. Next up, I proceeded to use the 64-bit DLL in a 64-bit build of the testbed and redid the DB and data population (just in case the DB itself was affected by 32-bitness, though unlikely since both DBs were equal in size and read correctly by both systems afterwards). Execution speeds were very similar, as were memory ramps. The query seemlessly executed, memory grew right up to the 6.4GB cache ceiling (above the baseline) without failing, without reporting an error and returned the correct result set. Almost an anti-climax, but nothing more to report really, it just worked. I increased the cache size to ~12GB but the next run topped out at just over 7GB before spitting out results and releasing the memory, so I
Re: [sqlite] SQLite memory testing
On Fri, Jul 11, 2014 at 12:23 AM, RSmith rsm...@rsweb.co.za wrote: Next up, I proceeded to use the 64-bit DLL in a 64-bit build of the testbed and redid the DB and data population (just in case the DB itself was affected by 32-bitness, though unlikely since both DBs were equal in size and read correctly by both systems afterwards). Execution speeds were very similar, as were memory ramps. The query seemlessly executed, memory grew right up to the 6.4GB cache ceiling (above the baseline) without failing, without reporting an error and returned the correct result set. Almost an anti-climax, but nothing more to report really, it just worked. I increased the cache size to ~12GB but the next run topped out at just over 7GB before spitting out results and releasing the memory, so I assume my query just did not require more than that. And yet they refuse to remove 'lite' from the name ;). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do. -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite memory testing
On 10 Jul 2014, at 11:23pm, RSmith rsm...@rsweb.co.za wrote: Special Omission: I was unable to devise a query or cache requirement larger than the machine's physical memory and thus unable to test it for function and error reporting - but I would assume when you plan to use insanely large cache values, some check to assert available system resources should be included. I once tested SQLite on a device which had a wide processor but limited memory. The testing was intended to ensure that errors in SQLite operations would lead to SQLite return codes rather than crashes in the program, which would have been a serious problem. My test programs were compiled using sqlite.h and sqlite.c as source files rather than an external library file. With two exceptions, when my operations (searches, creation of new indexes, etc.) required more memory than was available, I received an appropriate trappable error from SQLite itself. One exception was caused by a bug in SQLite which was reported and fixed a couple of years ago (actually it just disappeared because the whole routine was rewritten for another reason). In the other exception, the program crashed with an error from the operating system, something which should not happen. Repeated executions of the test program usually led to an OS-level crash, but not always the same error. The hardware technician I was working with and myself eventually decided that some part of the hardware we were using was faulty. Replacing the whole device with another whole device (the only option available to us) led to the expected trappable error from SQLite. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records in a listview.
What if the table has 10^16 or more items? Is that number within the limits as set by SQLite3 ? Than its my intention to handle it. No, I just made that up out of thin air. SQLite's maximum row limit is 2^63-1 I believe. It is unreachable on current physical media. Oh, there are some there, like a SQLITE_MAX_SQL_LENGTH of (max) a gig, and a SQLITE_MAX_COLUMN of (max) 32767. Together that would mean a maximum column-name length of about 32768 chars// No, it does not mean that at all. Your inference is not only wrong but also unneeded, meaning that you are imagining relationships where there are none. The limits for max sql length and max column are very clear, and in no way and by no virtue does it imply that the one informs the other. Why do you imagine that this is necessarily so? To be clear - when you go inside an elevator - you might see a weight limit stated as 500Kg / 13 Persons. This does not mean the limit per person is 500/13=38Kg at all, and there is no reason in the known universe to imagine that it does. (Good thing too cause I won't ever get to use the elevator). It does mean that even if you find an array of 20Kg children, you still cannot pack more than 13 in there, and if you have 5 really big (100Kg+) people hopping on ahead of you, best to wait for the next one. The limit statement is not fuzzy. ...//Also, the above SQLITE_MAX_COLUMN can be changed at *compile* time. What am I supposed to do with my regular API (max 2000 columns) when encountering a database made with such an altered API ? Will it even cause troubles at all ? *That* is what I mean with vague or fuzzy. If this was true it would be reason for concern - but the limit you refer to is a SQL language construct limit, not a table-width limit so the worry is not justified - but In a query one might be able to dream up a set of extracted columns that tops the query limit easily even if the underlying tables only sports a few columns. For this you will need to plan. The paradigm here is to cater for what is relevant. Very few people make queries longer than a few columns, but they might. It's a kind of bell curve, and if the users are those lying at the 6th+ standard deviation of column-count requirements, chances are they will have compiled their own sqlite version by now, and if they did not, ask them politely to adhere to whichever limit you picked. There is no SQLite-imposed hard limit (other than the physical), in stead, it supports the wide gamut of needs that cover the 99% bulk, and people with special needs roll their own (using the various compiler directives and the like). You don't even need to check this, SQLite will do it for you. Send a query with 101 columns, it will return a DUDE_THATS_TOO_MANY_COLUMNS result in stead, the (real) codes being available in the documentation. But if you like there is nothing wrong with setting your own maximum columns and doing your own checking, but the ability doesn't mean SQLite is soft in the head - just that it isn't restrictive and you have some freedom of design. most all of them can be adjusted to suit folks like yourself who wish to push the limits. I'm afraid you misunderstood. Its not my intention to push those limits, but I'll be damned if I let my intended basic database-browser refuse to display a legal table because *I* could not find a way to handle it. Not misunderstood, just a bit tongue-in-cheek, but the nuance probably misplaced, I apologise. Maybe in the end I still have to conceede defeat and use a few arbitrary limits, taking the risk the program cannot handle every table. But not before I tried my d*mn best not to do let that happen. :-) There is no such risk. SQLite will open each and every valid table in existence, and you can query it so long as the query itself conforms. In this regard you are quite justified to fear a table with 200 columns and you have a 99 col query limit, so you wont be able to ask for every column by name, though * will still work. One might say that 99 columns is more than any user might want to or be able to really look at... but if you disagree (and even I disagree, I think probably 200 is closer to a sensible human limit), then you might simply decide what it is that you feel would be the most anyone can useful observe in a query, say 500 if you like, or 1000, and make that your limit. And then, if ever a query needs more than those, split it into 2 queries. SQL DBA's are not as dumb as you might think, they are very used to working within limits. (Or maybe your intended user is of a different mindset, but that is up to you to figure out). Well, I wanted to start with browsing. Adding selections to limit the output (possibly also hiding interresting entries!) could come later. The idea behind that is that while browsing you might find stuff that looks interresting, something that could easily get hidden when limiting the output (using a LIKE clause) Ok, I'm not
Re: [sqlite] Working with SQLite-Net in Framework 2.0
Steve Rogers wrote: If I install the .NET 2.0 version of SQLite-Net on top of the .NET 4.0 version will that version install in different locations so that I can manage which version of the components I need to use? Why are you installing the System.Data.SQLite setup package instead of deploying application locally? Are you actually going to use the Visual Studio designer support? Can the two versions co-exist, or will there be problems? In theory, yes; however, that configuration has *NOT* been tested. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Working with SQLite-Net in Framework 2.0
On 7/10/2014 10:28 PM, Joe Mistachkin wrote: Steve Rogers wrote: If I install the .NET 2.0 version of SQLite-Net on top of the .NET 4.0 version will that version install in different locations so that I can manage which version of the components I need to use? Why are you installing the System.Data.SQLite setup package instead of deploying application locally? Are you actually going to use the Visual Studio designer support? I think you misinterpreted my question. For deployment I will only deploy the necessary DLL, and only the correct ones for the OS and its .NET Framework limitations. My question concerns only my development environment. This application is an inventory control program for pieces of laminated compositions that are left over at a computerized saw. The compositions have a top laminate and a bottom laminate and a core material ((thickness, plywood, particle board, Fiberex). The saw has the a Windows 2000 system. All the user of the saw needs to do is to input leftover scraps from the saw into the database. In engineering, where designs are made, they have a database of scraps that could save them a lot of buck if they could quickly locate s scrap that fits into an existing design so that a new 4x8 or 4x10 sheed of laminated material would not be needed for just a small pat. So all the correction of editing errors at the sae, and all usage of inventory takes place in engineeering where they have .Net 4.0 capable systems. So I just want to build one input screen for the saw in .NET 2.0 and the rest of the complex operation gets done in programs on the computers that run .NET 4.0. So my question is only a development machine question. Can the two versions co-exist, or will there be problems? In theory, yes; however, that configuration has *NOT* been tested. Can the two versions exist in a VS 2010 development environment on the same machine? I hope I have clarified that important detail. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - No virus found in this message. Checked by AVG - www.avg.com Version: 2014.0.4716 / Virus Database: 3986/7832 - Release Date: 07/10/14 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Working with SQLite-Net in Framework 2.0
Steve Rogers wrote: So I just want to build one input screen for the saw in .NET 2.0 and the rest of the complex operation gets done in programs on the computers that run .NET 4.0. So my question is only a development machine question. Ok. So, on the development machine, do you need to make use of the Visual Studio design-time components (e.g. the visual table designer, etc)? If not, there is [basically] no need to install the setup package. Can the two versions exist in a VS 2010 development environment on the same machine? I hope I have clarified that important detail. I'm not sure as I've never tested that setup. I do know that only the setup package for Visual Studio 2010 (which uses the .NET Framework 4) will allow it to actually make use of the design-time components for System.Data.SQLite. For your particular situation, quite a lot depends on whether or not you need the design-time support for SQLite. If you don't, you gain a lot more flexibility for testing and deployment (i.e. you can use the .NET Framework 2.0 version of System.Data.SQLite application locally with any version of the framework). It is somewhat more complex to accomplish this if you need design-time support. More details on the best practices for application local deployment can be found here: https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki On the above linked page, the Using Native Library Pre-Loading and Deployment Guidelines sections merit special attention. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users