Passing File to SQL

asked on March 8, 2021 • Show version history

This seems like it should be simple, but I can't seem to get it to work. I'm trying to send Forms attachments to SQL. I've got [Retrieve Business Process Variables] passing the File Upload file collection to the [For Each File]. Inside the loop, I'm trying to pass %(ForEachFile_CurrentFile) to the varbinary(max) column in the SQL table. I've tried the [Insert Data] activity, which fails with the error "Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.", and the [Custom Query] activity, which fails with "Failed to convert parameter value from a String to a Byte[].".

What am I missing?

Edit after continuing to bang my head against this:

Based on this:

and the fact that CurrentFile does show up in the token dialog, it seems that I should only need to do this:

But that throws the aforementioned errors. Since I couldn't figure out what it's trying to convert from a string, I tried pushing the token into a VARCHAR column instead and got "43:\4b3b10b4-4dea-498c-b212-b81f7765777a\RetrieveBusinessProcessVariables\2039\120206.pdf", so something seems to be preventing the token from actually carrying the file data.

Well, if the %(ForEachFile_CurrentFile) token is broken, maybe I can work around it: %(ForEachFile_CurrentFile_Source ID) appears to be the attachment_id from cf_bp_attachment_data, so we should be able to get the file data directly from the table instead:

This throws a warning that "Column 'attachment' is returning a large amount of data...", which would seem to indicate that we're successfully getting the file data out of the Forms table. I'm still getting the same errors on attempting to insert the data, however.

0 0

replied on March 12, 2021

If the database you are trying to insert the value into is on the same server as the LFForms database, you should be able to just insert the record to your database directly from the LFForms database.

I tested this by creating a table on my database named test, and gave it these fields:

file_name - nvarchar(255)
attachment - varbinary(MAX)
length - nvarchar(20)

Then I determined on the LFForms database what attribute_id value showed on my file attachment field on the cf_bp_data table (I determined this by searching the cf_bp_data table for records whose submission_id value matched the submission ID of my form being submitted - it was the only field with the file name in the value field.

The cf_bp_data table joins to the cf_bp_data_attachment_mapping table, which in turn joins to the cf_bp_attachment_data table.

Then I ran this custom query from Workflow (note, this is assuming ODBC connection). The Submission ID is populated from LFForms. and I hard-coded the attribute_id from my search above.

When the Workflow ran, it found the attachment and loaded the data to my test table.

Here's the full script:

INSERT INTO [CUSTOM_DATABASE_NAME].[dbo].[test]
(
  [file_name],
  [attachment],
  [length]
)

SELECT
  [file_name],
  [attachment],
  [length]
FROM [LFForms].[dbo].[cf_bp_data] AS bpd
LEFT JOIN [LFForms].[dbo].[cf_bp_data_attachment_mapping] AS am ON am.[bp_data_id] = bpd.[bp_data_id]
LEFT JOIN [LFForms].[dbo].[cf_bp_attachment_data] AS ad ON ad.[attachment_id] = am.[attachment_id]
WHERE bpd.[submission_id] = ?
  AND bpd.[attribute_id] = ?

0 0

View 1 previous reply

replied on March 18, 2021 • Show version history

Thanks for the suggestion, but I'm trying to insert to a different SQL server. I guess I could try setting up a linked server to do it this way, but I'd rather do it through Workflow connection, which really looks like it should work.

0 0

replied on March 19, 2021

Unless I'm mistaken, the Workflow tokens have size/length limitations on them that are likely to cause the value to be truncated.

0 0

replied on March 19, 2021

No, Workflow has size limits on the tracked values of tokens. The runtime has the full value, we only truncate the values Track Tokens logs.

As for the original questions, Workflow will not convert file tokens into their actual contents in Insert Data.

0 0

replied on March 19, 2021

So if Workflow is not truncating the size of the token value, then you could probably pull it from the LFForms database and then insert it directly in to another database.

If you are not manipulating the token in any way, it should be possible, in theory.

Unlike my post before with the query shown, I have not tested this behavior.

0 0

Question

Question

Replies

Sign in to reply to this post.