• Welcome, Guest. Please login.
 
August 20, 2019, 04:49:11 am

News:

Welcome to the SQLitening support forums!


Next Highest Row Example

Started by cj, March 03, 2017, 04:29:59 pm

Previous topic - Next topic

cj

March 03, 2017, 04:29:59 pm Last Edit: March 03, 2017, 04:42:29 pm by cj
Demonstrate get and insert "next highest row" in a single sql statement
It may be useful to know what the new highest row will be before inserting a record

Handles problem getting correct next highest record of empty table using coalesce
If multi-user/threaded call within a transaction so next highest row is locked

#INCLUDE "sqlitening.inc" 'InsertNextHighestRow.Bas

FUNCTION PBMAIN () AS LONG
slOpen "cj.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists T1(C1 Integer Primary Key AutoIncrement,C2)"

DO
  REDIM sArray(0) AS STRING
  slSelAry "select * from T1",sArray$(),"Q9c E0"
  IF slGetErrorNumber = 0 THEN sResult$ = JOIN$(sArray$(),$CR) ELSE ? slGetError,,"slSelAry"
  IF MSGBOX(sResult$,%MB_YESNO,"Do you want to insert record " + GetNextHighestRow) <> %IDYES THEN
    EXIT DO
  END IF
  slexe "insert into T1 values(null,(select 'Something '||(COALESCE(max(C1),0)+1) from T1))","E0"
  IF slGetErrorNumber THEN ? slGetError,,"Insert error"
LOOP

END FUNCTION

FUNCTION GetNextHighestRow AS STRING
slsel "select COALESCE(max(C1),0)+1 from T1",0,"E0"
IF slGetErrorNumber = 0 THEN
  slGetRow
  FUNCTION = slf(1)
ELSE
  ? slGetError,,LCASE$(FUNCNAME$)
END IF
END FUNCTION