I am top posting here because, (1) You are simply unable to articulate
your own problem clearly, (2) you are not listening to the advice that
you are getting, and (3) the quicker we put you on the right track the
better it will be for everyone.
First, if you have a table, you should have a primary key. You should
create your own primary key instead of using ROWID as that is used by
the software, and is not reliable for your own use. Creating a primary
key is as easy as defining a column as INTEGER PRIMARY KEY. For
example,
CREATE TABLE eventlog (id INTEGER PRIMARY KEY, foo TEXT, bar REAL);
There, that was easy, no? Now, sqlite will do the work for you,
automatically inserting primary keys for you, automatically ensuring
that there will be no collisions and you saving you from the misery of
figuring out what key to create and use next.
Second, if for some reason you want to know what the highest primary
key already in use is, you can ask the database
SELECT Max(id) FROM eventlog;
There, that was easy also.
Third, if, for some reason, you want to insert a new row, and then
immediately want to use the id of that new row for a different
operation, you can use last_insert_rowid()
BEGIN TRANSACTION;
-- insert a new row
INSERT INTO eventlog (foo, bar) VALUES ('blah blah', 21.546);
-- note that you didn't have to insert the id, as sqlite did that work for you
-- now, update some other table with the last inserted row id
UPDATE someothertable
SET col = ( SELECT last_insert_rowid() )
WHERE condition;
-- note, last_insert_rowid() is a db connection property,
-- so you don't have to specify a table. Make sure to
-- wrap the two in a transaction
COMMIT
Now, wasn't that easy?
Next step. Please read the docs and do some sql tutorials. You will
really benefit.
On Fri, Feb 5, 2010 at 2:54 PM, Vasanta <vtan...@gmail.com> wrote:
> I wroe this code to get ROWID, but if I assign output of sqlite3_step to an
> id, and assign that id, is OK?.
>
> const char * rowidSql = "SELECT max(ROWID)+1 "EVENTLOG_TBL ;
>
> const char * zSql;
> sqlite3_stmt * pStmt, pStmt2;
>
> /* This is added to run query to get ROWID */
> rc = sqlite3_prepare(pDB, rowidSql, -1, &pStmt2, 0);
> if( rc != SQLITE_OK )
> {
> ADP_PRINTF ("sqlite3_prepare failed for %s\n", rowidSql);
> return (ERROR);
> }
> eventLogRowIndex = sqlite3_step(pStmt2)
> sqlite3_bind_int(pStmt, 1, eventLogRowIndex);
>
>
> On Fri, Feb 5, 2010 at 3:30 PM, Shane Harrelson <sh...@sqlite.org> wrote:
>
>> As stated before, in general, you should not specify the ROWID on inserts,
>> but instead, let the database engine choose it for you.
>> This is true of most/all database engines.
>>
>> The syntax you're trying below is not supported. Indeed, even it were,
>> max(ROWID) is the maximum ROWID *in use*. Trying to insert another row
>> with
>> the same ROWID will result in a collision.
>>
>> At the very least you would need to do something like "SELECT max(ROWID)+1
>> from EVENTLOG_TBL;", use sqlite_step() to run this query, and bind the
>> ROWID
>> returned here into your insert statement and run that.
>>
>> You can read more on SQLite's ROWID usage at
>> http://www.sqlite.org/search?q=rowid
>>
>> HTH.
>> -Shane
>>
>>
>>
>>
>> On Fri, Feb 5, 2010 at 3:22 PM, Vasanta <vtan...@gmail.com> wrote:
>>
>> > This is my actual string, still not working:
>> >
>> > const char * replaceSql = "INSERT INTO "EVENTLOG_TBL \
>> > "(_ROWID_, component, facilityId, logLevel,"\
>> > "textMessage, binMessage) VALUES(?,?,?,?,?,?);
>> > SELECT max(ROWID) from EVENTLOG_TBL";
>> >
>> >
>> > On Fri, Feb 5, 2010 at 3:05 PM, Vasanta <vtan...@gmail.com> wrote:
>> >
>> > > Thanks jay.
>> > >
>> > > Can I combine like this:
>> > >
>> > > "INSERT INTO trends(UnitID,HeureTrends,DateTrends) VALUES(?,?,?);SELECT
>> > > max(ROWID) FROM table-name";
>> > >
>> > > or
>> > >
>> > > "INSERT INTO trends(UnitID,HeureTrends,DateTrends) VALUES(?,?,?);SELECT
>> > > last_insert_rowid() AS [ID]";
>> > >
>> > >
>> > > On Fri, Feb 5, 2010 at 2:49 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
>> > >
>> > >> On Fri, Feb 05, 2010 at 02:28:33PM -0500, Vasanta scratched on the
>> wall:
>> > >> > command "SELECT rowid from table-name;" gives all rows from 1 to
>> 100
>> > >> for
>> > >> > total 100 rows, any command to get last rowid?. I need insert from
>> > last
>> > >> > rowid onwards (if table already 100 records, need to insert from 101
>> > >> > onwards)
>> > >>
>> > >> SELECT max(ROWID) FROM table-name;
>> > >>
>> > >>
>> > >> You shouldn't be setting ROWIDs manually, however. Just insert the
>> > >> row and let SQLite pick the ROWID. If you have a ROWID alias in the
>> > >> form of an INTEGER PRIMARY KEY, in most cases you should still just
>> > >> let SQLite pick the value, possibly with AUTOINCREMENT.
>> > >>
>> > >> -j
>> > >>
>> > >> --
>> > >> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>> > >>
>> > >> "Our opponent is an alien starship packed with atomic bombs. We have
>> > >> a protractor." "I'll go home and see if I can scrounge up a ruler
>> > >> and a piece of string." --from Anathem by Neal Stephenson
>> > >> _______________________________________________
>> > >> 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
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, Wisconsin, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
