Interview Questions

The script for WinRunner Database Functions

Mercury WinRunner FAQ


(Continued from previous question...)

The script for WinRunner Database Functions

==========================================
The script for WinRunner Database Functions
by Amit Kulkarni
# Pre-requisites:
# ---------------
# Requires a Variable / Constant "gstrConnString" defined in your
# calling script / startup which holds the ODBC connection string
# How To Use:
# -----------
# Save this file as a compiled module in your search path.
# use load() OR reload() for using this script in the test or another function.
# define a variable/constant gstrConnString in your calling script/
# startup and put the ODBC connection string in this variable. eg.
# gstrConnString ="DRIVER={Oracle in OraHome92};SERVER=MANOJ; UID=BASECOLL;PWD=BASECOLL;DBA=W;APA=T;EXC=F; XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo; BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;";
# This is the string that I use; declaration is in Startup Script and get the value
# from configuration.xls
#
# public const gstrConnString = ddt_val(gstrConfigFilePath, "gstrConnString");
#
# Description:
# -----------
# Contains following functions
# 1. GetDBColumnValue(in strSql, in strColumn, out strVal)
# Use this function when you want only first /single value of strColumn.
# Usage:
# strSQL = "Select PRODUCT_CODE from PRODUCT_MASTER where PRODUCT_NAME = 'WINE'";
# strColumn = "PRODUCT_CODE";
# rc = GetDBColumnValue(strSql, strColumn, strVal);
# pause (strVal);
#
# 2. GetDBRow(in strSql, out strHeader, out nHeaderCount, out strRow )
# Use this function when you require entire first row of the result set.
# strSql = Query to execute
# strHeader = Header Names seperated by tab. (It will hold only 1024 char)
# Hence not so reliable
# nHeaderCount = Count of Columns in the result set.
# strRow = Row Tab seperated string.
#
# 3. GetDBColumnAllValues(in strSql, in strColumn, out strVal[], out nRecord)
# Use this function when you require entire content of a Column
# strSQL = Query to execute
# strColumn = Column form the Query for which values are required
# strVal[] = Array that holds the Values
# nRecord = Gives the Number of values retreived.
#
# 4. GetDBAllRows(in strSql,out strHeader, out nHeaderCount, out strRow[], out nRecord)
# Use this function when you require to get entire result set. This returns all the
# result set in an array each row in array is a tab seperated string.
# strSql = Query to execute
# strHeader = Header Names seperated by tab. (It will hold only 1024 char)
# Hence not so reliable
# nHeaderCount = Count of Columns in the result set.
# strRow[] = Array of Row Tab seperated string.
# nRecord = Count of values in strRow.
# # Notes:
# ------
# I have observed that I get correct results only when I use UPPER case while building the
# query.
# In case you find any defects in the script please communicate so that I am aware
# of the same and could enhance it further.
#-----------------------------------------------------------
public function GetDBColumnValue(in strSql, in strColumn, out strVal) #-----------------------------------------------------------

{
# Reference to the Connection String Constant
extern gstrConnString;
	
# Holds The result 0 is success any thing other 
than 0 is failed
	auto rc;
	
# Holds the Error that is returned by the ODBC...
auto strLastError;

# Holds the Number of rows that is returned by strSQL	
	auto nRecord;
	
	# Setting strLastError to Null
	strLastError ="";
	
	# Setting the rc to unsuccessfull	
	rc = -9999;
	
	# Attempt to connect...
rc  = db_connect("obDatabase",gstrConnString);
	if (rc!=0)
		{
	# If failed then return...error_code
report_msg("Could not Connect To database.");
			return rc;
		}

	# Attempt the query execution....
rc = db_execute_query("obDatabase", strSql,nRecord);
	if (rc!=0)
		{
		# If failed then return code
		db_disconnect("obDatabase");
report_msg("db_execute_query returned error.");
			return rc;

		}
	if (nRecord == 0)
	{
# If the records returned is 0 then....
		rc = 1;
		db_disconnect("obDatabase");
report_msg ("SQL: " & strSql & ". Returned Zero Rows !!!");
		return rc;
	}

	# Attempt to get the field value...
strVal = db_get_field_value ("obDatabase","#0",strColumn);
	if (strVal=="")
	{
# Case strVal is null ...
 Check whether any error has occured
db_get_last_error("obDatabase", strLastError);
		if (strLastError!="")
		{
# If error has occured then... return
		db_disconnect("obDatabase");
		rc = 2;
report_msg("Last DB Error: " & strLastError);
			return rc;
		}
# if there is no error then the
 field is having null as value
	}
	# Attempt to disconnect
	rc = db_disconnect("obDatabase");
	if (rc!=0)
	{
# If error then return...
report_msg("Could not disconnect.");
		return rc;
	}
	# Empty every thing and quit...
	strSql = "";
	strColumn ="";
	strLastError ="";
	return rc;
}

