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 the use of the bot solution. In this article, we will demonstrate a sample ODBC solution, and the steps involved are as follows.

First, create two new tables in an OBDC-compliant database. In our example, we are using an existing database named 'FCGUIXT' that we have created on a server named 'Trident'. However, the information in your database and Server is 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 specifies 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.
Create the following script files in your designated script directory.
- elogon.sjs: This file contains the user login credentials and functions to create a database connection and execute the actions inside the IW21 transactions.
- dbo.sjs: This file contains the connection parameters for the SQL server database and functions to perform the communication tasks between the ODBC database and SAP.
- FUNCTIONS_SYSTEM.sjs: This file contains the generic, or global functions that we are using in our example.
  This file contains only three functions.
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.
```
load('wsodbc'); 
```
- dbo.sjs: This file contains the connection parameters for the SQL server database.
```
load('dbo.sjs');
```
- FUNCTIONS_SYSTEM.sjs: This file contains the generic functions.Open the elogon.sjs file and write a load statement to load the following files.
```
load('FUNCTIONS_SYSTEM.sjs'); 
```
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.
Add an SQL query in the elogon.sjs file. This query reads the user information from the FTEUSER table and closes the session if there are no records that meet the defined criteria. Columns that are not sent to the SAP are marked as X. An example SQL query is shown below.
```
var SQL_DB = that.ODBCCon;
record = SQL_DB.select("SELECT * from [dbo].[FTEUSER] where COMPLETED is NULL"); 
```

Add a loop to check the table for modified records, extract the usernames and passwords, and if records are found, set the designated user's login credentials and run the 'iw21_save()' function on the next command. If no records are found, the process will exit. 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 'iw21_save' function does the main work. When there are modified records, this function will run and post the data from the database to SAP. The function starts with an SQL query to read data from the FTENOTIFICATION table. Here's an example query:
```
record = SQL_DB.select("SELECT * from [dbo].[FTENOTIFICATION] where [USER]=' "+username+" ' and MODIFIED='x'"
```
This query retrieves all the records from the database that correspond to the user names listed in the table and are marked as being modified.

Add a function to store data into an array, separating each data point. The function will retrieve the notification type, description, function location, and equipment type from the records. Each of these will be trimmed to the relevant part and then stored in the array.

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 in the array has been stored it is transferred to the SAP. To transfer the data, create a new variable ‘ii’, and set it to zero. Then, we will input the data from the array for the first record into SAP and save it. Will continue this process until all notifications in the array have been read into SAP. Once all notifications have been processed, the program 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:;

Once all notifications have been read, the process will print messages in the Console and then update each record by marking the 'COMPLETED' field with an 'X'. This will prevent a record from being read more than once. After that, the process will end. 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 autoexit functionality to re-login. The main function will then re-run and process the next user's records. A previous example explains The autoexit functionality in more detail, 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

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 are as follows.
- server: This specifies the server's name where the ODBC database resides. In our example, we are connecting to a server named 'Trident'. The format will be 'TRIDENT\\TRIDENT'.
- dbname: This specifies the database’s name 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 code for 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 the value of the dbConnectTo variable, as shown below.
```
dbConnectTo = dbTrident;
```
Set 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--; 
} 
```

To establish a connection to the database, we'll begin by creating an empty variable to hold the connection and then create both a trusted and an untrusted connection. Attempt a trusted connection first, then untrusted if necessary. Once the connection is made, we will return the name of the database we are connected to. The code 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 function 'opendb()' to open 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 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

The last file 'FUNCTIONS_SYSTEM.sjs' contains the generic functions, here we create only three functions which are as follows.

The first function is 'answeryes'. This function is used to log back into SAP after logging out if specific conditions are met. We use the SharedMemory API to write a variable to shared memory. Then we check this shared memory, and if the necessary conditions are met, we 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 checks whether 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

5.5 Connecting to ODBC Database with bot

ODBC: dbo.sjs

ODBC: FUNCTIONS_SYSTEM.sjs