More help with Postgres/Connector/Conversion

Q&A
1

I have a Postgres table, from which I want to get one row. This is necessary for my ‘registration’ procedure, that I need to get up quickly, and as a prototype for the rest of my project.

It has taken forever, as those of you who have read my rants and requests, know, to get this far.

My table:

CREATE TABLE public.customer
(
customerid bigint NOT NULL,
customer text,
password_open text,
password_encrypted text,
buyfor1 bigint,
buyfor2 bigint,
buyfor3 bigint,
buyfor4 bigint,
buyfor5 bigint,
lastchanged_date timestamp without time zone,
created_date timestamp without time zone,
CONSTRAINT customer_pkey PRIMARY KEY (customerid)
)
WITH (
OIDS=FALSE
);

Pretty simple that.

I have attached a simple (not for me, but for others) process (bos file) that reads, successfully one row. It packages it up nicely, as I think it should.

But I still can’t get the data out of it. The error “Declared return type class java.lang.String is not compatible with evaluated type class java.util.ArrayList for expression scrGetPostgresData” doesn’t make sense as I don’t have a String involved at this point.

My script is pretty simple to get the data, and it is returned using JsonBuilder:

import groovy.json.JsonBuilder;
import groovy.sql.Sql;
import java.sql.Driver;

List<Object> result = new ArrayList<Object>();

String query = "SELECT customerid, customer, password_open, buyfor1, created_date " +
"FROM customer " +
"LIMIT 1;";

Sql sql = BonitaSql.newInstance("jdbc:postgresql://172.17.0.2:5432/HFDataProd", "bonita_db_user","bpm", new org.postgresql.Driver());

sql.eachRow(query) { row ->
	def builder = new JsonBuilder();
	def line = builder{
		customerid  	row.customerid.toInteger();
		customer 		row.customer;
		password_open	row.password_open;
		buyfor1  		row.buyfor1.toString() == "null" ? ("0").toInteger() : row.buyfor1.toInteger();
		created_date	row.created_date;
	}
	
	result.add(line);
}
return result;</code>

My output target is also defined using JsonBulder. I’d think that JsonBuilder to JsonBuilder should work…

import groovy.json.JsonBuilder;
def builder = new JsonBuilder();
def line = builder{
customerid (“0”).toInteger();
customer null;
password_open null;
buyfor1 (“0”).toInteger();
created_date null;
}

return line;</code>

But it doesn’t. Can someone revise my thinking so that I can get data in and out? For this exercise, if I could have, I would have displayed my row as a table in Step 2…

THANKS!
2

Hi,

Looking at the configuration of your Postgres connector, it seems there is a mistake. In your case, the script scrGetPostgresData is supposed to return a SQL Query and not the result of that query.

To make it easier, what you can do is click on “Switch editor” and enter the following SQL Query: "SELECT customerid, customer, password_open, buyfor1, created_date from customer limit 1; "

Then click next, there you can choose scripting mode to use the resultSet executed to extract the data and convert it.

Cheers
Lio

3

Hi Ford,

Sorry I’ve been very busy with packing etc. moving to New Zealand.

I know what the problem is…and if I in anyway contributed to the problem I apologize.

First things first:

There are TWO types of Connector, Action ones and Actionable ones, well that’s what I call them.

The Action ones are ones that preexist, like the Postgres Connector that does all the work for you (well most of it), and the Actionable ones are ones that you write as Scripts and are then actioned accordingly.

What you have here is a mixing of the two.

In your Postgres connector (an Action connector) in Query (via Editor) you only have to return the actual Select statement. You do NOT action the data return. The select statement is always a string. So your code should be something like this:

String query = "SELECT customerid, customer, password_open, buyfor1, created_date" + " FROM customer " + " WHERE customer = " + bonitadefinedvariable + " LIMIT 1;";

return query;

The code editor allows the building of extremely complex select statement using variables from the pool of variables (including BDM objects).

This connector will ALWAYS return a resultset.

In the Output operations Definition this is where you will action the resultset to your variables:

  • Select Scripting Mode
  • Next
  • click on the resultset pencil
  • Select Script

and here is where you write the code to handle the return:

import groovy.json.JsonBuilder;

List result = new ArrayList();

while(resultset.next()) {
def builder = new JsonBuilder();
def line = builder{
customerid resultset.getInt(0);
customer resultset.getString(1);
password_open resultset.getString(2);
etc.
etc.
}

result.add(line);
}
return result;

