• Welcome, Guest. Please login.
 
August 20, 2019, 04:39:18 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - cj

41
General Board / Lowest of each group example
March 06, 2015, 09:51:30 am
'slSelAry "select team, min(Age), Player from mytable group by Team",sArray(),"Q9"

#DIM ALL
#INCLUDE "sqlitening.inc"  'Youngest player on each team example
'
FUNCTION PBMAIN () AS LONG 'GroupBy2.bas
  LOCAL sArray() AS STRING
  slopen "sample.db3","C"                      'open or create sample.db3
  slexe "drop table if exists mytable"         'drop table if it already exists
  slexe "create table if not exists mytable(Player,Age,Team)" 'create table
  '
  'insert player#, age#, team#
  slexe "insert into mytable values (1,15,2)"  'player 1, age 15, team 2
  slexe "insert into mytable values (2,14,1)"  'player 2, age 14, team 1
  slexe "insert into mytable values (3,13,3)"  'player 3, age 13, youngest team 3
  slexe "insert into mytable values (4,12,1)"  'player 4, age 12, team 1
  slexe "insert into mytable values (5,11,1)"  'player 5, age 11, youngest team 1
  slexe "insert into mytable values (6,10,2)"  'player 6, age 10, youngest team 2
  '
  'Select by lowest age on each team into an array with each column  separated by CHR$(9)
  slSelAry "select team, min(Age), Player from mytable group by Team",sArray(),"Q9"
  '
  'Joins each row/elmement into a string delimited by a carriage return for displaying
  ? JOIN$(sArray(),$CR),,"Youngest player on each team"
'Results:
  'Youngest player on each team
  'Team  min(age)  Player
  '1     11        5
  '2     10        6
  '3     13        3
END FUNCTION             
42
Get columns or rows from a 2D array

slSelAry Sql$,Array$()
sData$ = GetRange2D (FirstColumn&,FirstRow&,LastColumn&,LastRow&,Array$(),ColumnDelimiter$)

#DIM ALL 'GetRange2D.bas

FUNCTION GetRange2DArray (StartColumn AS LONG, _
                     StartRow AS LONG,    _
                     EndColumn AS LONG,   _
                     EndRow AS LONG,      _
                     sArray() AS STRING,  _
                     Column_Delimiter AS STRING) AS STRING

  LOCAL COL,ROW,cols,rows AS LONG
  LOCAL sError AS STRING

  rows = UBOUND(sArray(2))
  Cols = UBOUND(sArray(1))
  'check bounds
  IF rows < 1 THEN ? "No data in array passed",%MB_ICONERROR,FUNCNAME$:EXIT FUNCTION
  IF StartRow <1 OR  StartRow > rows THEN sError = "StartRow is out of range" + $CR
  IF EndRow   <1 OR  EndRow   > rows THEN sError+= "EndRow is out of range"   + $CR
  IF StartColumn <1 OR  StartColumn > cols THEN sError+= "StartColumn is out of range" + $CR
  IF EndColumn < 1 OR EndColumn   > cols THEN sError+= "EndColumn is out of range"
  IF LEN(sError) THEN ? sError,%MB_ICONERROR,FUNCNAME$:EXIT FUNCTION

  LOCAL sb AS ISTRINGBUILDERA  'much more efficient
  sb = CLASS "StringBuilderA"
  FOR ROW = StartRow TO EndRow
    FOR COL = StartColumn TO EndColumn
      sb.add sArray(COL,ROW)
      sb.add Column_Delimiter  'separate each column
    NEXT
    sb.char(sb.len) = 13  '$CR on end of line
  NEXT
  FUNCTION = sb.string
END FUNCTION

FUNCTION PBMAIN () AS LONG  'range.bas
  LOCAL sDelimit, sResult AS STRING
  DIM sRecordSet(3,4) AS STRING   'columns,rows

  sRecordSet(1,1) = "1": sRecordSet(2,1) = "2": sRecordSet(3,1) = "3"  'row 1
  sRecordSet(1,2) = "4": sRecordSet(2,2) = "5": sRecordSet(3,2) = "6"  'row 2
  sRecordSet(1,3) = "7": sRecordSet(2,3) = "8": sRecordSet(3,3) = "9"  'row 3
  sRecordSet(1,4)= "10":sRecordSet(2,4) = "11": sRecordSet(3,4) = "12" 'row 4
  sDelimit = $TAB
  ? GetRange2DArray(1,1,3,4,sRecordSet(),sDelimit),,"All"
  ? GetRange2DArray(3,1,3,4,sRecordSet(),sDelimit),,"Column 3"
  ? GetRange2DArray(1,2,3,2,sRecordSet(),sDelimit),,"Row 2"
