returning an array from db.select producing odd results.

DonnaMoores

I have a sahi script as shown below:

__setGlobal("WebServerUsername",     "WebServer");
    __setGlobal("WebServerPassword",     "Password99");
    __setGlobal("MicrosoftDriver",        "com.microsoft.sqlserver.jdbc.SQLServerDriver");
    __setGlobal("MicrosoftUrl",           "jdbc:sqlserver://MACAAA081\\SQLEXPRESS;databaseName=AnimalTracking");

    var db1 = _getDB(_getGlobal("MicrosoftDriver"), _getGlobal("MicrosoftUrl"), _getGlobal("WebServerUsername"), _getGlobal("WebServerPassword"));
    var $rs2=[];
    $rs2[0]=[];
    _set($rs2, db1.select("SELECT TOP 1 BREED_NAME FROM CONFIG.CATTLE_BREEDS"));
    _debug("boo:"+$rs2[0]["BREED_NAME"]);

Which works fine by returning "boo:Aberdeen Angus" in my Sahi console.

However I now want to put this into a function so that I can pass in the sql query as shown below:

function adHocQueryMicrosoft($sqlString)
{
    __setGlobal("WebServerUsername",     "WebServer");
    __setGlobal("WebServerPassword",     "Password99");
    __setGlobal("MicrosoftDriver",        "com.microsoft.sqlserver.jdbc.SQLServerDriver");
    __setGlobal("MicrosoftUrl",           "jdbc:sqlserver://MACAAA081\\SQLEXPRESS;databaseName=AnimalTracking");

    var db = _getDB(_getGlobal("MicrosoftDriver"), _getGlobal("MicrosoftUrl"), _getGlobal("WebServerUsername"), _getGlobal("WebServerPassword"));
    var $rs=[];
    $rs[0]=[];
    _set($rs, db.select("SELECT BREED_NAME FROM CONFIG.CATTLE_BREEDS"));
    _debug("hoo:"+$rs[0]["BREED_NAME"]);     
}

The problem is when I call the function using

_include("includes/adhocQueryFunctions.sah");
adHocQueryMicrosoft("SELECT TOP 1 RETURN_VALUE = BREED_NAME FROM CONFIG.CATTLE_BREEDS");

The output I get in my Sahi console window is "hoo: undefined".

Any suggestions would be very helpful

Thank you

Donna

stevenlong

Hi

Don't know why that's happening - seems that the _debug() statement is being run before the _set(), so I guess you could try moving the _debug() out of the function and calling it after.

For what it's worth, my approach is like this:

b2b_db_utils.sah

function getDbConnection() {
    return _getDB("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@//50.1.0.157:1521/DBNAME", "USER", "PASSWORD");
}

var $manual_booking_select = "select ponumber, to_char(anticipateddate, 'dd/mm/yyyy') as anticipateddate from booking b, bookedorder bo where bo.booking_id = b.id and b.bookingstatus = 'Manual'";

...
// other select statements

confirm_manual_delivery_booking.sah

_include("../includes/b2b_db_utils.sah");

var db = getDbConnection();
var $rs = [];
$rs[0] = [];

_set($rs, db.select($manual_booking_select));

...
// rest of test script steps

Might be suitable for what you want to do.

Steve.

narayan

If the result is a single row, and does not use a for loop over the result set, then use this pattern:

1) Write a function which makes the db connection and gets the row, extracts the column from the row and returns the column.
2) Invoke the above function from your scheduler function.

Rewriting the above case:

/*These variables are essentially constants. No need to use _set or _setGlobal*/
WebServerUsername = "WebServer";
WebServerPassword = "Password99";
MicrosoftDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
MicrosoftUrl = "jdbc:sqlserver://MACAAA081\\SQLEXPRESS;databaseName=AnimalTracking";

/*
This function does not use any scheduler function and so does not use any $ variable either.
It should always be invoked from inside a scheduler function
*/
function getColumn(sql, row, column){
    var db = _getDB(MicrosoftDriver, MicrosoftUrl, WebServerUsername, WebServerPassword);
    var rs = db.select(sql);
    return rs[row][column];     
}

/*
calls runQuery from inside the physical line starting with _set. 
So needs $ before the sqlString, row and column variables.
*/
function adHocQueryMicrosoft($sqlString, $row, $column)
{
    $breed = "";
    _set($breed, getColumn($sqlString, $row, $column));
    _debug($breed);
}

adHocQueryMicrosoft("SELECT TOP 1 RETURN_VALUE = BREED_NAME FROM CONFIG.CATTLE_BREEDS", 0, "BREED_NAME");

DonnaMoores

Thanks for all your help.

Cheers
Donna

deepakkadam

Below mentioned is the script that I am trying to execute in the controller itself. I am expecting it to return the EndDate. But it is returning "ExceptionOccured - [Object Error]. Why is this happening? The same thing I had tried some days back and it was working. Now what's the problem.

var db =_getDB("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:DBName", "UID", "PWD");
var $rs=[];
$rs[0]=[];
_set($rs,db.select("SELECT TOP 1 * from DBTABLE where StartDate='2008-08-13 00:00:00.000'"));
_debug($rs[0]["EndDate"]);

Please suggest some solution this problem. Is there anything wrong with the script?
Please help me out

Regards
Deepak