Hello,
is there a tutorial or howto for gettings some specific data out of a MSSQL db and populate that data (replacing some field values) of the datamapper metadata (origin of the metadata are extracted pdf fields).
Since datamapper cannot deal with multiple datasources I think it should be done in workflow. I already tried the database query but don’t have any clue how to achieve that. I’m used to TSQL but don’t know how to adress the fields in workflow.
thx in advance
Simple solution as long there are no null values in those database fields, dealing with null values I’ve had to recode in vbscript or use coalesce in the select statement. Here the jscript after datamapping:
var conn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
var src = "Provider=sqloledb; Data Source=mysqlserver; Initial Catalog=mydb; User ID=SQLUser; Password=SQLPassword;"
conn.Open(src);
var myMeta = new ActiveXObject("MetaDataLib.MetaFile");
myMeta.LoadFromFile(Watch.GetMetadataFileName());
Watch.Log(myMeta.job().group(0).count, 2); //Show document count of datamapper in Log
for(var i = 0; i < myMeta.job().group(0).count; i++) {
var thisDoc = myMeta.job().group(0).document(i);
var customerID = thisDoc.fieldByName("_vger_fld_CustID"); //get the key-value to search for
// standard query fails when there are null values in some fields (but works in vbscript)
// var query = "SELECT * FROM MYDB WHERE CustID = '" + customerID + "'";
//
// Alternatively use coalesce or isnull
var query = "SELECT coalesce ([field1],'') as field1 \
,coalesce([field2],'') as field2 \
FROM MYDB WHERE CustID = '" + customerID + "'";
rs.Open(query,conn,1,1);
if (!rs.eof) {
rs.MoveFirst();
thisDoc.fields.add2("_vger_fld_field1", rs.fields("field1") ,2); // etc.
}
else {
// empty lookup so let's do some special work
// like manipulation the metadata for splitting the print content
}
rs.Close();
}
myMeta.saveToFile(Watch.getMetadataFileName());