SQLitening Support Forum

Now there is only the general board.  No email alerts (for others.)

Please be sure RECEIVE EMAILS AND ALERTS is toggled on in each forum and your email address is current.
Instead of taking minutes to contact you it takes weeks or months.

Append host port on target line of shortcut to program



Example 1: ip address
c:\sql\bin\program.exe 192.168.0.2 12345

Example 2: computer name
c:\sql\bin\program.exe computername 12345

Example 3: Dynamic ipaddress using freemyip.com
c:\sql\bin\program.exe cool.freemyip.com 12345




#INCLUDE ONCE "sqlitening.inc"  'program.exe
FUNCTION PBMAIN  AS LONG
 IF LEN(COMMAND$) THEN slConnect COMMAND$(1),VAL(COMMAND$(2)) 
END FUNCTION

Hopefully, everyone should turn on the option "RECEIVE EMAILS AND ALERTS".
Click on "Not Following" and change it.  There is a button to do this for each forum.
Also sent an private message.

Fredrick,
I only needed to use task scheduler on my Windows 11 machine.
The sqliteningserver.exe service auto starts on other computers.

I wondered why I wasn't receiving email alerts.
The button has to be changed to receive email alerts in upper right corner.

I have not yet received a notification.

Example: Get every 7th row
select rowid from parts where rowid%7=0 order by rowid

'https://stackoverflow.com/questions/650480/get-month-from-datetime-in-sqlite

MACRO monthname(ColumnName)="substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m',"+ColumnName+"), -3) month"

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 slopen ":memory:"
 slexe  "create table t1(c1 text)
 slexe  "insert into t1 values(date('now'))"

 MSGBOX slselstr("select " + monthname("c1") + " from t1")
 MSGBOX slselstr("select substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m',c1),-3)as month from t1")

END FUNCTION

This example shows 3-ways to get price column to order by numerically
if printf with "AS" using the "price" column name is used.
1. Use no AS and ignore the long column name
2. Specify parts.price in the order by
3. Use a different name with "AS" than the price column

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN AS LONG
 slOpen "junk.db3"
 slexe  "drop table if exists parts"
 slexe  "create table if not exists parts(price integer)"
 slexe  "insert into parts values(1995),(1300),(4995),(101),(703)"

 'if AS is used with same name as column with printf the order by will be alpha
 rs "select printf('%.2f',price*.01) AS price from parts order by price"

 'these order numerically
 rs "select printf('%.2f',price*.01) from parts order by price"                'no AS
 rs "select printf('%.2f',price*.01) AS price from parts order by parts.price" 'use table.column
 rs "select printf('%.2f',price*.01) AS XYZ from parts order by price"         'AS different
 ? gs
END FUNCTION

