Home > Forum > Rules, JS, SQL > [SOLVED] Retrieve "Content as Base64" for ATT_ID (via SQL) with Attachment stored in Attachment DB

[SOLVED] Retrieve "Content as Base64" for ATT_ID (via SQL) with Attachment stored in Attachment DB
0

MVP

ORIGINAL SUBJECT WAS: Assistance Needed – Retrieve "Content as Base64" for All PDF-Attachments of an Instance


Dear All,

I am currently looking for a way to extract the "Content as Base64" for all attachments associated with a specific instance. My goal is to obtain a single text line in the following format:
'Base64 PDF 1', 'Base64 PDF 2', 'Base64 PDF 3'. -> The easiest way would be to use built-in tools, but unfortunately, that doesn’t work (CONTENT AS BASE64[GET ATTACHMENTS])

After attempting this myself without success, I came across an answer provided by Karol in 2023: Karol Częczek https://community.webcon.com/forum/thread/2906?messageid=2906

However, I’ve encountered an issue: my attachments are not stored in the WFAttachmentFiles table (there are ONLY 20 files in that table). Instead, they seem to only have metadata available in the WFDataAttachments table.

Does anyone have any insight into this, or perhaps an alternative solution to generate a single text line containing all Base64-encoded PDF attachments, separated by single quotation marks and commas?


Thank you in advance for your help and suggestions.

Best regards,
Bjoern

MVP
In reply to: Markus Jenni

Hi Björn

Could it be, that your files are in an attachment database? This would explain, why you only have metadata but almost no files. These 20 files are likely from a process where you did not change the attachment database.

Btw, by the look of the database name, Karol is retrieving the files from an attachment database.

Good morning Markus,

PERFECT – you’re absolutely right! Unfortunately, I hadn’t fully understood the FROM part. I didn’t realize that the Attachment Database had to be specified beforehand. Now it works perfectly!

I’ve put everything together in an image for you all in the attachment. Below, you’ll also find the SQL statement ready to copy (you’ll just need to adjust the constants and business rules parameters).

A huge THANK YOU again to you, Markus, for your quick response, and to Karol for the original tip [Karol Częczek https://community.webcon.com/forum/thread/2906?messageid=2906].

Wishing you a great Sunday,
Bjoern

---
Here the SQL statement:

SELECT
TOP (1) CAST (
(SELECT cast([ATF_Value] as VARBINARY(MAX)) FOR XML PATH(''))
AS VARCHAR(MAX)) as [Base64-of-ATF_Value]
FROM [{EGV:58}].[dbo].[WFAttachmentFiles]
WHERE
ATF_WFDID = {BRP:540}
and
ATF_ATTID = {BRP:-2}
and
ATF_IsDeleted = 0
ORDER BY ATF_Version DESC

Did you know that with WEBCON you can automate virtually any process? Even baking cookies 🍪
 
Speaking of cookies: we use the ones that are essential for our website to function properly, as well as additional ones that help us customize our content to your preferences. If you don’t mind cookies, click Accept. If you want to learn more, explore settings.
Settings