Liquid UI - Documentation - 5.5 Connecting to ODBC Database with bot

Updating SAP from an ODBC database is a use case that lends itself well to use of the bot solution. In this example, we will demonstrate a sample ODBC solution. The steps involved are

First, create two new tables in an OBDC-compliant database. In our example, we are using a database named 'FCGUIXT' that we have created on a server named 'Trident'. Your database and server information will of course be different. The two tables we created for this example are as follows.
- FTEUSER
- FTENOTIFICATION
Each table will contain specific columns of data. In the FTEUSER table, the data columns are as follows.
- USER: This column contains the user's SAP username in plain text.
- PASSWORD: This column contains the user's SAP password, also in plain text.
- COMPLETED: This column specified if the upload operation is or is not complete. If the data is uploaded to SAP, an 'X' will be placed in this column. Otherwise, the column is blank.
In the FTENOTIFICATION table, the data columns are listed below.
- USER: This column lists the user created. This should be the same as the SAP username.
- NOTIFTYPE: This column will list the notification type. This data corresponds to the 'Notification Type' field in SAP.
- DESCRIPTION: This column contains the description of the notification and corresponds to the description field in SAP.
- FUNCLOC: This column contains the functional location of the notification and corresponds to the same field in SAP.
- EQUIPMENT: This column contains the type of equipment the notification refers to and corresponds to the equipment field in SAP.
- MODIFIED: This column specifies if the data has been modified since the last upload. If any data has been changed and the data is ready to upload to SAP, this column will be marked by an 'X'. Otherwise, it will be blank.
Now create the necessary script files in your designated script directory. In our example, we will use three script files, which are as follows.
- elogon.sjs: This file contains the user login credentials. it also contains the function that creates the database connection as well as the function that actually executes the actions inside the IW21 transaction. This file will be explained in more detail later in this example.
- dbo.sjs: The dbo.sjs file contains the connection parameters for the SQL server database, as well as the functions that actually perform the communication tasks between the ODBC database and SAP. This file will also be explained in more detail later in this example.
- FUNCTIONS_SYSTEM.sjs: This file contains the generic, or global, functions that we are using in our example. This file contains only three functions, which will be explained in more detail later in this example.
Open the elogon.sjs file and write a load statement to load the following files.
- wsodbc.dll: This file contains the libraries that enable connections to be made between the ODBC database and SAP.
- dbo.sjs: This file contains the connection parameters for the SQL server database.
- FUNCTIONS_SYSTEM.sjs: This file contains the generic functions, as explained above.
An example set of load statements is shown below.

