Dynamic Database Switching: Difference between revisions

From DataFlex Wiki
Jump to navigationJump to search
m
Correcting INT file entry
m (adding)
m (Correcting INT file entry)
 
(8 intermediate revisions by the same user not shown)
Line 10: Line 10:
*NatHols - National Holidays
*NatHols - National Holidays


'''Customer Tables''' (exist many times)
'''Customer Tables''' (exist many times in different databases)
*Customers
*Customers
*Transactions
*Transactions
Line 16: Line 16:
Some applications - those used by the customers - will only access one database, perhaps defined by a user's login, while others - those used by administrators perhaps - will need to be able to switch between databases. However certain kinds of application, even as used by the customers, may require the capability to dynamically switch database: web applications (especially if using process pooling) are an example of this.
Some applications - those used by the customers - will only access one database, perhaps defined by a user's login, while others - those used by administrators perhaps - will need to be able to switch between databases. However certain kinds of application, even as used by the customers, may require the capability to dynamically switch database: web applications (especially if using process pooling) are an example of this.


This article will look at a technique for handling this using [[MySQL]] and the [[Mertech]] database driver for that database.
==Technique using a MySQL Database==


==Technique using MySQL Database==
This article will look at a technique for handling this using [[MySQL]] and the [[Mertech]] database driver for that database.  The techniques for other database servers may be added later.


===The Database Switching Object===
===The Database Switching Object===


The first thing that we need is a global object (global, at least partly because database connections and DataFlex file buffers are also global) to handle the actual database switching. This is probably best created in a package containing both the class definition and an object instance of that class - thus only the first "'''Use'''" statement in any program will actually include it and cause the object to be created.
 
<source lang="vdf">
<source lang="vdf">
Use MerTech.inc
Use MerTech.inc
Line 85: Line 86:
   End_Procedure  // CloseCustomerTables
   End_Procedure  // CloseCustomerTables
    
    
   // Switches database:
   // Switches database - this is the only fully public message in the class:
   Procedure SwitchDB String sDB
   Procedure SwitchDB String sDB
       String sCurr
       String sCurr
        
        
       Get_Database_Name to sCurr  // Note: Mertech docs say not supported on MySQl,
       Get_Database_Name to sCurr
                                  // but seems to work.
   
       Move (Trim(sDB)) to sDB
      Move (Uppercase(Trim(sDB)))  to sDB
       Move (Uppercase(Trim(sCurr))) to sCurr
       If (sDB = sCurr) Procedure_Return  // Don't do more than we have to.
       If (sDB = sCurr) Procedure_Return  // Don't do more than we have to.
        
        
Line 99: Line 101:
   End_Procedure
   End_Procedure
    
    
   // Add a table to the list of "customer" tables:
   // Add a table to the list of "customer" tables (semi-private):
   Procedure AddCustomerTable Integer iTable
   Procedure AddCustomerTable Integer iTable
       Integer i
       Integer i
Line 132: Line 134:
===DataDictionary Sub-Class===
===DataDictionary Sub-Class===


We then need to define which tables will be "customer" tables - that is the ones which will exist as different versions in different databases. To do this it is most convenient to have a subclass of the Data Access DataDictionary class, eith just in the workspace, or in a library workspace if it is to be used in more than one project (placed in the AppSrc directory it either case).  In this we will:
We then need to define which tables will be "customer" tables - that is the ones which will exist as different versions in different databases. To do this it is most convenient to have a subclass of the Data Access DataDictionary class, either just in the workspace, or in a [[library workspace]] if it is to be used in more than one project (placed in the AppSrc directory in either case).  In this we will:


* Create a property in Construct_Object
* Create a property in Construct_Object
Line 138: Line 140:
   
   
<source lang="vdf">
<source lang="vdf">
Use DataDict.pkg
Use DataDict.pkg // The Data Access DD class


// We want to should ensure that the global handle is defined, so that this
// We want to ensure that the global handle is defined, so that this
// class will work even if the database switch object is not in use.
// class will work even if the database switch object is not in use:
#IFDEF ghoDbSwitch
#IFDEF ghoDbSwitch
#ELSE
#ELSE
Line 148: Line 150:
#ENDIF
#ENDIF


