23.05 External Database Connection from SAPGUI

Prerequisites

• Products: Liquid UI WS, Liquid UI Server or Local DLL, Client Software
• Commands: return(), pushbutton(), load()

Purpose

To establish a connection to an external database from the SAP GUI and define functions to open the database, access its components, and close the connection. To demonstrate the process, we are considering the SAP Easy Access screen as an example and we will walk you through the following steps:

1. Delete the image container on the SAP Easy Access screen
2. Add push buttons to execute the corresponding functions
3. Add database connection parameters
4. Add functions to establish a connection and interact with the database

User Interface

//Create the file SAPLSMTR_NAVIGATION.E0100.sjs inside your scripts folder for customizing the SAP Easy Access screen.
//Now, add the Liquid UI script to the above file and save it.

Customization

1. Delete the image container on the SAP Easy Access screen using the del() command.
   
    

   //Deletes the image container on the SAP screen
   del("X[IMAGE_CONTAINER]");
   

    
   

    
2. Add three toolbar push buttons with labels Open DB, Execute SQL, and Close DB to execute the corresponding functions.
   
    

   //Creates push buttons and executes the corresponding functions
   pushbutton([TOOLBAR],'Open DB','?',{'process':procOpenDB})
   pushbutton([TOOLBAR],'Execute SQL','?',{'process':procExecute})
   pushbutton([TOOLBAR],'Close DB','?',{'process':procCloseDB})
   

    
   
    
3. Define database connection parameters and assign them to a variable.
   
    

   //Adds database connection parameters
   var dbName ={server:'SQLSVRNAME',dbname:'TESTDB',user:'sa',pass:'mypwd'};     // Database Connection String
   //Assigns database connection parameters to the variabledb
   ConnectTo = dbName;
   if(!db) {
       db = null;
   }
   

4. Add the ODBCconnect function to establish a connection to the external database, as defined below.
   
    

   // Function to establish a connection to the Database
   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;
   }
   

5. Now, add another function called procOpenDB to open the database connection.
   
    

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

6. Establish the procExecute function to validate the database connection and provide an error notification if the connection is not successfully established.
   
    

   // Function to check if the database connection exists
   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');
       }
   }
   

7. Then, create a function called procCloseDB to close the database connection.
   
    

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

    

   //At this juncture, invoke the wsodbc function within the ESESSION.sjs file located in your scripts directory by using load(); save the file.

   // Loads the wsodbc
   load('wsodbc');
   

SAP Process

1. Refresh the SAP screen, and click the Open DB toolbar push button. Then, you will be connected to the external database, as depicted in the image below.
   
    
   

    
2. Similarly, upon clicking on the Execute SQL and Close DB toolbar push buttons, you can access the database and terminate the connection.