Wrapped java.sql.SQLException: General error

OL Connect Professional DataMapper
1

When trying to connect to my odbc I am getting a general error. I’ve used this format before without issue, but can’t get past this error now.

Thanks in advance.

var connectionURL = "jdbc:odbc:TrainingChecklist";
var Item_Number = record.fields.Item_Number;

if(Item_Number){
  var csvConnection = db.connect(connectionURL,"","");
  var csvQuery = "SELECT * FROM \"PSOcodesAdjustedv1.csv\" WHERE EDP_Code =" + "'" + Item_Number + "'";
  resultSet = csvConnection.createStatement().executeQuery(csvQuery);
  resultSet.next();
}
2

See this recent post. Even though it’s about Workflow, I believe the same issue is occurring in the DataMapper (since both issues are about querying CSV files through ODBC).

I’ve just tested your code with my own CSV file (after implementing the changes mentioned in that other post) and everything works as expected.

By the way, here’s a quick tip to make your JS code more readable. Use the following syntax to avoid having to escape quotes or switching between double and single quotes inside a string:

var csvQuery = "SELECT * FROM [myFile.csv] WHERE ID = '@@@' ";
resultSet = csvConnection.createStatement().executeQuery(
  csvQuery.replace("@@@",Item_Number)
);

I personally find that easier to read.

3

Would the same be applicable to a lookup done from datamapper to an Excel workbook? Does the workbook’s cells need to be set to a certain data type to correct the “general error” problem?
Right now, the workbook I’m trying to use for the look up has “General” set as the data type for the cells I want to use as the key parameter.

4

If the data file is a CSV file, then the same applies. If it’s an XLS file, then the driver used is distinct from CSV and it probably behaves differently (I haven’t tried).