#--------------------------------------------------
public function GetDBRow(in strSql, out strHeader,
 out nHeaderCount, out strRow )
#---------------------------------------------------
{
# Reference to the Connection String Constant 
extern gstrConnString;
	
	# Holds the result set	
	auto rc;

#Holds the Error that is returned by the ODBC...
	auto strLastError;

# Holds the Number of records that are 
	returned by query....
	auto nRecord;

	# Set the strLastError to null.
	strLastError ="";

	# Set rc as unsuccessful
	rc = -9999;
	
# Attempt to establish a connection
rc  = db_connect("obDatabase",gstrConnString);
	if (rc!=0)
		{
# On error return the error code.
	
report_msg("Could not Connect To database.");
		return rc;
		}

#Attempt to execute the query...
rc = db_execute_query("obDatabase", strSql, nRecord);
	if (rc!=0)
		{
# On error return the error code
	db_disconnect("obDatabase");
report_msg("db_execute_query returned error.");
		return rc;
		}
# Case the number of records returned is zero then
	if (nRecord == 0)
	{
		rc = 1;
		db_disconnect("obDatabase");
report_msg ("SQL: " & strSql & ". Returned Zero Rows !!!");
		return rc;
	}
	# Attempt to get the Row 
rc = db_get_row("obDatabase", "#0", strRow);
	if (rc!=0)
		{
		# Case error
		db_disconnect("obDatabase");
report_msg("db_get_row returned error.");
			return rc;
		}	
	# Attempt to get Headers
rc = db_get_headers("obDatabase",nHeaderCount, strHeader);
	if (rc!=0)
		{
		# Case error then
		db_disconnect("obDatabase");
report_msg("db_get_headers returned error.");
			return rc;
		}
# if strRow is null then check if any error has occured
	if (strRow =="")
	{
db_get_last_error("obDatabase", strLastError);
		if (strLastError!="")
		{
# If strLastError is not null then return the error.
		rc = 2;
		db_disconnect("obDatabase");
report_msg("Last DB Error: " & strLastError);
			return rc;
		}
	}
	# Disconnect the db
	rc = db_disconnect("obDatabase");
	if (rc!=0)
	{
	report_msg("Could not disconnect.");
		return rc;
	}
	strSql = "";
	strLastError ="";
	return rc;
}


