Liquid UI - WS Reference Library

External Database Connection from SAPGUI


Prerequisites


Runs On

  SAP GUI Android iOS WM Web
Direct Connect          
Liquid UI Server Connect        


Purpose

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

User Interface

Log into SAP and on the SAP Easy Access Screen you will see the toolbar pushbuttons to Open DB, Execute SQL and Close DB.



Liquid UI Script

//ESESSION.sjs

load('wsodbc');

//SAPLSMTR_NAVIGATION.E0100.sjs


// User Interface
del('X[IMAGE_CONTAINER]');
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;
    }
}