// Register the procedure we are (maybe) going to call, so calss will work in
// Register the procedure we are (maybe) going to call, so class will work in
// all cases.
// all cases:
Register_Procedure AddCustomerTable Integer iTab
Register_Procedure AddCustomerTable Integer iTab


// Our sub-class of the standard DataDictionary class, on which we will base all
// our table data disctionaries:
Class cMyDataDictionary is a DataDictionary
Class cMyDataDictionary is a DataDictionary
    
    
Line 163: Line 167:
       Forward Send End_Construct_Object
       Forward Send End_Construct_Object


       // IF this is a customer table AND we have the switcher object:
       // IF this is a customer table AND we have the switcher object, call
       If (pbCustomerTable(Self) and (ghoDbSwitch <> 0)) ;
      // the procedure passing it the file number:
       If (pbCustomerTable(Self) and ghoDbSwitch) ;
         Send AddCustomerTable of ghoDbSwitch (Main_File(Self))
         Send AddCustomerTable of ghoDbSwitch (Main_File(Self))
   End_Procedure  // End_Construct_Object
   End_Procedure  // End_Construct_Object
Line 170: Line 175:
End_Class  // cMyDataDictionary
End_Class  // cMyDataDictionary
</source>
</source>
In the [[Visual DataFlex Studio]] you should then set this sub-class to be the super-class for all of your data dictionaries: Tools -> Configure Workspace -> Class Preferences tab -> DataDictionary and put cMyDatadictionary (or whatever you are calling yours) and cMyDataDictionary.pkg in the two columns.
===Data Dictionaries===
In the data dictionary class file for each of your tables, you should ensure that these are based on your data dictionary sub-class, then, if the table to be marked as a "customer" table, set that property in the Construct_Object procedure:
<source lang="vdf">
Use  cMyDataDictionary.pkg          // DataDictionary Class Definition
Open Customer
Class Customer_DataDictionary  is a cMyDataDictionary
    Procedure Construct_Object
      Forward Send Construct_Object
     
      Set pbCustomerTable to True
    End_Procedure  // Construct_Object
End_Class  // Customer_DataDictionary
</source>
===Without Data Dictionaries===
If you need to have the database switching capability for tables you are opening directly, instead of using data dictionaries for them, you can just use the same call that was placed in the data dictionary sub-class, but using the table's File_Number rather than the Main_File property:
<source lang="vdf">
  If ghoDbSwitch Send AddCustomerTable of ghoDbSwitch Customer.File_Number
</source>
===Swiching Database===
Finally, having set up all the required infrastructure, actually invoking the database change becomes very simple. Each code module which uses it should implement the conditional declaration and initialisation of the global handle and register the SwitchDB procedure to ensure it will not cause compile-time or run-time errors if the Database Switcher is not present:
<source lang="vdf">
#IFDEF ghoDbSwitch
#ELSE
  Global_Variable Handle ghoDbSwitch
  Move 0 to ghoDbSwitch  // Initalise to zero
#ENDIF
Register_Procedure SwitchDB String sDatabase
</source>
(''Possibly this too might be placed in a package file of its own, including registering the AddCustomerTable procedure as well, since effectively it has already been used three times.'')
Then switching database becomes a simple matter of invoking that procedure:
<source lang="vdf">
      If ghoDbSwitch Send SwitchDB sDatabase
</source>
This might be invoked in the login module of some applications, where the database to use is determined by the user's identity, or in the Session Manager module of an [[Ajax]] [[Web Application]] where the database to use is maintained in the Session record, or simply in the change of a visual control (such as a Combo Form) in an application in which the user can choose between many databases to work on.
===In the .INT files===
The Mertech Set_Database_Name command, on which the underlying functionality is based, will only be effective if the database is '''not''' set in the .INT file - so you should '''remove''' the line "'''DATABASE_SPACE_NAME XXXXX'''" from the appropriate .INT files (and indeed probably from all the .INT files). Then the .TD files should be deleted so that they will be freshly regenerated on next access from the .INT files.
[[Category:Database Connectivity]]
[[Category:Tutorials]]
[[Category:Cookbook]]

Navigation menu