SQLitening Support Forum

1. No.  How are col;umns related?
http://www.garybeene.com/sw/gbchartmaster.htm
https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-for-windows/61086-gbchartmaster-discussion

Quote from: undefinedI find the way this is handled in SQLite to be one of the great features.
I normally create columns with no data type, which will result in an
affinity of none, by using the following create syntax:
Code Select
  Create Table T1 (C1, C2, C3)
I have found no advantage in assigning column affinity using the following:
Code Select
  Create Table T1 (C1 Integer, C2 Text, C3 Real)

Hi, Fredrick!

It goes against the above post #1, but had to say something.
Glad we are doing it the same way.
I haven't been assigning affinity or NOT NULL in my demos and will try to remember.

I am bringing back this very old subject because of problems not defining a column as TEXT.
Be careful not specifying INTEGER or TEXT in CREATE statements.

1. text and binary values are inserted into the column
2. 101 is not "101" so searching and sorting may not return expected value.

If column is UNIQUE:
insert 101 and '101' and two values are insert if no datatype
insert 101 and '101' and second value produces duplicate error if datatype was specified

Bottom line:
Specifying (or not specifing) data type in CREATE statements is very important

CREATE statement without specifying column type and 2 records are inserted
create table t1(key1 primary key) without rowid
insert into t1 values('101') 'insert success
insert into t1 values(101)   'insert sucess

CREATE statement specifying column as TEXT and 1 record is insert and second produces duplicate error
create table t1(key1 TEXT primary key) without rowid"
insert into t1 values('101') 'insert success
insert into t1 values(101)   'duplicate

This example demonstrates inserting 2-values into a unique column
The first loop creates table without a datatype and 2-values are insert
The second loop creates table with a datatype and 1-value is insert

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 slSetProcessMods "E1"
 LOCAL x AS LONG, sArray() AS STRING
 FOR x = 1 TO 2
  slopen "junk.db3","C"
  slexe "drop table if exists t1"

  IF x =1 THEN
   slexe "create table if not exists t1(key1 primary key) without rowid"
  ELSE
   slexe "create table if not exists t1(key1 TEXT primary key) without rowid"
  END IF

  slexe "insert into t1 values('101')"
  slexe "insert into t1 values(101)"
  slselary "select * from t1",sArray()
  ? JOIN$(sArray(),$CR),,"create table if not exists t1(key1 primary key) without rowid"
  slClose
 NEXT
END FUNCTION

'Test multiple clients without multiple computers or SQLitening server

#INCLUDE "sqlitening.inc"  'fredrick9.bas

FUNCTION PBMAIN AS LONG

 LOCAL x,hthread AS LONG

 'Simulate 3 client workstations
 FOR x = 1 TO 3
  THREAD CREATE WorkStation(x) TO hThread
  THREAD CLOSE hThread  TO hThread
 NEXT
 DO:SLEEP 100:LOOP UNTIL THREADCOUNT=1
END FUNCTION

 ViewAll

'===========================================================
THREAD FUNCTION WorkStation(BYVAL x AS LONG)
 Helper x
END FUNCTION

SUB Helper(x AS LONG) THREADSAFE
 slopen "hello.db3","C"   '<--- would be in a shared folder on network
 slexe  "create table if not exists t1(column1)"
 slexe  USING$("insert into t1 values('write from thread#')",x)
 slclose
END SUB


'===========================================================
FUNCTION ViewAll AS STRING
 LOCAL s() AS STRING
 slopen "hello.db3"   
 slselary "select * from t1",s(),"Q9c"
 ? JOIN$(s(),$CR),,"All users done"
END FUNCTION

True, just a normal local area network.
Share a folder on one computer, and map to that folder on the clients.
The SQLitening server/service would not be needed nor the SQLiteingServer.exe be distributed.
Clients would use something like this: slOpen "z:\test\database.db3","C"

Fredrick,
Download/extract SQLiteningServer.bas https://sqlitening.planetsquires.com/index.php?topic=9697.0

SUB LogIt() can be modified to whatever you want.
Compile and replace SQLiteningServer.exe
'===================================================================================

Bypassing the server is much faster and less to distribute.
I tested without using SQLiteningServer to a mapped drive without issue.

slOpen "z:\test\database.db3","C"
Always use "begin immediate" or "begin exclusive" instead of just "begin"

I've used the server for many years and thinking of swithing to simple networking.

Is the server really needed or better on a small LAN?

Instructions added to post #1

Definitely.  See the first post in the general forum.

The link is to a modified SQLiteningServer.bas that might already do what you want.
Compile it and copy  SqliteningServer.exe to whereever your current SQLiteningServer.exe is.

If you installed the modified SQLiteningServer.Bas
the log (SqliteningServer.log) would look like this:

07-21-2019 14:45:23  SELECT * FROM parts LIMIT 1
07-21-2019 14:45:55  SELECT * FROM parts LIMIT 2 ORDER BY PRICE

Also, if you run the filewatcher.exe program, every time someone
accesses the server you would see a scrolling window move up one
line to show you the last line in the log file (SqliteningServer.log)

