Kittayya:
My issue is, I already have imported table in the Database, there alreay
around 1000 records in that table where ROWID is from 1 to 1000, now system
generates new events, where ROWID again starts from beginning from 1, now
these new events are overwriting the earlier imported events by "REPLACE
INTO......", I made that change to instead REPLACE, I need INSERT, but now I
need new ROWID (I need to update at the end of previous imported records. I
don't want to overwrite original records.
On Fri, Feb 5, 2010 at 4:09 PM, P Kishor <punk.k...@gmail.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
