[sqlite] sqlite3_step to select and update the same table
Hello,
I am trying to select some columns from a table and use that to
update another column in the same table using prepare/step/reset/finalize
methods. However, when I use the update statement while stepping it is
executing the flow 2 times. In order to clarify, I am pasting some pseudo
code
zSql = sqlite3_mprintf("SELECT * FROM table1 WHERE abc=? ;");
if( (rc = sqlite3_prepare_v2(db, zSql, (int)strlen(zSql), &stmt, NULL)) !=
SQLITE_OK ) {
fprintf(stderr, "SQL error: preparing update statement\n");
goto error;
}
rc = sqlite3_bind_text(stmt, 1, temp, (int)strlen(temp), NULL);
if( rc != SQLITE_OK ) { goto error;}
/* Execute the query expression */
while(sqlite3_step(stmt) == SQLITE_ROW) {
ref = sqlite3_column_int(stmt,3);
printf("Before sql execution %d \n",ref);
delSql = sqlite3_mprintf("UPDATE table1 SET ghi = %d WHERE def = %d
;",1,ref);
if( (rc = sqlite3_exec(db, delSql, NULL, NULL, &zErrMsg)) != SQLITE_OK
) {
sqlite3_free(zErrMsg); goto error; }
}
sqlite3_reset(stmt);
sqlite3_finalize(stmt);
Output :
Before sql execution 5
Before sql execution 5
Before sql execution 6
Before sql execution 6
As you can see the print statement is being printed 2 times and this code is
not in a loop, apart from the while for stepping. If I replace the update
statement with another select statement I behaves normally i.e. just
executes the query once, prints just once.
Do I have to do something special If I want to do an update on the table I
am selecting from? I am new to SQLITE hope someone can help me resolve my
newbie problem.
Thanks
--
View this message in context:
http://old.nabble.com/sqlite3_step-to-select-and-update-the-same-table-tp30152284p30152284.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about manifest typing/data affinity
On Sat, Nov 06, 2010 at 08:35:10PM -0300, Tito Ciuro scratched on the wall: > Hello, > > I have a question about manifest typing/data affinity. Assume I have > created this table: > > CREATE TABLE foo (ROWID INTEGER PRIMARY KEY, key TEXT, attr TEXT, value NONE); > > I was reading the Using SQLite book and came across page #38 (#60 on > the PDF version) where it states: I hope the book is proving useful. > "None: A column with a none affinity has no preference over storage > class. Each value is stored as the type provided, with no attempt > to convert anything." > > So it seems that using NONE is a hint that different data types are > going to be stored in that column, correct? I suppose you could look at it that way, but it isn't meant to be explicit. With the exception of an INTEGER PRIMARY KEY, any column can hold row-values of any type. The affinity will influence stored values to specific types, but it does not prevent other types from being stored if the conversion cannot be made. The NONE affinity simply indicates to the database that you always store values in their provided type, and that no conversions should be attempted, even if a conversion could be done in a loss-less way. > My main question has to do with binding values to precompiled statements. > For the value column, should I: > > a) use sqlite3_bind_value()? sqlite3_bind_value() is for binding "sqlite3_value" data structures. If you have one of those (from an sqlite3_column_value(), for example), and it is in the type representation you want (text, int, etc), then go ahead and use it. If you're binding a more traditional value, such as a string or integer, you should use one of the sqlite3_bind_text(), sqlite3_bind_int(), or similar functions. The bind function you choose will set the "type" of the value. If the column has a NONE affinity, no conversion will be attempted and the value, in the representation you provided, will be stored directly. > b) store it as a string using sqlite3_bind_text()? Will > sqlite3_bind_text() allow SQLite to choose the proper data affinity > even though I'm binding it as text? An affinity is a property of a table column. The affinity of a column is defined by the "SQL type" passed to CREATE TABLE. Once the table has been created, the affinity of each column is set. So the only time SQLite "chooses" an affinity is when it parses the CREATE TABLE statement. So, for example, if you have an INSERT statement that binds a text value to a parameter used to set a column with a NONE affinity, the value in the new row will *always* be stored as a text value, regardless of the string value. If you were to bind an integer, the value would always be stored as an integer, and so on. This would work differently if the column had a NUMERIC affinity, for example. In that case, binding the string "abc" would result in storage of a text value, while binding the string "132" would result in an integer and "85.3" would result in a floating-point. Binding 43 (the integer, not a two-character string, using sqlite3_bind_int()) would result in an integer as well. As you might know, you can use different sqlite3_column_xxx() functions to extract values in a specific representation (type), even if that returned representation does not match the type of the stored value. For example, if you know you're going to print out a value, you can use sqlite3_column_text() to get a text representation of the value, even if that value is stored in the database as an integer. The conversions used for this are given in table 7.1 of Using SQLite. On the input side, you can use different sqlite3_bind_xxx() function to provide values in a representation (type) that might not match the type used to actually store the value. The affinity defines your "preferred" storage type, and is used as a hint to do input conversions. So if you're taking use input for a number, you can take the text value passed in by your applications GUI and pass that directly to sqlite3_bind_text(), even if the string represents a number, and you want to store it as a number (and have told the database this by using an INTEGER, REAL, or NUMERIC affinity). The NONE affinity is simply a way of saying that you will always provide values in the representation that should be used to store them. This means that the specific sqlite3_bind_xxx() function you choose more or less sets the type of the value. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about manifest typing/data affinity
Quoth Tito Ciuro , on 2010-11-06 20:35:10 -0300: > "None: A column with a none affinity has no preference over storage > class. Each value is stored as the type provided, with no attempt to > convert anything." Note that type affinities are not usually specified as column types directly. Instead, an SQL column type is used. In fact, the column type "NONE" will be detected as NUMERIC affinity, per the rules in the documentation. I would use a blank type to declare a column of varying type; that would give the NONE affinity you desire. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about manifest typing/data affinity
On 6 Nov 2010, at 11:35pm, Tito Ciuro wrote: > My main question has to do with binding values to precompiled statements. For > the value column, should I: > > a) use sqlite3_bind_value()? > b) store it as a string using sqlite3_bind_text()? Will sqlite3_bind_text() > allow SQLite to choose the proper data affinity even though I'm binding it as > text? Use the binder appropriate to the value you want to store and retrieve. Ignore the place the value will be stored, just worry about whether you're currently holding is typed an integer, text, real, etc.. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about manifest typing/data affinity
Tito Ciuro wrote: > My main question has to do with binding values to precompiled statements. For > the value column, should I: > > a) use sqlite3_bind_value()? No, except in certain special cases. You would normally have no way to obtain sqlite_value pointer, anyway. > b) store it as a string using sqlite3_bind_text()? Yes, if you have a piece of text to store, use sqlite3_bind_text. If you have an int to store, use sqlite3_bind_int. You get the idea. > Will sqlite3_bind_text() allow SQLite to choose the proper data affinity even > though I'm binding it as text? What's "proper data affinity"? I'm afraid I'm not familiar with this term. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about manifest typing/data affinity
Hello, I have a question about manifest typing/data affinity. Assume I have created this table: CREATE TABLE foo (ROWID INTEGER PRIMARY KEY, key TEXT, attr TEXT, value NONE); I was reading the Using SQLite book and came across page #38 (#60 on the PDF version) where it states: "None: A column with a none affinity has no preference over storage class. Each value is stored as the type provided, with no attempt to convert anything." So it seems that using NONE is a hint that different data types are going to be stored in that column, correct? My main question has to do with binding values to precompiled statements. For the value column, should I: a) use sqlite3_bind_value()? b) store it as a string using sqlite3_bind_text()? Will sqlite3_bind_text() allow SQLite to choose the proper data affinity even though I'm binding it as text? Thanks in advance, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] re SQLite ODBC Driver,
Thanks Kees, Christian,
SQLSetConnectAttrW(q->hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0); this is what I already had and I think you
have answered the question with "The SQLite ODBC driver allows only one SQL
statement per SQLExecDirect()", this is the conclusion that I came to.
Kees, your suggestion is exactly what I did, tah is a single statement per
SQLExecDirect( ... ) SQLExecDirect("INSERT INTO t (col1,col2) VALUES
('val11','val21')");
Thanks to both of you for your quick reply, looks like I will have to accept
the way things are, but that's a small price to pay for a very useful database.
Regards
Len
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite/fts3 feature request: select snippet() from fts3 where docid=1
On Sat, Nov 6, 2010 at 7:45 AM, Simon Hefti wrote: > Hello > > Do I understand correctly that fts3 snippet function does only work > with match queries like > select snippet(text) from text where text match 'foo' > > I would be interested to have: > select snippet(text) from text where docid=1 > > In sqlite 3.7.2 this statement is valid but returns an empty string as > snippet. > Without a MATCH operator to identify your keywords of interest, how should the snippet function figure out which except from the document to return? > > Thanks! > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] To close or not to close
On Fri, Nov 5, 2010 at 2:47 PM, Chris Vernor wrote: > I am fairly new to the SQLite world, and have a quick question: > > > > I have an application that migrates files form a windows environment to > a solaris environment, and it can be run multi-threaded. I have seen > several articles that show apps opening and closing the connection with > each Query or Insert, and other that establish a connection once and > hold it open until a thread completes. What would be the better method > for this? > > Hold the connection open. There is a small but non-trivial cost to opening and closing connections. > > > Thanks, > > Chris > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite/fts3 feature request: select snippet() from fts3 where docid=1
Hello Do I understand correctly that fts3 snippet function does only work with match queries like select snippet(text) from text where text match 'foo' I would be interested to have: select snippet(text) from text where docid=1 In sqlite 3.7.2 this statement is valid but returns an empty string as snippet. Thanks! Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ODBC Driver
> I do not seem to be able to parse a multiple insert statement through > the odbc drive using SQLExecDirect(...) I have tried with BEGIN, COMMIT > TRANSACTION does anyone have any ideas? The SQLite ODBC driver allows only one SQL statement per SQLExecDirect() and SQLPrepare(). When using transactions the right sequence of calls is SQLSetConnectAttr(...SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF), SQLExecDirect() and finally SQLEndTran() to commit or rollback. There's no need to send BEGIN/COMMIT statements through SQLExecDirect() Hope that helps, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] To close or not to close
I am fairly new to the SQLite world, and have a quick question: I have an application that migrates files form a windows environment to a solaris environment, and it can be run multi-threaded. I have seen several articles that show apps opening and closing the connection with each Query or Insert, and other that establish a connection once and hold it open until a thread completes. What would be the better method for this? Thanks, Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] To close or not to close
I am fairly new to the SQLite world, and have a quick question: I have an application that migrates files form a windows environment to a solaris environment, and it can be run multi-threaded. I have seen several articles that show apps opening and closing the connection with each Query or Insert, and other that establish a connection once and hold it open until a thread completes. What would be the better method for this? Thanks, Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL hash collisions
In wal.c, it reads "8 or 10 comparisons (on average) suffice to either locate a frame in the WAL or to establish that the frame does not exist in the WAL". I'm wondering -- how often does it occur that only a small subset of pages is written to again and again, in sequence, such that the WAL index hash ends up with a lot of collisions? This would turn the hash search into a linear scan of 8k entries. Is this rare enough in practice to be negligible? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error
Also... Can you write a small example program to duplicate the problem and post it? That's the best way for us to help. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Serena Lien Sent: Sat 11/6/2010 3:08 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error No, I didn't explain correctly. My application is not open overnight, only the computer. So I do not have any database connections open at all, they are not going stale. In the morning, I start the application, and it opens some databases in readwrite mode and executes statements without errors. It opened some databases in readonly mode and executes statements, these return SQLITE_BUSY errors, nothing else is using the databases. Therefore there is nothing wrong with the network connection as it has already accessed networked databases before my errors occur. I can only conclude it must be a bug with locking using the readonly flag - either sqlite or something vista OS specific going wrong. cheers Serena. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error
#1 What version of Sqlite and Windows client/server are you using? #2 What language is your application written in? #3 Are all your databases on the same share mount point? #4 Is your share mounted as a drive letter? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Serena Lien Sent: Sat 11/6/2010 3:08 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error No, I didn't explain correctly. My application is not open overnight, only the computer. So I do not have any database connections open at all, they are not going stale. In the morning, I start the application, and it opens some databases in readwrite mode and executes statements without errors. It opened some databases in readonly mode and executes statements, these return SQLITE_BUSY errors, nothing else is using the databases. Therefore there is nothing wrong with the network connection as it has already accessed networked databases before my errors occur. I can only conclude it must be a bug with locking using the readonly flag - either sqlite or something vista OS specific going wrong. cheers Serena. On Fri, Nov 5, 2010 at 10:52 PM, H. Phil Duby wrote: > On Fri, Nov 5, 2010 at 3:25 AM, Serena Lien wrote: > > > > I would like to inform the group that I managed to resolve the issue with > a > > workaround. I am not sure if this is a bug in vista or whether there > might > > in fact be some slight issue with sqlite... > > > > The problem seems to be hardware and software related, and occurs for me > > when my application is running on Windows Vista and is opening a database > on > > a networked drive (running Windows Small Business Server 2008) with the > > read-only flag. Running sql queries on the database after this will > > return SQLITE_BUSY when no one is using the database. Once the > application > > is closed and restarted the error does not re-occur. This is highly > > reproducible once per day (in the morning after the machine has powered > down > > the screen only, it doesn't go to sleep). > > Given that sequence / timing, I *suspect* this is actually caused by > the network connection 'timing out'. Your application thinks it has > the database file open, but SBS has _decided_ that the connection is > stale, and closed it. I do not know why your symptoms would only show > up for read only connections. You might be able to continue after the > error, by having the application close and reopen / reconnect to the > database file. You might get an error doing a normal close as well, > because SBS thinks it is already closed. > > When your Vista system powers down the screen, it may *also* be > powering down the [wired or wireless] network card, which could > trigger SBS to close the read only connection. > > > Removing that flag and making sure I only open databases in read-write > mode > > has resolved the problem. I have tested this thoroughly over a period of > > many days. > > > > As I said I'm not sure if this is an sqlite bug or not (and I'm sure very > > difficult to reproduce) but I'm writing this in case anyone else has the > > same problem in the hope it helps them. > > > > cheers Serena > -- > Phil > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ODBC Driver
On Sat, 06 Nov 2010 15:36:46 +1100, "Len(c-sharplizards)"
wrote:
> I do not seem to be able to parse a multiple insert statement through
> the odbc drive using SQLExecDirect(...) I have tried with BEGIN, COMMIT
> TRANSACTION does anyone have any ideas?
You don't give us much to work on.
- Which ODBC driver do you use?
- What does your "multiple insert statement" look like?
- What is the result (error messages, state of the db before
and after the statement)?
- What do you expect the result to be?
For your information, sqlite does not support the form
INSERT INTO t
(col1,col2)
VALUES
('val11','val21'),
('val12','val22'),
('val13','val23');
In general it is a bit challenging to expect that the whole
chain supports something like:
sql="BEGIN TRANSACTION;
INSERT INTO t (col1,col2) VALUES ('val11','val21');
INSERT INTO t (col1,col2) VALUES ('val12','val22');
INSERT INTO t (col1,col2) VALUES ('val13','val23');
COMMIT TRANSACTION;"
SQLExecDirect(sql);
Better change that to:
SQLExecDirect("BEGIN TRANSACTION");
SQLExecDirect("INSERT INTO t (col1,col2) VALUES
('val11','val21')");
SQLExecDirect("INSERT INTO t (col1,col2) VALUES
('val12','val22')");
SQLExecDirect("INSERT INTO t (col1,col2) VALUES
('val13','val23')");
SQLExecDirect("COMMIT TRANSACTION")
I hope this helps, if not please take some effort to
describe your exact problem.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error
No, I didn't explain correctly. My application is not open overnight, only the computer. So I do not have any database connections open at all, they are not going stale. In the morning, I start the application, and it opens some databases in readwrite mode and executes statements without errors. It opened some databases in readonly mode and executes statements, these return SQLITE_BUSY errors, nothing else is using the databases. Therefore there is nothing wrong with the network connection as it has already accessed networked databases before my errors occur. I can only conclude it must be a bug with locking using the readonly flag - either sqlite or something vista OS specific going wrong. cheers Serena. On Fri, Nov 5, 2010 at 10:52 PM, H. Phil Duby wrote: > On Fri, Nov 5, 2010 at 3:25 AM, Serena Lien wrote: > > > > I would like to inform the group that I managed to resolve the issue with > a > > workaround. I am not sure if this is a bug in vista or whether there > might > > in fact be some slight issue with sqlite... > > > > The problem seems to be hardware and software related, and occurs for me > > when my application is running on Windows Vista and is opening a database > on > > a networked drive (running Windows Small Business Server 2008) with the > > read-only flag. Running sql queries on the database after this will > > return SQLITE_BUSY when no one is using the database. Once the > application > > is closed and restarted the error does not re-occur. This is highly > > reproducible once per day (in the morning after the machine has powered > down > > the screen only, it doesn't go to sleep). > > Given that sequence / timing, I *suspect* this is actually caused by > the network connection 'timing out'. Your application thinks it has > the database file open, but SBS has _decided_ that the connection is > stale, and closed it. I do not know why your symptoms would only show > up for read only connections. You might be able to continue after the > error, by having the application close and reopen / reconnect to the > database file. You might get an error doing a normal close as well, > because SBS thinks it is already closed. > > When your Vista system powers down the screen, it may *also* be > powering down the [wired or wireless] network card, which could > trigger SBS to close the read only connection. > > > Removing that flag and making sure I only open databases in read-write > mode > > has resolved the problem. I have tested this thoroughly over a period of > > many days. > > > > As I said I'm not sure if this is an sqlite bug or not (and I'm sure very > > difficult to reproduce) but I'm writing this in case anyone else has the > > same problem in the hope it helps them. > > > > cheers Serena > -- > Phil > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
