I want to get tracking data into SQL from the data I am sorting - a mix of pdf and csv. The two options seem to be :-
1 - as Workflow - Import XML Data into MS SQL Database - posted on 16th February - i.e. to ue the database connector. I have set this up by extracting the meta data and using SQL to insert into a database. However unless I am missing something this need to be done line by line looping through he meta data. This took about 40 seconds for 100 records which is too slow. Is there a better way?
OR
2 - extract the xml using extract data and then saving this xml into a folder which can then be imported using SQLCMD later (or as a command line operation). However as I am using the meta date for in the workflow I cannot just extract XML but end up doing it twice - once as metadata and once for xml. I cannot see how the change emulation works. Any help?
Returning data into an SQL server is not something that the Workflow plugins are really effective at, to be honest. At least certainly not the Database plugin, since it would need to run for every individual record separately (aka establish the connection, make the request, close the connection, which is really ineffective).
I’d say, honestly, your best bet would be to use a script. Once all your metadata filtering and modification is done, you could have access only to the metadata documents that you actually want to insert into the database, and insert them with an SQL statement (with the database connection being opened and closed only once).
Here’s an example of how this could be done (haven’t tested this code since I don’t really have access to your data!). Note that it’s in jScript:
var src = "Driver={MySQL ODBC 3.51 driver};uid=root;pwd=;option=131242;database=aaa";
var conn = new ActiveXObject("ADODB.Connection");
conn.Open(src);
var MyMeta = new ActiveXObject("MetaDataLib.MetaFile");
MyMeta.LoadFromFile(Watch.GetMetadataFileName);
for(i=0; i < MyMeta.Job.Group(0).Count; i++) {
var recordID = MyMeta.Job.Group(0).Document(x-1).FieldByName("_vger_record_id");
var invoiceNumber = MyMeta.Job.Group(0).Document(x-1).FieldByName("_vger_fld_invoiceNumber");
var clientID = MyMeta.Job.Group(0).Document(x-1).FieldByName("_vger_fld_clientID");
conn.Execute("INSERT INTO tracking (`recordID`, `invoiceNumber`, `clientID`) VALUES ('"+recordID+"', '"+invoiceNumber+"', '"+clientID+"')");
}
conn.Close();
Of course, the field names and the connection string on the first line will need some adjustment, but this is more definitely the most effective method to do it.
Thanks make a lot of sense. I am getting a connection OK and writing to the SQL but can’t access the Metadata -
MyMeta.LoadFromFile(Watch.GetMetadataFileName);
It reports that Microsoft Jscipt runtime error: Object doesn't support this property or method. Do I need to point it to a specific file location or is that assumed?