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


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

                Comment


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

                  Comment


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

                    How long is an idea? Write it down.

                    Comment


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

                      Comment


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

                        How long is an idea? Write it down.

                        Comment


                        • #13
                          deleted
                          Dale

                          Comment


                          • #14
                            Michael,

                            Posted source code in the test forum (it works there.)
                            https://forum.powerbasic.com/forum/t...test-forums-aa
                            How long is an idea? Write it down.

                            Comment


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

                              Comment


                              • #16
                                Code:
                                Tags working!
                                Thank you!!, PowerBASIC!
                                How long is an idea? Write it down.

                                Comment

                                Working...
                                X