How to read Array of SQL:ActiveRecord

[DLally]

We're trying to write a script here to query a database that keeps inventory of our servers, and automatically increment the server name to the next available number in the chain.  We're able to query the database but the output looks to be encrypted as it's not readable.  I'm thinking I need to convert it somehow to make it useful.  Anyone know how I could do this?  I'm a bit new to javascript so this is all new to me. 

Here's the script we've got so far.

var preservername = resultVMName;

function zeroPad(num,count)

{

  var numZeropad = num + '';

  while(numZeropad.length < count) {

    numZeropad = "0" + numZeropad;

}

  return numZeropad;

}

var query = 'SELECT MAX(ServerName) FROM servers WHERE ServerName like "%'+preservername+'%" limit 1';

System.log("Executing query" + query + "on database" + database.name);

resultRecordsOut = database.readCustomQuery(query);

System.log(resultRecordsOut[0]);

  var maxserver = resultRecordsOut[0].toString();

  System.log("This is the maxserver value" + maxserver);

    var prenumber = maxserver.substring(0,10);

    var numbers = parseInt(maxserver.substring(10,13));

    var newnumbers = zeroPad(numbers + 1, 2);

    var newname = prenumber + newnumbers;

System.log(newname);

[stvkpln]

The SQL plugin is going to return an array... even if the array is 1... So, what your variable actually needs to be (I'm assuming the resulting query actually returns a single row) is actually set it like this:

resultRecordsOut = database.readCustomQuery(query)[0];

Otherwise, if you're expecting the result to be greater than one, and only want to read the last record in the database....

resultRecordsOut = database.readCustomQuery(query);

resultLastRecord = resultRecordsOut[resultRecordsOut.length - 1];

[DLally]

[2014-07-16 09:04:34.524] [I] Executing querySELECT MAX(ServerName) FROM servers WHERE ServerName like "%testname%" limit 1on database

[2014-07-16 09:04:34.524] [I] DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {MAX(ServerName)=testname}

[2014-07-16 09:04:34.524] [I] This is the maxserver valueDynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {MAX(ServerName)=testname}

[2014-07-16 09:04:34.524] [I] DynamicWraNaN

[DLally]

It wont let me reply everything i wanted to say, weird.

But the post above is what is being outputted.  It's like it's not reading the array possibly?  Would I need to deserialize maybe?

[stvkpln · Accepted solution]

Ok... so, I think the last piece of the puzzle is this!  The value for each column is stored as a property inside the array, so, I think what you need to do is something like this:

resultRecordsOut = database.readCustomQuery(query);

resultLastRecord = resultRecordsOut[resultRecordsOut.length - 1];

System.log(resultLastRecord.getProperty("MAX(ServerName)");

That may get you where you need to be, more or less.

[DLally]

You're awesome, that's exactly what we needed!  Thank you so much.

[stvkpln]

No problem! Happy to help.... We struggled with how to extract data for an Oracle query for quite some time until we sorted out what was actually happening.

Happy scripting!

[DLally]

Just seeing if i could squeeze a little more help here!

One final thing I'm trying to do to complete this workflow is if resultsrecords1 comes back null or there is no value, then start at 01.  Currently it will increment the next server name based off the record it pulls.  For example, if it returns a record testname03 then it will increment the server name testname04.  However right now if there's no record then the workflow fails.  It's working fine right now like I said if the query returns a record, if not it's just returning a value 1 for the ResultVMName and not a server name.

I might not be explaing this well enough.  If not just let me know.

var preservername = recordOut;

function zeroPad(num,count)

{

  var numZeropad = num + '';

  while(numZeropad.length < count) {

    numZeropad = "0" + numZeropad;

}

  return numZeropad;

}

var query1 = 'SELECT MAX(ServerName) FROM servers WHERE ServerName like "%'+preservername+'%" limit 1';

System.log("Executing query" + query1 + "on database" + database.name);

resultRecordsOut1 = database.readCustomQuery(query1);

var resultLastRecord = resultRecordsOut1[resultRecordsOut1.length - 1];

System.log(resultLastRecord.getProperty("MAX(ServerName)"));

  var maxserver = resultLastRecord.getProperty("MAX(ServerName)") || '';

  System.log("This is the maxserver value" + maxserver);

    var prenumber = maxserver.substring(0,10);

    var numbers = parseInt(maxserver.substring(10,13));

if(maxserver.length>1){

   var newnumbers = zeroPad(numbers + 1, 2);

}else if (maxserver.length==0){

  var newnumbers = 01;

}

var newname = prenumber + newnumbers;

System.log(newname);

resultVMName = newname.toLowerCase();

[DLally]

bump, not sure why the forum isnt showing this thread as updated

[stvkpln]

Why not check the length of the returned array.. if the value is greater than 0, it runs through your process.. if the value isn't, it sets the output you need to 01? Should be an easy add...