web2py - Choose DB

Question

I have two DB (Oracle and MSSQL) with the same Tables. It's possible to Choose one of those DB when logging into the Application.

view.html

<form action="{{=URL('test?dbs=')}}">
    <label for="dbs">Choose a DB:</label>
    <select name="dbs">
      <option value="oracle">Oracle</option>
      <option value="mssql">MSSQL</option>    
    </select> 
    <input type="submit" value="Submit">
</form>

{{=db_choose}}

controller.py

def test():
    db_choose = request.vars.dbs
    if db_choose == "oracle":
        db = DAL('oracle://username/password@test')
        return dict(db_choose = db)
    elif db_choose == "mssql":
        db = DAL('mssql://username:password@localhost/test')
        return dict(db_choose = db_choose)
    else:
        return dict(db_choose = db_choose)
    return dict(db_choose = db_choose)

Answer 1

You need to persist the choice from request to request, so maybe store the choice in the session.

In a model file (e.g., db.py):

db = DAL('oracle://username/password@test' if session.db == 'oracle' \
         else 'mssql://username:password@localhost/test')

In the controller:

def test():
    session.db = request.vars.dbs or 'mssql'
    return dict(db_choose = session.db)

If you want the choice to persist beyond a single session, consider storing each user's choice in their auth_user record (if there is a login), or storing it in localStorage in the browser.