Demonstrate creating up to 999 columns and use slExeBind

[cj]

'Create a table with 1 to maximum of 999 columns
'Demonstrate slExeBind

Code Select Expand


#COMPILE EXE"\sql\bin\universal.exe"
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"
THREADED threaded_field AS STRING
%Display_Results_In_MessageBox = 1 '0 = only count rows

FUNCTION PBMAIN () AS LONG
  LOCAL sDataBase,sTableName,sFields,sData,s,sIpAddress AS STRING
  LOCAL ColumnNumber, NumberOfColumns,CreateIfNotExists,DropPreviousTable AS LONG
  LOCAL x,Insert_This_Many_Rows,columns,PortNumber AS LONG

  '-------------- Modify these variables to test ---------------------------------
  'Results are displayed in a message box so
  Insert_This_Many_Rows = 2
  NumberOfColumns       = 999 '1 to 999, >999 = too many SQL variables error
  'sIpAddress = "192.168.1.2":PortNumber = 51234 'optional client/server

  sDataBase             = "junk.db3"
  sTableName            = "universal"
  CreateIfNotExists     = 1   '1=create table if none found
  DropPreviousTable     = 1   'get rid of previous table
  '--------------------------------------------------------------------------------

  IF LEN(sIpAddress) THEN
    slConnect sIpAddress,PortNumber 'client/server
  END IF
  slopen sDataBase,"C"
  CreateTable sTableName,NumberOfColumns,CreateIfNotExists,DropPreviousTable
  'create the field statement only once and support multithreading
  threaded_field = REPEAT$(NumberOfColumns-1,"?"+$NUL) + "?"  'field statement
  if Insert_This_Many_Rows > 1 THEN
    slExe "BEGIN IMMEDIATE"
  END IF
  FOR x = 1 TO Insert_This_Many_Rows
    sData = ""
    'This is where you put each column into database
    FOR ColumnNumber = 1 TO NumberOfColumns            '
      sData =  sData & slBuildBindDat(FORMAT$(x), "T")' & slBuildBindDat("two","T")
    NEXT
    Bind sTableName,sData 'insert row
  NEXT
  IF Insert_This_Many_Rows > 1 THEN
    slExe "END"
  END IF
  IF %Display_Results_In_MessageBox THEN
    slSel "Select * from " + sTableName
  ELSE
    slSel "Select count(*) from " + sTableName
  END IF
  columns = slGetColumnCount
  DO WHILE slGetRow
    FOR x = 1 TO Columns
      s = s + slf(x) + ","
    NEXT
    ASC(s,LEN(s)) = 13 'replace last , with $CR
  LOOP

  IF LEN(sIpAddress) THEN
    ? s,,"Client/server at " + sIpAddress + " on port" + STR$(PortNumber)
  ELSE
    ? s,,EXE.FULL$
  END IF

END FUNCTION

SUB CreateTable(sTableName AS STRING,    _
                NumberOfColumns AS LONG, _
                IfNotExists AS LONG,     _
                DropPreviousTable AS LONG)
  LOCAL x AS LONG, sCols AS STRING
  IF NumberOfColumns < 1 THEN
    ? "Number of columns only " + STR$(NumberOfColumns)
    EXIT SUB
  END IF
  IF DropPreviousTable THEN
    slexe  "drop table if exists " + sTableName
  END IF

  FOR x = 1 TO NumberOfColumns
    sCols = sCols + "F" + FORMAT$(x)+","
  NEXT
  sCols = LEFT$(sCols,-1)
  IF IfNotExists THEN
    slexe "create table if not exists " + sTableName + "(" + sCols + ")"
  ELSE
    slexe "create table "               + sTableName + "(" + sCols + ")"
  END IF
END SUB

FUNCTION Bind(sTable AS STRING, sData AS STRING) AS LONG
  'threaded_field is a threaded variable so multithreading is supported
  'and eliminate passing or create ? + $NUL multiple times
  slExeBind(slBuildInsertOrUpdate(sTable, threaded_field),sData)
END FUNCTION