END FUNCTION               
       
43
You've got Questions? We've got Answers! / ddoc sample
December 09, 2014, 03:28:10 pm
Note:  The ddoc sample Test32P.bas only needs this modification to work with PBWin10.
It has far more functionality than my posted example.

FUNCTION WINMAIN ( _
   BYVAL hInstance   AS DWORD, _
   BYVAL hPrevInst   AS DWORD, _
   BYVAL lpszCmdLine AS WSTRINGZ PTR, _
   BYVAL nCmdShow    AS LONG ) AS LONG 





Here is a very simple test using ddoc to print a recordset in an array.
The height of the document in inches is added after each line to show when a new page is created.


#COMPILE EXE   'ddoctest.bas
#DIM ALL
#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"

FUNCTION PBMAIN () AS LONG
  LOCAL rs() AS STRING
  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,TheBin,TheStyle,DefaultZoom,NoDialog,x,EndCode,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL OutputFile AS ASCIIZ * 64
  LOCAL DocTitle AS ASCIIZ * 64
  LOCAL height,lineheight,LeftMargin,topmargin AS SINGLE
  LOCAL TheText AS ASCIIZ * 300
  LOCAL DefaultFont AS ASCIIZ * 64

  slopen "sample.db3","C"
  slexe  "drop table if exists t1"
  slexe "create table if not exists t1(f1)"
  FOR x = 1 TO 62  'insert 62 lines
    slexe "insert into t1  values('Line" + STR$(x) + "')"
  NEXT
  slselAry "select * from t1",rs(),"Q9c"
  IF UBOUND(rs) < 1 THEN ? "No data",,EXE.NAME$:EXIT FUNCTION
  '? Join$(rs(),$CRLF),,"Data in array"


  PageHeightMaximum = 10.1  'inches

  'DefaultZoom = %DDOC_ZOOM100
  'DefaultZoom = %DDOC_ZOOM75
  defaultzoom = %DDOC_ZOOMFIT
  defaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  topmargin= .25
  lineheight = .1666 '1/6  'each line height
  'outputfile ="cj.ddc"  'if multiuser would need unique names
  doctitle = "Test"

  'NoDialog = 1 'print direct with no dialog

  IF NoDialog THEN
    endCode = %DDOC_END_PRINT + %DDOC_END_DELETE
    DefaultZoom+= %DDOC_VIEWBUILD 'so no dialog will appear
  ELSE
    endCode = %DDOC_END_VIEW + %DDOC_END_DELETE

  END IF
  'outputfile = "MyFile.DDC" 'optionally name .DDC files and delete when done

  height = topmargin

  ihandle = dpStartDoc(0,_
                       DocTitle, _
                       outputfile,_
                       %DDOC_INCH,_
                       %DDOC_PAPER_LETTER,_
                       %DDOC_SYSTEM_DEFAULT, _
                       TheBin,_
                       DefaultZoom& + %DDOC_VIEWBUILD)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  outputfile + " already exists so will delete document",,"dpStartDoc"
        KILL outputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF
  'if you let ddoc create the outputfile and need its name
  'dpGetFileName iHandle, outputfile, 64: ? outputfile,,"DDoc outputfile"

  IF FontSize THEN
     dpFont iHandle, FontStyle,FontSize,DefaultColor,DefaultFont$
  ELSE
     dpFont iHandle, FontStyle,12,DefaultColor,DefaultFont$
  END IF

  FOR x = 1 TO UBOUND(rs)
    TheText = rs(x) + " current height" + STR$(height)
    GOSUB PrintDetailLine
    IF height => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      height = topmargin  'start new page
    END IF
  NEXT
  IF NODIALOG THEN endCode = %DDOC_END_PRINT_NODIALOG
  'IF DpSpecifyPrinter(Ihandle,zPrinterName)THEN endcode% = %DDOC_END_SPECIFIC_PRINTER
  SLEEP 200
  dpEndDoc iHandle, EndCode
