• Welcome, Guest. Please login.
 
July 17, 2019, 03:57:31 pm

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

31
If you slPushDatabase the current database is no longer available until you slPopDatabase
Be careful if connection fails, etc.

This might work just as well without rotating database handle
1 IF can't get to host then no need to push and pop database handles
2 If can get to host then disconnect, read local log, reconnect, send local log
Sounds like good planning like no cloud no work.

Did this way to try to avoid lots of IF statements.

After looking at it awhile workinglocal  and workingremote would need to duplicate code.
The program would probably need to use IF gconnect to share the routines.

#DIM ALL
#INCLUDE "sqlitening.inc"
GLOBAL gConnect AS LONG
'-------------------------------------------------------
FUNCTION PBMAIN () AS LONG
LOCAL x,ecode,portnumber AS LONG
LOCAL sHost AS STRING
slSetProcessMods "E0"

sHost = "192.168.0.13"
PortNumber = 80

FOR x = 1 TO 1 'try multiple times to connect
  ecode = slConnect(shost,portnumber)
  IF ecode = 0 THEN gConnect = 1:EXIT FOR ELSE BEEP
NEXT
IF gConnect THEN
  WorkingRemote
ELSE
  IF MSGBOX("Could not connect, work local?",%MB_YESNO OR %MB_SYSTEMMODAL,"Connect error") = %IDYES THEN
   WorkingLocal
  ELSE
   ? "Ending the program",%MB_SYSTEMMODAL,"Thank you"
  END IF
END IF

END FUNCTION
'-------------------------------------------------------
SUB WorkingLocal
? "Local routines create log"
END SUB
'-------------------------------------------------------
SUB WorkingRemote
slDisconnect
? "Connected/Disconnected"
END SUB
33
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
34
'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
35
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
36
No problem unless:
"The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY."
37
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
38
#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

39
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
40
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.
41
Just know that Gary Beene would have to be contacted.
42
I only use grids or a huge multiline textbox.
Does PowerDev or what you use have a control for this?
43
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

44
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.
45
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.