[sqlite] WxDevCpp and Sqlite 3.4.1
Hi all, Under linux I've no problems with g++ I've compiled sqlite 3.4.1 and created a makefile with the parameters (include,library) where I compile the main.cpp and all is right. I want to compile the same file main.cpp under WxDevCpp but I don't know how setup sqlite 3.4.1. Under linux I've the library libsqlite3.so (generated by the compilation of sqlite) but under windows I don't know how to setup WxDevCpp with sqlite 3.4.1. main.cpp is simple (shell) program that create the db file and populate it with a simple insert. Any help, links, suggests are appreciated. -- Massimiliano Marini - http://www.linuxtime.it/massimilianomarini/ "It's easier to invent the future than to predict it." -- Alan Kay
RE: [sqlite] File Permission and Busy Handler Issue in SQLite
All, Let me confess that the topic has changed a bit from "how to solve" to "why should this occur" scenario. Now suppose my Linux system already has a DB with "444" permission (say I copied from another Linux machine) and tried running my application, then I want to trap the read-only (unable to write) scenario. This is one of the few scenarios I can think of. Like this there might be N number of scenarios wherein we may encounter some sort of issue with DB. So my (might I say holistic view) question is if sqlite3_exec is unable to differentiate a busy_handler trigger due to permission other related issues and DB lock, won't we get ourselves into some mis-leading (ambiguity) scenario. Regards, Kiran -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 8:00 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] File Permission and Busy Handler Issue in SQLite What is the goal of changing permissions? Kiran <[EMAIL PROTECTED]> wrote: The interesting part of the scenario is, when you change the file permission using chmod 000 bulk.db, there is no effect on the process of writing (with sqlite3_exec and busy handler combo) however the same does not work for the chmod 444 bulk.db. This make me suspect that there is some point which I am missing or may be it is a defect in SQLite. Anyways thanks for effort Joe -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 6:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] File Permission and Busy Handler Issue in SQLite It is not reasonable to change a database file to read-only in the middle of a transaction. I suppose sqlite could be changed to detect it, but why bother? By the same logic, if another process wrote random bytes in the middle of an sqlite database file, should sqlite be responsible for recovering from that as well? --- Kiran wrote: > I am currently using SQLite in my application as in Multi-process > environment. I.e. my application will contain multi-process which will > handle add/delete request to a single db - (bulk.db). I am using the > busy_handler concept (with 5ms delay) for locking issues. > > However, during testing, when 5 processes is up and running, I by chance > changed the file permission of bulk.db (chmod 444 bulk.db) to read only. Now > I see that the sqlite_exe is not able to trap the read-only mode error of > the database (while it tries to add records) and is just returning a > busy_handler and thus in loop. > > Bottom line, if you are using busy_handler and if DB file permission is > changed to read-only (manually) instead of throwing an error, SQLite will > continue triggering busy_handler. Thus not able to handle this situation. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] New User: Creating/Modifying tables
I created my first Delphi program with DISQLite3 1.40 and I used the Importer component to create the table and I imported a few of the rows from MySQL 5. The problem is the dates in the SQLite table show up as floating point, which is how they are natively stored. 1) How can I determine what the date is with SQLiteSpy? I need the dates formatted as '2006-05-11'. Do I have to write a program every time I 2) How do I create/alter tables with a GUI? I can't find that option in SQLiteSpy or SQLite3 Mgr. TIA Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] In-Memory
from sqlite3 import dbapi2 as sqlite
connection = sqlite.connect(':memory:')
cursor.execute(' . . . sql statements . . .')
cursor.close()
Whatever sql operations you do will be in memory. Once you exit the
applcation, DB will be lost
Uma Krishnan <[EMAIL PROTECTED]>
08/08/07 10:44 PM
Please respond to
sqlite-users@sqlite.org
To
sqlite-users@sqlite.org
cc
Subject
RE: [sqlite] In-Memory
Hello,
Has anyone used SQLite as an In-Memory implementation. If so would you
like to share your ideas?
Thanks in advance
- Uma
***
This e-mail and any attached documents may contain confidential or proprietary
information.
If you are not the intended recipient, please advise the sender immediately and
delete this
e-mail and all attached documents from your computer system. Any unauthorized
disclosure,
distribution or copying hereof is prohibited.
***
Re: [sqlite] UNION?
would have been nice to see the double parentheses in your example ... I have seen MS Access as well being overly parenthesized, what does the SQL92 standard say about that? Jim Lee Crain wrote: Richard, Thanks for suggesting it but no, I don't think invisible control characters are the problem. I have copied and pasted the query from my source code into a MS SQL Server Management Studio interface and executed it against a SQL Server mockup of our SQLite database. It works perfectly. I experimented and tried some variations on the query. ___ This query does not work in SQLite but works in MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) ...> UNION ...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) ...> ORDER BY Items.name ASC; SQL error: near "UNION": syntax error sqlite> However, this query works in both SQLite and MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> (SELECT related_item FROM RelatedItems WHERE item = 1777 ...> UNION ...> SELECT item FROM RelatedItems WHERE related_item = 1777) ...> ORDER BY Items.name ASC; 1706|Arizona Character|T 1707|Arizona Clothing and Props|T 1660|Arizona Hair|T 2325|Bonnie V3 Teen|T 1425|Isabella for Stephanie 3|T 1918|Little Darling for V3/SP|T 106|Rose Character|T 1778|Teresa Hair|T sqlite> Further experimentation showed that the extra pair of parentheses in the first query (around each SELECT statement) caused the syntax error. Thanks for your response, Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 3:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? "Lee Crain" <[EMAIL PROTECTED]> wrote: The query at the bottom of this email is failing on the word "UNION". (The query works correctly in MS SQL Server.) Works when I try it Do you think you might have some invisible control characters or something in the middle of the SQL? __ SELECT Items.items_idx, Items.name, Items.active FROM Items WHERE active = 'T' AND Items.items_idx IN (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 UNION SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) ORDER BY Items.name ASC; -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Is SQLite Case Sensitive?
On 08/08/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Lee Crain <[EMAIL PROTECTED]> wrote: > > Would a search for an 'a' return a different result than a search for > > an 'A'? > > By default, yes. > > > SELECT * FROM table WHERE field1 = 'a'; > > > > Vs. > > > > SELECT * FROM table WHERE field1 = 'A'; > > > > If SQLite is case sensitive, is there an easy override for this to > > enforce > > all lowercase letters? > > select * from tableName where field1 = 'a' collate NOCASE; > -- or > select * from tableName where lower(field1) = 'a'; > > Igor Tandetnik MySQL's text searches are by default _not_ case sensitive. Yet another example of the differences in the behaviour between the different SQL systems. Is there an existing wiki page or place on the web where this sort of thing is documented? This sort of information is gold, and while I've only been on the mailing list a short time, many of the messages have revolved around the SQL language and varying behaviours across platforms. thanks Paul - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is SQLite Case Sensitive?
Dwight, I had come to the same conclusion. The data has been manually typed, inconsistently, over the last 4 years. I've decided that during data importation, I'm going to force all pertinent fields to lower case before they are written to the database. That should solve the problem. Thanks, Lee Crain __ -Original Message- From: Dwight Ingersoll [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 5:11 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is SQLite Case Sensitive? --- Lee Crain <[EMAIL PROTECTED]> wrote: > I am working on an application where I am importing > data for which great care has NOT been taken to > ensure uppercase and lowercase letters have been > entered appropriately. Just a suggestion: This sounds like it's a candidate for some data scrubbing and cleanup rather than trying to code for a lot of 'what if' scenarios, especially since you indicate that the data is pretty freeform. It will probably save a lot of development time and make future debugging easier if your data is in a known consistent state rather than the currently somewhat random state you implied. __ __ Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is SQLite Case Sensitive?
--- Lee Crain <[EMAIL PROTECTED]> wrote: > I am working on an application where I am importing > data for which great care has NOT been taken to > ensure uppercase and lowercase letters have been > entered appropriately. Just a suggestion: This sounds like it's a candidate for some data scrubbing and cleanup rather than trying to code for a lot of 'what if' scenarios, especially since you indicate that the data is pretty freeform. It will probably save a lot of development time and make future debugging easier if your data is in a known consistent state rather than the currently somewhat random state you implied. Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
Richard, Thanks for suggesting it but no, I don't think invisible control characters are the problem. I have copied and pasted the query from my source code into a MS SQL Server Management Studio interface and executed it against a SQL Server mockup of our SQLite database. It works perfectly. I experimented and tried some variations on the query. ___ This query does not work in SQLite but works in MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> ((SELECT related_item FROM RelatedItems WHERE item = 1777) ...> UNION ...> (SELECT item FROM RelatedItems WHERE related_item = 1777)) ...> ORDER BY Items.name ASC; SQL error: near "UNION": syntax error sqlite> However, this query works in both SQLite and MS SQL Server: sqlite> SELECT items_idx, [name], active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> (SELECT related_item FROM RelatedItems WHERE item = 1777 ...> UNION ...> SELECT item FROM RelatedItems WHERE related_item = 1777) ...> ORDER BY Items.name ASC; 1706|Arizona Character|T 1707|Arizona Clothing and Props|T 1660|Arizona Hair|T 2325|Bonnie V3 Teen|T 1425|Isabella for Stephanie 3|T 1918|Little Darling for V3/SP|T 106|Rose Character|T 1778|Teresa Hair|T sqlite> Further experimentation showed that the extra pair of parentheses in the first query (around each SELECT statement) caused the syntax error. Thanks for your response, Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 3:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? "Lee Crain" <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) Works when I try it Do you think you might have some invisible control characters or something in the middle of the SQL? > > __ > SELECT Items.items_idx, Items.name, Items.active FROM Items > WHERE active = 'T' AND Items.items_idx IN > (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 > UNION > SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) > ORDER BY Items.name ASC; > -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
--- Lee Crain <[EMAIL PROTECTED]> wrote: > I've queried it in both the command line interface and via an > sqlite3_exec() call in a C++ environment. You must have a typo somewhere. SQLite version 3.4.1 Enter ".help" for instructions sqlite> CREATE TABLE Items(items_idx, name, active); sqlite> INSERT INTO "Items" VALUES(1,'dog','T'); sqlite> INSERT INTO "Items" VALUES(2,'cat','A'); sqlite> INSERT INTO "Items" VALUES(1777,'pig','G'); sqlite> CREATE TABLE RelatedItems(item, related_item); sqlite> INSERT INTO "RelatedItems" VALUES(1,2); sqlite> INSERT INTO "RelatedItems" VALUES(1777,1); sqlite> .mode column sqlite> .header on sqlite> SELECT Items.items_idx, Items.name, Items.active FROM Items ...> WHERE active = 'T' AND Items.items_idx IN ...> (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 ...> UNION ...> SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) ...> ORDER BY Items.name ASC; items_idx nameactive -- -- -- 1 dog T Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
I've queried it in both the command line interface and via an sqlite3_exec() call in a C++ environment. Lee Crain ___ -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 3:30 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNION? There's nothing wrong with your query. It works fine against this schema in sqlite 3.4.1: CREATE TABLE Items(items_idx, name, active); CREATE TABLE RelatedItems(item, related_item); What language/tool are you using to query sqlite? --- Lee Crain <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) > > I believe this is, unfortunately correct, since the SQLite documentation > does not mention the reserved word "UNION" in the set of supported and > recognized SQL words (http://www.sqlite.org/lang.html). > > Is this correct? If so, is there another way to accomplish this query as a > single query? > > Thanks, > > Lee Crain > > __ > > > SELECT Items.items_idx, Items.name, Items.active FROM Items > > WHERE active = 'T' AND Items.items_idx IN > > (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 > > UNION > > SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) > > ORDER BY Items.name ASC; __ __ Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION?
"Lee Crain" <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) Works when I try it Do you think you might have some invisible control characters or something in the middle of the SQL? > > __ > SELECT Items.items_idx, Items.name, Items.active FROM Items > WHERE active = 'T' AND Items.items_idx IN > (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 > UNION > SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) > ORDER BY Items.name ASC; > -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION?
There's nothing wrong with your query. It works fine against this schema in sqlite 3.4.1: CREATE TABLE Items(items_idx, name, active); CREATE TABLE RelatedItems(item, related_item); What language/tool are you using to query sqlite? --- Lee Crain <[EMAIL PROTECTED]> wrote: > The query at the bottom of this email is failing on the word "UNION". (The > query works correctly in MS SQL Server.) > > I believe this is, unfortunately correct, since the SQLite documentation > does not mention the reserved word "UNION" in the set of supported and > recognized SQL words (http://www.sqlite.org/lang.html). > > Is this correct? If so, is there another way to accomplish this query as a > single query? > > Thanks, > > Lee Crain > > __ > > > SELECT Items.items_idx, Items.name, Items.active FROM Items > > WHERE active = 'T' AND Items.items_idx IN > > (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 > > UNION > > SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) > > ORDER BY Items.name ASC; Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNION?
http://www.sqlite.org/lang_select.html Lee Crain wrote: The query at the bottom of this email is failing on the word "UNION". (The query works correctly in MS SQL Server.) I believe this is, unfortunately correct, since the SQLite documentation does not mention the reserved word "UNION" in the set of supported and recognized SQL words (http://www.sqlite.org/lang.html). Is this correct? If so, is there another way to accomplish this query as a single query? Thanks, Lee Crain __ SELECT Items.items_idx, Items.name, Items.active FROM Items WHERE active = 'T' AND Items.items_idx IN (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 UNION SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) ORDER BY Items.name ASC; - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UNION?
The query at the bottom of this email is failing on the word "UNION". (The query works correctly in MS SQL Server.) I believe this is, unfortunately correct, since the SQLite documentation does not mention the reserved word "UNION" in the set of supported and recognized SQL words (http://www.sqlite.org/lang.html). Is this correct? If so, is there another way to accomplish this query as a single query? Thanks, Lee Crain __ SELECT Items.items_idx, Items.name, Items.active FROM Items WHERE active = 'T' AND Items.items_idx IN (SELECT related_item FROM RelatedItems WHERE RelatedItems.item = 1777 UNION SELECT item FROM RelatedItems WHERE RelatedItems.related_item = 1777) ORDER BY Items.name ASC; - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] The Callback Function Interface?
The callback interface will stay around. The newer interface is more flexible. You can use it to build a callback interface, but you can also make other structures and re-use compiled SQL. In fact if for some reason the callback API disappeared you could rebuild it with prepare/bind/reset instructions. Lee Crain wrote: John, Understood. Is the callback function interface going to be eliminated? What advantages does the new interface offer over the callback function interface? Lee Crain _ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 8:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite The callback interface is there to support legacy applications. It has been replaced by prepare/step for new work. Lee Crain wrote: Dennis, Are you certain that the callback function interface has been deprecated? From the link you posted: --- "2.2 Executing SQL statements typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**); The sqlite3_exec function works much as it did in SQLite version 2. Zero or more SQL statements specified in the second parameter are compiled and executed. Query results are returned to a callback routine." --- I couldn't find a reference to its deprecation. Lee Crain -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 07, 2007 1:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite Stephen Sutherland wrote: use the quick start code http://www.sqlite.org/quickstart.html That's what I used to build all my code from Stephen, The quickstart code is very old. It uses the callback function interface which is a depreciated API function that is maintained primarily for backward compatibility. You should really look at the prepare/bind/step/column set of API functions introduced in version 3 which is described at http://www.sqlite.org/capi3.html Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Select, update on the row, and step leads to crash
Any particular reason why you're using 3.3.13? I don't plan to compile it. --- karthikeyan <[EMAIL PROTECTED]> wrote: > Ah yes, in the sqlite3_bind_int, I need to pass id, thanks. Also, please try > compiling, the sqlite code (3.3.13) with "-DSQLITE_OMIT_SHARED_CACHE=1". It > should crash with the example code. On Versions, 3.3.17 and 3.4.1 its > working fine. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] In-Memory
Hello, Has anyone used SQLite as an In-Memory implementation. If so would you like to share your ideas? Thanks in advance - Uma
RE: [sqlite] The Callback Function Interface?
Thanks for your reply, Richard. I've invested a considerable amount of time and effort in designing a very simple and robust SQLite API wrapper for our application development staff that uses the SQLite callback function. I didn't want to have to redesign it anytime soon. And thanks for your commitment to not breaking existing interfaces. Lee Crain __ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 10:25 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] The Callback Function Interface? "Lee Crain" <[EMAIL PROTECTED]> wrote: > > Is the callback function interface going to be eliminated? > No. At SQLite, we work very hard to support all of our past mistakes :-) Seriously, we work very hard to make sure that the C-language API and the on-disk file format for SQLite never change in incompatible ways. We might add new features and better ways of doing things from time to time, but old features and interfaces are always preserved to the best of our ability. The previous paragraph does NOT apply to interfaces that are declared "experimental". Nor does it apply to pragmas. Though in practice, neither of these change very often. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] The Callback Function Interface?
"Lee Crain" <[EMAIL PROTECTED]> wrote: > > Is the callback function interface going to be eliminated? > No. At SQLite, we work very hard to support all of our past mistakes :-) Seriously, we work very hard to make sure that the C-language API and the on-disk file format for SQLite never change in incompatible ways. We might add new features and better ways of doing things from time to time, but old features and interfaces are always preserved to the best of our ability. The previous paragraph does NOT apply to interfaces that are declared "experimental". Nor does it apply to pragmas. Though in practice, neither of these change very often. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Select, update on the row, and step leads to crash
Hi,
Ah yes, in the sqlite3_bind_int, I need to pass id, thanks. Also, please try
compiling, the sqlite code (3.3.13) with "-DSQLITE_OMIT_SHARED_CACHE=1". It
should crash with the example code. On Versions, 3.3.17 and 3.4.1 its
working fine.
Thanks
Karthik
This e-mail and attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed
above. Any use of the information contained herein in any way (including,
but not limited to, total or partial disclosure, reproduction, or
dissemination) by persons other than the intended recipient's) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: Monday, August 06, 2007 8:10 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Select, update on the row, and step leads to crash
Updates during a SELECT work fine once you correct these mistakes:
You shouldn't be using SQLITE_STATIC as buf is temporary - use
SQLITE_TRANSIENT instead.
The two different ways you populate the table are different.
If createUsingExec is 1, your are inserting strings beginning with 'name is
1'. Whereas when createUsingExec is 0, you are inserting strings beginning
with 'name is 0' due to your for loop:
for (i = 0; i < 5; i ++)/sqlite3_bind_int (pInsRow, 1, i);
In your last while loop you are mistakenly using the variable 'i'
instead of 'id'.
You cannot use ++ in the sprintf to change the id variable as this will
change the id which you will later bind with.
--- karthikeyan <[EMAIL PROTECTED]> wrote:
> The sample code is below. I am trying this on windows(xp)(sqlite
> 3.3.13) and have disabled almost all sqlite features.
> When createUsingExec variable is true in the sample code, I am able to
> reproduce the problem.
>
> Thanks
> karthik
>
> //== begin
> ==
> int select_while_update ();
> int main ()
> {
> select_while_update ();
> }
>
> int select_while_update ()
> {
> int i = 0;
> int ret;
> sqlite3 *pDb = NULL;
> const char *dbName = "./t.db";
> const char *crTable = "CREATE TABLE A ( ID INTEGER, NAME TEXT) ;";
> const char *selAll = "SELECT * from A;";
> const char *insRow = "INSERT INTO A VALUES (?,?);";
> const char *updRow = "UPDATE A SET NAME=? WHERE ID=?;";
>
> sqlite3_stmt *pSelAll = NULL;
> sqlite3_stmt *pInsRow = NULL;
> sqlite3_stmt *pUpdRow = NULL;
> int createUsingExec = 1;
>
> char buf [64] = { 0 };
>
> remove (dbName);
> ret = sqlite3_open (dbName, &pDb);
> sqlite3_busy_timeout (pDb, 3000);
>
> if (createUsingExec) {
> const char *c2 = "INSERT INTO A VALUES ( 1, 'name is 1' )
;";
> const char *c3 = "INSERT INTO A VALUES ( 2, 'name is 2' )
;";
> const char *c4 = "INSERT INTO A VALUES ( 3, 'name is 3') ;";
> const char *c5 = "INSERT INTO A VALUES ( 4, 'name is 4' )
;";
> const char *c6 = "INSERT INTO A VALUES ( 5, 'name is 5' )
;";
>
> sqlite3_exec (pDb, crTable, 0, 0, 0);
> sqlite3_exec (pDb, c2, 0, 0, 0);
> sqlite3_exec (pDb, c3, 0, 0, 0);
> sqlite3_exec (pDb, c4, 0, 0, 0);
> sqlite3_exec (pDb, c5, 0, 0, 0);
> sqlite3_exec (pDb, c6, 0, 0, 0);
>
> } else {
>
> ret = sqlite3_exec (pDb, crTable, NULL, NULL, NULL);
> if (SQLITE_OK != ret) {
> printf ("error creating table\n");
> return -1;
> }
>
> ret = sqlite3_prepare_v2 (pDb, insRow, -1, &pInsRow, NULL);
>
>
> for (i = 0; i < 5; i ++) {
> sprintf (buf, "name is %d", i);
>
> sqlite3_reset (pInsRow);
> sqlite3_bind_int (pInsRow, 1, i);
> sqlite3_bind_text (pInsRow, 2, buf, -1,
SQLITE_STATIC);
> sqlite3_step (pInsRow);
>
> }
>
> sqlite3_finalize (pInsRow);
> }
>
> ret = sqlite3_prepare_v2 (pDb, selAll, -1, &pSelAll, NULL);
> sqlite3_bind_parameter_count (pSelAll);
> sqlite3_reset (pSelAll);
> ret = sqlite3_step (pSelAll);
>
> while (SQLITE_ROW == ret) {
> // get id from select statemnt results
> int id;
> char *text;
>
> sqlite3_data_count (pSelAll);
> sqlite3_column_count (pSelAll);
>
> sqlite3_column_type (pSelAll, 0);
> sqlite3_column_byte
[sqlite] The Callback Function Interface?
John, Understood. Is the callback function interface going to be eliminated? What advantages does the new interface offer over the callback function interface? Lee Crain _ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 8:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite The callback interface is there to support legacy applications. It has been replaced by prepare/step for new work. Lee Crain wrote: > Dennis, > > Are you certain that the callback function interface has been deprecated? > >>From the link you posted: > > --- > > "2.2 Executing SQL statements >typedef int (*sqlite_callback)(void*,int,char**, char**); >int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, > char**); > The sqlite3_exec function works much as it did in SQLite version 2. Zero > or more SQL statements specified in the second parameter are compiled and > executed. Query results are returned to a callback routine." > > --- > > I couldn't find a reference to its deprecation. > > Lee Crain > > > > > -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 07, 2007 1:08 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Extremely new to SQLite > > Stephen Sutherland wrote: > >>use the quick start code >> http://www.sqlite.org/quickstart.html >> >> That's what I used to build all my code from >> >> > > Stephen, > > The quickstart code is very old. It uses the callback function interface > which is a depreciated API function that is maintained primarily for > backward compatibility. > > You should really look at the prepare/bind/step/column set of API > functions introduced in version 3 which is described at > http://www.sqlite.org/capi3.html > > Dennis Cote > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > > > -- --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- --- > -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Is SQLite Case Sensitive?
Lee Crain <[EMAIL PROTECTED]> wrote: Would a search for an 'a' return a different result than a search for an 'A'? By default, yes. SELECT * FROM table WHERE field1 = 'a'; Vs. SELECT * FROM table WHERE field1 = 'A'; If SQLite is case sensitive, is there an easy override for this to enforce all lowercase letters? select * from tableName where field1 = 'a' collate NOCASE; -- or select * from tableName where lower(field1) = 'a'; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Help Creating database????
kishora.subramanyajois-e8pVGp8MVTP/[EMAIL PROTECTED] wrote: I have sqlite3.exe When I execute it, the following prompt will be displayed sqlite> I would like to know how to create a database? I tried, sqlite>sqlite3 test.db Type this line at the OS command prompt, _not_ at the sqlite3 command prompt. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Permission and Busy Handler Issue in SQLite
What is the goal of changing permissions? Kiran <[EMAIL PROTECTED]> wrote: The interesting part of the scenario is, when you change the file permission using chmod 000 bulk.db, there is no effect on the process of writing (with sqlite3_exec and busy handler combo) however the same does not work for the chmod 444 bulk.db. This make me suspect that there is some point which I am missing or may be it is a defect in SQLite. Anyways thanks for effort Joe -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 6:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] File Permission and Busy Handler Issue in SQLite It is not reasonable to change a database file to read-only in the middle of a transaction. I suppose sqlite could be changed to detect it, but why bother? By the same logic, if another process wrote random bytes in the middle of an sqlite database file, should sqlite be responsible for recovering from that as well? --- Kiran wrote: > I am currently using SQLite in my application as in Multi-process > environment. I.e. my application will contain multi-process which will > handle add/delete request to a single db - (bulk.db). I am using the > busy_handler concept (with 5ms delay) for locking issues. > > However, during testing, when 5 processes is up and running, I by chance > changed the file permission of bulk.db (chmod 444 bulk.db) to read only. Now > I see that the sqlite_exe is not able to trap the read-only mode error of > the database (while it tries to add records) and is just returning a > busy_handler and thus in loop. > > Bottom line, if you are using busy_handler and if DB file permission is > changed to read-only (manually) instead of throwing an error, SQLite will > continue triggering busy_handler. Thus not able to handle this situation. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Extremely new to SQLite
The callback interface is there to support legacy applications. It has been replaced by prepare/step for new work. Lee Crain wrote: Dennis, Are you certain that the callback function interface has been deprecated? From the link you posted: --- "2.2 Executing SQL statements typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**); The sqlite3_exec function works much as it did in SQLite version 2. Zero or more SQL statements specified in the second parameter are compiled and executed. Query results are returned to a callback routine." --- I couldn't find a reference to its deprecation. Lee Crain -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 07, 2007 1:08 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Extremely new to SQLite Stephen Sutherland wrote: use the quick start code http://www.sqlite.org/quickstart.html That's what I used to build all my code from Stephen, The quickstart code is very old. It uses the callback function interface which is a depreciated API function that is maintained primarily for backward compatibility. You should really look at the prepare/bind/step/column set of API functions introduced in version 3 which is described at http://www.sqlite.org/capi3.html Dennis Cote -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] File Permission and Busy Handler Issue in SQLite
The interesting part of the scenario is, when you change the file permission using chmod 000 bulk.db, there is no effect on the process of writing (with sqlite3_exec and busy handler combo) however the same does not work for the chmod 444 bulk.db. This make me suspect that there is some point which I am missing or may be it is a defect in SQLite. Anyways thanks for effort Joe -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 6:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] File Permission and Busy Handler Issue in SQLite It is not reasonable to change a database file to read-only in the middle of a transaction. I suppose sqlite could be changed to detect it, but why bother? By the same logic, if another process wrote random bytes in the middle of an sqlite database file, should sqlite be responsible for recovering from that as well? --- Kiran <[EMAIL PROTECTED]> wrote: > I am currently using SQLite in my application as in Multi-process > environment. I.e. my application will contain multi-process which will > handle add/delete request to a single db - (bulk.db). I am using the > busy_handler concept (with 5ms delay) for locking issues. > > However, during testing, when 5 processes is up and running, I by chance > changed the file permission of bulk.db (chmod 444 bulk.db) to read only. Now > I see that the sqlite_exe is not able to trap the read-only mode error of > the database (while it tries to add records) and is just returning a > busy_handler and thus in loop. > > Bottom line, if you are using busy_handler and if DB file permission is > changed to read-only (manually) instead of throwing an error, SQLite will > continue triggering busy_handler. Thus not able to handle this situation. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] File Permission and Busy Handler Issue in SQLite
It is not reasonable to change a database file to read-only in the middle of a transaction. I suppose sqlite could be changed to detect it, but why bother? By the same logic, if another process wrote random bytes in the middle of an sqlite database file, should sqlite be responsible for recovering from that as well? --- Kiran <[EMAIL PROTECTED]> wrote: > I am currently using SQLite in my application as in Multi-process > environment. I.e. my application will contain multi-process which will > handle add/delete request to a single db - (bulk.db). I am using the > busy_handler concept (with 5ms delay) for locking issues. > > However, during testing, when 5 processes is up and running, I by chance > changed the file permission of bulk.db (chmod 444 bulk.db) to read only. Now > I see that the sqlite_exe is not able to trap the read-only mode error of > the database (while it tries to add records) and is just returning a > busy_handler and thus in loop. > > Bottom line, if you are using busy_handler and if DB file permission is > changed to read-only (manually) instead of throwing an error, SQLite will > continue triggering busy_handler. Thus not able to handle this situation. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Delete all other distinct rows
I don't think this would be very scalable, but you could do something like: DELETE FROM table WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM table GROUP BY NAME ) This is totally untested, BTW - just a thought. :) -Tom > -Original Message- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 08, 2007 5:30 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Delete all other distinct rows > > How to delete all other distinct rows except first one. > > > > If I have a table with rows > > > > ID, NAME > > > > 1, SOME NAME > > 2, SOME NAME > > 3, SOME NAME > > 4, ANOTHER NAME > > 5, ANOTHER NAME > > > > > > The delete should work even if you don't know what the value > of name is, > so simply for anything that is duplicate. > > > > The distinct delete should delete rows 2, 3, 5 and just keep > 1 and 4, is > there a single SQL statement that can achieve this? > > > > Thanks > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Delete all other distinct rows
The solution was actually so simple, thanks. -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: 08 August 2007 12:01 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Delete all other distinct rows Andre, C:\Joinerysoft\JMS\dev\trunk>sqlite3 tst.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table tmp( id integer, name text ); sqlite> insert into tmp values( 1, 'some name' ); sqlite> insert into tmp values( 2, 'some name' ); sqlite> insert into tmp values( 3, 'some name' ); sqlite> insert into tmp values( 4, 'another name' ); sqlite> insert into tmp values( 5, 'another name' ); sqlite> sqlite> delete from tmp where id not in ( select min(id) from tmp group by name ); sqlite> sqlite> select * from tmp; 1|some name 4|another name sqlite> Rgds, Simon On 08/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > How to delete all other distinct rows except first one. > > > > If I have a table with rows > > > > ID, NAME > > > > 1, SOME NAME > > 2, SOME NAME > > 3, SOME NAME > > 4, ANOTHER NAME > > 5, ANOTHER NAME > > > > > > The delete should work even if you don't know what the value of name is, > so simply for anything that is duplicate. > > > > The distinct delete should delete rows 2, 3, 5 and just keep 1 and 4, is > there a single SQL statement that can achieve this? > > > > Thanks > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Delete all other distinct rows
Andre, C:\Joinerysoft\JMS\dev\trunk>sqlite3 tst.db SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table tmp( id integer, name text ); sqlite> insert into tmp values( 1, 'some name' ); sqlite> insert into tmp values( 2, 'some name' ); sqlite> insert into tmp values( 3, 'some name' ); sqlite> insert into tmp values( 4, 'another name' ); sqlite> insert into tmp values( 5, 'another name' ); sqlite> sqlite> delete from tmp where id not in ( select min(id) from tmp group by name ); sqlite> sqlite> select * from tmp; 1|some name 4|another name sqlite> Rgds, Simon On 08/08/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > How to delete all other distinct rows except first one. > > > > If I have a table with rows > > > > ID, NAME > > > > 1, SOME NAME > > 2, SOME NAME > > 3, SOME NAME > > 4, ANOTHER NAME > > 5, ANOTHER NAME > > > > > > The delete should work even if you don't know what the value of name is, > so simply for anything that is duplicate. > > > > The distinct delete should delete rows 2, 3, 5 and just keep 1 and 4, is > there a single SQL statement that can achieve this? > > > > Thanks > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] File Permission and Busy Handler Issue in SQLite
Hi All, I am currently using SQLite in my application as in Multi-process environment. I.e. my application will contain multi-process which will handle add/delete request to a single db - (bulk.db). I am using the busy_handler concept (with 5ms delay) for locking issues. However, during testing, when 5 processes is up and running, I by chance changed the file permission of bulk.db (chmod 444 bulk.db) to read only. Now I see that the sqlite_exe is not able to trap the read-only mode error of the database (while it tries to add records) and is just returning a busy_handler and thus in loop. Bottom line, if you are using busy_handler and if DB file permission is changed to read-only (manually) instead of throwing an error, SQLite will continue triggering busy_handler. Thus not able to handle this situation. Can any one help me in solving this scenario? Thanks and Regards, Kiran Kumar M Huawei Technologies India Private Limited Bangalore - 560008. "Programmers seem to think the shortest distance between two points is the great circle route on a spherical distortion of Euclidean space" ___ This e-mail and attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient's) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! ___
[sqlite] Delete all other distinct rows
How to delete all other distinct rows except first one. If I have a table with rows ID, NAME 1, SOME NAME 2, SOME NAME 3, SOME NAME 4, ANOTHER NAME 5, ANOTHER NAME The delete should work even if you don't know what the value of name is, so simply for anything that is duplicate. The distinct delete should delete rows 2, 3, 5 and just keep 1 and 4, is there a single SQL statement that can achieve this? Thanks
