How to create a DSN-less connection to external databases in Connect Datamapper

OL Connect Professional DataMapper
1

Hi,

I hope someone can help me.

My XML data file contains several records. There is a field in each record, let’s call it connectonURL.
Its value can be dynamic depending on the record:

 "jdbc:odbc:Driver={Microsoft Access Driver(*.txt, *.csv)};DBQ=D:\\DB\\Manifest\\IR\\"

 "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + database + ";DriverID=22;READONLY=true"

 "jdbc;odbc:Driver={Microsoft Excel Driver (*.xls)};" +  "Driverid=790;" +  "Dbq=C:\MyPath\SpreadSheet.xls;" + "DefaultDir=C:\MyPath;"

The point I am making is that each record may connect to different external databases (Excel, CSV, Access, SQL or MySQL) to lookup additional data to complement the record.

How do I create a DSN-Less connection in my Action step in the datamapper?

As an example I have:
var connectionURL =“jdbc:odbc:Driver={Microsoft Access Driver(*.txt, *.csv)};DBQ=D:\DB\Manifest\IR\”;
var conn = db.connect(connectionURL,“”,“”);

But I get this error:
Action : Error running script (Wrapped java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (#8)) (DME000019)

Many thanks,
Steve

2

Connect is a 64-bit application that can therefore only use 64-bit drivers. At least one of the connection strings you gave as examples - Microsoft Access Driver (*.mdb) - is a 32-bit driver (the 64-bit version is named Microsoft Access Driver (*.mdb, *.accdb) ).

Those drivers happen to be installed on my system, so I was able to test this and it works just fine with the following code:

let cs = data.extract('./connections[1]/connectionString[1]');
// The field contains "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:/Tests/Animated.mdb;"

var conn = db.connect(cs,"","");
var rs = conn.createStatement().executeQuery('SELECT * FROM Characters where FullName="Mike Wazowski"');
rs.next();

logger.info(rs.getString("LastName"));
// output: Wazowski
3 Likes
3

It worked really well. The main issue was setting up the correct driver version and I guess this is where we were going wrong. Thank you once again for your valuable help.

1 Like