SQL Active Record and aliases

[ericr999]

Hello,

I'm trying to run a SQL Query with joins, and some fields share the same names in two different tables, but I need there status, my query works perfectly directly in MySQL, and to keep a record which column is from which table i'm creating an alias on it.

SELECT n.status as rds, i.status as installation FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id WHERE s.id=447;

Normally the results look like this:

+---------+--------------+

| rds     | installation |

+---------+--------------+

| created | created         |

+---------+--------------+

But in my scriptable task, I see this:

[2016-08-30 13:00:19.371] [I] SELECT n.status as rds, i.status as installation FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id WHERE s.id=447;

[2016-08-30 13:00:19.415] [I] DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {status=created}

[2016-08-30 13:00:19.424] [E] Error in (Workflow:Validation des étapes pré-installation / Get server request id (item0)#24) Couldn't get a response from the database

[2016-08-30 13:00:19.525] [E] Workfow execution stack:

***

item: 'Validation des étapes pré-installation/item0', state: 'failed', business state: 'null', exception: 'Couldn't get a response from the database (Workflow:Validation des étapes pré-installation / Get server request id (item0)#24)'

workflow: 'Validation des étapes pré-installation' (d3bff982-2107-4585-b0aa-ef92f6eba4ae)

|  'attribute': name=database type=SQL:Database value=dunes://service.dunes.ch/CustomSDKObject?id='c9402fe2-f833-4411-8c8e-f0ed37130c96'&dunesName='SQL:Database'

|  'attribute': name=serverRequestId type=string value=

|  'input': name=vmid type=string value=447

|  'output': name=nstatus type=string value=null

|  'output': name=istatus type=string value=null

*** End of execution stack.

I have no clue which status is this and then the rest of my code doesn't work.

Here's my complete scriptable task:

var result = null;

var query = 'SELECT n.status as rds, i.status as installation ' +

            'FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id ' +

            'WHERE s.id=' + vmid + ';';

System.log(query);

var response = database.readCustomQuery(query);

System.log(response);

for (var i = 0; i < response.length; i += 1) {

  if (response != null && response.lenght == 1) {

  nstatus = response[i].getProperty("rds"); 

  istatus = response[i].getProperty("installation"); 

  break;

  }

}

if (result) {

  nstatus = result.toString();

  istatus = result.toString();

  System.log('Result: ' + nstatus);

  System.log('Result: ' + istatus);

} else {

  throw 'Couldn\'t get a response from the database';

}

[iiliev]

Hi,

On the first line of your scriptable task you are setting the value of the variable result to null. However, nowhere in the rest of the code you are changing this value, so when the execution reaches the last if statement, the result value will be still null and the condition will always be evaluated as false, causing the else statement to be executed and the error 'Couldn't get a response...' to be thrown.

So you need to revisit your code and either assign some actual value to result variable before checking it for null, or use some other condition to check if there is a response from the database.

[ericr999]

mmm correct! Oups my bad!

Also, how can I access the different columns ? with the getProperty and getFieldNames ?

[iiliev]

Yes.

[ericr999]

Still something weird about the result i'm getting.

For the moment I've simplified my task to this:

var query = 'SELECT n.status as rds, i.status as installation ' +

            'FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id ' +

            'WHERE s.id=' + vmid + ';';

System.log(query);

var response = database.readCustomQuery(query);

System.log(response);

System.log("1: " + response[0].getProperty('status'));

the result is:

[2016-08-31 13:23:50.930] [I] SELECT n.status as rds, i.status as installation FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id WHERE s.id=447;

[2016-08-31 13:23:50.945] [I] DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {status=toto}

[2016-08-31 13:23:50.951] [I] 1: toto

it seems as if I only have one column, and even the alias rds and installation, doesn't show up because if I change my code:

var query = 'SELECT n.status as rds, i.status as installation ' +

            'FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id ' +

            'WHERE s.id=' + vmid + ';'

System.log(query);

var response = database.readCustomQuery(query);

System.log(response);

System.log("1: " + response[0].getProperty('rds'));

I get this:

[2016-08-31 13:25:17.130] [I] SELECT n.status as rds, i.status as installation FROM serveur as s join nas as n on n.id=s.nas_id join installation as i on i.id=s.installation_id WHERE s.id=447;

[2016-08-31 13:25:17.142] [I] DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {status=toto}

[2016-08-31 13:25:17.144] [E] Error in (Workflow:Validation des étapes pré-installation / Get server request id (item0)#7) ch.dunes.util.NoSuchPropertyException

Of course it doesn't see the rds column, but why ? To me its seems to be returning the last column from the query.

I also tried this:

System.log("1: " + response[0].getProperty('status'));

System.log("2: " + response[0].getProperty(response[0].getFieldNames()[0]));

System.log("2: " + response[0].getProperty(response[0].getFieldNames()[1]));

And got this:

[2016-08-31 13:32:52.897] [I] 1: toto

[2016-08-31 13:32:52.900] [I] 2: toto

[2016-08-31 13:32:52.903] [E] Error in (Workflow:Validation des étapes pré-installation / Get server request id (item0)#9) ch.dunes.util.NoSuchPropertyException

So clearly there's only 1 column, unless I misunderstood how to use the object, but there's little documentation on it in the API Explorer.

[iiliev]

I tested it in my environment (using a PostgreSQL db) and it appears to work - there were 2 columns in the result.

Perhaps it is some MySQL-specific issue. Do you have a different database type to test with (ie. PostgreSQL)? If it also doesn't work for you with PostgreSQL, could you send me the SQL scripts you use to create/populate your database so I can recreate the exactly same db structure in my environment?

Also, which versions of vRO and MySQL are you using?

[ericr999]

A collegue of mine had an idea. Since I usualy do specific queries and only get 1 single column, and I never actually tried to do a simple select but with multiple columns on a single table.

And this test worked as expected. So i'm guessing that there's an issue with the join and the sql plugin used. I'll open up a case with the support. You did a join as well in your query on your postgresql server ? Wondering if its an issue with mysql or other db as well ?

Using VRO 7.0.1 and mysql 5.5, and the vro sql plugin version 1.1.4

Thanks Illian!

[iiliev · Accepted solution]

Yes, I used a verbatim copy of your query, with 2 joins

SELECT n.status as rds, i.status as installation

FROM serveur as s

  join nas as n on n.id=s.nas_id

  join installation as i on i.id=s.installation_id

WHERE s.id=447;