Note: The wsodbc file does not need to have the extension specified because it is a DLL. SJS files must have the extension specified in the load command.
```
load('wsodbc'); 
load('dbo.sjs'); 
load('FUNCTIONS_SYSTEM.sjs'); 
```
Still in the elogon script file, write an SQL query. This query will read the user information from the FTEUSER table, if the 'Completed' column is not marked 'X', indicating that the information already has been sent to SAP. This query will close the session if there are no records that meet the criteria you have defined. An example SQL query is shown below.
```
var SQL_DB = that.ODBCCon;
record = SQL_DB.select("SELECT * from [dbo].[FTEUSER] where COMPLETED is NULL"); 
```
This is followed by a loop that will check the table for records that have been modified, and will harvest the username and password associated with each of those records. If there are no records, the process will exit, otherwise, it will proceed to set the designated user's the login credentials and then to run the associated function 'iw21_save()' on the next enter command. The example code follows.
```
 if (record.length > 0){  
 for (var loop=0; loop < record.length; loop++){   
  var username = record[loop].USER;   
  var password = record[loop].PASSWORD;  
  } 
 } else {  
  enter('/nex'); 
 }
 set("F[User]", "&V[username]");
 set("F[Password]", "&V[password]"); 
 enter({process:iw21_save}) 
```
The main work is done by the 'iw21_save' function we introduced in the previous step. This function will run if there are any records that have been modified and will post the data from the database to SAP. The first element of this function is an SQL query that will read data from the FTENOTIFICATION table. An example query is shown below.
```
record = SQL_DB.select("SELECT * from [dbo].[FTENOTIFICATION] where [USER]=' "+username+" ' and MODIFIED='x'"
```
This query will get all records from the database for the user names listed in the table and which have been marked as being modified.
The next part of the function is designed to store data into an array, separating each data point. We will check to ensure that the record actually contains data and then we will extract the notification type, the description, the functional location, and the equipment type. Each of these will be trimmed to the relevant part and then stored in the array. An example is shown below.
```
arrNOTIF = [];  
 var tmp =[]; 
 if(record.length == 0){ 
 goto end_process; 
 } 
 for(var i = 0; i < record.length; i++) {
  tmp=[];  
  tmp.push(record[i].NOTIFTYPE.trim());  
  tmp.push(record[i].DESCRIPTION.trim()); 
  tmp.push(record[i].FUNCLOC.trim()); 
  tmp.push(record[i].EQUIPMENT.trim()); 
  arrNOTIF.push(tmp); 
 } 
ii=0; 
enter('/niw21');
```
After the data has been stored into the array as explained above, the function proceeds to the next action, which is to transfer the data in the array to SAP. To do this, we will first create a new variable 'ii' and set it to the value of zero. Then we will enter the data from the array for the first record into SAP and save it. As long as there are more notifications to be read, we will proceed to read each notification into SAP in turn. Once there are no more notifications in the array, the process will exit. The example code is shown below.
```
ii=0; 
enter('/niw21'); 
onscreen 'SAPLIQS0.0100' 
read_next_notification:; 
set("F[Notification type]", arrNOTIF[ii][0]); 
enter(); 
onscreen 'SAPLIQS0.7200' 
set('F[VIQMEL-QMTXT]', arrNOTIF[ii][1]); 
set('F[Functional loc.]', arrNOTIF[ii][2]); 
set('F[Equipment]', arrNOTIF[ii][3]); 
enter('/11'); 
//onscreen 'SAPLIQS0.0100' 
onscreen '*' 
ii++; 
if (ii > record.length - 1){   
 goto end_notification; 
} else { 
 goto read_next_notification; 
 } 
 end_notification:; 
```
After all the notifications have been read, the process first prints any messages in the Console and then updates each record by marking the 'COMPLETED' field with an 'X'. This prevents a record from being read more than once. The process then ends. The example code is shown below.
```
println('\n&&&&&&&-------message='+_message); 
updatequery ="UPDATE [dbo].[FTEUSER] SET COMPLETED='X' WHERE [USER]='"+username+"'"; 
var SQL_UPDATE = that.ODBCCon; 
SQL_UPDATE.exec(updatequery); 
end_process:; 
```
After the process ends for this user, we will use the auto exit functionality to re-login. The main function will then run again and process the next user's records. The auto exit functionality is explained in more detail in a previous example, so we will merely show the code below.
```
if(SharedMemory('WS').read().autoexit) 
 enter('/i', {process:answeryes}); 
} 
```
The related files for this example are as follows.
- dbo.sjs
- elogon.sjs
- FUNCTIONS_SYSTEM.sjs
These files are explained in more detail in the following sections.

ODBC: dbo.sjs

One of the script files necessary for the ODBC example we previously explained is the dbo.sjs file. This file contains the connection parameters and the functions used for creating the database connection with the ODBC database. The file is explained as follows.

The first section of this file creates a new connection. To do this, we will create a new variable for the connection and pass the specific parameters to it. The parameters involved are as follows.
- server: This is the name of the server where the ODBC database resides. In our example, we are connecting to a server named 'Trident'. The format will be 'TRIDENT\\TRIDENT'.
- dbname: This is the name of the database to which we are connecting. In our example, we are connecting to a database named 'FCGUIXT'.
- user: This is the username of the user connecting to the database. In our example, it is 'test'.
- pass: This is the password for the user connecting to the database. In our example, the password is 'password'.
The entire statement creating the new connection is shown below.
```
var dbTrident ={server:'TRIDENT\ 
\TRIDENT',dbname:'FCGUIXT',user:'user',pass:'password'}; 
```
We will now specify the new 'dbTrident' connection as the one to which we will connect by setting it as the value of the dbConnectTo variable as shown below.
```
dbConnectTo = dbTrident;
```
The following piece of code sets a global reference to the 'this' operator.

