SQLitening Support Forum

Thanks, Paul for bringing up using table 61.
Unique keys can be created as needed for titles, totals, anything ...

#INCLUDE "sqlitening.inc"     'onthefly.bas
THREADED ts AS STRING         'combine results
'NOTE: if sql uses LIKE or SUBSTR a full table SCAN is always performed  instead of indexed SEARCH.
'If someone knows how to use search instead of scan using LIKE, please let us know!

FUNCTION PBMAIN () AS LONG

LOCAL sql,s AS STRING

slOpen "junk.db3","C"
slexe "drop table if exists t61"
slexe "create table t61(t61key text primary key,t61answer) without rowid"

slexe "insert or replace into t61 values('t01-client','1')"
slexe "insert or replace into t61 values('t01-total' ,'2')"
slexe "insert or replace into t61 values('t01-client','1000')" 'replace client
slexe "insert or replace into t61 values('t01-total' ,'2000')" 'replace total

GetData "select t61answer from t61 where t61key = 't01-client'"
GetData "select t61answer from t61 where substr(t61key,1,3) ='t01'" 'SCANS entire table
? ts
END FUNCTION

SUB Getdata(sql AS STRING)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9c"
IF UBOUND(sArray)> 0 THEN ts+= sql + $CR + JOIN$(sArray(),",") + $CR + $CR
END SUB

No problem unless:
"The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY."

Quote
Excellent. Thanks for the sample code. The problem is how do I individually read each values into the
aData array subscripts.
aData(0) = ? : aData(1) = ? : aData(2) = ? : aData(3) = ? : aData(4) = ?

The code did this so don't understand the question?   
aData(0) = row 1 temperature as a DOUBLE
aData(1) = row 2 temperature as a DOUBLE
aData(2) = row 3 temperature as a DOUBLE
aData(3) = row 4 temperature as a DOUBLE
aData(4) = row 5 temperature as a DOUBLE

REDIM sArray(0) AS STRING
slSelAry "select temperature from table1 LIMIT 5",sArray(),"Q9"
elements = UBOUND(sArray)
IF elements > 0 THEN
REDIM aData(elements) AS DOUBLE
FOR x = 1 TO elements
  aData(x-1) = VAL(sArray(x)) read each value into the aData array subscript.
NEXT
END IF

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
LOCAL x,elements AS LONG

slOpen "junk.db3","C"
slexe "drop table if exists table1
slexe "create table if not exists table1(temperature integer)"
slexe "insert into table1(temperature)values(30),(40),(70),(60),(20),(17),(88)"

REDIM sArray(0) AS STRING
slSelAry "select temperature from table1",sArray(),"Q9"

'read each value into the aData array subscript.
elements = UBOUND(sArray)
IF elements > 0 THEN
  REDIM aData(elements) AS DOUBLE
  FOR x = 1 TO elements
   aData(x-1) = VAL(sArray(x))
  NEXT
END IF
END FUNCTION

QuoteHow can I completely suppress every error message and let the library calls fail when there is a lock timeout?
Do I need to set the mods on every call?

slSetProcessMods "r" must be after slOpen (which you have done.)
Are you sure you need T1000 (1-second) which causes the problem and no retry?
The default of 10000 (10-seconds) works well so MSGBOX shouldn't appear.

Getting rid of the T option and the pops and pushes would be my suggestion.

Anyway, playing with this code might be useful.


#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL hThread AS LONG
slSetProcessMods "E0"

slopen "junk1","CT1000"  'make busy-timeout extremely small (not suggested)
slSetProcessMods "r" 'required after slOpen so busy MSGBOX will not appear

slexe "create table if not exists t1(c1)"
THREAD CREATE LockDataBase(0) TO hThread
THREAD CLOSE hThread to hThread
SLEEP 1000 'so other thread locks before we attempt insert
slexe "insert into t1 values('one')
? "Done"

END FUNCTION

THREAD FUNCTION LockDataBase(BYVAL notused AS LONG) AS LONG
slOpen "junk1"
slExe "begin exclusive"
? "transaction active",,FUNCNAME$
slExe "end"
END FUNCTION

If you don't mind using .NET or VB6 here are 2 great grids.

