Announcement

Collapse
No announcement yet.

SQLitening simultaneous remote and local database access?

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

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

  • #2
    https://forum.powerbasic.com/forum/u...ote-connection

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          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
          Michael Mattias
          Tal Systems Inc. (retired)
          Racine WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            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'
            How long is an idea? Write it down.

            Comment


            • #7
              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.
              How long is an idea? Write it down.

              Comment

              Working...
              X