Home > Forum > Rules, JS, SQL > Changing the comment format in Webcon 2022 (dbo.f_clear_description error)

Changing the comment format in Webcon 2022 (dbo.f_clear_description error)
0

Hi
We recently changed the version of Webcon from 2020 to 2022 and I came across this problem.
In the report we used a calculated column to show comments:
(SELECT ISNULL (dbo.f_clear_description(wfelems.WFD_SIGNATURE), 'None'))

However, from what I can see in database, the comment format has changed and now when using this function I get an error:
"Invalid length parameter passed to the LEFT or SUBSTRING function."
In the attachment, I marked in green old comments, and those starting with [{ are new.
Old comments show up in the report and when I go to newer issues I get an error in the report.

Does anyone have a solution to make comments in the old and new format work?

Regards
Michael

MVP

Hi Michał,

is the f_clear_desription a custom function? I can't find it in my databases.

Have you checked whether the value is converted, when an instance is saved? I don't have a similar environment, so I can't verify it.
If this the case you could use a date or alternatively you could test whether the value starts with the old or new format.

If you need to process the new comment format you could use either one as a starting point. Both approaches return each comment in an own record, so it won't match your case.
This one requires a database compatibility level for sql server 2016 (130).
https://daniels-notes.de/posts/2023/comments-to-data-table-and-pdf#sql-server-2016

For lower version you could use:
https://daniels-notes.de/posts/2023/comments-to-data-table-and-pdf#sql-server-2014-and-less

Best regards,
Daniel

MVP
In reply to: Michał

Hello Daniel
I'm not sure if this is a standard feature or not. But now that it compares to other functions, after you wrote that you don't have it in your environment,
there's a chance that maybe my predecessor created it. Function use a cursor that concatenates comments into one string.
I will have to improve this function so that it extracts comments depending on the pattern.

What a coincidence, I got a request this afternoon to modify a data table which displays information about subworkflows.
The comments from the subworkflows should be displayed.

Here's the part regarding extracting the comments and concatenating them back

Best regards,
Daniel

,(select STUFF(
(

select left(Comments.Date,16)+' '+ Displayname +':' +Comment+Char(10)
from OPENJSON(WFD_Description)
with (
Date DATETIME2 '$.d',
Account varchar(200) '$.l',
Displayname varchar(200) '$.a',
Comment nvarchar(max) '$.c'
) as Comments
for xml path ('')
),1,0,'') as dummy
) as Comments
from V_WFElements

In reply to: Daniel Krüger (Cosmo Consult)

What a coincidence, I got a request this afternoon to modify a data table which displays information about subworkflows.
The comments from the subworkflows should be displayed.

Here's the part regarding extracting the comments and concatenating them back

Best regards,
Daniel

,(select STUFF(
(

select left(Comments.Date,16)+' '+ Displayname +':' +Comment+Char(10)
from OPENJSON(WFD_Description)
with (
Date DATETIME2 '$.d',
Account varchar(200) '$.l',
Displayname varchar(200) '$.a',
Comment nvarchar(max) '$.c'
) as Comments
for xml path ('')
),1,0,'') as dummy
) as Comments
from V_WFElements

Hello Daniel
Thank you very much for your answer.
Unfortunately, I couldn't use OPEN JSON because my SQL Compatibility Level is too low.
So all newer solutions are out of my reach :/
INFO: https://kb.webcon.pl/tryb-zgodnosci-baz-danych-webcon-bps-compatibility-level-dobre-praktyki/ .

I did a quick solution the old way, for comments I use a function that checks whether it is an old or new comment and
uses the appropriate function to read it. I use it in one report so I can afford such a move.

Maybe it will be useful to someone I couldn't add the code so I added screenshot with code.