Announcement

Collapse
No announcement yet.

SQLitening simultaneous remote and local database access?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mike Doty
    replied
    Code:
    Tags working!
    Thank you!!, PowerBASIC!

    Leave a comment:


  • Mike Doty
    replied
    Switch between local and remote database.
    It is easier to use a separate thread.

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

    Code:
      Tags are not working in this forum

    Leave a comment:


  • Mike Doty
    replied
    Michael,

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

    Leave a comment:


  • Dale Yarker
    replied
    deleted

    Leave a comment:


  • Mike Doty
    replied
    Code:
      Test if this line is indented.
    Does bold work here?
    It works when previewing in this database programming forum.

    Leave a comment:


  • Mike Doty
    replied
    '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.

    Code:
    #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

    Click image for larger version

Name:	posterror.png
Views:	87
Size:	132.8 KB
ID:	797621

    Leave a comment:


  • Mike Doty
    replied
    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.

    Leave a comment:


  • Michael Rice
    replied
    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.

    Leave a comment:


  • Mike Doty
    replied
    '
    Code:
    #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

    Leave a comment:


  • Mike Doty
    replied
    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.

    Leave a comment:


  • Mike Doty
    replied
    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.
    Code:
    $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'

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • Michael Rice
    replied
    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!!


    Code:
    #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
    
      slAttach sDB_FileName_Out,"LOCAL"
    
       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

    Leave a comment:


  • Michael Rice
    replied
    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.

    Leave a comment:


  • Manuel Valdes
    replied
    https://forum.powerbasic.com/forum/u...ote-connection

    Leave a comment:


  • SQLitening simultaneous remote and local database access?

    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.
    Last edited by Michael Rice; 1 Aug 2020, 10:05 AM.
Working...
X