Example: dbo.sjs

Connection Parameters

The first section of the dbo.sjs file contains the connection parameters for the ODBC operation. Any global variables used for the operation also can be stored in this section. The connection parameters are as follows.

var db1 ={server:'TEST1',dbname:'NOTIFICATION',user:'user1',pass:'password'};
var db2 ={server:'TEST2',dbname:'WORKORDER',user:'user2',pass:'password1'};
var db3 ={server:'TEST3',dbname:'USER',user:'user3',pass:'password2'}; 

Below the variable for determining the database resides an IF statement. This creates the 'ODBCCon' object that holds the ODBC connection parameters and it also performs the database open and table create operations. The code is as follows.

if(LOAD_ONCE){
if(!ODBCCon)
ODBCCon = ODBCconnect(dbtest);
if(ODBCCon)
	dbTableCreate();
else 
	println("Failed DB Table Create Block");
LOAD_ONCE--;
}
var SESSIONID;

ODBCconnect(dbase)

The ODBCconnect function is passed the database name as a parameter and it then calls the database open function. This function also will qwuery to ensure that the requested database actually exists. Finally, it includes code to handle any errors that might occur during these operations. The code is shown below.

  function ODBCconnect(dbase)
{
  var dbtest ={server:'TEST2',dbname:'WORKORDER',user:'user2',pass:'password1'};

  try{
		db = new Odbc(dbtest);
	}
	catch(err){
		message("E: " +err.description);
	}
}

opendb()

This function is the one that actually opens a connection to whatever database you specify in the connection parameters. This function is called by the ODBCConnect function introduced above. THe code is as follows.

function opendb() {
	println(">>1 that.ODBCCon",that.ODBCCon);
    if(!that.ODBCCon) {
		println('Opening session database...');
        that.ODBCCon = ODBCconnect( dbConnectTo );
		println(">>2 DB connected to",that.ODBCCon);
	}
	println(">>3 that.ODBCCon",that.ODBCCon);
	return that.ODBCCon;
}

closedb()

The closedb function is the counterpart to the opendb function introduced above. This function will close the connection to the database once all operations are complete. The code is shown below.

function closedb() {
    /* \/ \/ \/ \/ \/ \/ \/ */
    if(that.ODBCCon) {
	that.ODBCCon = null;
	delete that.ODBCCon;
	}

    // this.name is set by newSession
    print('Connection closed\n');
	enter('/nex')
}

userTable(dbase)

The userTable function performs the following operations on the database:

• Checks to ensure that the user table exists.
• If the user table does not exist, the function will create the user table.
• If the user table exists, the function will query for the user 'admin'.
• If the user table does not exist, then there can be no 'admin' user, so the function will create the table and then will insert the 'admin' user.

The code is shown below.

function userTable(dbase){
	sqlQuery = "if not exists ( select * from sysobjects where name='user' and xtype='U')" +
"CREATE TABLE[dbo].[USER](" + "[RECID][varchar](15)NOT NULL," + "[USERNAME][varchar](12)NOT NULL," + 
"[PASSWORD][varchar](40)NOT NULL," + "[CLIENT][varchar](3)NOT NULL," + "[LANG][varchar](2)NULL," +
"[CREATE_DATA][date]NOT NULL," + "[MODIFIED_DATE][date]NULL," + "[CREATED_BY][varchar](10)NOT NULL," +
"[MODIFIED_BY][varchar](10)NULL," + "[LOCKED][varchar](1)NULL," + "[FIRST_LOGIN][varchar](1)NULL," +
"[PLANT][varchar](4)NOT NULL," + "
	dbase.exec(sqlQuery);
	
sqlQuery = "if not exists(select * from [dbo].[USER] where username = 'admin')
insert [dbo].[USER] values ('"+ system.newRecId('$S1')+ "','admin','adminpass','100','EN','10/12/2012','',
'system','','','X','1000','1000')"
}