1. PowerBASIC code can be put into a DLL and loaded from VB6.
2. PowerBASIC code can use IPC (inner process communications) to talk between programs.
https://10tec.com/activex-grid  (many, features for grouping, etc.)  2  32-bit OCX's
http://grid2000.com (lot of features, but less samples)           1 32-bit OCX

I have Spread 7 somewhere and used it a long time ago.
I checked the current price at GrapeCity and about fell over.

Just know that Gary Beene would have to be contacted.

I only use grids or a huge multiline textbox.
Does PowerDev or what you use have a control for this?

What you are doing might require reading some changes table, updating array, redisplay current screen if necessary.
There might be an easy way, but don't have a solution.

The good news:
Virtual listviews and grids only need to refresh at an interval to see if something changed that is currently in view.
While scrolling through pages they are current.
If you sit on a page, refresh the page every so often in a loop, timer, thread using the last executed SQL statement.

'only called at an interval like every so many seconds using a timer
sdata = RefreshData(sql)                 'get current screen data
IF sdata <> sDataBefore THEN         'something has changed
  sdatabefore = sdata                     'set values equal
  'refresh page
END IF



#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG 'autoupdate.bas
RANDOMIZE
LOCAL sTest,sdata,sdatabefore,sql AS STRING, x AS LONG
KILL "junk1.db3"
ERRCLEAR
sql   = "select * from t1"
slOpen "junk1.db3","C"
slexe  "create table if not exists t1(c1)"
FOR x = 1 TO 100
  IF x MOD 10 = 0 THEN slexe "insert into t1 values('hello')"

  sdata = RefreshData(sql)                        'get current screen data
  IF sdata <> sDataBefore THEN                    'something has changed
    sdatabefore = sdata                           'set values equal
    sTest+= USING$("New screen when x=#",x) + $CR 'only for test purposes
  END IF
NEXT

? sTest,%MB_SYSTEMMODAL,"Screen updates"

END FUNCTION

FUNCTION RefreshData(sql AS STRING) AS STRING
DIM sArray() AS STRING
slSelAry sql,sArray(),"Q9C"
FUNCTION = JOIN$(sArray(),$CR)
END FUNCTION

QuoteOh! My database is not empty. It has other tables with records, except  the table I am importing to. Will give it another try.

If the table you are importing to doesn't have the same format it should fail.

No error here and no idea what an IDE has to do with this?
Please be sure you are starting with a fresh, empty database.

Please try running this.
Can't test your CSV file.

#INCLUDE "sqlitening.inc"
'
FUNCTION PBMAIN () AS LONG     'CSV2Sqlite.bas
  LOCAL sCSVFile,sDataBaseName,sTableName AS STRING
  LOCAL DropTable,QuoteNumbers, counter AS LONG

  sDataBaseName = "junk.db3"  'sqlite database to write to
  sTableName    = "table1"          'table to write to
  DropTable     = 1             '0=append, 1= start fresh
  QuoteNumbers  = 1             'enclose all columns with $SQ
  sCSVFile      = "CSV.csv"   'input csv file
 
  'create csv file
  OPEN sCSVFile FOR OUTPUT AS #1
  LOCAL s AS STRING
  FOR counter = 1 TO 10
   s = FORMAT$(counter)
   WRITE #1, s,"now","brown","cow"
  NEXT
  CLOSE #1


  counter = Csv2Sqlite _
    (sCsvFile,sDatabaseName,sTableName,DropTable,QuoteNumbers)
  ? USING$("Records in table #,",counter),,"Csv2Sqlite"

  DIM sArray() AS STRING
  slOpen "junk.db3"
  slSelAry "select * from table1",sArray(),"Q9"
  ? JOIN$(sArray(),$CR)
