as already done in workflow (works really good), see here , I want to lookup some SQL data in datamapper (it’s easier there since at design time all values are present). I’m stuck at:
I’ve an action step getting some fields out of a PDF, then the extraction.
In the first Action step I get a variable (customernr) and some other data
I want to lookup the database record for that variable in the external db.
So where to put the code for the lookup (in that first Action step)?
And how to assign the dbfield data to datamapper fields (and where)?
Perform your lookup in the first Action Step and store the results in a property whose Scope setting is set to Record. For instance, let’s say you named your property “myLookup”.Then, in any extraction step, add a field, set its Mode to JavaScript and set the JS Expression to sourceRecord.properties.myLookup;
Hi Phil, o.k. but I can’t find any helping ressource for that. The documentation of datamapper lacks some examples, e.g. db.open.
So how to connect to a db? the ActiveXObject(“ADODB.Connection”) doesn’t work in datamapper. I can’t find any documentation or example on how to open or retrieve data!
var src = ‘jdbc:sqlserver://db-server;databaseName=db-name;’;
var conn = new db.connect(src, “myuser”, “mypwd”);
var mycustomerID = sourceRecord.properties.customerID;
var stmt = conn.createStatement();
var query = “SELECT * FROM mydb WHERE customerID = '” + customerID + “'”;
rs = stmt.executequery(query);
if (!rs.eof) {
rs.MoveFirst();
sourceRecord.properties.MyLookup = rs; //want to assign the whole recordset to that Array
}
else {
// nothing found in database
}
rs.Close();
Here is an example, connecting to SQLServer Express Edition and retrieving the first name and last name of a user whose ID is 12:
var connectionString = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;integratedSecurity=true;databaseName=MyDB";
var conn = db.connect(connectionString, "", "");
res = conn.createStatement().executeQuery("SELECT * FROM dbo.Users where ID=12");
res.next();
var result = res.getString("FirstName")+" "+res.getString("LastName");
res.close();
conn.close();
result;
Obviously, the SQL Server must be properly configured to allow TCP/IP connections on port 1433, the firewall must also allow connections through that port, and your credentials must be valid.
thx! works!