Database Query - Truncating Data

OL Connect Professional Workflow
1

I am using the Database Query tool in Workflow and it is truncating the data, I have 2 fields that are larger than 255 characters, is there a limit in the database query tool?

2

Hi iBaldie,

This generally shouldn’t happen, but I’ll need a little more details. Notably, how are you connecting to the database (ODBC, DSN, direct connection?), what database it is, which field types are being truncated and finally, which Output File Emulation you chose.

This last one might be a hint for a quick solution : if you select CSV emulation, I’ve seen instances where CSV fields are cut off to 255 characters (this might be a limitation of a driver or library that’s used in Workflow). Try using XML instead, and see if that changes anything.

Hope this helps!
~Evie

3

Hi Evie

I am connecting via DSN, it is an MSSQL Database, and the fields are ntext fields.

The Output emulation is XML however I have tried csv and this also has the same results as you mentioned.

Thanks

Liam

4

It’s possible that ntext fields are not supported, unfortunately. There’s no mention of this specific data type in the documentation, but there is this technical warning:

Database emulation supports SQL ANSI 92 or higher, and supports the following data types: string, integer, floating point, all date formats, and text-only MEMO. It does not support any binary data types such as Binary Large Object (BLOB), images, sound files, and MEMO data that includes binary data.

(source: http://help.objectiflune.com/en/planetpress-workflow-user-guide/8.3/Default_CSH.html#/shared/Emulation-Database.html )

As a side-note, Microsoft itself is deprecating ntext in later versions of SQL Server (no mention of a specific version) so perhaps switching to a different field type would be appropriate.

However, a workaround for this would be to use a Run Script action in either VBScript or JScript. With the ADODB ActiveX object, you should be able to query the database and retrieve any of these data types. Here’s an example you can use, on pastebin.

~Evie

5

Hi Evie

I have changed our database to be a NVarChar(MAX) field, still getting the same issue…

I have also tried your pastebin however this method is really slow!

Thanks

Liam