• Welcome, Guest. Please login.
 
May 26, 2019, 05:07:11 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.

Messages - cj

16
1) See if your shortcut has "Run as administrator checked
2) If not that, then "Allow an app  through Windows Firewall"
3) If neither of these then "IPCONFIG" at command prompt and see if IP address is different in sqliteningserver.cfg
17
'Upsert without rowid might be optimal

'A big advantage of binding is text doesn't have to be encoded with single quotes (strings and even files can be insert or updated into column "as is".)
'A good example of this is trying to save a SQL statement that has single quotes, commas and (punctuation) that would be very hard to insert.
'The Upsert is definitely optional, but highly suggest using binding.

$Insert ="Insert into t1 values(?,?) ON conflict(c1) DO update SET c2=?"
#INCLUDE "sqlitening.inc" 'upsert.bas

FUNCTION PBMAIN () AS LONG
slOpen "upsert.db3","C"
slexe  "create table if not exists t1(c1 text primary key,c2 text) without rowid"
Upsert "key1","Jane Doe"
Upsert "key1","Heidi Klum"
END FUNCTION

FUNCTION Upsert(sKey AS STRING, sData AS STRING) AS LONG
LOCAL sBind AS STRING
sBind  = slBuildBindDat(sKey, "T") +_   'bind key as text
          slBuildBindDat(sData,"T") +_   'insert
          slBuildBindDat(sData,"T")      'on conflict (update)
slexebind $Insert,sBind
END FUNCTION
18
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
19
No problem unless:
"The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY."
20
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
21
#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

22
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
23
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.
24
Just know that Gary Beene would have to be contacted.
25
I only use grids or a huge multiline textbox.
Does PowerDev or what you use have a control for this?
26
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

27
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.
28
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.
29
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
30
Hopefully a cable connection.
Good luck, talk tomorrow if you need help.