SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

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

Pages: [1] 2 3 ... 47
1
You've got Questions? We've got Answers! / Re: Encryption at database level
« on: December 18, 2018, 08:48:53 PM »
SQlitening's built-in AES256 Cookbook encryption is used on columns and can be turned on and off.
SQLitening's was written by Greg Turcheson and would not be understood by third-party viewers.

Buying SQLite's AES256 encryption or https://www.zetetic.net/sqlcipher/ encrypt at the database level.
These products may work with some third-party products


2
You've got Questions? We've got Answers! / Re: Auto Switching and Uploading
« on: December 06, 2018, 02:06:08 PM »
I don't think slpushdatabase and slpopdatabase will help much
The big thing is keep the databases in sync and how to prevent duplicate keys from being rejected.
I'm thinking like an ATM machine can't give out money if central server is down.
One thing about cloud computing is the data is duplicated and maintained by different locations.
Some may not know that they have to pay for that duplicated data if sent to different servers.
Is cloud safer and more secure?  Yes, in my opinion.  Sorry for getting off subject.

#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.12"
 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 = 0 THEN
  IF MSGBOX("Could not connect, work local?",%MB_YESNO OR %MB_SYSTEMMODAL,"Connect error") = %IDNO THEN
   ? "Ending the program",%MB_SYSTEMMODAL,"Thank you"
   EXIT FUNCTION
  END IF
 END IF

 IF gConnect THEN
  slDisconnect
  ? "disconnect and end"
 ELSE
  ? "end"
 END IF
END FUNCTION

3
You've got Questions? We've got Answers! / Re: Auto Switching and Uploading
« on: December 06, 2018, 01:31:11 PM »
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

4

https://sqlite.org/2018/sqlite-dll-win32-x86-3260000.zip

477,151 bytes sqlite-dll-win32-x86-3260000.zip
911,698 bytes sqlite3.dll

5
You've got Questions? We've got Answers! / Re: Can not start the server
« on: December 04, 2018, 01:48:42 PM »
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

6
You've got Questions? We've got Answers! / Re: Using rowid zero
« on: November 20, 2018, 01:08:46 PM »
'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

7
You've got Questions? We've got Answers! / Re: Using rowid zero
« on: November 20, 2018, 08:51:52 AM »
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

8
You've got Questions? We've got Answers! / Re: Using rowid zero
« on: November 19, 2018, 10:48:44 AM »
No problem unless:
“The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.”

9
You've got Questions? We've got Answers! / Re: SQLitening & RMChart
« on: November 05, 2018, 09:51:28 AM »
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

10
You've got Questions? We've got Answers! / Re: SQLitening & RMChart
« on: November 04, 2018, 09:09:12 PM »
#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


11
You've got Questions? We've got Answers! / Re: Messages suppression
« on: November 01, 2018, 12:58:19 PM »
Quote
How 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

12
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.

13
Just know that Gary Beene would have to be contacted.

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

15
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


Code: [Select]
#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

Pages: [1] 2 3 ... 47