External Database Connection from SAPGUI

[Benjamin Dasari]

Purpose:
To create a connection to an external Database from SAPGUI and interact with it and close the connection.

Liquid UI Code:

//***********************************************
// ESESSION.sjs
// Loading Files on new session
//***********************************************
load('wsodbc');


//***********************************************
// SAPLSMTR_NAVIGATION.E0100.sjs
// User Interface
//***********************************************
del('X[IMAGE_CONTAINER]');                   // Delete ActiveX Container on SAP Easy Access screen
pushbutton([TOOLBAR],'Open DB','?',{'process':procOpenDB})
pushbutton([TOOLBAR],'Execute SQL','?',{'process':procExecute})
pushbutton([TOOLBAR],'Close DB','?',{'process':procCloseDB})

var dbName ={server:'SQLSVRNAME',dbname:'TESTDB',user:'sa',pass:'mypwd'};    // Database Connection String
dbConnectTo = dbName;
if(!db) {
   db = null;
}


// Function to establish connection to Database based on parameters
function ODBCconnect(dbase)   {   
// var sConnectTrusted = 'Driver={SQL Server Native Client 10.0};Server={'+dbase.server+'};Database={'+dbase.dbname+'};Trusted_Connection=Yes';
// var sConnectUser = 'Driver={SQL Server Native Client 10.0};Server={'+dbase.server+'};Database={'+dbase.dbname+'};UID={'+dbase.user+'};PWD={'+dbase.pass+'}';
    var sConnectTrusted = 'Driver={SQL Server};Server={'+dbase.server+'};Database={'+dbase.dbname+'};Trusted_Connection=Yes';
    var sConnectUser = 'Driver={SQL Server};Server={'+dbase.server+'};Database={'+dbase.dbname+'};UID={'+dbase.user+'};PWD={'+dbase.pass+'}';
   
   println("sConnectTrusted: ",sConnectTrusted);
   println("sConnectUser: ",sConnectUser);
    var sConnect = '';

    if(dbase.user) {
      sConnect = sConnectUser;
   } else {
      sConnect = sConnectTrusted;
   }
    println('Connect String Used: ' + sConnect);
    try{
      var dbObj = new Odbc(sConnect);
   }
   catch(err){
      message("E: Error with Database Connectivity" + err.description);
      return NULL;
   }
   
   println('\nConnected Successfully to '+dbObj.dbms+'. Server '+dbase.server+' Database '+dbase.dbname);
 
    return dbObj;
}


// Function to Open Database Connection
function procOpenDB() {      
    if(!db) {
      println('Opening session database...');
        db = ODBCconnect(dbConnectTo);
   }
}


// Function to execute commands/scripts once the connection is established
function procExecute() {   
    if(db) {
      println('DB Details:'+db.dbms);
      var strSQL = "select * from usertbl";
      for record <- db.exec('select username from usertbl') {
         println(record['username']);
      }
   } else {
      return('E: Database Connection/Reference Lost');
   }
}


// Function to Close Database Connection
function procCloseDB() {   
    if(db) {
      println('Closing ODBC Connection');
      db = null;
   }
}


See attachments for code samples!