END FUNCTION
'
FUNCTION Csv2SQLite ( _
  sCSVFILE AS STRING, _
  sDataBaseName AS STRING , _
  sTableName    AS STRING,  _
  DropTable     AS LONG,    _
  QuoteNumbers  AS LONG) AS LONG

  'Create table with sColumnNames$ = "C1,C2, ..."

  LOCAL x, hFile,cols AS LONG
  LOCAL sInputLine,sColumnNames,sOutputLine,s,sInsert AS STRING
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  LINE INPUT #hFile, sInputLine 'read first line
  cols = PARSECOUNT(sInputLine) 'number of columns
  CLOSE #hFile
  'first line could be column names, using C1,C2, ..."
  FOR x = 1 TO cols
    sColumnNames = sColumnNames + "C" + FORMAT$(x)+ ","
  NEXT
  sColumnNames = LEFT$(sColumnNames,-1) 'your column names
  slOpen sDataBaseName,"C"
  IF DropTable THEN slexe "Drop table if exists " + sTableName
  slexe "Create Table if not exists " + sTableName + "(" + sColumnNames + ")"
  slexe "BEGIN EXCLUSIVE"
  hFile = FREEFILE
  OPEN sCSVFile FOR INPUT AS #hFile
  DO UNTIL EOF(#hFile) 'process line
    LINE INPUT #hFile, sInputLine
    RESET sOutputLine
    FOR x = 1 TO cols
      s = PARSE$(sInputLine,x)
        IF QuoteNumbers THEN        'enclose all columns in $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      ELSEIF IsNumeric(s) = 0 THEN 'only enclose strings with $SQ
        REPLACE $SQ WITH $DQ IN s
        s = $SQ & s & $SQ
      END IF
        sOutputLine = sOutPutLine +  s + ","
    NEXT
    sOutPutLine = LEFT$(sOutputLine,-1)
    sInsert = "Insert into " + sTableName + " values (" + sOutputline + ")"
    REM ? sOutputLine:end 'look at first output record and end
    slExe sInsert
  LOOP
  slEXE "END"
  LOCAL sArray() AS STRING 'return number of inserts
  slSelAry "select count(*) from " + sTableName,sArray(),"Q9c"
  FUNCTION = VAL(JOIN$(sArray(),$CRLF))
END FUNCTION
'
FUNCTION isNumeric(Answer AS STRING) AS LONG
  Answer = TRIM$(Answer) 'may be required if right-justified text
  IF (LEN(Answer) = 0)                  OR _
     (VERIFY (Answer , ".-0123456789")) OR _
     (RIGHT$(Answer,1) = ".")           OR _
     (INSTR(-1,Answer,"-") > 1)         OR _
     (TALLY(Answer,".") > 1) THEN
     EXIT FUNCTION  'exit, return 0 not-numeric
  END IF
  FUNCTION = -1 'numeric
END FUNCTION

Hopefully a cable connection.
Good luck, talk tomorrow if you need help.

QuoteHow can I connect my router to the my wireless internet connection? My router don't have a usb port and there is no way to connect my wireless internet with a cable.

Wireless computers connect to router using built-in or USB wireless network adapters
The modem connects to the internet and router plugs into the modem.
Some modems and routers are a single device.  Hopefully you have a cable modem connected to a cable line into the building.

You already plugged in the wireless adapter and no wires are needed

In the router software:  started by http://192.168.1.1  netgear routers are http://192.168.0.1
You may have to get into router software with an ethernet cable the first time since you don't have wifi working.

STEP A
Find wireless settings
1 Enable SSID Broadcast
2 Name (SSID)  Enter name everybody will see with wireless to logon
3 Passphrase    Enter secret logon password


Step B

   TPLink on port forwarding:
https://www.tp-link.com/us/faq-134.html

Name:               Anything you want, example  My Wireless Server
Extemal port:      51234
Internal port:      51234
Internal Server IP 169.254.107.158


Step C (Optional, but suggested)
   Address reservation so IP address can't change
   This assigns the MAC address of the network card to an IP address so the same IP address is always reserve when Windows boots.
https://www.tp-link.com/ae/faq-187.html

MAC Address:  It should appear so you don't have to manually type it in
Reserved IP: Address 169.254.107.158

Connect
In Windows wireless machines:
Click the logon icon on the taskbar
Select network by name
Enter passphrase

Can you see your wireless network name  in the list on any machine or a cell phone?  9/23/18  11:12 AM CST
------------------------------------------------------------------------------------------------------------------
SQLitening server on port 51234 may have to be manually started after Windows begins because wireless drivers may load last.

If you have a shortcut to  SQLiteningServer.exe be sure it uses Run as Administrator.

SQLiteningServer.Cfg
Hosts = 169.254.107.158
Port   = 51234

If using Windows Firewall    SQLiteningServer.exe must be in "Allow an app through Windows Firewall"

Welcome.  Be sure to encrypt and hmac over the internet unless you figured out how to use certificates with SQLitening.