I would like to get hold of the uploaded file name and full path in the event script "BefExportToDat", as I need to extract values from particular fields. However I have not yet found a way to do this.
- The input variable "strFile" returns the .Dat file path in the Outbox, to which it is about to export the data. This is no use to me.
- The API variable RES.PstrFilename is returning nothing
I am using RES.PstrFilename in the "BefFileImport" event script in a different FDM application and it works fine, however I need to find a way to get this to work in the "BefExportToDat" event script.
If you are processing files through the the on-line workflow, the last input file details are held on the tPovPartition table, so you could do a read on the table for the selected locaction. If you are processing via batch, i think the file information is held on one of the batch tables, tBatchContents?) but you would need to review the contents to determine the best method to identify the correct file.
Many thanks for your reply. Can you please guide me as to how I can query the table results in the event script? Are there special API calls to do this? How can one step through and view the data? I am not sure at present whether or not we will be granted access to run SQL queries direct on the FDM application database, so it would be great to be able to fault-find and work through this in the event script if possible. Please let me know.
I am looking through the API calls in FDM Workbench, but cannot see the table (tPOVPartitions) you mentioned listed. Is this the correct name? And do I just use the function listed in the object browser to run the query?
Furthermore (going back to my initial thoughts of using strFile), it appears that although the variable contains the .Dat filename and path, the actual file is non-existent when "BefExportToDat" is executed:
Error: Export failed.
Detail: File not found.
This would make sense, but it does make the variable "strFile" a little pointless since one cannot make use of the file in this particular event script. Do you please have any thoughts on this?
Set rs = DW.DataAccess.farsFireHose("SELECT PartLastImpFile FROM tPOVPartition WHERE PartName=" & strLoc, False)
strFileName = rs.Fields("PartLastImpFile")
Set rs = Nothing
This should get what you need, but an explanation would be lengthy. The DW objects provide native database operations, and ADODB provides functionality for native and external databases. There is info in the Object guide on using DW, and there is plenty of info online about using ADODB and recordsets.
I decided to move the whole script to the "BefLoad" event script in the end, since this script can interrogate the .Dat file since it has actually been generated by this stage. I had wanted to keep it in the "BefExportToDat" though, as this then gives the user the option not to export to Essbase should the custom-built Essbase data-clear fail.
So your solution above may help me to achieve this - will post again here if I get this working but will close this post for now. Thanks!