EXIT FUNCTION

PrintDetailLine:
  dpText iHandle,LeftMargin, height,%ddoc_left,TheText
  height+= lineheight
RETURN
END FUNCTION
44
General Board / Bind RowId
October 14, 2014, 07:09:30 am
'IF slExeBind uses 'NULL' as parameter for rowid as in (null,?,?) the rowid cannot be manually added.
'To get around this the function BindRowID is below.

MACRO JustBind(sData)        = slBuildBindDat(sData)       'no compression or encryption
#INCLUDE "sqlitening.inc"                                  'sqlitening routines
FUNCTION PBMAIN&                                           '
  DropTable& = 0                                           'non-zero to drop table
  sID$      = "null"                                       'pass number or nothing/null for next highest
  sColumn2$ = "Jane"                                       'data to insert into column one
  sColumn3$ = "Dough"                                      'data to insert into column two
  slOpen "sample.db3","C"                                  'open sample database
  IF DropTable& THEN slexe "drop table if exists t1"       'drop table if set
  slexe "create table if not exists t1(ID INTEGER PRIMARY KEY AUTOINCREMENT, FNAME,LNAME)" 'table with 2 columns
  sBindData$ = BindRowId(sID$) + JustBind(sColumn2$) + JustBind(sColumn3$)
  slExeBind "Insert into t1 values(?,?,?)",sBindData$        'insert bind data
  slselAry "select * from t1",sRecordSet$(),"Q9"     'select into sRecordSet() array
  MSGBOX JOIN$(sRecordSet$(),$CRLF),,"Join all rows"       'display recordset array
END FUNCTION

FUNCTION BindRowID(sNumber AS STRING) AS STRING
  IF LEN(sNumber) = 0 OR UCASE$(sNumber) = "NULL" THEN
    FUNCTION = slBuildBindDat("","Z")      'bind as null if nothing passed
  ELSE
    FUNCTION = slBuildBindDat(sNumber,"T") 'bind as text if a number is passed
  END IF
END FUNCTION
                                            '
'If using both encryption and compression
'MACRO CompressEncrypt(sData) = slBuildBindDat(sData,"CN")  'compress/encrypt
'MACRO Compress(sData)        = slBuildBindDat(sData,"C")   'compress
'MACRO Encrypt(sData)         = slBuildBindDat(sData,"N")   'encrypt
'slSetProcessMods "K" + SPACE$(32)                         'required encrption key
'Q one-dimension 9=tab delimited  U1,2 uncompress columns 1,2    D1,2 Decrypt columns 1,2
'slselAry "select rowid, * from t1",sRecordSet$(),"Q9  U1,2  D1,2" 'include c for no column names
45
I know there is a post about binding select statements, but cannot find it.
Is anyone doing this?
46
Each line must end with a semicolon.
All entries are automatically wrapped within a transaction.
You have the source so anything can be changed.

Any number of lines can be executed as long as they end with a semicolon.
Even end -- comment lines with a semicolon so execution continues.

Suggest compiling to execute.exe in sqlitening server bin folder so all DLL's are available.
IP address and port are optional.

Comments/suggestions are welcome!

The source code is in PBWIN 10.   A VB6 version could be created if anyone is interested.

Uploaded Execute.Zip which contains
execute.bas
execute.exe


Just added ALL.ZIP which contains:
execute.bas
execute.exe
sample.db3
sqlite3.dll
sqlitening.dll


If you have any problem running it, please let me know.

47
#DIM ALL
$RefreshFactString = CHR$(15,0,0,0,0,23,0,1,0,0,0,0,0,0,0)
'---------------------------------------------------------
FUNCTION PBMAIN AS LONG
  LOCAL result,PortNumber AS LONG
  LOCAL sIpaddress AS STRING
  PortNumber = 51234
  sIpAddress = "192.168.1.2"
  result = RefreshFACT(PortNumber&,sIpAddress)
  IF result = 0 THEN ? "Fact refreshed",,"Success"
