SQLitening simultaneous remote and local database access?

[Michael Rice]

I've looked here and also googled it...but can't seem to land on the answer. Has anyone been able to use SQLitening to open both a remote and local database simultaneously? I appreciate any experiences or insight you might be willing to share on the topic.

[Manuel Valdes]

https://forum.powerbasic.com/forum/u...ote-connection

[Michael Rice]

Hi Manuel, yes I also saw that thread. I think it’s leaning towards an issue with trying to get a server accessible via network. That part I have working... https://forum.powerbasic.com/forum/u...ing-pbforum-db

What I’m seeking to do now... As a client, link to that remote server and also create a local database (on client’s workstation) and make a local copy of the remote database.

[Michael Rice]

Below is an example of the type of thing I'm trying to get to work...

The slAttach statement (in red) is returning an error saying the local database doesn't exist...but it actually does.

Added later...I believe that the reason it's returning correctly an error saying the file doesn't exist on the server side!!

#BREAK         ON
#COMPILE       EXE
#DEBUG DISPLAY ON
#DEBUG ERROR   ON
#DIM           ALL
#OPTIMIZE      SPEED
#REGISTER      NONE
#TOOLS         OFF

#INCLUDE ONCE "C:\SQLitening\inc\SQLitening.inc"

FUNCTION PBMAIN

   LOCAL sDB_FileName_Out              AS STRING
   LOCAL sSQL                          AS STRING

   sDB_FileName_Out = EXE.Path$ + "PBforum_LOCAL.db"

   slOpen sDB_FileName_Out,"C"

   sSQL = "CREATE TABLE IF NOT EXISTS Forums "                               + _
          "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
          " RecordAddedDate TEXT(15) NOT NULL, "                             + _
          " Name TEXT(50) UNIQUE NOT NULL COLLATE NOCASE, "                  + _
          " ForumURL TEXT UNIQUE NOT NULL COLLATE NOCASE); "                 + _
          "CREATE TABLE IF NOT EXISTS ForumAuthors "                         + _
          "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
          " RecordAddedDate TEXT(15) NOT NULL, "                             + _
          " Author TEXT(25) UNIQUE NOT NULL COLLATE NOCASE); "               + _
          "CREATE TABLE IF NOT EXISTS Threads "                              + _
          "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
          " RecordAddedDate TEXT(15) NOT NULL, "                             + _
          " ThreadNumber INTEGER UNIQUE NOT NULL, "                          + _
          " ThreadURL TEXT UNIQUE NOT NULL COLLATE NOCASE, "                 + _
          " Forum_Link INTEGER NOT NULL, "                                   + _
          " ThreadTitle TEXT(20) NOT NULL COLLATE NOCASE, "                  + _
          " FOREIGN KEY(Forum_Link) REFERENCES Forums(RowID)); "             + _
          "CREATE TABLE IF NOT EXISTS ThreadContent "                        + _
          "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
          " RecordAddedDate TEXT(15) NOT NULL, "                             + _
          " Thread_Link INTEGER NOT NULL, "                                  + _
          " PostNumber INTEGER NOT NULL, "                                   + _
          " Author_Link INTEGER NOT NULL, "                                  + _
          " PostDate TEXT(15) NOT NULL, "                                    + _
          " PostContent TEXT NOT NULL, "                                     + _
          " FOREIGN KEY(Thread_Link) REFERENCES Threads(ThreadNumber),"      + _
          " FOREIGN KEY(Author_Link) REFERENCES ForumAuthors(RowID));"

   slExe sSQL

   IF slGetErrorNumber THEN
      CON.STDOUT "Error slExe failed " + FORMAT$(slGetErrorNumber)
      CON.STDOUT sSQL
   END IF

   slClose

   slConnect "38.17.54.213",51234
   slOpen "PBforum\PBforum.db" + $BS + "Read%Only", "R"  '1 open with read-only password

