Prerequisites
- Products: Liquid UI WS, Liquid UI Server or Local DLL, Client Software
- Commands: return(), pushbutton(), load()
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;
}
}