Sounds like all you need to do is install the modification
and optionally run file watcher to see the changes to the log in real-time.

Quote from: undefinedI've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

>> Really? How does that work?

REM slConnect

Fredrick,

I haven't used procs, but was thinking of making it easier to insert functions into the server.
I'm not convinced adding code to the server is harder than using a proc once a function is added to demonstrate it that can be duplicated and modified.

After the modification below, SQliteningServer.bas compiles with a click.

1. Did you download and install this:
https://sqlitening.planetsquires.com/index.php?topic=9697.msg26453;topicseen#msg26453

2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

I've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

Downloaded 97-times.
Wonder if anyone uses it?

Might add a new function or two to see if it would
be easier and faster than using the Procs method.
The code compiles "as is" with the update above.

Instead of killing and adding grid this version uses MLG_PUTEX
It also leaves the scroll bars on at all times using /D switch

#PBFORMS CREATED V2.01

#COMPILE EXE    'slMlg.bas
#DIM ALL
'----------------------------------------------------------------------------------------
GLOBAL ghDlg,hGrid AS LONG

#PBFORMS BEGIN INCLUDES
#RESOURCE "slMLG.pbr"
#INCLUDE ONCE "WIN32API.INC"
#INCLUDE ONCE "COMMCTRL.INC"
#INCLUDE ONCE "PBForms.INC"
#PBFORMS END INCLUDES
#INCLUDE ONCE "mlg.inc"
#INCLUDE ONCE "sqlitening.inc"
'------------------------------------------------------------------------------
#PBFORMS BEGIN CONSTANTS
%IDC_GRID      = 1009
%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 %HWND_DESKTOP
END FUNCTION

SUB Resize
 LOCAL DialogWidth,DialogHeight AS LONG
 LOCAL GridWidth  ,GridHeight  AS LONG

 'get size of dialog
 DIALOG  GET SIZE  ghDlg TO DialogWidth,DialogHeight

 'get size of MLG
 CONTROL GET SIZE ghDlg,%IDC_GRID TO GridWidth  ,GridHeight    'grid  width,height
 IF GridWidth THEN 'if 0 then do not redraw
  CONTROL SET SIZE ghDlg,%IDC_GRID,  DialogWidth-14,GridHeight  'set grid width
  CONTROL REDRAW  ghDlg,%IDC_GRID
 END IF
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
    LOCAL s AS STRING
    ghDlg = CB.HNDL
    GridFromSQL

    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_CHAR

    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, "sample.db3", 43, 297, 99, 13
  CONTROL ADD TEXTBOX,  hDlg, %TEXT_SQL, "select * from parts limit 234", 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
CONTROL SET TEXT hDlg,%TEXT_SQL, _
"select manuf,redref,product,language,cpu_os,media,type,pgroup,printf('%.2f',price*.01) as PRICE from parts"

  '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
      'CONTROL SET FOCUS hDlg,%TEXT_SQL          'focus to sql input control
      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 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

 slOpen sDatabase,"C E0"  'E0 we will handle open database error open/close database each time called
 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)  'SQLitening(col,row) order
 cols = UBOUND(sRecordset,1)  'SQLitening(col,row) order
 REDIM RowsCols(rows,cols) AS STRING 'MLG(row,col) order

 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

 STATIC newgrid AS LONG
 IF newgrid = 0 THEN
  newgrid = 1
  CONTROL KILL ghDlg,  %IDC_GRID    'start a fresh grid
  s = USING$("r#/c#/d-0",Rows,cols) 'initial rows and columns  /d-0 don't hide scrollbars
  CONTROL ADD "MYLITTLEGRID", ghDlg, %IDC_GRID, s, x, y, gridwidth,gridheight, %MLG_STYLE
  CONTROL HANDLE ghDlg, %IDC_GRID TO hGrid
  #IF %DEF(%MLG_UNLOCK)
  MLG_UNLOCK
  #ENDIF
 END IF

 'Create MLG(Row,Col) from SQLitening(Col,Row)
 FOR rownum = 0 TO rows
  FOR colnum = 1 TO cols
  RowsCols(rownum,colnum) = sRecordSet(colnum,rownum)
  NEXT
 NEXT
 Mlg_PutEx hGrid,RowsCols(),-4,1    'put array to MLG in row,col order
 FOR x = 1 TO UBOUND(RowsCols,2)    'column names should have been in grid?
  MLG_PUT hGrid,0,x,RowsCols(0,x),1
 NEXT
 slClose
 CONTROL SET TEXT ghDlg,%IDC_STATUSBAR1,USING$("Rows #,    Columns #",rows,cols)
 Resize
END FUNCTION

I was wondering how to do a transaction.  Here is an example:
https://www.thegeekstuff.com/2012/09/sqlite-command-examples/

Welcome.  Hope it helps.

Everything can be done within PowerBASIC using this shell routine.

https://sqlitening.planetsquires.com/index.php?topic=9743.msg26446;topicseen#msg26446