[COLOR=#c0392b]  slAttach sDB_FileName_Out,"LOCAL"[/COLOR]

   sSQL = "INSERT INTO LOCAL.Forums "                                        + _
          "           (RowID, "                                              + _
          "            RecordAddedDate, "                                    + _
          "            Name, "                                               + _
          "            ForumURL) "                                           + _
          "SELECT T1.RowID, "                                                + _
          "       T1.RecordAddedDate, "                                      + _
          "       T1.Name, "                                                 + _
          "       T1.ForumURL "                                              + _
          "  FROM Forums T1 "                                                + _
          " WHERE T1.RowID NOT IN (SELECT RowID FROM LOCAL.Forums) "         + _
          " ORDER BY T1.RowID ASC;"

   slExe sSQL

   IF slGetErrorNumber THEN
      CON.STDOUT "Error slExe failed " + FORMAT$(slGetErrorNumber)
      CON.STDOUT sSQL
      EXIT FUNCTION
   END IF

   slClose

END FUNCTION

[Michael Mattias]

While the SQLIte DB and its API will have its own rules... GENERALLY in DB software each separate database to be SIMULATANEOUSLY open for access requires a separate connection, which means more than one connection handle or connection object, meaning one separate PB variable per connection to keep track of these handles or objects and use them for subsequent commands.

That a DB is remote or local *GENERALLY* makes no difference (but see SQLite documentation), although the connection strings/commands may be different for remote/local physical databases.

Note there are no (AFIAK) "Windows Native" Sql-compliant database tools or programming libraries (except WMI which is not useful for this scenario). They are all "third party products."

MCM

[Mike Doty]

MM is correct. SQLitening internally keeps a unique handle for each slOpen.
It also keeps track of all variables whether thread is in local or remote mode.

In this example both the background and foreground thread pass 1 so they both call slConnect to use server.
Any combination of calling with or without server is fine.

$IP="YOUR IP HERE"
%PORT=51234

#INCLUDE ONCE "win32api.inc"
#INCLUDE ONCE "sqlitening.inc"

GLOBAL gs AS STRING                    'results of all threads

FUNCTION PBMAIN () AS LONG

 slSetProcessMods "E2"                 'continue or abort option on errors.
 LOCAL hThread  AS LONG                'thread handle
 THREAD CREATE Remote(1) TO hThread    'pass 1 to use server in other thread
 DoEverything 1                        'pass 1 to use server in PBMAIN
 WaitForSingleObject hThread,%INFINITE 'wait for thread to finish
 THREAD CLOSE hThread TO hThread       'close thread handle
 ? gs,,"Results"                       'display results from all threads

END FUNCTION

THREAD FUNCTION Remote(BYVAL UseServer AS LONG) AS LONG
 DoEverything UseServer
END FUNCTION

SUB DoEverything(ConnectVariable AS LONG)
 LOCAL x AS LONG
 LOCAL sArray() AS STRING
 IF ConnectVariable THEN slConnect $IP,%PORT
 slOpen "sample.db3"
 FOR x = 1 TO 1
  slSelAry "select count(*) from parts",sArray(),"Q9c"
  LogIt sArray()
 NEXT
 IF ConnectVariable THEN slDisconnect
END SUB

SUB LogIt(sArray() AS STRING) THREADSAFE
 STATIC counter AS LONG
 INCR counter
 gs+= USING$("#  &",counter,JOIN$(sArray(),$CR)) + $CR
END SUB'

[Mike Doty]

When in remote mode the filename must be in or underneath the server folder.
I am surprised you do not get an access denied error -8 since the filename is not allowed to have c: in it.

Attach
'--- Remote Mode ---
'The FileName is assumed to be relative to the folder which the service is running from.
'Since this is running on a remote server SQLitening can not allow the user to access files anyplace on the server.
' Access is denied to any FileName that has a colon,
'a double dot, or begins with a backslash. This will insure that the file is in same folder as the service or below it.

[Mike Doty]

'

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slSetProcessMods "E2"
 LOCAL sDB,sharename AS STRING

 'LOCAL MODE
 sharename = "z:\bin\"    'server folder mapped
 sDB = "PBforum_LOCAL.db" 'must be in relative path to server
 slOpen sharename + sDB,"C"
 slClose

 'REMOTE MODE
 slConnect "38.17.54.213",51234
 slOpen "sample.db3"
 slAttach sDB,"LOCAL"
 slDisconnect
 ? "done"
END FUNCTION'

You should also know about slSetProcessMods "L0" and slSetProcessMods "L1" when both local and remote mode are used in the same thread.
If for example, an error occurs in remote mode and you want to switch to local mode the command slSetProcessMods "L0" may be needed.

slSetProcessMods

· Ln = Load either the local or remote processing lib and ruts. This along with push/pop database allows you to access both a local and remote database in the same running program. WARNING: The misuse of this may cause a GPF.
· n = 0 will load SQLite3.DLL for Local Access Mode
· n = 1 will load SQLiteningCliend.DLL. for Remote Access Mode

[Michael Rice]

Michael & Mike, thanks for your feedback on my question. After some trial/error with SQLitening I ended up finding exactly as you both describe. SQLite does have a nice ATTACH feature for attaching an additional database to a single connection. It looks like (although not tested by me) SQLitening even allows for attaching this additional DB...looks like they both just need to be on the same end (remote or local) Given what SQLitening delivers...it seems very reasonable to me that it would need to be implemented that way.

When I get a chance I’ll post a record-by-record copy utility to make a local copy of the database. That same framework can then be used as a template for doing that for any local database or file format.

[Mike Doty]

slCopyDatabase (rsDestinationFileName String, [rsModChars String, rsDatabaseName String]) Long

I will work on a program that switches back and forth between local and remote mode. It requires slPopDataBase and slPushDatabase.
I have not done that in a long time and never needed it.

[Mike Doty]

'Open databases and "slPushDatabase shandle(x)" once to save needed data for each database.
'"slPopDatabase shandle(x)" is called to make a database current.

'This does not demonstrate switching between local and server mode.
'That requires slSetProcess "L0" for local mode and slSetProcessMods "L1" for server.

The purpose of this is to be able to switch between open databases.


Preview shows correctly. Posting removes indentation.

#INCLUDE "sqlitening.inc"
THREADED tshandle(),tsAll AS STRING
MACRO bind(parm)=slBuildBindDat(parm,"T")              'easy binding of text strings

FUNCTION PBMAIN () AS LONG

 LOCAL x AS LONG, sData, sDbName AS STRING
 DIM tshandle(1 TO 3)                                   'array to hold database handle and more

 'slConnect "xxx.xx.xx",12345                           '

 FOR x= 1 TO UBOUND(tshandle)                           'open and optionally create databases

  sDbName = "db" + FORMAT$(x)                          'database names
  slOpen sDbName,"C"                                   'open database
  slPushDatabase tshandle(x)                            'save info on database (no handle is current)
  slPopDatabase  tshandle(x)                            'make handle current
  slexe "drop table if exists table1"                  'start fresh
  slexe "create table if not exists table1(c1 text)"   'create table
  sData = bind("data in table1 of database " + sDBName)'bind string
  slexeBind "insert into table1 values(?)",sData       'insert into table1 of database x

 NEXT

 FOR x = 1 TO UBOUND(tshandle)                         'for each database
  GetDatabase x                                        '   make database x current
 NEXT                                                  'loop

 ? tsAll,,"Push once pop many"                         'display results

END FUNCTION                                           '

SUB GetDatabase(x AS LONG)
 IF x <1 OR x> UBOUND(tshandle) THEN BEEP:EXIT SUB
 slPopDatabase tshandle(x)                             'make database current
 LOCAL sql AS STRING                                   '
 LOCAL sArray() AS STRING                              'array to hold recordset
 sql = "select * from table1"                          'select statement
 slSelAry sql,sArray(),"Q9c"                           'execute select
 tsAll+= JOIN$(sArray(),$CR) + $CR                     'join into global string
END SUB

[Mike Doty]

  Test if this line is indented.

Does bold work here?
It works when previewing in this database programming forum.

[Dale Yarker]

deleted

[Mike Doty]

Michael,

Posted source code in the test forum (it works there.)
https://forum.powerbasic.com/forum/t...test-forums-aa

[Mike Doty]

Switch between local and remote database.
It is easier to use a separate thread.

https://forum.powerbasic.com/forum/u...ith-sqlitening

  Tags are not working in this forum

[Mike Doty]

[SIZE=12px][COLOR=#c0392b][B]T[/B][/COLOR][/SIZE][SIZE=16px][COLOR=#c0392b][B]ags working![/B][/COLOR][/SIZE][SIZE=12px][COLOR=#c0392b]  [/COLOR][/SIZE]
Thank you!!, [B]PowerBASIC![/B]