SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Next Highest Row Example  (Read 953 times)

cj

  • Master Geek
  • ****
  • Posts: 700
    • View Profile
Next Highest Row Example
« on: March 03, 2017, 02:59:59 PM »

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
« Last Edit: March 03, 2017, 03:12:29 PM by cj »
Logged