Hi Robert
I appreciate your response. The QuoteValue is set to true - it is what my code
always used and relied on, but it breaks down in TS7.
Here is an extract from a TAF file from an Insert Action focussing on 2 value
items being inserted, con_name and con_surname:
<ValueItem>
<Name>con_name</Name>
<Value><![CDATA[<@SQ><@ARG con_name><@SQ>]]></Value>
<QuoteValue>false</QuoteValue>
<IncludeIfEmpty>true</IncludeIfEmpty>
<NullValue>false</NullValue>
</ValueItem>
<ValueItem>
<Name>con_surname</Name>
<Value><![CDATA[<@ARG con_surname>]]></Value>
<QuoteValue>true</QuoteValue>
<IncludeIfEmpty>true</IncludeIfEmpty>
<NullValue>false</NullValue>
</ValueItem>
When con_surname is empty, the INSERT statement emitted by the Insert Action
contains NULL for its value (pre TS7 it would be '').
As a workaround, I changed the processing of con_name so that QuoteValue is
false and I added <@SQ> metatags. That works and will yield '' when con_name is
empty.
Here is the relevant bit of debug trace:
[Query] [11] INSERT INTO [dbo].[contact] ([con_title], [con_name],
[con_surname], [con_position], [con_cust_id], [con_default], [con_sl_default],
[con_disabled], [con_email], [con_telephone], [con_mobile], [con_notes]) VALUES
('MR', '', NULL, NULL, 1944, 0, 0, 0, NULL, NULL, NULL, NULL)
Is there any way that the pre-TS7 handling of empty strings can be restored?
Kind regards
Andrej
From: Robert Shubert <rshub...@tronics.com>
Sent: Sunday, October 22, 2023 5:07 PM
To: TeraScript-Talk@terascript.com
Subject: RE: TeraScript-Talk: The effect of ANSI99SQL=true
Check the QuoteValue setting in the Insert/Update actions. If that is true an
empty string should insert/update as '' not NULL.
From: Andrej Popovic [mailto:and...@workflowengine.co.uk]
Sent: Tuesday, October 03, 2023 6:25 AM
To: terascript-talk@terascript.com<mailto:terascript-talk@terascript.com>
Subject: TeraScript-Talk: The effect of ANSI99SQL=true
Hi everyone
A customer had recently upgraded to TeraScript 7 and had to set the server
configuration variable ANSI99SQL to TRUE so that TeraScribe actions generate
the correct SQL ANSII joins.
However, either the TS7 itself or the ANSI99SQL setting caused a problem with
handling empty strings in the INSERT and UPDATE actions. With previous versions
of TS an empty string would generate an empty string SQL value assignement, now
it is translated into NULL. Consequently we have a database with many NULL
values where we normally expect blank strings. This is causing problems with
legacy code.
Is there any way to force TS7 globally to stop converting empty strings into
NULLs in actions with ANSI99SQL=true?
Please note that custom SQL does not have this problem. We are looking for a
solution that would avoid having to hand-revise dozens of legacy TAF files.
Many thanks for any responses in advance!
Andrej Popovic
https://workflowengine.co.uk
________________________________
To unsubscribe from this list, please send an email to
lists...@terascript.com<mailto:lists...@terascript.com> with "unsubscribe
terascript-talk" in the body.
________________________________
To unsubscribe from this list, please send an email to
lists...@terascript.com<mailto:lists...@terascript.com> with "unsubscribe
terascript-talk" in the body.
----------------------------------------
To unsubscribe from this list, please send an email to lists...@terascript.com
with "unsubscribe terascript-talk" in the body.
