• Welcome, Guest. Please login.
 
October 18, 2021, 09:29:38 PM

News:

Welcome to the SQLitening support forums!


How to get the last rowid inserted? Reuse deleted rowid's?

Started by cj, December 04, 2011, 07:31:46 PM

Previous topic - Next topic

cj

1) How do I get the last rowid inserted?
The rowid was inserted using NULL as ClientNumber where ClientNumber is INTEGER PRIMARY KEY.

Answer to 1:
1) slGetInsertID returns a QUAD of the last rowid.
Hopefully, slGetChangeCount indicates that the last insert was successful?
Just tested  count and equals 1 if multiple records inserted within a transaction.

2) To reuse a deleted rowid should a list of deleted rowid's be kept?
I could mark records as deleted and always update them, but that adds to complication.
In the past I just negated the clientnumber, but it doesn't seem like a good choice.

Fred Meier

QuoteHopefully, slGetChangeCount indicates that the last insert was successful?
Just tested  count and equals 1 if multiple records inserted within a transaction.
You normally would check after each slExe.  slGetChangeCount returns the
number of rows that were changed.  If no ModChar T then returns the number
of rows that were changed or inserted or deleted by the most recently
completed SQL statement.  Changes caused by triggers are not counted.  If
ModChar T then returns the number of row changes since open.  Changes
caused by triggers are included. 
QuoteTo reuse a deleted rowid should a list of deleted rowid's be kept?
I never reuse a deleted RowID.  I many time will even add the
AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration to create
RowIDs that are unique over the lifetime of the table. 


cj

Thank  you.  I will change the logic of my system to never reuse a rowid.
It was only done this way because client folders are pre-numbered and  reused.
I never liked the way it worked by having blank records in the btrieve table.
It did have the advantage of prealloating disk space and everything was an update.

Trying to figure out if  AUTOINCREMENT is worth the extra overhead.

cj

I now see the difference with AUTOINCREMENT when using NULL.
The next highest value is not destroyed (kept in an internal table) when all records are deleted.
I didn't see the difference with AUTOINCREMENT until inserting records using NULL after deleting all records.

create table t1(f1 integer primary key autoincrement);
insert into t1 values(null);
insert into t1 values(null); 
delete from t1;
insert into t1 values(null);
select rowid from t1;
3

Bern Ertl

** Bumping this old thread **