END FUNCTION
'---------------------------------------------------------'
FUNCTION RefreshFACT(PortNumber AS LONG,_
                     sIpAddress AS STRING) AS LONG
  LOCAL hTCP AS LONG
  hTCP = FREEFILE
  TCP OPEN PORT PortNumber AT sIpAddress AS #hTCP
  IF ERR THEN
    FUNCTION = ERR
    EXIT FUNCTION
  END IF
  TCP SEND #hTCP, $RefreshFactString
  IF ERR THEN
     FUNCTION = ERR
     EXIT FUNCTION
  END IF
  TCP CLOSE #hTCP 'success, return 0
END FUNCTION
48
Shows how database handle can be saved so database is not reopened with a new handle using slOpen.


#DIM ALL    'Test slPushDataBase/slPopDataBase
#INCLUDE "sqlitening.inc"
#INCLUDE "win32api.inc"
%Databases=3
%NumberOfTests=3

FUNCTION PBMAIN () AS LONG
  DIM sArray() AS STRING, sDBHandle() AS STRING, s AS STRING
  DIM counter AS LONG, dbNumber AS LONG
  DIM sDBHandle(%DataBases)
  FOR counter = 1 TO %NumberOfTests
    FOR dbNumber = 1 TO %Databases
      IF counter = 1 THEN                   'first time
        slOpen "DB"+ FORMAT$(dbNumber),"C"  'open database
        slExe "Drop Table if exists t"      'drop table in each DB, optional
        slPushDataBase sDBHandle(dbNumber)  'push handle
        slPopDataBase  sDBHandle(dbNumber)  'set current db handle
      ELSE
        slPopDataBase  sDBHandle(dbNumber)  'set current db dhandle
      END IF
      slExe "Create Table if not exists t(Column_One)"
      slexe "Insert into t values('Insert - Hello, world! from table t of database  DB" + FORMAT$(dbNumber) + "')"
    NEXT
  NEXT
  FOR dbNumber = 1 TO %DataBases
    slPopDataBase sDBHandle(dbNumber)        'set current db handle
    slSelAry "Select * from t", sArray(), "Q9c"
    s = s +  JOIN$(sArray(),$CRLF) + STRING$(2,$CRLF)
  NEXT
  slClose  'close last DB used
  ? s,, "Done, closed current database" + STR$(%DataBases)

  IF %DataBases > 1 THEN  'prove DB1 open
    slPopDataBase sDbHandle(1)
    slExe "Insert into t values('Insert - Bye world! in DB1')"
    slSelAry "Select * from t", sArray(), "Q9c"
    ? JOIN$(sArray(),$CRLF),, "Prove DB1 is still open"
  ELSE
    ? "No databases open",,"Bye"
  END IF

END FUNCTION
49
Somebody could be updating the database while you are creating a recordset.
Should BEGIN IMMEDIATE be used before all select statements?
result = slSel(SQLStatement,0,"B1")     
50
Private Declare Function SendMessage Lib "user32" _
      Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
      ByVal wParam As Long, lParam As Any) As Long

SendMessage Text1.hwnd, &HC, 0&, ByVal sResult
51
If an attempt to connect fails the system is set to REMOTE mode and any attempt
to open a local database will fail unless slSetProcessMods "L0" is issued to return to LOCAL mode.

Error -18 TCP send/receive errors will be returned trying to work local if in remote mode and not connected.

#COMPILE EXE "\sql\bin\testconnect"
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
  slSetProcessMods "E0"
  IF slConnect("bad address") THEN
    ? "Connected failed so change to local mode"
    slSetProcessMods "L0"
  ELSE
    ? "Connected"
  END IF
  IF slOpen("sample.db3") THEN
    ? "Database error " + slGetError
  ELSE
    ? "Database opened"
  END IF
  SLEEP 5000
END FUNCTION


52
General Board / Array to disk
March 18, 2014, 07:19:05 pm
Sometimes it is nice to be able to view or email results to someone.
This takes a recordset which is in an array and saves to a file and
optionally shells to the output file.  I use .TXT so NOTEPAD views it.

#INCLUDE "sqlitening.inc"   'ArrayToDisk.bas
#INCLUDE "win32api.inc"