#-------------------------------------------------------
public function GetDBColumnAllValues(in strSql, in 
strColumn, out strVal[], out nRecord)
#-------------------------------------------------------
{
	# Reference to the Connection String Constant
	extern gstrConnString;
	
# Holds The result 0 is success any thing other
# than 0 is failed
	auto rc;
	
# Holds the Error that is returned by the ODBC...
	auto strLastError;

	# Holds index of the strVal array.	
	auto i;

	# Setting strLastError to Null
	strLastError ="";
	
	# Setting the rc to unsuccessfull	
	rc = -9999;
	
	# Attempt to connect...
rc  = db_connect("obDatabase",gstrConnString);
	if (rc!=0)
		{
	# If failed then return...error_code
report_msg("Could not Connect To database.");
			return rc;
		}

	# Attempt the query execution....
rc = db_execute_query("obDatabase", strSql,nRecord);
	if (rc!=0)
		{
		# If failed then return code
		db_disconnect("obDatabase");
report_msg("db_execute_query returned error.");
			return rc;

		}
	if (nRecord == 0)
	{
	# If the records returned is 0 then....
		rc = 1;
		db_disconnect("obDatabase");
report_msg ("SQL: " & strSql & ". Returned Zero Rows !!!");
		return rc;
	}

	i = 1;
	do 
	{
# Attempt to get the field value...
strVal[i] = db_get_field_value ("obDatabase","#" & 
(i-1),strColumn);
		if (strVal[i]=="")
		{
# Case strVal is null ... 
Check whether any error has occured
db_get_last_error("obDatabase", strLastError);
	if (strLastError!="")
	{	
# If error has occured then... return
	db_disconnect("obDatabase");
		rc = 2;
report_msg("Last DB Error: " & strLastError);
				return rc;
	}
# if there is no error then the field
 is having null as value
		}
		i++;	
	}
	while (i <= nRecord);

	# Attempt to disconnect
	rc = db_disconnect("obDatabase");
	if (rc!=0)
	{
# If error then return...
report_msg("Could not disconnect.");
	return rc;
	}
	# Empty every thing and quit...
	strSql = "";
	strColumn ="";
	strLastError ="";
	return rc;
}


#-----------------------------------------------------
public function GetDBAllRows(in strSql,out strHeader, 
out nHeaderCount, out strRow[], out nRecord)
#----------------------------------------------------
{
# Reference to the Connection String Constant 
	extern gstrConnString;
	
	# Holds the result set	
	auto rc;

# Holds the Error that is returned by the ODBC...
	auto strLastError;

	# Holds the index of Array strRow[]
	auto i;
	# Temporary string
	auto strTmp;
	# Set the strLastError to null.

	strLastError ="";
	strTmp = "";

	# Set rc as unsuccessful
	rc = -9999;
	
	# Attempt to establish a connection
rc  = db_connect("obDatabase",gstrConnString);
	if (rc!=0)
		{
		# On error return the error code.
report_msg("Could not Connect To database.");
			return rc;
		}

	# Attempt to execute the query...
rc = db_execute_query("obDatabase", strSql, nRecord);
	if (rc!=0)
		{
# On error return the error code
	db_disconnect("obDatabase");
report_msg("db_execute_query returned error.");
		return rc;
		}
# Case the number of records returned is zero then
	if (nRecord == 0)
	{
		rc = 1;
		db_disconnect("obDatabase");
report_msg ("SQL: " & strSql & ".
 Returned Zero Rows !!!");
		return rc;
	}
	i = 1;
	
	do 
	{
		strTmp = "";
# Attempt to get the Row 
rc = db_get_row("obDatabase", (i-1), strTmp);
		if (rc!=0)
		{
			# Case error
db_disconnect("obDatabase");
report_msg("db_get_row returned error.");
			return rc;
		}
# Push the strTmp in the array
		strRow[i] = strTmp;
		# Increment i
		i++;
	}while (i <= nRecord);

# Attempt to get Headers
rc = db_get_headers("obDatabase", nHeaderCount,
 strHeader);
	if (rc!=0)
		{
# Case error then
		db_disconnect("obDatabase");
report_msg("db_get_headers returned error.");
			return rc;
		}

	# Disconnect the db
	rc = db_disconnect("obDatabase");
	if (rc!=0)
	{
	report_msg("Could not disconnect.");
		return rc;
	}
	strSql = "";
	strLastError ="";
	strTmp="";
	return rc;
}

public function getConnection( inout strConn)
{
# Reference to the Connection String Constant 
	extern gstrConnString;
	
	# Holds the result set	
	auto rc;
	# Set rc as unsuccessful
	rc = -9999;
	
# Attempt to establish a connection
rc  = db_connect(strConn,gstrConnString);
	if (rc!=0)
		{
	# On error return the error code.
report_msg("Could not Connect To database.");
			return rc;
		}
	return rc;
}

(Continued on next question...)

Other Interview Questions