Note: You should not make any changes to this section of the code.
```
that = this; 
```
The next section of the dbo.sjs file checks to see if the database connection is open. If the connection is not open, this will open the connection. The code is shown below.
```
if(LOAD_ONCE){ 
 if(!ODBCCon){  
  ODBCCon = opendb(); 
 }  LOAD_ONCE--; 
} 
```

Once the connection is open, we need to execute an actual connection to the database. In this section of the file, we will first create an empty variable, to which we will pass a connection. Then, we will create two connections -one trusted and one untrusted. We will initially attempt to connect via the trusted connection. If the trusted connection is not available, we will connect via an untrusted connection. Once the connection is established, we will return the database to which we are connecting. The code we used is shown below.

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 sConnect = '';
  
   if(dbase.user) sConnect = sConnectUser;    
   else sConnect = sConnectTrusted; 
   try{  
  db = new Odbc(sConnect); 
 } 
 catch(err){ 
  message("E: Error with database connectivity");  
  return NULL; 
 }    
  return db; 
}

We also created a small function 'opendb()' that actually performs the task of opening the database. It serves to pass the connection parameters to the connection function 'ODBCConnect(dbase)' that we previously explained. This function is placed directly below the main connection function in the dbo.sjs file and the code we used is shown below.
```
 function opendb() {  
   if(!that.ODBCCon) {
  that.ODBCCon = ODBCconnect( dbConnectTo );  
  } 
 return that.ODBCCon; 
} 
```
The final function in the dbo.sjs file is the function that closes the database once all the tasks have been successfully executed. This function is called 'closedb()' and it deletes the connection and exits from SAP.
```
function closedb() {   
   if(that.ODBCCon) { 
 that.ODBCCon = null; 
 delete that.ODBCCon; 
 }   
  enter('/nex') 
}
```

ODBC: FUNCTIONS_SYSTEM.sjs

We created one other script file for this example of connecting to a ODBC database aside from the elogon.sjs and dbo.sjs files. This last file is called 'FUNCTIONS_SYSTEM.sjs' and it contains the generic functions that we are using for the example. This file contains only three functions, which are described as follows.

The first function that we placed in this file is the 'answeryes' function. This function is used to log back into SAP after logout if certain conditions are met. We are using the SharedMemory API to write a variable to shared memory. We then check this shared memory and if the requisite conditions are met, we will log back into SAP and repeat the process. The code is as follows.
```
function answeryes() 
 { 
  onscreen 'SAPLSPO1.0100'
  enter('=YES'); 
  SharedMemory('avatar').write({loop:true}); 
 } 
```

The second function in the FUNCTIONS_SYSTEM.sjs file checks to see if a variable contains a value or not. The code is shown below.

function isBlank(jvar) {    
    if (jvar== void 0 || jvar=="" || jvar==null) {       
	  return true;    
	} else {
    	return false;    
	} 
}

The final function in the FUNCTIONS_SYSTEM.sjs file is a trim function. It removes blank or white space from the values of the variables. The code is shown below.
```
String.prototype.trim = function() { 
 return this.replace(/^\s+|\s+$/g,""); 
}
```

Efficient SAP PM

Efficient SAP SD

Efficient SAP MM

Efficient SAP WM

Desktop

Mobile

For Android

For Apple

For Scanguns

Web

Offline

Server

Your IT Infrastructure

Developer Toolkit

Plant Maintenance

Sales & Distribution

Material Management

Warehouse Management

IT

Liquid UI Community

Liquid UI Documentation

Download Tools

Quick Links

Industries

Free Trial

5.5 Connecting to ODBC Database with bot

ODBC: dbo.sjs

ODBC: FUNCTIONS_SYSTEM.sjs