FUNCTION PBMAIN ()
  LOCAL sArray() AS STRING  'Q9c would not include element 0, column names
  slOpen "sample.db3"
  slSelAry "select rowid,product from parts order by rowid desc", sArray(), "Q9"
  ArrayToDisk sArray(), "JUNK.TXT", 1
END FUNCTION

FUNCTION ArrayToDisk(sArray() AS STRING, sFileName AS STRING, OPTIONAL ViewFile AS LONG) AS LONG
  LOCAL hFile AS LONG
  hFile = FREEFILE
  OPEN sFileName FOR OUTPUT AS #hFile
  IF ERR THEN FUNCTION = ERR:BEEP:EXIT FUNCTION
  PRINT #hFile, sArray()
  IF ERR THEN BEEP:FUNCTION = ERR
  CLOSE #hFile
  IF ERR = 0 AND ISMISSING(ViewFile) = 0 THEN
     SLEEP 500
     ShellIt sFileName
  END IF
END FUNCTION

SUB Shellit(sFileName AS STRING)
  ShellExecute BYVAL 0, "OPEN", sFileName+$NUL, BYVAL 0, $NUL, BYVAL 1
END SUB
53
slExeBind "Insert into T1 values(?, ?, ?, ?)", _
   slBuildBindDat("This is some Blob data") & _
   slBuildBindDat("This is some compressed Text", "TC") & _
   slBuildBindDat("This is a compressed and encrypted Blob", "CE") & _
   slBuildBindDat("123.456", "D")
54
Is there a way to examine a recordset to see which columns are encrypted or compressed?

Adding rowid or count(*)  to a SQL statement requires using SLF for that column where encrypted
or compressed columns might use SLFX.   This requires knowing in advance what columns will be
returned in what format and coding IF or CASE logic to handle each case.

55
select rowid from t1 order by rowid
changed to:
select rowid from t1
Thank you, Bern!  My example should not have had the order by statement in it below.

Would someone please show where in the SQLite documentation it shows
the rowid's  returned may be of the last indexed column using SELECT ROWID from T1?

This pretty much answers the question that "order by rowid"  should be used.
http://www.sqlite.org/queryplanner.html#sorting

#DIM ALL       'RowIDOrder.bas
'http://www.sqlite.org/queryplanner.html#sorting
GLOBAL gs AS STRING 'accumulate results
#INCLUDE "\sql\inc\sqlitening.inc"
FUNCTION PBMAIN AS LONG
  slOpen "Test.db3","C"
  slExe "drop table if exists t1"
  slExe "create table if not exists t1(f1 integer primary key, f2 unique, f3)"
  slExe "insert into t1 values(null, 3, 'C' )"
  slExe "insert into t1 values(null, 1, 'B' )"
  slExe "insert into t1 values(null, 2, 'A' )"
  SQL   "select rowid from t1",1   'order by rowid removed 8/10/13
END FUNCTION

FUNCTION SQL(SqlStatement AS STRING, ShowResults AS LONG) AS STRING
LOCAL columns,x AS LONG
gs = gs + SQLStatement + $CR
slSel SqlStatement
columns = slGetColumnCount
DO WHILE slGetRow
  FOR x = 1 TO Columns
    gs = gs + slf(x) + ","
   NEXT
  ASC(gs,LEN(gs)) = 13
LOOP
gs = gs + "------------------" + $CR
IF ShowResults THEN ? gs
FUNCTION = gs
END FUNCTION
56
Please post any comments/suggestions.

Eliminate calling slBuildInsertOrUpdate, slBuildBindDat and slExeBind.
Those functions are called automatically within InsertRecord and UpdateRecord.

Calling syntax:
InsertRecord sTableName$, sInsertData$(),sInsertMod$()
UpdateRecord sTableName$,sColumnsToUpdate$,sUpdateData$(),sUpdateMod$(), sWhere$

The data for each column goes into either sInsertData$() or sUpdateData$().

If performing an update:
Columns names are separated by commas in sColumnsToUpdate$.
Example: sColumnsToUpdate$ =  "F1,F2,F3,Client,Invoice"

