This can be done with a combination of AppScript and DOS command line commands. It's a significant bit of work. I can't share the actual code but here's a description of the solution:
Client's SBM was configured to store file attachments in the SBM AE Database, not on the SBM Server. This would seem to rule out using AppScript to update the TS_ATTACHMENTS table. A web service or Orchestration call would allow creating file attachments independent of the system configuration, however there is this dire warning in the SBM Orchestrations Guide:
Orchestrations are not intended to be used to process very large blocks of data or file attachments. For example, the sbmappservices72 GetFileAttachment call enables you to retrieve file attachments for a given item; however, you should avoid using this call in an orchestration because orchestrations are not designed to handle this kind of data efficiently.
In addition, the content of the files would have to be base64 encoded for the OE. This can be accomplished with AppScript, but it is not easy.
Depending on the version of SBM, either the SBM System Administrator tool (ttadmin) or SBM Configurator is used to configure how the system handles attachments. Additional settings specify the directory path for file system based attachments and sub-directory naming conventions that allow storing attachments by year or year/month. Changing the setting from “Store attachments on the file system” to “Store attachments in the database” will cause the system to copy attachments from the attachments directory into the AE database. Changing the setting from “Store attachments in the database” to “Store attachments on the file system” to will cause the system to move the files from the database to the attachments directory.
What isn’t documented is that apparently
SBM is always looking at the “file system” vs. “database” setting and “doing the right thing” in the background. For example, if SBM is configured to “Store attachments in the database” and it detects an entry in TS_ATTACHMENTS that indicates that a file is in the attachments directory, then SBM will copy the attachment to the database. We take advantage of this by simply creating the attachment on the file system using AppScript and letting SBM do it’s job and move it to the database. This undocumented behavior existed on 10.1.4.1 and several versions prior to that. No guarantees about anything else. If Serena reads this, they'll probably say "that's a bug that needs to be fixed..."
Creating the attachment involves copying the "template" file from a static directory on the server to the SBM attachments directory, then creating an entry in the TS_ATTACHMENTS table.
Several factors complicate the process:
- A file can’t be copied using a built-in AppScript function. It must be copied using a DOS command, in this case I used “XCOPY”
- The destination directory (SBM Attachments directory) is stored in the Windows Registry. There isn’t an AppScript function that will read the Registry. Use AppScript to execute the DOS “REG” command. Alternatively, hard-code the Attachments Dir in the AppScript.
- The temporary attachment file must have a unique name in the attachments directory.
- The DOS command-line commands redirect output to a temp file which is then parsed by the AppScript, then deleted. The SBM AppScript “Ext.CmdLineWait” command is run with privileges from the Windows IIS AppPool Process. That process has read/write privileges to “C:\Windows\Temp” so that is where the output is directed to. The AppScript functions that deal with files can’t expand DOS environment variables (like “%TEMP%”) so the path to the temp directory must be hardcoded. This would present challenges if the system has configured a different directory for “TEMP”. Assume that is not the case and pray your server admins don't change it.
- When redirecting output from DOS command-line command to a temp file, the name of that file must be unique to prevent contention. This would happen if multiple users performed the same action at the same time that initiated the AppScript. The system needs to ensure that each user’s redirected output is not over-written by another user. I.e. the script needs to generate unique filenames.