Re: Re: [sqlite] sqlite3_open() fails on WinCE due to utf8ToUnicode / unicode
It really looks like this UTF-8 codepage is not avaiable. Is there any WinCE developer that uses SQLite newer than version 3.3.9 on this list? - Did you have similiar problems since the unicode conversion functions were changed? - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Igor, Why it's unpredictable? It's because of different sqlite versions or even in the same sqlite version it's unpredictable? If the latter is the case, I would like to know the internal of sqlite which makes it unpredictable. Richard, In sqlite 3.3.8, since it allows INSERT while SELECT statement is running, I assume that it will return an 11th row. Can you explain how step operation works interiorly on a table? (Does it gets all the results at one time and returns a single result during each sqlite3_step or it searches for the rows matching the criteria one after the other (i.e. in each sqlite3_step call it searches for the row from the current row onwards till the end of the table) ? Regards, Phani -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 06, 2007 5:41 PM To: SQLite Subject: [sqlite] Re: Does sqlite3_step searches for a row in the table / or some results buffer? B V, Phanisekhar [EMAIL PROTECTED] wrote: Assume a query select * from table Let there be 10 rows in the table Hence there will be 10 rows in the result corresponding to the above query. We can get all these 10 rows by calling sqlite3_step 10 times. Assume after 3 sqlite3_step calls, we insert a row into this table. Now after insertion we call sqlite3_step for the instruction select * from table. Will this newly added row also get returned in this case? It may or may not. In general, it's unpredictable. In this particular case, it will most likely appear at the end of enumeration, as an 11th row. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
B V, Phanisekhar [EMAIL PROTECTED] wrote: Why it's unpredictable? You are enumerating rows in some order. A new row may be inserted before or after your current position in this order. If it is inserted before, you will not see it in this enumeration. If it is inserted after, you will eventually reach it. With simple enough queries, you may guess (or explicitly specify wit ORDER BY) the order in which rows are enumerated, and can predict whether a newly inserted order will be seen. With complex queries, it may be difficult to make such a prediction. In sqlite 3.3.8, since it allows INSERT while SELECT statement is running, I assume that it will return an 11th row. Can you explain how step operation works interiorly on a table? It walks a B-Tree, moving from current node to the next. (Does it gets all the results at one time Not most of the time, but some queries require such a temporary intermediate resultset. E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records, sort them in a temporary resultset, then enumerate that resultset. In this case, by the way, a row inserted into the table in the middle of enumeration will not be seen. or it searches for the rows matching the criteria one after the other SQLite tries hard to do it this way, but for some queries it is not possible. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Thanks Igor, Why it's unpredictable? Why can't the unpredictable be made predictable? Assume I update the column of a row that meets the criteria of some select stmt and I am yet to encounter that row in sqlite3_step. So the sqlite3_step on that row will return the old column value or new column value for that row? E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records In case of complex queries for e.g. ORDER BY (as mentioned by you), I assume I will not be able to see the updated column value. Doesn't u think there is an inconsistency? Also, is there a better function to retrieve all (rows) of the results in one call? Sqlite3_get_table does that, but it uses internally sqlite3_step, which takes lot of time. So is there a way that I can get all the result rows in one step rather then stepping for each row. Regards, Phani -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 5:28 PM To: SQLite Subject: [sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer? B V, Phanisekhar [EMAIL PROTECTED] wrote: Why it's unpredictable? You are enumerating rows in some order. A new row may be inserted before or after your current position in this order. If it is inserted before, you will not see it in this enumeration. If it is inserted after, you will eventually reach it. With simple enough queries, you may guess (or explicitly specify wit ORDER BY) the order in which rows are enumerated, and can predict whether a newly inserted order will be seen. With complex queries, it may be difficult to make such a prediction. In sqlite 3.3.8, since it allows INSERT while SELECT statement is running, I assume that it will return an 11th row. Can you explain how step operation works interiorly on a table? It walks a B-Tree, moving from current node to the next. (Does it gets all the results at one time Not most of the time, but some queries require such a temporary intermediate resultset. E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records, sort them in a temporary resultset, then enumerate that resultset. In this case, by the way, a row inserted into the table in the middle of enumeration will not be seen. or it searches for the rows matching the criteria one after the other SQLite tries hard to do it this way, but for some queries it is not possible. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3_temp_directory in main.c
I can see your point, I guess, though I can't say that it seems like a major issue to me. Just out of curiosity, why aren't the defaults derived in os_win.x and os_unix.c sufficient? -Original Message- From: weiyang wang [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 06, 2007 11:17 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3_temp_directory in main.c the concern here is to configure the default temp directory in library compile time, on the OS porting layer. in current version, the default value of sqlite3_temp_directory is hardcoded as 0 in main.c for all platforms. would it be better to make it configrable for different platforms and use PRAGMA to overide the defult in runtime. i am looking forward to your opiniions. thanks again. wang On 6/6/07, Tom Briggs [EMAIL PROTECTED] wrote: Why not just use PRAGMA temp_store_directory, as the comments directly above that line suggest? -Original Message- From: weiyang wang [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 06, 2007 7:09 AM To: sqlite-users Subject: [sqlite] sqlite3_temp_directory in main.c hi, i found the following line in main.c char *sqlite3_temp_directory = 0; which seems to remove the possibilities that the customer platform could specify sqlite3_temp_directory. i suggest that this line is removed. any comments? thanks in advance. wang -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
B V, Phanisekhar [EMAIL PROTECTED] wrote: Why it's unpredictable? Why can't the unpredictable be made predictable? Please feel free to submit a patch, if you believe it's that easy. Assume I update the column of a row that meets the criteria of some select stmt and I am yet to encounter that row in sqlite3_step. So the sqlite3_step on that row will return the old column value or new column value for that row? It depends on whether the query reads live data from the table or uses a temporary resultset. E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records In case of complex queries for e.g. ORDER BY (as mentioned by you), I assume I will not be able to see the updated column value. Doesn't u think there is an inconsistency? That's why I said the outcome is unpredictable. Anyway, what are you driving at? What exactly is your suggestion? Also, is there a better function to retrieve all (rows) of the results in one call? Sqlite3_get_table does that, but it uses internally sqlite3_step, which takes lot of time. I, too, would like a database engine that always retrieves all rows in a resultset in the same small amount of time, regardless of how large the resultset is or how many rows must be looked at to calculate it. I also want a pony. However, I realize that I'm highly unlikely to ever get either, and go on with my life. Which I guess is a long way of saying that no, sadly there is no magical way to retrieve all rows in a resultset instantaneously. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Igor Tandetnik wrote: B V, Phanisekhar [EMAIL PROTECTED] wrote: Why it's unpredictable? Why can't the unpredictable be made predictable? Please feel free to submit a patch, if you believe it's that easy. Assume I update the column of a row that meets the criteria of some select stmt and I am yet to encounter that row in sqlite3_step. So the sqlite3_step on that row will return the old column value or new column value for that row? It depends on whether the query reads live data from the table or uses a temporary resultset. E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records In case of complex queries for e.g. ORDER BY (as mentioned by you), I assume I will not be able to see the updated column value. Doesn't u think there is an inconsistency? That's why I said the outcome is unpredictable. Anyway, what are you driving at? What exactly is your suggestion? Also, is there a better function to retrieve all (rows) of the results in one call? Sqlite3_get_table does that, but it uses internally sqlite3_step, which takes lot of time. I, too, would like a database engine that always retrieves all rows in a resultset in the same small amount of time, regardless of how large the resultset is or how many rows must be looked at to calculate it. I also want a pony. However, I realize that I'm highly unlikely to ever get either, and go on with my life. Which I guess is a long way of saying that no, sadly there is no magical way to retrieve all rows in a resultset instantaneously. Igor Tandetnik Igor, as soon as you achieve that goal, let us know. We need someone to bring peace to the Middle East and you would be the best qualified. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Predictability is ensured by using transactions. By using BEGIN and COMMIT to make transactions atomic you enforce a predictable state. B V, Phanisekhar wrote: Thanks Igor, Why it's unpredictable? Why can't the unpredictable be made predictable? Assume I update the column of a row that meets the criteria of some select stmt and I am yet to encounter that row in sqlite3_step. So the sqlite3_step on that row will return the old column value or new column value for that row? E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records In case of complex queries for e.g. ORDER BY (as mentioned by you), I assume I will not be able to see the updated column value. Doesn't u think there is an inconsistency? Also, is there a better function to retrieve all (rows) of the results in one call? Sqlite3_get_table does that, but it uses internally sqlite3_step, which takes lot of time. So is there a way that I can get all the result rows in one step rather then stepping for each row. Regards, Phani -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 5:28 PM To: SQLite Subject: [sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer? B V, Phanisekhar [EMAIL PROTECTED] wrote: Why it's unpredictable? You are enumerating rows in some order. A new row may be inserted before or after your current position in this order. If it is inserted before, you will not see it in this enumeration. If it is inserted after, you will eventually reach it. With simple enough queries, you may guess (or explicitly specify wit ORDER BY) the order in which rows are enumerated, and can predict whether a newly inserted order will be seen. With complex queries, it may be difficult to make such a prediction. In sqlite 3.3.8, since it allows INSERT while SELECT statement is running, I assume that it will return an 11th row. Can you explain how step operation works interiorly on a table? It walks a B-Tree, moving from current node to the next. (Does it gets all the results at one time Not most of the time, but some queries require such a temporary intermediate resultset. E.g. a query with ORDER BY on a table that doesn't have a suitable index has no choice but to retrieve all records, sort them in a temporary resultset, then enumerate that resultset. In this case, by the way, a row inserted into the table in the middle of enumeration will not be seen. or it searches for the rows matching the criteria one after the other SQLite tries hard to do it this way, but for some queries it is not possible. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] FTS-2
I have just started to use FTS2 and it is working well but I would like to ask any other users if they have had good or bad experiences and why they would use FTS2 rather than FTS1. The software is new and I have not seen any feedback at this stage and we are yet to apply large data sets and high volumes of transactions. We have developed some user functions to generate key strings and these might be of interest to other users. Basically they concanentate the columns of interest into a meta tag string then strip out noise words such as prepositions and punctuation, upshift the words and then update a column used for the FTS2 index. A complementary function similarly processes a string to provide a compatible MATCH string. Soundex versions of the words can be added. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS-2
On 6/7/07, John Stanton [EMAIL PROTECTED] wrote: I have just started to use FTS2 and it is working well but I would like to ask any other users if they have had good or bad experiences and why they would use FTS2 rather than FTS1. The software is new and I have not seen any feedback at this stage and we are yet to apply large data sets and high volumes of transactions. The entire fts subproject was only begun about a year ago, meaning that fts2 has existed for much longer than fts1 had when it was released - fts2 has certainly had more thorough testing at this point. I can't think of any good reason to use fts1 at this point. Admittedly, I haven't updated the wiki with this info. I'll put that on my list to get done. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
John Stanton [EMAIL PROTECTED] wrote: Predictability is ensured by using transactions. By using BEGIN and COMMIT to make transactions atomic you enforce a predictable state. Not if you modify the same data you are iterating over, on the same DB connection and thus within the same transaction. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DSN-less connection string
Greetings! I am trying to connect to a SQLite database from inside a C++ program (written in Visual Studio 6) using ADO without using a DSN. The database file is c:\program files\wincaps\trend01.trd. The library developed in house to wrap ADO calls contains an Open() method that accepts a string of the form DSN=SomeDSNName. The method prepends Provider=MSDASQL; to that and sends it to an ADO Connection object. I tried to pass in Data Source=c:\Program Files\WinCaps\Trend01.trd to it instead. I got an error complaining that the data source name is too long. So I copied the file into my C:\Misc folder and passed in Data Source=c:\Misc\Trend01.trd. The final connection string was Provider=MSDASQL;Data Source=c:\misc\trend01.trd. The Microsoft ODBC Manager complained that the data source was not found and no default driver was specified. I downloaded and installed the ADO.Net provider, but I did not see anywhere anything telling me what provider name to use with it. I can't use a DSN because the database to be opened must be selected by the user at run time. How do I do this? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS-2
Scott Hess wrote: On 6/7/07, John Stanton [EMAIL PROTECTED] wrote: I have just started to use FTS2 and it is working well but I would like to ask any other users if they have had good or bad experiences and why they would use FTS2 rather than FTS1. The software is new and I have not seen any feedback at this stage and we are yet to apply large data sets and high volumes of transactions. The entire fts subproject was only begun about a year ago, meaning that fts2 has existed for much longer than fts1 had when it was released - fts2 has certainly had more thorough testing at this point. I can't think of any good reason to use fts1 at this point. Admittedly, I haven't updated the wiki with this info. I'll put that on my list to get done. -scott Scott, Thankyou for the prompt response and for some elegant work in realizing FTS2. JS - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?
Igor Tandetnik wrote: John Stanton [EMAIL PROTECTED] wrote: Predictability is ensured by using transactions. By using BEGIN and COMMIT to make transactions atomic you enforce a predictable state. Not if you modify the same data you are iterating over, on the same DB connection and thus within the same transaction. Igor Tandetnik A good point to be aware of when designing for transactional integrity. Read locks are important. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Slow View Performance
I have Tables TabA, TabB and TabC I have a view ViewBC that is a view SELECT fields FROM TabB INNER JOIN TabC If I run SELECT fields FROM TabA LEFT OUTER JOIN ViewBC it is way slow (like ignoring all indexes) If I run SELECT fields FROM TabA LEFT OUTER JOIN TabB INNER JOIN TabC it runs as I expect. Is this a know issue? Is there a simple workaround? Matt Froncek QODBC Development Support / FLEXquarters.com LLC Consultant QODBC Driver for QuickBooks - Unleash your data at http://www.qodbc.com/ www.qodbc.com
RE: [sqlite] Slow View Performance
Matt, if looks like you have a cross join between your tables. Are the tables A, B, C related in some way? IF so, then your queries should look something like SELECT fields from TabA LEFT OUTER JOIN TabB where TabA.IDb = TabB.IDb And your view like SELECT fields FROM TabB INNER JOIN TabC where TabB.IDc = TabC.IDc This assumes that IDx is the related column Regards, Noah -Original Message- From: Matt [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 10:41 AM To: sqlite-users@sqlite.org Subject: [sqlite] Slow View Performance I have Tables TabA, TabB and TabC I have a view ViewBC that is a view SELECT fields FROM TabB INNER JOIN TabC If I run SELECT fields FROM TabA LEFT OUTER JOIN ViewBC it is way slow (like ignoring all indexes) If I run SELECT fields FROM TabA LEFT OUTER JOIN TabB INNER JOIN TabC it runs as I expect. Is this a know issue? Is there a simple workaround? Matt Froncek QODBC Development Support / FLEXquarters.com LLC Consultant QODBC Driver for QuickBooks - Unleash your data at http://www.qodbc.com/ www.qodbc.com CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Why do you use SQLite? Comments for an article needed
I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. Tim http://www.itwriting.com/blog - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Slow View Performance
Thanks Noah, Yes that is what I have is a cross join. I was trying to simplify the definition. A more correct definition is: TabA.ID1 TabA.ID2 TabA.field1 TabB.ID1 TabB.ID2 TabB.field2 TabC.ID1 TabC.field3 ViewBC: SELECT * FROM TabB INNER JOIN TabC On TabB.ID1 = TabC.ID1 This is slow: SELECT field1, field2, field3 from TabA LEFT OUTER JOIN ViewBC ON TabA.ID1 = ViewBC.ID1 AND TabA.ID2 = ViewBC.ID2 This is fast: SELECT field1, field2, field3 from TabA LEFT OUTER JOIN TabB ON TabA.ID1 = TabB.ID1 AND TabA.ID2 = TabB.ID2 INNER JOIN TabC ON TabB.ID1 = TabC.ID1 Any insight would help as I am bailing on Views and have a major programming change to do so. Thanks, Matt -Original Message- From: Noah Hart [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 10:45 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Slow View Performance Matt, if looks like you have a cross join between your tables. Are the tables A, B, C related in some way? IF so, then your queries should look something like SELECT fields from TabA LEFT OUTER JOIN TabB where TabA.IDb = TabB.IDb And your view like SELECT fields FROM TabB INNER JOIN TabC where TabB.IDc = TabC.IDc This assumes that IDx is the related column Regards, Noah -Original Message- From: Matt [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 10:41 AM To: sqlite-users@sqlite.org Subject: [sqlite] Slow View Performance I have Tables TabA, TabB and TabC I have a view ViewBC that is a view SELECT fields FROM TabB INNER JOIN TabC If I run SELECT fields FROM TabA LEFT OUTER JOIN ViewBC it is way slow (like ignoring all indexes) If I run SELECT fields FROM TabA LEFT OUTER JOIN TabB INNER JOIN TabC it runs as I expect. Is this a know issue? Is there a simple workaround? Matt Froncek QODBC Development Support / FLEXquarters.com LLC Consultant QODBC Driver for QuickBooks - Unleash your data at http://www.qodbc.com/ www.qodbc.com CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Why do you use SQLite? Comments for an article needed
I am using SQLite because it is easy to use (zero config). To add to your project. And it was well designed by Mr. Hipp, with a really to use API. A wild SQL support. Because there is not Client-Server, and thousands times better than access and foxpro Many people uses, so there is a lot of help. A lot wrappers for C++, PHP, Python, Ruby on rails, and others. Because it is done in C, it will have a great performance A proof of the power of SQLite is that is used in Google Gear Project. On 07/06/07, Tim Anderson [EMAIL PROTECTED] wrote: I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. Tim http://www.itwriting.com/blog - To unsubscribe, send email to [EMAIL PROTECTED] - -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] Why do you use SQLite? Comments for an article needed
Hi, Tim. We are using SQLite for two main reasons: - no daemon needed: to use RDBMS on a cluster machine is quite annoying. Most clusters administrators does not want more daemons running. - SQLite can be very fast when you tweak some of its basic configuration pragmas, being more than 200% faster than MySQL or Postgres on batch insert operations. Hope this can at least give you some ideas for your article. Good luck Alberto On 6/7/07, Tim Anderson [EMAIL PROTECTED] wrote: I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. Tim http://www.itwriting.com/blog - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Why do you use SQLite? Comments for an article needed
I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? - no restrictions, like only 1 LONG VARCHAR in the table, numer of columns, maximum network packet size and similar (actually there are some restrictions regarding sizes, but with resonable max values) - fast - open source - free Is the open source aspect important? Very. I don't work with orygnal SQLite, but with customized version. For that matter, anything you really don't like about SQLite? - no full support for ALTER TABLE and JOIN - no strict typing and missing date type - no high concurrency support Wiktor Adamski -- Wicie, rozumicie Zobacz http://link.interia.pl/f1a74 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Can't update table from itself or multiple tables
Hi, Trey. I checked not implemented features list of sqlite and found nothing about update ... from. There's no FROM on http://www.sqlite.org/lang_update.html, so that's a hint that it's not supported. All the same, maybe this should be added to http://www.sqlite.org/omitted.html update table1 set val = ss.v from (select t2.some as v, t1.id as id from table1 t1, table2 t2 where t1.id = t2.nid) as ss where ss.id = table1.id How about update table1 set val = (select some from table2 where table1.id = table2.nid ); - TMack It works. Thanx. Sometimes this approach it's too slow, sometimes it doesn't works but I can go ahead now :). Hello, I wish to propose another statement, though it does not look so attractive. But sure it is fast: insert or replace into table1 (rowid, id, val2, val) select t1.rowid, t1.id, t1.val2, t2.some from table1 t1 left outer join table2 t2 on t2.id = t1.nid; This is equivalent to the update statement, assuming: 1. table1 has no primary key (so rowid is used) 2. table1 has no further columns as the ones mentioned (id, val and val2) The outer join is added fot complete equivalence. It arranges that val gets assigned null where no matching row is found in table2. If that is not desirable, this can be omitted to leave singular rows unchanged. Hope this is useful, Ed Pasma - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] custom collation functions not called for integer columns
It appears that I can *NOT* create a custom collation function for a column declared as an INTEGER. Well, I can create it, and prepare a statement to use it, but my custom function is *NEVER* called. Is this by design? The exact same code works for a TEXT column. Should the sqlite3_prepare() call return an error if I try to use a custom COLLATE on an INTEGER column? If it's by design, perhaps it could be spelled out a bit better in the documentation. Thanks. -Shane
Re: [sqlite] Why do you use SQLite? Comments for an article needed
Tim Anderson wrote: I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. We are working on a project for the Census Bureau and needed an embeddable database that was zero configuration for the user and fast. We evaluated SQLite against numerous competitors and it came out on top for the following reasons: 1. No licensing costs. The application will be widely distributed at no charge to the user and therefore must be zero or minimal cost per license. 2. Active support community. 3. Mature library 4. Source readily available 5. Zero config. One of our requirements is to ensure minimal burden to our users. 6. Fast. We have some requirements tied to performance, and SQLite met all of them handily. John Elrick Software Designer/Developer Fenestra Technologies - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Why do you use SQLite? Comments for an article needed
John Elrick wrote: Tim Anderson wrote: I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. I'm mainly using it for development testing. It's really easy to migrate to other systems once you reach production stage. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Why do you use SQLite? Comments for an article needed
We chose SQLite for many reasons: - zero configuration/installation - availability of wonderful ADO.NET 2.0 wrapper - easily extensible with custom functions - performance (4x faster than MSSQL in our tests) - available commercial support The only thing we don't like is the lack non-standard loose data type behavior and lack of many data types (particularly dates). Best regards, Samuel Neff Sr Software Engineer B-Line Medical http://www.blinemedical.com --- 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: Tim Anderson [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 1:50 PM To: sqlite-users@sqlite.org Subject: [sqlite] Why do you use SQLite? Comments for an article needed I'm writing an article about SQLite and I'd love to get some comments from users about why you use it. Performance? Features? Reliability? Cost? Is the open source aspect important? Anything else? For that matter, anything you really don't like about SQLite? You can email me at tim(at)itwriting.com or comment here if you prefer - but to use your quote I'd need at least a full name, what you do and the company you work for Thanks in advance for your help. Tim http://www.itwriting.com/blog - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Truncate Issue
Hi all, I am working in a porting project of SQLite from windows. I Could not port the Truncate () function (For Eg: WinTruncate () in windows code in the file os_win.c ) as the same logic cannot be applied to my platform. Can I make it a dummy function by just returning SQLITE_OK? Please help. Regards Jimmy Mathew
Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser
You don't seem to be positioning on a row in the Parameter table with a WHERE clause. Ellis Robin (Bundaberg) wrote: Could I please get some help on the syntax required to perform my UPDATE based on a selection from multiple tables? I've been through the archives but can't seem to make much sense of the examples given. I have a table containing 'new' paramater values, I need to update the relevant records in the 'existing' parameter table, however my selection is based on a handful of tables. Here's the selection I run to view my old and new parameters side by side, this works fine: select Scenario.ScenarioID, Scenario.NetworkID, Link.LinkID, Catchment.CatchmentID,Catchment.Name, FunctionalUnit.FunctionalUnitID, FunctionalUnitDefinition.Name, StandardFU.RainfallRunoffModelID, Parameter.Parameter, Parameter.ParameterValue, NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter However when I use this selection to update the relevent field in the parameters table I don't get a 'correct' update, instead I get the first value encountered in the NewParams table over writing ALL of the old parameter values. Here's my syntax: Update Parameter set ParameterValue = (select NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) Can anyone shed some light on this for me? I've tried adding more WHERE statements after the nested selection, but with no better results. Thanks Rob Robin Ellis Natural Resources Water PO Box 1167 Bundaberg QLD 4670 Ph: +617 4131 5771 Fax: +617 4131 5823 The information in this email together with any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any form of review, disclosure, modification, distribution and/or publication of this email message is prohibited, unless as a necessary part of Departmental business. If you have received this message in error, you are asked to inform the sender as quickly as possible and delete this message and any copies of this message from your computer and/or your computer system network. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Syntax help with UPDATE in SQLite Database Browser
Thanks John, that's what I thought too... My positioning on a row is dependent on 2 values, and without any better knowledge of SQLite I've tried implementing this: Update Parameter set ParameterValue = (select NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) Where Parameter.ModelID = (select Parameter.ModelID from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) and Parameter.Parameter = (select Parameter.Parameter from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) However my results are the same with this attempt at row positioning. Both of the select statements used in the WHERE expression do retturn valid records. Any more hints? Rob -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, 8 June 2007 2:48 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser You don't seem to be positioning on a row in the Parameter table with a WHERE clause. Ellis Robin (Bundaberg) wrote: Could I please get some help on the syntax required to perform my UPDATE based on a selection from multiple tables? I've been through the archives but can't seem to make much sense of the examples given. I have a table containing 'new' paramater values, I need to update the relevant records in the 'existing' parameter table, however my selection is based on a handful of tables. Here's the selection I run to view my old and new parameters side by side, this works fine: select Scenario.ScenarioID, Scenario.NetworkID, Link.LinkID, Catchment.CatchmentID,Catchment.Name, FunctionalUnit.FunctionalUnitID, FunctionalUnitDefinition.Name, StandardFU.RainfallRunoffModelID, Parameter.Parameter, Parameter.ParameterValue, NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter However when I use this selection to update the relevent field in the parameters table I don't get a 'correct' update, instead I get the first value encountered in the NewParams table over writing ALL of the old parameter values. Here's my syntax: Update Parameter set ParameterValue = (select NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
Re: [sqlite] Truncate Issue
If you don't truncate a file then you may have untruncated files. Why can't you truncate a file? It is implemented one way or another on pretty much every OS. Sqlite uses truncate in it b-tree logic and probably elsewhere so you would very likely encounter problems with no truncate. Jimmy Mathew Ambalathuruthel wrote: Hi all, I am working in a porting project of SQLite from windows. I Could not port the Truncate () function (For Eg: WinTruncate () in windows code in the file os_win.c ) as the same logic cannot be applied to my platform. Can I make it a dummy function by just returning SQLITE_OK? Please help. Regards Jimmy Mathew - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Syntax help with UPDATE in SQLite Database Browser
Thanks Miguel, I fear you're right. For this infrequent update process it just may just be easier to throw the data into another db format. Thanks again Rob -Original Message- From: miguel manese [mailto:[EMAIL PROTECTED] Sent: Friday, 8 June 2007 3:31 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser Maybe you really have to loop outside sqlite to align the rows values. From the result you got and the UPDATE documentation, I can guess that the subselect in the assignment is flattened to a scalar. Unfortunately sqlite does not have something like update tbl1 set col=tbl2.col from tbl2 where tbl2.id=tbl1.id which would have been what you needed. Cheers, M. Manese On 6/8/07, Ellis Robin (Bundaberg) [EMAIL PROTECTED] wrote: Thanks John, that's what I thought too... My positioning on a row is dependent on 2 values, and without any better knowledge of SQLite I've tried implementing this: Update Parameter set ParameterValue = (select NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) Where Parameter.ModelID = (select Parameter.ModelID from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) and Parameter.Parameter = (select Parameter.Parameter from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) However my results are the same with this attempt at row positioning. Both of the select statements used in the WHERE expression do retturn valid records. Any more hints? Rob - To unsubscribe, send email to [EMAIL PROTECTED] - The information in this email together with any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any form of review, disclosure, modification, distribution and/or publication of this email message is prohibited, unless as a necessary part of Departmental business. If you have received this message in error, you are asked to inform the sender as quickly as possible and delete this message and any copies of this message from your computer and/or your computer system network. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser
Maybe you really have to loop outside sqlite to align the rows values. From the result you got and the UPDATE documentation, I can guess that the subselect in the assignment is flattened to a scalar. Unfortunately sqlite does not have something like update tbl1 set col=tbl2.col from tbl2 where tbl2.id=tbl1.id which would have been what you needed. Cheers, M. Manese On 6/8/07, Ellis Robin (Bundaberg) [EMAIL PROTECTED] wrote: Thanks John, that's what I thought too... My positioning on a row is dependent on 2 values, and without any better knowledge of SQLite I've tried implementing this: Update Parameter set ParameterValue = (select NewParams.parametervalue from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) Where Parameter.ModelID = (select Parameter.ModelID from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) and Parameter.Parameter = (select Parameter.Parameter from Scenario, Link, Catchment, FunctionalUnit, FunctionalUnitDefinition, StandardFU, Parameter, NewParams where Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID = Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID = FunctionalUnitDefinition.FunctionalUnitDefinitionID and FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name = NewParams.Subcatchments and FunctionalUnitDefinition.Name = NewParams.FU_name and Parameter.Parameter = NewParams.parameter) However my results are the same with this attempt at row positioning. Both of the select statements used in the WHERE expression do retturn valid records. Any more hints? Rob - To unsubscribe, send email to [EMAIL PROTECTED] -