Saw a discussion on the Nabble/SQLite discussion list (post #4) where it was mentioned that sqlite3_last_insert_rowid() [which is the base function used by slGetInsertID()] should be called within an explicit transaction to ensure you get the correct answer.  If I get time later, I might cobble together a new SQLitening function to perform an insert and return the RowID in one function call, but for now, if you want to use slGetInsertID(), you need to do something like this (this is just a template, you can add your error checking and/or expand for modchars, etc.):

Function MyslGetInsertID( sInsertSQL AS STRING) AS QUAD

LOCAL qResult AS QUAD
slEXE "BEGIN EXCLUSIVE;" + sInsertSQL
qResult = slGetInsertID()
slEXE "Commit;"
FUNCTION = qResult

END FUNCTION

cj

Transaction needed single-user not using threads?

Could cause a transaction within a transaction if in another function.
I think this is enough if using threads or multiuser.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe  "create table t1(c1 integer primary key)"
  slexe  "insert into t1 values(9999)"
  slexe "begin exclusive"
  ? FORMAT$(slGetInsertID),,"Last insert row"
  slexe "end"
END FUNCTION

Bern Ertl

The transaction needs to wrap both the insert and the get ID call.  The issue is avoiding the possibility of another insert occurring in the database between your process/thread's insert and get row ID call.  In your code, the slexe "begin exclusive" call needs to be moved up one line to precede the insert.

cj

I intended to simulate anybody inserting record 9999 and then seeing what was inserted last
and possibly use another rowid based upon it.

You did give me an idea using slGetInsertID to check validity of last insert instead of using slGetChangeCount.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe  "create table t1(c1 integer primary key)"
  slexe "begin exclusive"
  slexe  "insert into t1 values(9999)"
  IF slGetInsertId = 9999 THEN ? "success"
  slexe "end"
END FUNCTION


On second thought,
another user could have inserted 9999 and you would think you inserted it (perhaps with different column values.)
I'd stick with using slGetChangeCount to determine if inserts are succesfull
Kinda getting off subject, but could to have discussions on these things.

cj

For others, not familar with SQLitening or SQLite on testing if a record was inserted.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  slopen "sample.db3","C"
  slexe  "create table if not exists t1(c1 integer primary key)"
  slexe "begin exclusive"
  slexe  "insert into t1 values(9999)","E0" 'or use slSetProcessMods "E0"
  IF slGetChangeCount =1 THEN
     ? "Inserted record",,"Success"
  ELSE
    ? slGetError,,"Insert error"
  END IF
  slexe "end"
  REM slexe "drop table if exists t1"
END FUNCTION

Bern Ertl

Quote from: cj on June 28, 2016, 01:36:56 PM...
On second thought, another user could have inserted 9999 and you would think you inserted it (perhaps with different column values.) ...

Wrong.  That's what wrapping the insert and getinsertid calls within the "begin exclusive" transaction guarantees.

cj

The last insert row may be the same as the one you are about to insert and a duplicate error could occur.
An additional check would be needed for that or use slGetChangeCount.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG
  slSetProcessMods "E0"
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe  "create table if not exists t1(c1 integer primary key)"
  FOR x = 1 TO 2
    slexe "begin exclusive"
    slexe "insert into t1 values(9999)","E0"
    IF slGetErrorNumber THEN ? slGetError 'need this before slGetInsertID
    IF slGetInsertId = 9999  THEN ? "Insert ok" ELSE ? "Insert failed"
    slexe "end"
NEXT
END FUNCTION


This would be a better way to check for the insert:
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG
  slSetProcessMods "E0"
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe  "create table t1(c1 integer primary key)"
  FOR x = 1 TO 2
    slexe "begin exclusive"
    slexe "insert into t1 values(9999)"
    IF slGetChangeCount <> 1 THEN ? "Insert error when x is" + STR$(x)
    slexe "end"
NEXT
END FUNCTION





cj

Adding this for completeness if someone does multiple inserts and may or may not care if all are successful.
This uses slGetChangeCount("T") as was used by Fred in at least one demo.

%Cause_Error=1'change to 1 to cause an error and rollback (0=cause no errors.)

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL x,NumberOfChanges AS LONG, s AS STRING
  slopen "sample.db3","C"
  slexe "drop table if exists t1"
  slexe  "create table if not exists t1(c1 integer primary key)"
  'Insert 1 record to check if totals are correct at the end
  slexe "insert into t1 values(null)"

  slexe "begin exclusive"
NumberOfChanges = slGetChangeCount("T")
  FOR x = 1 TO 10 'insert 9 more records and cause an error
    IF %Cause_Error AND x = 10 THEN  'cause a duplicate error
      slexe "insert into t1 values(1)","E0"
    ELSE
      slexe "insert into t1 values(null)","E0"
    END IF
  NEXT
NumberOfChanges =slGetchangeCount("T")-NumberOfChanges
  IF NumberOfChanges = 10 THEN
    slexe "end"
    s = "Successfully insert" + STR$(NumberOfChanges)
  ELSE
    slexe "rollback"
    s = "Only"+  STR$(NumberOfChanges)+ " records inserted, rollback."
  END IF

  'Display results of insert then number of records in the table
  LOCAL sResult() AS STRING
  slSelAry "select count(*) from t1",sResult(),"Q9c"
  ? s + $CR + "Records in table" + JOIN$(sResult(),$CR)
END FUNCTION



Bern Ertl

It appears as if you forgot to release the exclusive lock in the case that you have to do a rollback.


cj


Bern Ertl

Ah, yes, you are correct.  I had missed that:
Quote...
Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. ...

https://www.sqlite.org/lang_transaction.html