FUNCTION rs(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF ISFALSE(slSelAry(sql,sArray(),"Q9 E2")) THEN
  gs+= JOIN$(sArray(),$CR) + $CR + $CR
 ELSE
  gs = "error:" + $CR + sql + $CR + $CR
 END IF
END FUNCTION

#PBFORMS CREATED V2.01
#COMPILE EXE   'Save as "Modeless.bas"  PBFORMS with Jose Roca includes
#DIM ALL
%UNICODE = 1
#INCLUDE ONCE "Windows.inc"
#INCLUDE ONCE "sqlitening.inc"
'------------------------------------------------------------------------------
#PBFORMS BEGIN INCLUDES
#RESOURCE "modeless.pbr"
#PBFORMS END INCLUDES
GLOBAL ghDlg AS DWORD
'------------------------------------------------------------------------------
#PBFORMS BEGIN CONSTANTS
%IDD_DIALOG1 =  101
%LABEL1      = 1001
%TEXTBOX1    = 2001
%BTN_BUTTON1 = 3001
%TEXTBOX2    = 3002
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
FUNCTION PBMAIN()
 ShowDIALOG1 %HWND_DESKTOP
END FUNCTION
'------------------------------------------------------------------------------
CALLBACK FUNCTION ShowDIALOG1Proc()
  SELECT CASE AS LONG CB.MSG
    CASE %WM_INITDIALOG
      ghDlg = CB.HNDL
    CASE %WM_NCACTIVATE
      STATIC hWndSaveFocus AS DWORD
      IF ISFALSE CB.WPARAM THEN
        ' Save control focus
        hWndSaveFocus = GetFocus()
      ELSEIF hWndSaveFocus THEN
        ' Restore control focus
        SetFocus(hWndSaveFocus)
        hWndSaveFocus = 0
      END IF
    CASE %WM_COMMAND
      ' Process control notifications
      SELECT CASE AS LONG CB.CTL
        CASE %BTN_BUTTON1
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
           LOCAL hThread AS LONG
           THREAD CREATE SQLitening(0) TO hThread
           THREAD CLOSE hThread TO hThread
          END IF
      END SELECT
    CASE %WM_DESTROY
      PostQuitMessage 0 'required with modeless Jose Roca message pump
      EXIT FUNCTION
  END SELECT
END FUNCTION
'------------------------------------------------------------------------------
FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
  LOCAL lRslt AS LONG
#PBFORMS BEGIN DIALOG %IDD_DIALOG1->->
  LOCAL hDlg   AS DWORD
  LOCAL hFont1 AS DWORD
  LOCAL hFont2 AS DWORD

  DIALOG NEW hParent, "", 72, 138, 919, 380, %WS_POPUP OR %WS_BORDER OR _
    %WS_DLGFRAME OR %WS_THICKFRAME OR %WS_CAPTION OR %WS_SYSMENU OR _
    %WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_VISIBLE OR %DS_MODALFRAME OR _
    %DS_CENTER OR %DS_3DLOOK OR %DS_NOFAILCREATE OR %DS_SETFONT, _
    %WS_EX_CONTROLPARENT OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR, TO hDlg
  CONTROL ADD BUTTON,  hDlg, %BTN_BUTTON1, "Execute", 5, 360, 50, 15
  CONTROL ADD TEXTBOX, hDlg, %TEXTBOX1, "", 5, 25, 910, 265, %WS_CHILD OR _
    %WS_VISIBLE OR %WS_HSCROLL OR %WS_VSCROLL OR %ES_LEFT OR %ES_MULTILINE _
    OR %ES_AUTOHSCROLL OR %ES_AUTOVSCROLL, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT _
    OR %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD LABEL,   hDlg, %LABEL1, "Modeless DDT with Jose Roca includes", _
    387, 5, 230, 20, %WS_CHILD OR %WS_VISIBLE OR %SS_CENTER, %WS_EX_LEFT OR _
    %WS_EX_LTRREADING
  CONTROL ADD TEXTBOX, hDlg, %TEXTBOX2, "select rowid,* from parts", 5, 300, _
    725, 55, %WS_CHILD OR %WS_VISIBLE OR %WS_TABSTOP OR %WS_HSCROLL OR _
    %WS_VSCROLL OR %ES_LEFT OR %ES_MULTILINE OR %ES_AUTOHSCROLL OR _
    %ES_AUTOVSCROLL OR %ES_WANTRETURN, %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR _
    %WS_EX_LTRREADING OR %WS_EX_RIGHTSCROLLBAR

  FONT NEW "MS Sans Serif", 10, 1, %ANSI_CHARSET TO hFont1
  FONT NEW "Courier New", 12, 0, %ANSI_CHARSET TO hFont2

  CONTROL SET FONT hDlg, %LABEL1, hFont1
  CONTROL SET FONT hDlg, %TEXTBOX2, hFont2
#PBFORMS END DIALOG
  DIALOG SHOW MODELESS hDlg, CALL ShowDIALOG1Proc TO lRslt
  LOCAL uMsg AS tagMsg
  WHILE GetMessage(uMsg, %NULL, 0, 0)
    IF IsDialogMessage(hDlg, uMsg) = 0 THEN
      TranslateMessage uMsg
      DispatchMessage uMsg
    END IF
   WEND '---------- End Jose Roca message pump
#PBFORMS BEGIN CLEANUP %IDD_DIALOG1
  FONT END hFont1
  FONT END hFont2
#PBFORMS END CLEANUP
  FUNCTION = lRslt
END FUNCTION

THREAD FUNCTION sqlitening(BYVAL x AS LONG) AS LONG
 LOCAL s,sql,sdata2,sdata3,sArray() AS STRING
 CONTROL SET TEXT ghDlg,%TEXTBOX1,s
 slOpen  "sample.db3","C"
 CONTROL GET TEXT ghDlg,%TEXTBOX2 TO sql
 IF sql = "select rowid,* from parts" THEN
  sql =   "select rowid as ROW,MANUF,REDREF,PRODUCT,LANGUAGE,CPU_OS,MEDIA,TYPE,PGROUP,printf('%.2f',price*.01) AS PRICE from parts"
 END IF
 slSelAry sql,sArray(),"Q9 E2 "
 REDIM lTabStop(1 TO 9) AS LONG
 lTabStop(1) = 22  'manuf
 lTabStop(2) = 160 'redref
 lTabStop(3) = 200 'product
 lTabStop(4) = 440 'language
 lTabStop(5) = 500 'cpu_os
 lTabStop(6) = 540 'media
 lTabstop(7) = 590 'type
 lTabstop(8) = 640 'pgroup
 lTabstop(9) = 820 'price
 CONTROL SEND ghDlg,%TEXTBOX1,%EM_SETTABSTOPS,UBOUND(lTabStop), VARPTR(lTabStop(1))
 s = JOIN$(sArray(),$CRLF)
 LockWindow
 CONTROL SET TEXT ghDlg,%TEXTBOX1,s                    'fill textbox
 REM CONTROL SEND ghDlg,%TEXTBOX1,%WM_VSCROLL,%SB_BOTTOM,0 'scroll to bottom of textbox
 UnlockWindow
END FUNCTION
'_______________________________________________________________________________________________
FUNCTION rs(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 IF slSelAry(sql,sArray(),"Q9 E2 c") THEN
  ? sql + $CR + slGetError,%MB_SYSTEMMODAL OR %MB_ICONERROR,"Recordset"
 ELSE
  FUNCTION=JOIN$(sArray(),$CR)
 END IF
END FUNCTION
'_______________________________________________________________________________________________
FUNCTION LockWindow AS LONG
  CONTROL SEND ghDlg, %TEXTBOX1,%WM_SETREDRAW,0,0
END FUNCTION

FUNCTION UnlockWindow AS LONG
 CONTROL SEND ghDlg,%TEXTBOX1,%WM_SETREDRAW,1,0
END FUNCTION

Forum: https://sqlite.org/forum/forum
First post about SQLitening, by anyone,  on SQLite forum:  https://sqlite.org/forum/forumpost/a0a775c72e
Someone asked about client/server for SQLite and I couldn't help myself.

This demonstrates how multiple global handles are incorrectly created (programming error) for the current database.
Notice the database cannot close correctly or be killed if this is done.  Permission denied error.
Calling slOpen many times without closing it will eventually produce an error 7.

GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
slSetProcessMods "E2"
LOCAL x AS LONG
Logit "Open database only if not open"
FOR x = 1 TO 5
  IF ISFALSE(slIsOpen) THEN slopen "junk.tmp","C": Logit USING$("handle #",slGetHandle)
NEXT
slClose
KILL "junk.tmp"
Logit  ERROR$(ERR) + " killing junk.tmp"


[b]'Now show how to incorrectly use slOpen[/b]

Logit ""
Logit "Open database without testing if already open"
FOR x = 1 TO 5
  slopen "junk.tmp","C"
  Logit USING$("handle #",slGetHandle)
NEXT
slClose
KILL "junk.tmp"
LOGIT ERROR$(ERR) + " killing junk.tmp"

? gs

END FUNCTION

SUB LogIt(s AS STRING)
gs = gs + s + $CR
END SUB

Enables a column delimiter and a new row delimiter in new JOIN2$ function.
This makes it easy to display a string from a 2-dimensional array with a $CR  or other characters as a row delimiter.

Improved money macro to automatically display passed column name instead of printf statement.
SQlite "AS" is optional so just appended colname to the money macro.
Don't like leading 0 displaying on money columns so used ltrim to remove it.

Enjoy!

#DIM ALL
MACRO money(colname)= CHR$("ltrim(printf('%.2f',",colname,"*.01),'0')",colname)
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG

 LOCAL sql,sColDel,sRowDel,rs() AS STRING
 slopen "sample.db3"
 sql = "select manuf,redref,"+ money("PRICE") + " from parts limit 20"
 slselary sql,rs()
 sColDel = "   "
 sRowDel = $CR
 ? JOIN2(rs(),sColDel,sRowDel),,USING$("Rows #_, Cols #",UBOUND(rs,2),UBOUND(rs))

END FUNCTION

FUNCTION JOIN2(rs() AS STRING,sColumnDelimiter AS STRING,sRowDelimiter AS STRING) AS STRING
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "STRINGBUILDERA"
 'sb.capacity = 1024*1000 'does well without capacity

 LOCAL c              AS LONG
 LOCAL LowCol         AS LONG
 LOCAL HighCol        AS LONG
 LOCAL HighCol_minus1 AS LONG

 LOCAL r              AS LONG
 LOCAL LowRow         AS LONG
 LOCAL HighRow        AS LONG

 LowCol = LBOUND(rs,1)
 HighCol= UBOUND(rs,1)
 HighCol_minus1 = HighCol-1
 LowRow = LBOUND(rs,2)
 HighRow= UBOUND(rs,2)

 FOR r = LowRow TO HighRow
  FOR c= LowCol TO HighCol_minus1
   sb.add  rs(c,r)
   sb.add sColumnDelimiter
  NEXT c
  sb.add rs(c,r)
  sb.add sRowDelimiter
 NEXT r
 FUNCTION = sb.string
END FUNCTION

1. If $Viewfile has a length the output goes to a text file and is displayed using default text viewer.
2. If $Viewfile is remarked, the output goes to the command line environment.
3. All select statements must be terminated with a semi-colon.
4. Backup, clone, import, export, view sql, ...
5. Many commands can be sent at once by separating each semi-colon terminated command with a $CRLF

Example:
sdatabase= "sample.db3"
sheller "select * from parts;" + $CRLF

'SQLite3Sheller -  script processor  6/24/19
'All select statements must be terminated with a semi-colon.
'Many uses; including backup, clone, import, export, view sql, ...

DECLARE FUNCTION ShellExecute LIB "Shell32.dll" ALIAS "ShellExecuteA" ( _
    BYVAL hwnd AS DWORD, lpOperation AS ASCIIZ, lpFile AS ASCIIZ, _
    lpParameters AS ASCIIZ, lpDirectory AS ASCIIZ, BYVAL nShowCmd AS LONG) _
    AS DWORD

$CommandFile="cj.cmd" 'file read by SQLite3.exe
$BatchFile  ="cj.bat" 'file shelled to
'$ViewFile  ="cj.txt" '<---  remark to use cmd.exe or unremark to view .txt file
'=====================================================================================
FUNCTION PBMAIN () AS LONG 'sqlite commands .modes ascii column html insert line list tabs tcl
 LOCAL s,sdatabase AS STRING
 sdatabase= "sample.db3"
 #IF %DEF($ViewFile)
  s=CHR$(".output ",$ViewFile,$CRLF)
 #ENDIF
 s+= CHR$("select '10 records of '||count(*)||' records (.mode tab)' from parts;",$CRLF)
 s+= CHR$(".mode tabs",$CRLF)
 s+= CHR$("select rowid,redref,substr(product,1,40) from parts limit 10;",$CRLF)
 s+= CHR$("select '';",$CRLF)
 s+= CHR$("select '10 records of '||count(*)||' records (.mode csv)' from parts;",$CRLF)
 s+= CHR$(".mode csv",$CRLF)
 s+= CHR$("select rowid,redref,substr(product,1,40) from parts limit 10;",$CRLF)
 sheller sdatabase,s
 #IF %DEF($ViewFile)
  DisplayTextFile $ViewFile
 #ENDIF
END FUNCTION
'=====================================================================================
FUNCTION sheller(sDataBase AS STRING,sCommands AS STRING) AS LONG
 LOCAL stemp AS STRING, h AS LONG
 h = FREEFILE
 OPEN $CommandFile FOR OUTPUT AS #h
 IF ERR THEN ? "Unable to open " + $CommandFile,%MB_SYSTEMMODAL,"Error":EXIT FUNCTION
 PRINT #h,sCommands;
 CLOSE #h
 h = FREEFILE
 OPEN $BatchFile FOR OUTPUT AS #h
 IF ERR THEN ? "Unable to open " + $BatchFile,%MB_SYSTEMMODAL,"Error":EXIT FUNCTION
 stemp = "sqlite3.exe " + sdatabase + " < " + $CommandFile
 PRINT #h,"@cls"
 PRINT #h,"@echo off"
 PRINT #h,stemp
 #IF NOT %DEF($ViewFile) 'viewfile not wanted
  PRINT #h,"pause"      'pause batch file so we can see results
 #ENDIF
 CLOSE #h
 #IF %DEF($ViewFile)
  h = SHELL($BatchFile,0)
 #ELSE
  h = SHELL($BatchFile)
 #ENDIF
END FUNCTION
'=====================================================================================
FUNCTION DisplayTextFile(sFileName AS STRING) AS LONG
 LOCAL zFileName AS ASCIIZ * 257
 zFileName = sFileName
 ShellExecute (0, "OPEN", zFileName, BYVAL 0, CURDIR$, %SW_SHOWNORMAL)
END FUNCTION

Shows how easy it is to display 2-dimensional recordsets in MLG
MLG_PUT hGrid,rownum,colnum,sRecordSet(colnum,rownum),0

Pressing Enter key (at anytime) or clicking Run executes the SQL and fills grid.

Added statusbar 6/22/19  1:39AM CST

Suggestions welcome

MLG can be purchased from Gary Beene gbeene@airmail.net

#PBFORMS CREATED V2.01
'------------------------------------------------------------------------------
#COMPILE EXE    'slMlg.bas
#DIM ALL
GLOBAL ghDlg AS DWORD

#PBFORMS BEGIN INCLUDES
#INCLUDE ONCE "WIN32API.INC"
#PBFORMS END INCLUDES
#INCLUDE ONCE "mlg.inc"
#INCLUDE ONCE "sqlitening.inc"
'------------------------------------------------------------------------------
#PBFORMS BEGIN CONSTANTS
%IDC_GRID      =  100
%GridLabel      = 1010
%TEXT_DATABASE  = 1011
%TEXT_SQL      = 1012
%LBL_DATABASE  = 1013
%LBL_SQL        = 1014
%BTN_RUN        = 1015
%LBL_LABEL1    = 1016
%IDC_STATUSBAR1 = 1017
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
FUNCTION PBMAIN()
  ShowDIALOG1 0
END FUNCTION

SUB Resize
 LOCAL DialogWidth,DialogHeight AS LONG
 LOCAL GridWidth  ,GridHeight  AS LONG
 DIALOG  GET SIZE  ghDlg TO DialogWidth,DialogHeight            'dialog width,height
 CONTROL GET SIZE ghDlg,%IDC_GRID TO GridWidth  ,GridHeight    'grid  width,height
 CONTROL SET SIZE ghDlg,%IDC_GRID,  DialogWidth-14,GridHeight  'set grid width
 CONTROL REDRAW  ghDlg,%IDC_GRID
END SUB

CALLBACK FUNCTION ShowDIALOG1Proc()
 LOCAL DialogWidth,DialogHeight AS LONG
 LOCAL gridwidth,gridheight AS LONG
 LOCAL SelStart,SelEnd AS LONG

  SELECT CASE AS LONG CB.MSG
    CASE %WM_EXITSIZEMOVE
    Resize

    CASE %WM_INITDIALOG
    ghDlg = CB.HNDL
    DIALOG  GET SIZE  ghDlg TO DialogWidth,DialogHeight            'dialog width,height
    CONTROL GET SIZE ghDlg,%GridLabel TO GridWidth ,GridHeight    'label size
    CONTROL SET SIZE ghDlg,%GridLabel,  DialogWidth-14,Gridheight  'set grid width

    CASE %WM_NCACTIVATE
      STATIC hWndSaveFocus AS DWORD
      IF ISFALSE CB.WPARAM THEN
        hWndSaveFocus = GetFocus()
      ELSEIF hWndSaveFocus THEN
        SetFocus(hWndSaveFocus)
        hWndSaveFocus = 0
      END IF

    CASE %WM_COMMAND
      SELECT CASE AS LONG CB.CTL
        CASE %IDC_STATUSBAR1
        CASE %TEXT_DATABASE
        CASE %TEXT_SQL
          IF CB.CTLMSG = %EN_SETFOCUS THEN 'SQL input control got focus
            CONTROL SEND CB.HNDL, %TEXT_SQL,%EM_GETSEL, VARPTR(SelStart), VARPTR(SelEnd)
            CONTROL SEND CB.HNDL, %TEXT_SQl,%EM_SETSEL,SelEnd,SelEnd
          END IF

        CASE %BTN_RUN
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            GridFromSQL
            CONTROL SET FOCUS CB.HNDL,%TEXT_SQl
            CONTROL SEND CB.HNDL, %TEXT_SQL,%EM_GETSEL, VARPTR(SelStart), VARPTR(SelEnd)
            CONTROL SEND CB.HNDL, %TEXT_SQl,%EM_SETSEL,SelEnd,SelEnd
          END IF
        CASE %IDC_GRID
      END SELECT
  END SELECT
END FUNCTION

FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
  LOCAL lRslt AS LONG

#PBFORMS BEGIN DIALOG %IDD_DIALOG1->->
  LOCAL hDlg  AS DWORD

  DIALOG NEW hParent, "", 286, 170, 768, 359, %WS_POPUP OR %WS_BORDER OR _
    %WS_DLGFRAME OR %WS_THICKFRAME OR %WS_CAPTION OR %WS_SYSMENU OR _
    %WS_MINIMIZEBOX OR %WS_MAXIMIZEBOX OR %WS_VISIBLE OR %DS_3DLOOK OR _
    %DS_NOFAILCREATE OR %DS_SETFONT, %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR, TO hDlg
  CONTROL ADD TEXTBOX,  hDlg, %TEXT_DATABASE, "", 43, 297, 99, 13
  CONTROL ADD TEXTBOX,  hDlg, %TEXT_SQL, "select * from parts limit 5", 42, _
    313, 441, 13
  CONTROL ADD LABEL,    hDlg, %GridLabel, "%GridLabel - Grid will display " + _
    "here", 0, 0, 752, 260
  CONTROL SET COLOR      hDlg, %GridLabel, %WHITE, %BLUE
  CONTROL ADD LABEL,    hDlg, %LBL_DATABASE, "Database", 5, 297, 32, 11
  CONTROL ADD LABEL,    hDlg, %LBL_SQL, "SQL", 5, 315, 23, 10
  CONTROL ADD BUTTON,    hDlg, %BTN_RUN, "Run", 5, 329, 23, 10
  CONTROL ADD LABEL,    hDlg, %LBL_LABEL1, "Press Enter or click Run", 5, _
    277, 324, 10
  CONTROL ADD STATUSBAR, hDlg, %IDC_STATUSBAR1, "Rows 0    Columns 0", 0, 0, _
    0, 0
#PBFORMS END DIALOG
  DIALOG SET TEXT hdlg,EXE.NAME$
  LOCAL Msg AS TagMsg
  LOCAL flag AS LONG  'prevent grid displaying twice when we set focus
  DIALOG SHOW MODELESS hDlg, CALL ShowDIALOG1Proc TO lRslt

  DO WHILE GetMessage(Msg, %NULL, 0, 0)
  IF IsDialogMessage(hDlg, Msg) = %FALSE THEN
    TranslateMessage Msg
    DispatchMessage  Msg
  END IF

  IF msg.wparam = 13 THEN                      'enter pressed in any control
    'DIALOG SET TEXT ghDlg,USING$("ID #  Counter #",GetDlgCtrlID(GetFocus),gCounter)
    IF GetDlgCtrlID(GetFocus) = %TEXT_SQL THEN 'in %TEXT_SQL control?
      IF flag = 0 THEN GridFromSQL              'execute sql and MLG
      flag = flag XOR 1                        'toggle flag
      LOCAL selStart,SelEnd AS LONG
      CONTROL SEND hDlg, %TEXT_SQL,%EM_GETSEL, VARPTR(SelStart), VARPTR(SelEnd)
      CONTROL SEND hdlg, %TEXT_SQl,%EM_SETSEL,SelEnd,SelEnd
    END IF
    CONTROL SET FOCUS hDlg,%TEXT_SQL          'always focus to sql input
  END IF

  LOOP WHILE ISWIN(hDlg)

  FUNCTION = lRslt
END FUNCTION

FUNCTION GridFromSQL AS LONG
 LOCAL rownum,colnum,rows,cols AS LONG
 LOCAL x AS LONG,y AS LONG,gridwidth,gridheight,hGrid AS LONG
 LOCAL s AS STRING
 LOCAL sDatabase AS STRING
 LOCAL sql AS STRING
 LOCAL sRecordSet() AS STRING
 CONTROL GET TEXT ghDlg,%TEXT_DATABASE TO sDatabase
 CONTROL GET TEXT ghDlg,%TEXT_SQL  TO sql

 IF LEN(TRIM$(sDataBase)) = 0 THEN
  sDatabase = "sample.db3"
  sql = "select manuf,redref,product,language,cpu_os,media,type,pgroup,printf('%.2f',price*.01) as PRICE from parts limit 5"
  CONTROL SET TEXT ghDlg,%TEXT_Database,"sample.db3"
  CONTROL SET TEXT ghDlg,%TEXT_SQL,sql
  DIALOG REDRAW ghDlg
 END IF

 slOpen sDatabase,"C E0"  'E0 we will handle open database error
 IF slGetErrorNumber THEN
  ? "Database name: " +  WRAP$(sdataBase,$DQ,$DQ) + $CR + slGetError,%MB_SYSTEMMODAL,"SQL Open"
  EXIT FUNCTION
 END IF

 slSelAry sql,sRecordSet(),"E0"  'E0 we will handle SQL error
 IF slGetErrorNumber THEN
  ? CHR$(slGetError,$CR,"Database name: ",WRAP$(sdataBase,$DQ,$DQ),$CR,$CR,sql),%MB_SYSTEMMODAL,"SQL Error"
  EXIT FUNCTION
 END IF

 rows = UBOUND(sRecordset,2)
 cols = UBOUND(sRecordset,1)
 s = USING$("r#/c#",Rows,cols) 'rows and columns
 CONTROL GET LOC ghdlg,%GridLabel TO x,y
 CONTROL GET SIZE ghdlg,%GridLabel TO gridwidth,gridheight
 CONTROL SHOW STATE ghDlg, %GridLabel, %SW_HIDE 'must hide grid position label
 CONTROL KILL ghDlg,%IDC_GRID                  'start a fresh grid
 CONTROL ADD "MYLITTLEGRID", ghDlg, %IDC_GRID, s, x, y, gridwidth,gridheight, %MLG_STYLE
 CONTROL HANDLE ghDlg, %IDC_GRID TO hGrid
 FOR rownum = 0 TO rows
  FOR colnum = 1 TO cols
  MLG_PUT hGrid,rownum,colnum,sRecordSet(colnum,rownum),0
  NEXT
 NEXT
 slClose
 CONTROL SET TEXT ghDlg,%IDC_STATUSBAR1,USING$("Rows #,    Columns #",rows,cols)
 Resize
END FUNCTION

#INCLUDE "sqlitening.inc"
'added these 2-macros 2/15/22
MACRO money0(colname)= CHR$("printf('%.2f',",colname,"*.01)") 'with leading 0
MACRO money(colname)= CHR$("ltrim(printf('%.2f',",colname,"*.01),'0')")

FUNCTION PBMAIN AS LONG

 LOCAL sql,sArray() AS STRING

 slopen "junk.db3","C"

 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 integer)"

 slexe "insert into t1 values(-1000),(-10),(-9),(-1),(0),(1),(9),(10),(100),(1000)"

 sql = "select rowid,"  +_
       " case" +_
       " when c1 > 0 then       ltrim(printf('%.2f',c1*.01),'0')"  +_ 
       " when c1 < 0 then '-'|| ltrim(printf('%.2f',c1*.01),'-0')" +_ 
       " else '.00'" +_
       " end  AS TheValues"+_
       " from t1"

 slSelAry sql,sArray(),"Q9"
 ? JOIN$(sArray(),$CR),,"No leading zero"

END FUNCTION
  

https://www.sqlite.org/lang_vacuum.html#vacuuminto

Not sure how I missed slSelBind.
slSelBind was added a long time ago and can prevent SQLite injection
https://sqlitening.planetsquires.com/index.php?topic=3378.0;wap2

Quote
Added the slSelBind function in order to avoid SQL injection and to improve Unicode processing.

Example extracting encrypted text (3-ways)

slexe  "create table if not exists t1(MyKey UNIQUE,MyData)"
slSetProcessMods "K" + SPACE$(32)
slSelBind "select MyData from t1 where MyKey = ?",slBuildBindDat(sKey,"T")
DO WHILE slGetRow
  ? slConvertDat(slf(1),"D")
  ? slfx(1,"D")
  ? slfnx("MyData","D")
LOOP

'Encapsulated 3 functions to include logging
'slSel    ----> slSe
'slExe    ----> slEx
'slSelAry ----> slSelAr

'slInsert added for simple text inserting

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG 'BindAndLog.Bas  9/12/18 CJ
LOCAL sTemp() AS STRING
REDIM sCol(1 TO 2) AS STRING 'columns in table
KILL   "junk.db3":ERRCLEAR
slOpen "junk.db3","C"
slexe  "create table if not exists trantable(statements)"
slex   "create table if not exists t1(c1,c2)"

'slInsert - insert data without quoting strings
sCol(1)="c1 binding"
sCol(2)="c2 binding"
slInsert "t1",sCol() 'tablename$,datacols$()

slex     "insert into t1 values('c1 no bind','c2 no bind')"
slse     "select sqlite_version()",0,"E0":slGetRow':? slf(1),,"SQLite Version"
slex     "drop table if exists FimTable1234"

slSelAr "select rowid,* from t1",sTemp(),"Q9"
? "T1 Table" + $CR + JOIN$(sTemp(),$CR) + $CR + $CR + "Log" + $CR +_
Viewer("select rowid,* from trantable" ,"Q9"),,"Both tables"

END FUNCTION
'-------------------------------------------------------------------------------------
FUNCTION slInsert(sTable AS STRING,sCol() AS STRING) AS STRING
LOCAL x AS LONG, sInsert,sBind,sQuestionMarks,sLog AS STRING
FOR x = 1 TO UBOUND(sCol)
  sBind+=slBuildBindDat(sCol(x),"T")
NEXT
sQuestionMarks = LEFT$("(" + REPEAT$(UBOUND(sCol),"?,"),-1) + ")"  '(?,?,?)
slexeBind "Insert into " + sTable + " values " + sQuestionMarks,sBind
IF slGetChangeCount = 0 THEN ? "Insert error",%MB_SYSTEMMODAL,"slInsert"
sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + "Insert into " + sTable + " values (" + JOIN$(sCol(),",") + ")"
slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION
'-------------------------------------------------------------------------------------
FUNCTION Viewer(sql AS STRING,sModChars AS STRING) AS STRING
LOCAL sArray() AS STRING
FUNCTION = slSelAr(sql,sArray(),sModChars)
END FUNCTION
'-------------------------------------------------------------------------------------
'(BYREF rsStatement AS STRING, BYREF wsaColsAndRows() AS STRING, OPTIONAL BYVAL rsModChars AS STRING) AS LONG
FUNCTION slSelAr(rsStatement AS STRING, wsaColsAndRows() AS STRING,OPT rsModChars AS STRING) AS STRING
LOCAL rsModChars2 AS STRING
LOCAL sLog AS STRING
LOCAL wsaColsAndRows() AS STRING
IF ISFALSE(ISMISSING(rsModChars)) THEN rsModChars2 = rsModChars
slSelAry rsStatement,wsaColsAndRows(),rsModChars2
FUNCTION = JOIN$(wsaColsAndRows(),$CR)
sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + rsStatement
slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION
'-------------------------------------------------------------------------------------
'BYREF rsStatement AS STRING, OPTIONAL BYVAL rsModChars AS STRING) AS LONG
FUNCTION slex(sql AS STRING,OPT rsModChars AS STRING) AS LONG
LOCAL rsModChars2 AS STRING
LOCAL sLog AS STRING
IF ISFALSE(ISMISSING(rsModChars)) THEN rsModChars2 = rsModChars
slexe sql,rsModChars2
sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + sql
slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION
'-------------------------------------------------------------------------------------
'(BYREF rsStatement AS STRING, OPTIONAL BYVAL rlSetNumber AS LONG, OPTIONAL BYVAL rsModChars AS STRING) AS LONG
FUNCTION slse(rsStatement AS STRING,OPT rlSetNumber AS LONG,rsModChars AS STRING) AS LONG
LOCAL rlSetNumber2 AS LONG
LOCAL sLog,rsModChars2  AS STRING
IF ISFALSE(ISMISSING(rlSetNumber)) THEN rlSetNumber2= rlSetNumber
IF ISFALSE(ISMISSING(rsModChars))  THEN rsModChars2 = rsModChars
slSel rsStatement ,rlSetNumber2,rsModChars2
sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + rsStatement
slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION



Didn't like previous version.
This version captures errors in log and doesn't need any other functions

#INCLUDE "sqlitening.inc"
$E1 = "...................................................." + $CR
$E2 = "...................................................." + $CR

FUNCTION PBMAIN () AS LONG  'errorlog.bas 9/13/18 CJ

LOCAL sql AS STRING
slSetProcessMods "E1"  'any error is captured into the log

KILL "junk.db3":ERRCLEAR
slopen "junk.db3","C"

sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
sql = "select * from trantable"                         :slsel sql:logit(sql)
sql = "drop table if exists HeidiKlum"                  :slexe sql:logit(sql)
sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
sql = "create table wrong"                              :slexe sql:logit(sql)
sql = "drop table if exists Table1"                     :slexe sql:logit(sql)
sql = "select rowid      from trantable"                :slsel sql:logit(sql)
sql = "select statement  from trantable"                :slsel sql:logit(sql)
sql = "JIBBERISH"                                       :slexe sql:logit(sql)
sql = "select *          from trantable"                :slsel sql:logit(sql)
sql = "select * from xyz"                               :slsel sql:logit(sql)
sql = "select * from trantable"                         :slsel sql:logit(sql)
sql = "select 'Have'||' a'||' good'||' day'"            :slsel sql:logit(sql)

viewall

END FUNCTION

SUB ViewAll
LOCAL sArray() AS STRING
IF slSelAry("select statement from trantable",sArray(),"Q9E0") THEN
   ? slGetError,%MB_SYSTEMMODAL,"Viewer"
ELSEIF UBOUND(sArray) < 1 THEN
  ? "No data",%MB_SYSTEMMODAL,"Viewer"
ELSE
  ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Viewer"
END IF
END SUB

FUNCTION LogIt(s AS STRING) THREADSAFE AS LONG
LOCAL sHeader AS STRING  'returns 1 on success
LOCAL AnError AS LONG
AnError = slGetErrorNumber 'set flag

IF AnError THEN 'experimental, show last error in log
  slexebind "insert into trantable values(?)",slBuildBindDat($E1 + slGetError,"T"),"E0"
END IF

sHeader = LEFT$(DATE$,5) + " " + TIME$ + " " + s
slexebind "insert into trantable values(?)",slBuildBindDat(s,"T"),"E0"
IF slGetErrorNumber THEN
   ? slGetError + $CR + $CR + s,%MB_SYSTEMMODAL,"LogIt"
   EXIT FUNCTION
END IF
IF AnError THEN slexebind "insert into trantable values(?)",slBuildBindDat($E2,"T"),"E0"
IF slGetChangeCount <> 1 THEN
   ? "Write to log failed",%MB_SYSTEMMODAL,"LogIt"
END IF

END FUNCTION

Is there a limit to the number of functions called in a SELECT statement?
Each of the strftime function calls work if broken into shorter Select statements.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
DIM sArray() AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1)"
slselAry "select strftime('%Y',c1),strftime('%m',c1),strftime('%d',c1)," +_
                 "strftime('%H',c1),strftime('%M',c1),strftime('%S',c1) from t1",sArray()
? JOIN$(sArray(),$CR)
END FUNCTION

SQLite added UPSERT equivalent today 6/4/2018 IN version 3.24.0

http://www.sqlite.org/lang_UPSERT.html

If an INSERT fails then an UPDATE to the same ROWID is attempted.
The update after a failing insert can fail if it is also a duplicate.

This links says it is the same as INSERT OR REPLACE INTO.
I think that may work, but it will delete all columns and then INSERT.
https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace


This example has 2 columns and it demonstrates the second column is not deleted, hurray!

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL sRecordSet AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 unique, c2)"
DO
  slexe "insert into t1 values('Hello, world',' am i deleted') on conflict(c1) do update set c1 = '*duplicate so insert timer="+FORMAT$(TIMER) + "*'"
  sRecordSet = ""
  slsel "select rowid,* from t1"
  DO WHILE slgetrow
    sRecordSet+= slf(1) + " " + slf(2) + " " + slf(3) + $CR
  LOOP
  IF MSGBOX(sRecordSet,%MB_YESNO,"Yes = insert       No = done") <> %IDYES THEN EXIT DO
LOOP
END FUNCTION