Upland OL User community

Datamapper ODBC Query in Repeat Step

#1

I have a xml file that I am looping on a particular node to extract data. I need to reference that value of the extraction and search a ODBC connection csv file for another value. My connection works and I am able to pull data in but not in the way I am looking for. As soon as I put the ODBC connection and extraction into the repeat steps, everything breaks and I am getting an error “Cannot read property fields from undefined”.

var connectionURL = “jdbc:odbc:TrainingChecklist”;
//var Description = record.tables.EDP[1].fields.Description;
var Description = record.tables.EDP[steps.currentLoopCounter].fields.Description;

if(Description){
var csvConnection = db.connect(connectionURL,"","");
var csvQuery = “SELECT * FROM “psocodes.csv” WHERE Description=” + “’” + Description + “’”;
logger.info(csvQuery);
resultSet = csvConnection.createStatement().executeQuery(csvQuery);
logger.info(resultSet);
resultSet.next();
}

image
image.png262×537 11.7 KB

#2

Which lines of the script give that error?

Also, in the following query:
“SELECT * FROM “psocodes.csv” WHERE Description=” + “’” + Description + “’”;
Make sure that the quotes around psocodes.csv aren’t the same as the ones handling the SQL query.

#3

I am getting the error on line:
var Description = record.tables.EDP[steps.currentLoopCounter].fields.Description;

The script pulls data from the ODBC connection if I move the LookUp Table, ODBC Variables and Action below the repeat. It isn’t accurate as I have to hard coding the EDP table iteration [1], but at least I know my connection works:

var connectionURL = “jdbc:odbc:TrainingChecklist”;
var Description = record.tables.EDP[1].fields.Description;
//var Description = record.tables.EDP[steps.currentLoopCounter].fields.Description;

if(Description){
var csvConnection = db.connect(connectionURL,"","");
var csvQuery = “SELECT * FROM “psocodes.csv” WHERE Description=” + “’” + Description + “’”;
logger.info(csvQuery);
resultSet = csvConnection.createStatement().executeQuery(csvQuery);
logger.info(resultSet);
resultSet.next();
}

#4

steps.currentLoopCounter is 1 based but using it as you do, an index in an array, it needs to be 0 based.

Try steps.currentLoopCounter-1

1 Like
#5

Oh my goodness! I swear I tried that, but tried SO many variations that I may have just missed that in this one. Thank you. Sometimes it is just another set of eyes because everything seems to blur when you look at it for so long.

1 Like
#6

Did that mistake a couple of times myself :wink:

1 Like