[TSL Script] 在WinRunner中应用DB语句 zz

原文地址 http://www.cntesting.com/hphtml/?thread-71.html

在TCL中经常使用DB数据库语句增加测试比对、数据抽取、测试用例其他一些应用，由于WinRunner自带的语句包装不够，所以自升级一些DB支持语句是非常必要的，下面是Amit Kulkarni提供的一些这方面支持语句：
1. GetDBColumnValue(in strSql, in strColumn, out strVal)，这个方法取得某数据列值，前提是这个数据列只有唯一的值，不能出现多列。
例如：
strSQL = "Select count(1) from PRODUCT_MASTER";
strColumn = "COUNT";
rc = GetDBColumnValue(strSql, strColumn, strVal);
2. GetDBRow(in strSql, out strHeader, out nHeaderCount, out strRow )，这个方法取得某数据行值，前提是这个数据列只有唯一的行，不能出现多行。
3. GetDBColumnAllValues(in strSql, in strColumn, out strVal[], out nRecord)，这个方法取得某数据所有列值。
4. GetDBAllRows(in strSql,out strHeader, out nHeaderCount, out strRow[], out nRecord)，这个方法取得某数据所有行值。

#--------------------------------------------------------------------------------------
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 = db_get_field_value ("obDatabase","#" & (i-1),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
           }
           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 = 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;
}