2 arrays should be REDIMed before each call (this may be improved upon.)
NumberOfColumnsToUpdate = 2  'change this
REDIM sUpdateData(NumberOfColumnsToUpdate)AS STRING
REDIM sUpdateMod(NumberOfColumnsToUpdate) AS STRING

Flags are set for each column by the elements in the arrays sInsertMod$() and sUpdateMod$().
sUpdateMod$(1) =  "TCN"  column 1 is  text/compress/encrypt.
sUpdateMod$(2) = " "T"     column 2 is text.

To decrypt/uncompress recordsets use SQLitening routines slFX, slFNX or slSelAry.
SlSelAry requires all rows for all columns used the same compression/encryption.


FUNCTION InsertRecord(sTableName AS STRING, sInsertData() AS STRING,sInsertMod() AS STRING) AS LONG
  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING
  NumberOfColumns = UBOUND(sInsertData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sInsertData(COL),sInsertMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind slBuildInsertOrUpdate(sTableName,sPlaceHolders),sBindData
END FUNCTION


FUNCTION UpdateRecord(sTableName       AS STRING, _
                      sColumnsToUpdate AS STRING, _
                      sUpdateData()    AS STRING,    _
                      sUpdateMod()     AS STRING, _
                      sWhere           AS STRING) AS LONG
  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING
  NumberOfColumns = UBOUND(sUpdateData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sUpdateData(COL),sUpdateMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind(slBuildInsertOrUpdate(sTableName,sPlaceHolders,sColumnsToUpdate,sWhere),sBindData)
END FUNCTION 


Here is a demo with help after the code on modchars, compression and encryption.
[code]
#DIM ALL
#INCLUDE "sqlitening.inc"  'binding.bas
%DropTable = 0
FUNCTION PBMAIN () AS LONG

  LOCAL sDatabaseName, sTableName,sData,sColumnsToUpdate,sResult,sWhere,sIp AS STRING
  LOCAL TotalColumns,NumberOfColumnsToUpdate,x,PortNumber AS LONG

  '-- Define encryption key  ------------------------------------
  LOCAL sEncryptionKey AS STRING * 16 '16,24, or 32
  sEncryptionKey = "VALID"
  slSetProcessMods "K" + sEncryptionKey 'do not combine Mods

  '-- Set Database name, table name, option IP address ----------
  sDatabaseName = "Test.db3"
  sTableName     = "T1"
  sIp            = "192.168.1.2"   'optional IP address or computer name
  sIp            = "174.74.114.227" 'optional IP address or computer name
  PortNumber     = 0       '0 = default (normally 51234)

  '-- Connect to server if sIP defined --------------------------
  IF LEN(sIp) THEN  slConnect sIp,PortNumber 'internet

  '--  Open database ---------------------------------------------
  slOpen sDatabaseName,"C"
top:
  '-- Drop previous table (optional) ----------------------------
  IF %DropTable THEN slExe "Drop Table if Exists " + sTableName

  '-- Create table if it does not exist -------------------------
  slExe "Create Table If Not Exists " + sTableName + "(F1,F2)"

  '-- Insert record ---------------------------------------------
  TotalColumns = 2
  REDIM sArray(1 TO TotalColumns) AS STRING
  REDIM sInsertMod(1 TO TotalColumns) AS STRING
  FOR x = 1 TO TotalColumns
    sArray(x) = "(New column "+FORMAT$(x) + ")"
    sInsertMod(x) = "TCN"  'Text,compress,encrypt
  NEXT
  InsertRecord sTableName, sArray(),sInsertMod()
  sResult = "After insert:" + $CR + SQL("Select * from " + sTableName)

  '-- Update all columns ----------------------------------------
  NumberOfColumnsToUpdate = 2 'REDIM update arrays
  REDIM sUpdateData(1 TO NumberOfColumnsToUpdate)    AS STRING
  REDIM sUpdateMod(1 TO NumberOfColumnsToUpdate) AS STRING
  sUpdateData(1) = "(1)"
  sUpdateData(2) = "(2)"
  sUpdateMod(1) = "TCN"
  sUpdateMod(2) = "TCN"
  sColumnsToUpdate = "F1,F2"
  sWhere = "*"
  UpdateRecord sTableName,sColumnsToUpdate,sUpdateData(),sUpdateMod(), sWhere
  sResult = sResult + $CR + "After two column update:" + $CR + SQL("Select * from " + sTableName)

  '-- Update only one column ------------------------------------
  NumberOfColumnsToUpdate = 1 'REDIM update arrays
  REDIM sUpdateData(1 TO NumberOfColumnsToUpdate)    AS STRING
  REDIM sUpdateMod(1 TO NumberOfColumnsToUpdate) AS STRING
  sUpdateData(1) = "(Column 2 only updated)"
  sUpdateMod(1) = "TCN"
  sColumnsToUpdate = "F2"
  sWhere = "*"
  UpdateRecord sTableName,sColumnsToUpdate,sUpdateData(),sUpdateMod(), sWhere
  sResult = sResult + $CR + "After one column update:" + $CR + SQL("Select * from " + sTableName)
  x = MSGBOX(sResult,%MB_YESNOCANCEL,"[Y]es=Rerun   [N]o=Exit   Cancel=Drop table/rerun")
  IF x = %IDYES THEN
     GOTO TOP  ' ALT/Y rerun ALT/N exit (if options go off the screen)
  ELSEIF x = %IDNO THEN
    EXIT FUNCTION
  ELSEIF x = %IDCANCEL THEN
    slExe "Drop Table if Exists " + sTableName
    GOTO TOP
  END IF

  IF LEN(sIp) THEN slDisconnect
END FUNCTION
  '--------------------------------------------------------------
FUNCTION InsertRecord(sTableName AS STRING, sInsertData() AS STRING,sInsertMod() AS STRING) AS LONG

  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING

  NumberOfColumns = UBOUND(sInsertData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sInsertData(COL),sInsertMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind slBuildInsertOrUpdate(sTableName,sPlaceHolders),sBindData
END FUNCTION
   '--------------------------------------------------------------
FUNCTION UpdateRecord(sTableName       AS STRING, _
                      sColumnsToUpdate AS STRING, _
                      sUpdateData()          AS STRING,    _
                      sUpdateMod()         AS STRING, _
                      sWhere           AS STRING) AS LONG

  LOCAL COL, NumberOfColumns AS LONG
  LOCAL sPlaceHolders, sBindData AS STRING

  NumberOfColumns = UBOUND(sUpdateData)
  DIM sArray(1 TO NumberOfColumns) AS STRING
  FOR COL = 1 TO NumberOfColumns
    sArray(COL) = slBuildBindDat(sUpdateData(COL),sUpdateMod(COL))
  NEXT
  sBindData = JOIN$(sArray(),"")
  sPlaceHolders=REPEAT$(NumberOfColumns-1,"?"&$NUL) + "?"
  slExeBind(slBuildInsertOrUpdate(sTableName,sPlaceHolders,sColumnsToUpdate,sWhere),sBindData)
END FUNCTION
  '--------------------------------------------------------------
FUNCTION SQL(SqlStatement AS STRING) AS STRING
LOCAL columns,x AS LONG, s AS STRING
slSel SqlStatement: columns = slGetColumnCount
DO WHILE slGetRow
  FOR x = 1 TO Columns
    s = s + slfx(x,"DU") + ","  'slfx decrypt/uncompress
   NEXT
  ASC(s,LEN(s)) = 13            '$CR after each row
LOOP
FUNCTION = s
END FUNCTION
'Calling syntax:
'InsertRecord sTableName$, sInsertData$(),sInsertMod$()
'UpdateRecord sTableName$,sColumnsToUpdate$,sUpdateData$(),sUpdateMod$(), sWhere$

'See remarks after code on compression and encryption.
'Flags are set for each column by the elements in the arrays sInsertMod() and sUpdateMod$().
'sUpdateMod(1) =  "TCN"  column 1 is  text/compress/encrypt.
'sUpdateMod(2) = " "T"     column 2 is text.

'To decrypt/uncompress data see SQLitening routines slFX and slFNX.

'
'FUNCTION InsertRecord/UpdateRecord call slBuildBindDat to produce the specially
'formatted string for each column of the record. Supply the letters in the element
'InsertMod() or UpdateMod() for each column in the record.
'--
'Calling Insert/Record/UpdateRecord does the following automatically:

'Returns aBindDat entry specially formatted string required by slExeBind.
'Data contains the value you want converted into a BindDat. A BindDat(s) is required
'to be passed to slExeBind. The returned data may also be compressed and/or encrypted.
'If an error occurs then the return value will be an empty string.
'Use slGetError or slGetErrorNumber to determine the error.

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

#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
58
Processing arrays is simple with SQLitening.
Just trying to make it a bit easier to understand using 1 or 2 dimensional arrays.
Discussion welcome.

SUB QueryArray
  DIM lsaColsRows(0) AS STRING
  ModChars = "Q9" 'Q one-dimensional array, delimit with 9/$TAB
  ModChars = ""   'no Q parameter so make 2-dimensional
  slSelAry "Select * from Customers", lsaColsRows(),ModChars
  ReadArray lsaColsRows(),ModChars
END SUB       

SUB ReadArray(lsaColsRows() AS STRING, ModChars AS STRING)
  'Process recordset created by slSelAry
  'slSelAry "Select * from Customers", lsaColsRows(),ModChars
  'If Q parameter in ModChars it is a  1-dimensional array

  LOCAL sLine,sAllLines AS STRING
  LOCAL Rows,r,Cols,c AS LONG
  IF INSTR(ModChars,"Q") THEN  '1-dimensional array was passed
    Rows = UBOUND(lsaColsRows)
    FOR r = 1 TO Rows
      sAllLines = sAllLines & lsaColsRows(r) + $CR
    NEXT
    MSGBOX "ModChars " & $DQ & ModChars & $DQ & " so 1-dimensional." & $CR & $CR & sAllLines,,"ReadArray"
  ELSE
    Rows = UBOUND(lsaColsRows(2))
    Cols = UBOUND(lsaColsRows(1))
    FOR r = 1 TO Rows
      sLine = ""
      FOR c = 1 TO Cols
         sLine = sLine & lsaColsRows(c, r) & $TAB
      NEXT
      sAllLines = sAllLines & LEFT$(sLine,-1) & $CR
    NEXT
    MSGBOX "ModChars " & $DQ & ModChars & $DQ & " no Q so 2-dimensional." & $CR & $CR & sAllLines,,"ReadArray"
  END IF

END SUB     
59
Service only starts automatically if started using Computer Management console.
Is this normal?  It was set to automatic, but rebooting does not start the server.
I restarted server from Comptuer Management console and now reboots restart the server.

Note,  Windows updates has somehow changed my settings to automatically update at 3AM.
I had it set to not automatically update.

Another thing,  Windows Explorer 10 now has an option in the about box to automatically update.
I have unchecked it.   Never want the server to automatically reboot.  See Tools, About.
60


#COMPILE EXE  "\sql\bin\memory"   'memory.bas
#DIM ALL
#INCLUDE "\sql\inc\sqlitening.inc"
FUNCTION PBMAIN () AS LONG
  LOCAL sb AS ISTRINGBUILDERA
  LOCAL rows,cols,ROW,COL AS LONG
  DIM s(0) AS STRING
  sb = CLASS "StringBuilderA"
  slOpen "WORDS.DB3","C"         'open database, create if not exists
  sb.clear
  sb.add "Begin;"
  sb.add "Drop Table if Exists Word_Table;"
  sb.add "CREATE TABLE IF NOT EXISTS WORD_TABLE (WORD_FIELD UNIQUE COLLATE NOCASE NOT NULL ON CONFLICT IGNORE);"
  sb.add "INSERT INTO WORD_TABLE VALUES('A');
  sb.add "INSERT INTO WORD_TABLE VALUES('A');
  sb.add "END;"
  slexe sb.string
  slSelAry "SELECT * FROM WORD_TABLE",s()
  rows = UBOUND(s(2))
  Cols = UBOUND(s(1))
  IF cols = 0 THEN ? "no columns":EXIT FUNCTION
  sb.clear
  FOR ROW = 1 TO Rows
    FOR COL = 1 TO cols
      sb.add s(COL,ROW)
    NEXT
    sb.add $CRLF
  NEXT
  ? sb.string,,"Words" + STR$(Rows)
  slDisconnect
END FUNCTION