Question

How can I store the binary (hex) value from a SQL Query without Workflow conveting it to a series of decimal numbers?

Workflow Version 8 Integrations

Updated January 14, 2014

asked on January 10, 2014

I have a query in Laserifche Workflow that is returning a binary (hex) value from the database. When I try to present the value in an email, it becomes converted to a decimal number (or a series of decimal numbers).

How can I receive a binary value and present it as a string and be untouched within workflow?

Sample return value: 0x000000000001B029

I'm having a difficult time converting or casting the value properly within the SQL query.

0 0

Answer

SELECTED ANSWER

replied on January 14, 2014 • Show version history

Kenny,

I did a little research on casting a binary to a varchar in SQL 2005 and came across this. It works on my system (SQL 2008 R2) so I know the syntax is correct. Hopefully it will work on SQL 2005.

SELECT TOP 1 toc.tocid, toc.creator, '0x' + CAST('' as XML).value('xs:hexBinary(sql:column("toc.creator"))', 'varchar(MAX)') FROM toc;

The binary column in this query is "toc.creator". When I execute this query I get the varchar value '0106000000000009ADDCBA00936D0B6318B5CB48A3C2C30A188FE81602000000' returned so I pre-pended the '0x' prefix to that result..

Let me know if this helps...

1 0

Replies

replied on January 10, 2014 • Show version history

Kenny,

How about "CONVERT(varchar(MAX),mybinaryfield,1)" (where mybinaryfield is your fieldname) to get SQL to convert the varbinary field to a varchar that you can use as a string value in workflow?

0 0

replied on January 11, 2014 • Show version history

So a more complete example would be something like this (from the Laserfiche database);


SELECT TOP 1 toc.tocid, toc.creator, CONVERT(varchar(MAX), toc.creator, 1) AS HexString FROM toc

The value returned in the 'HexString' column from my system is 0x0106000000000009ADDCBA00936D0B6318B5CB48A3C2C30A188FE81602000000

Since the 'HexString' column is being returned as a varchar then I would assume that you could store that in a string token and workflow would leave that value intact to use as necessary.

0 0

replied on January 13, 2014

Unfortuantley, I tried that, but the returned value is blank for the convert column/value. I think the source database is SQL 2005.

Any other ideas?

0 0

replied on January 13, 2014

If you don't mind my asking, why would you want to email somebody a binary value?

0 0

View 2 previous replies

replied on January 13, 2014

It is actually used as a part of a URL string in our CRM system. I need to attach the binary value to the end of the URL to pull up the specific activity.

Is there a way to prevent Laserfiche from converting the binary into a series of decimal numbers? The value I get back in the track tokens is something like (not actual):

175

When I try to use the token value for that column returned in the SQL query, the result is always "0".

When I do compare operations (routing decision, etc), I have to calculate the decimal value and use that instead of the binary equivalent.

I understand that our CRM vendor was not smart in using binary all over the system tables, but that is what we are stuck with. Please advise.

0 0

replied on January 14, 2014

There is much support now for dealing with binary array values in a token... largely because there are so many different formats that it can be encoded as.

What is happening now is the data activity stores your value as a byte[] which workflow treats as a multivalued token of bytes. By default, when resolving such a token to text, it grabs the first value of the MV token (the 0).

Your best bet is going to be to transforming it in the sql query like others have mentioned. Alternatively use a script and process the byte array for the correct format for your CRM vendor. For instance, if the format was Base64:

byte[] test = (byte[])this.GetTokenValue("DataActivity_ColumnName");     string base64 = Convert.ToBase64String(test);            
this.SetTokenValue("NewToken", base64);

0 0

SELECTED ANSWER

replied on January 14, 2014 • Show version history

Kenny,

I did a little research on casting a binary to a varchar in SQL 2005 and came across this. It works on my system (SQL 2008 R2) so I know the syntax is correct. Hopefully it will work on SQL 2005.

SELECT TOP 1 toc.tocid, toc.creator, '0x' + CAST('' as XML).value('xs:hexBinary(sql:column("toc.creator"))', 'varchar(MAX)') FROM toc;

Let me know if this helps...

1 0

replied on January 14, 2014

That's what I foudn too! The only thing I needed is the "sql:column(toc.creator)". The sample I had found was using sql:variable() instead of a column.

Do you know where I can find the reference guide for the "sql:xxx" statements in the future?

Sorry, I should have posted what I found that worked sooner. I found it after I started this post. I was really hoping for a good way to handle binary values in Workflow.

thanks, all!

0 0

replied on January 14, 2014

Glad you found a solution! As far as a good SQL reference is concerned my 'goto' is Google!

1 0

You are not allowed to follow up in this post.

Question

Question

How can I store the binary (hex) value from a SQL Query without Workflow conveting it to a series of decimal numbers?

Answer

Replies

Sign in to reply to this post.