× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. September 2024

Re: rpg embedded sql json and escaped output

here is the complete query formatted...

again, when values not passed in consents fields, they do not show as
expected (this is what I'm trying to do)...
But then the fields in metadata have single escape char's in them...
when values are passed in consents fields, they are built in the json as
expected (this is what I'm trying to do)...
But then the fields in metadata and consents have single/double/triple
escape chars.

exec sql

values

'{"query": '
concat

'"mutation UpdatePhoneNumberConsents($input: '
concat

'ConsumerCommunicationsUpdatePhoneNumberConsentsInput!) '
concat

'{systems '
concat

'{capabilities '
concat

'{consumerCommunications '
concat

'{updatePhoneNumberConsents(input: $input) '
concat

'{successOrFailure message}'
concat

'}'
concat

'}'
concat

'}'
concat

'}",'
concat

'"variables": '
concat

nullif(json_object(

'input'
//o1s

: nullif(json_object(

'phoneNumber'
//o2s

: trim(:p_phoneNumber)

,'phoneType'

: case when :p_phoneType <> ' '

then trim(:p_phoneType)

end format json

,'metadata'

: nullif(json_object(

'sourceChannel'
//o3s

: trim(:p_sourceChannel)

format json

,'customerName'

: case when :p_customerName <> ' '

then trim(:p_customerName)

end

,'roleType'

: case when :p_roleType <> ' '

then trim(:p_roleType)

end format json

,'uaiId'

: case when :p_uaiId <> ' '

then trim(:p_uaiId)

end format json

,'appId'

: case when :p_applicationId <> ' '

then trim(:p_applicationId)

end

,'teamMemberId'

: case when :p_teamMemberId <> ' '

then trim(:p_teamMemberId)

end format json

absent on null)
//o3e

,'{}')



,'consents'

: nullif(json_array(
//a1s

nullif(json_object(

'businessFunction'
//o4s

: case when :p_businessFunction1 <> ' '

then trim(:p_businessFunction1)

end format json

,'hasConsented'

: case when :p_businessFunction1 <> ' '

then

case when :p_hasConsented1 = 'Y'

then 'true'

else 'false'

end

end format json

,'channels'

: nullif(json_array(
//a2s

nullif(json_object(
//o5s

'communicationsChannel'

: case when :p_communicationChannel <> ' '

then trim(:p_communicationChannel)

end format json

,'hasConsented'

: case when :p_communicationChannel <> ' '

then

case when :p_smsPhoneConsent = 'Y'

then 'true'

else 'false'

end

end format json

absent on null)
//o5e

,'{}')

format json

absent on null)
//a2e

,'[]')

format json

absent on null)
//o4e

,'{}')

,nullif(json_object(
//o6s

'businessFunction'

: case when :p_businessFunction2 <> ' '

then trim(:p_businessFunction2)

end format json

,'hasConsented'

: case when :p_businessFunction2 <> ' '

then

case when :p_hasConsented2 = 'Y'

then 'true'

else 'false'

end

end format json

absent on null)
//o6e

,'{}')

format json

absent on null)
//a1e

,'[]')

,'updatedAt'

:trim(:p_rcdEffectiveTimestamp)

format json

absent on null)

,'{}')
//o2e

format json

absent on null)
//o1e

,'{}')

concat '}'

into :g_clob;

thanks

jay



On Fri, Sep 20, 2024 at 12:00 PM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

oh, you can ignore this at the end...

concat '}' // end query

There was some concatenated string at the beginning of this that defined a
graphQL mutation path.... but this json part is the main part...

also apologize about the formatting I should have applied fixed width to
it...

thanks

Jay


On Fri, Sep 20, 2024 at 11:30 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

ok everything in the consents object is either escaped, double/triple
escaped...

exec sql

values

nullif(

json_object(key 'input'
//o1s

value nullif(

json_object(
//o2s

key 'phoneNumber'

value trim(:p_phoneNumber)

,'phoneType'

: case when :p_phoneType <> '
'

then trim(:p_phoneType)

end

,key 'metadata'

value nullif(

json_object(
//o3s

'sourceChannel'

: trim(:p_sourceChannel)

,'customerName'

: case when :p_customerName
<> ' '

then
trim(:p_customerName)

end

,'roleType'

: case when :p_roleType <> ' '

then trim(:p_roleType)

end

,'uaiId'

: case when :p_uaiId <> ' '

then trim(:p_uaiId)

end

,'appId'

: case when :p_applicationId
<> ' '

then
trim(:p_applicationId)

end

,'teamMemberId'

: case when :p_teamMemberId
<> ' '

then
trim(:p_teamMemberId)

end

format json

absent on null)
//o3e

,'{}')



,key 'consents'

value nullif(

json_array(
//a1s

nullif(

json_object(
//o4s

'businessFunction'

: case when :p_businessFunction1
<> ' '

then
trim(:p_businessFunction1)

end format json

,'hasConsented'

: case when :p_businessFunction1
<> ' '

then

case when :p_hasConsented1
= 'Y'

then 'true'

else 'false'

end

end format json

,key 'channels'

value nullif(

json_array(
//a2s

nullif(

json_object(
//o5s

'communicationsChannel'

: case when :p_communicationChannel
<> ' '

then
trim(:p_communicationChannel)

end format json

,'hasConsented'

: case when :p_communicationChannel
<> ' '

then

case when :p_smsPhoneConsent
= 'Y'

then 'true'

else 'false'

end

end format json

absent on null)
//o5e

,'{}')

format json

)
//a2e

,'[]')

absent on null)
//o4e

,'{}')

,nullif(

json_object(
//o6s

'businessFunction'

: case when :p_businessFunction2
<> ' '

then
trim(:p_businessFunction2)

end format json

,'hasConsented'

: case when :p_businessFunction2
<> ' '

then

case when :p_hasConsented2
= 'Y'

then 'true'

else 'false'

end

end format json

absent on null)
//o6e

,'{}')

format json

)
//a1e

,'[]')

,'updatedAt'

:trim(:p_rcdEffectiveTimestamp)

format json

absent on null)
//o2e

,'{}')

format json

absent on null)
//o1e

,'{}')

concat '}' // end query

into :g_clob;


tia

Jay

On Fri, Sep 20, 2024 at 11:27 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

it is quite a beast - I'll try but there is no CTE here with this one

Jay

On Fri, Sep 20, 2024 at 11:14 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Could you show the SQL-Statement in embedded SQL?
Could it be you are using a Common Table Expression or nested
sub-selects,
for generating JSON information that is used in the final SELECT
statement?
If so you have to add FORMAT JSON after each generated JSON information
used
in another Common Table Expression or nested Sub-Select or final SELECT
statement.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so
they
don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay
Vaughn
Sent: Friday, 20 September 2024 16:20
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: rpg embedded sql json and escaped output

I have an rpg pgm doing some embedded sql and using json_object.

It consists only of about 5 fields all char fields...

In debug, when I view the output it is escaped.

When I code this same json_object in ACS run sql scripts (with hard
coded
values), there are no escape characters in the output.

What is causing the escape char in my output in by rpg pgm when
debugging?

Also, there is more json around this json_object, and this particular
json_object is nested... but still, none of the other structures should
impact this one in regards to the escaping, correct?

I have tried "format json" for each field as well with no luck - and I
do
not even had to specify "format json" in my ACS run sql.

any clarification would be appreciated.

thanks

Jay
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.