Now in relation to the full code you have in the connector presently, this is to me Actionable code and should be written ONLY in a SCRIPT connector.

The rational for this is:

  • you create the query
  • you create and execute the query
  • you action the resultset directly
import groovy.json.JsonBuilder; import groovy.sql.Sql; import java.sql.Driver;

List result = new ArrayList();

String query = "SELECT customerid, customer, password_open, buyfor1, created_date " +
"FROM customer " +
“LIMIT 1;”;

Sql sql = BonitaSql.newInstance(“jdbc:postgresql://172.17.0.2:5432/HFDataProd”, “bonita_db_user”,“bpm”, new org.postgresql.Driver());

sql.eachRow(query) { row →
def builder = new JsonBuilder();
def line = builder{
customerid row.customerid.toInteger();
customer row.customer;
password_open row.password_open;
buyfor1 row.buyfor1.toString() == “null” ? (“0”).toInteger() : row.buyfor1.toInteger();
created_date row.created_date;
}

result.add(line);

}
return result;

So in summary you have two options:

  1. Keep using the Postgresql connector but rewrite the two code pieces (to return only the query and then action the data in the output definition), or
  2. Delete the Postgresql connector from your process and replace it with a pure SCRIPT connector and write your code in there.

Which one you want to do is up to you. We use a combination depending on circumstances.

I hope the above will help you work out which is good for you but at the moment you’re definitely mixing the two, which will not work.

regards
Seán

PS: If this reply answers your question, please mark as resolved.

4

New Zealand - wow. Good luck and hope to hear from you soon. Take care!

And thanks. I will get on that right away.

5

After we get this example working I’ll upload a Postgres database/bos file example to a permanent shared google drive folder as an example of external database access - it seems to be an issue for many more than me, but there’s no real example left of the solution afterwards. I’ll fix that.

6

Lio, unfortunately the SQL query window is inconsistent in how it returns the result. I cannot guarantee that the resultset will be graphical… and for complex queries, it almost always defaults to scripted mode, leaving me exactly where I am. Also, unless I’m sorely mistaken, this:

import groovy.json.JsonBuilder;
import groovy.sql.Sql;
import java.sql.Driver;

List result = new ArrayList();

String query = "SELECT customerid, customer, password_open, buyfor1, created_date " +
"FROM customer " +
“LIMIT 1;”;

Sql sql = BonitaSql.newInstance(“jdbc:postgresql://172.17.0.2:5432/HFDataProd”, “bonita_db_user”,“bpm”, new org.postgresql.Driver());

sql.eachRow(query) { row →
def builder = new JsonBuilder();
def line = builder{
customerid row.customerid.toInteger();
customer row.customer;
password_open row.password_open;
buyfor1 row.buyfor1.toString() == “null” ? (“0”).toInteger() : row.buyfor1.toInteger();
created_date row.created_date;
}

result.add(line);
}
return result;

returns the result, not the query.

But thanks for the try…

My issue is converting the resultset into something that I can get get out of the connector. If you can shed further light, I’d really appreciate it.

EDIT - Lio, apologies - after getting Sean’s reply, I now understand what you were trying to say. Thanks very much.

7

Hi,

No problem, Sean described it really well!

Hope you’re making good progress.

Lio

8

Sean, I’m pretty sure we’re getting there… but now I can’t move the data to a variable that I can use.

I have constructed two bos files - v1 and v2.

v1 uses query and assignment script, and I’m pretty sure I get a result, but I can’t get it into a variable: “is not compatible”.

v2 uses the straight script approach, and when I test it inside the connector,it tells me I have an empty script???

Since I haven’t managed to get anything useful into my form, it is pretty scant.

v1 and v2, if you could have a look.

Thanks

How’s New Zealand?

9

Hi Ford,

definitely getting there, and I’ve just created a bug report against Bonitasoft Data Handling, specifically for the External Data issues everyone is seeing. I’ll let you know how it goes.

I’m also working on a real example of how to show the data you now have in JSON format on the Form. Similar to my previous post on how to add data to a database, it’s taking time, and it’s frustrating.

I’m thinking I might have it completed today, but not sure…I don’t like the solution.

re NZ, no idea, yet. I’m packing up after 12 years in Saudi Arabia and after a holiday in the UK for a couple of weeks will be emigrating there. No longer a working trip but a complete change in life trip. :slight_smile:

regards
Seán