vmwareteam3
Contributor
Contributor

Orchestrator MSSQL and getInt

Jump to solution

Hi all,

we have a problem with the orchstrator appliance and the database driver. We have some workflows which retrieves data from an MSSQL database, and those workflows fail on the appliance if a column in the table is of type integer. The same workflow runs fine on an orchestrator instance on Windows.

We are using version 4.2.1 build 555 on windows and linux.

Any ideas....

TIA

     Thomas

0 Kudos
1 Solution

Accepted Solutions
dimitrovg
VMware Employee
VMware Employee

If you check the API defined for the ResultSet class there is no getInt() method described. But there is getNumber(string) defined.

This should work for you.

View solution in original post

0 Kudos
9 Replies
igorstoyanov
VMware Employee
VMware Employee

Do you use the SQL plugin to retrieve the data from MSSQL?

If yes, did you updated to the latest update release: http://communities.vmware.com/message/2070837

Visit http://blogs.vmware.com/orchestrator for the latest in Cloud Orchestration.
0 Kudos
thechaos
Enthusiast
Enthusiast

Hi,

no, i'm using my own script's, like this:

System.log("dbserver "+dbServer);

System.log("dbport   "+dbPort);

System.log("dbuser   "+dbUser);

System.log("dbname   "+dbName);

System.log("dbdomain "+dbDomain);

var ConfigArray = new Array();

if (!dbServer) {

    dbServer = "dbserver";

}

if (!dbPort) {

    dbPort   = "1433";

}

if (!dbName) {

    dbName   = "db";

}

if (!dbUser) {

    dbUser = "vco";

}

if (!dbPassword) {

    dbPassword = "password";

}

if (!dbDomain) {

    var dburl = "jdbc:jtds:sqlserver://"+dbServer+":"+dbPort+"/"+dbName;

} else {

    var dburl = "jdbc:jtds:sqlserver://"+dbServer+":"+dbPort+"/"+dbName+";domain="+dbDomain;

}

System.log("URL => "+dburl);

var main = new JDBCConnection();

var con;

try  {

    con = main.getConnection( dburl, dbUser, dbPassword );

    //

    //  get the db version

    //

    var SQLQUERY = "select * from dbo.dbinfo";

    var qversion = con.createStatement();

    var result   = qversion.executeQuery(SQLQUERY);

    while (result.next()) {

//

// here the error happens .......

// if i use getString i get an result

//

        dbversion = result.getInt("version");

        System.log("DB Version => "+dbversion);   

    }

    switch(dbversion) {

        case 2:

            SQLQUERY = "select * from dbo.config";

            break;

        default:

            System.log("DB version not supported");

            con.close();

            return ConfigArray;       

            break;

    }

    var qconfig      = con.prepareStatement(SQLQUERY);

    var result      = qconfig.executeQuery();   

    var x = 0;

    if (result) {

        result.next();

        switch (dbversion) {

            case 2:

                ConfigArray[x++] = result.getString("mailfrom");

                System.debug(ConfigArray[0]);

                ConfigArray[x++] = result.getString("mail_approver");

                System.debug(ConfigArray[1]);

                ConfigArray[x++] = result.getString("vcuser");

                System.debug(ConfigArray[2]);

                break;

            default:

                System.log("DB version not supported");

                con.close();

                return ConfigArray;   

                break;

        }

    }

}

catch( ex )  {

    throw "Something failed (Reason: " + ex + ")";

} finally {

    if (con) {

        con.close();

    }

}

Any ideas ?

     Thomas

P.S. The databse plugin version is 2.0.0.42, and the SQL plugin has version 1.1.0.337

return ConfigArray;

0 Kudos
bbonev
VMware Employee
VMware Employee

Hi Thomas,

What kind of error do you get? Can you show some log messages?

Also you can try to implement your scenario using the SQL plugin. I think it is a lot easier with it and you don't need to write so much additional code.

Best regards,

Boyan

0 Kudos
vmwareteam3
Contributor
Contributor

Hi,

i get the following error:

[2012-07-03 16:02:12.924] [I] dbdomain
[2012-07-03 16:02:12.925] [I] URL => jdbc:jtds:sqlserver://hajdb211vm03.appl.msds.insite:1433/TUI_vCO_Prod
[2012-07-03 16:02:12.938] [I] Something failed (Reason: InternalError: Invalid column index 0. (Dynamic Script Module name : GetConfig#52)) (Dynamic Script Module name : GetConfig#91)

Changing this by using SQL will take some time, as we have a lot of workflows where we use this way, so for the moment this is not an solution, and on windows it is working 😉

Regards

     Thomas

0 Kudos
bbonev
VMware Employee
VMware Employee

Can you try to change the SQL query for getting the DB version from "select * from dbo.dbinfo" to "select version from dbo.dbinfo" to see if it is any better. Another cause for the problem may be different MS JTDS driver versions, but you already said the vCO server is  same version.

0 Kudos
dimitrovg
VMware Employee
VMware Employee

If you check the API defined for the ResultSet class there is no getInt() method described. But there is getNumber(string) defined.

This should work for you.

View solution in original post

0 Kudos
vmwareteam3
Contributor
Contributor

Hi,

ok that seems to work, will test my workflows. The question then only is why does it not give an error on windows 😉

Regards

     Thomas

0 Kudos
dimitrovg
VMware Employee
VMware Employee

Well, getInt is not documented and not tested. The reason it fails is that getInt is overloaded in Java - there are two methods getInt(int) and getInt(String). In JavaScript there is no overloading by type because there is no strong types. By some reason in linux JDK implementation returns first the getInt(int) method and the parameter is cast to number - this is how you get Invalid column index 0. Under Windows the same returns first getInt(String) so everything works.

0 Kudos
vmwareteam3
Contributor
Contributor

Hi,

ok, sometimes it's better to check the api and not 'google' ;-), i will replace all getInt with getNUmber, and than test everything, if it then fails i will ask again Smiley Wink

Thanks for the help

     Thomas

0 Kudos