I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------

#SQL

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

Thanks for your help.  I'll try that.

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

I changed the second parameter to character.  I changed the 3rd parameter to output.  The stored procedure keeps getting a null error on the insert from the JSON_TABLE values, even though I'm passing in data.  I'm not sure if it is how I'm passing it in, or if it is my JSON_TABLE code.  I've made a lot of changes trying different things and rearranging the order of the fields on the insert, and I keep getting that same error on the insert, where the first field listed gets a null error.  It has 3 parameters:
        IN INTRANS CHAR (01),
        IN INVDTA CHAR (5000),
        OUT OUTID INTEGER 

When I call it in Run SQL Scripts, I pass this:

        call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code for the stored procedure.  When I call it as it is coded right now from Run SQL Scripts as above, it returns 
SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

Do you see anything obvious that I'm doing wrong here?

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Sat August 20, 2022 09:52 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

Thanks for your help.  I'll try that.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

Dear Amy

>>>>   I changed the second parameter to character <<<<

It appears you misunderstood my response to you.  You should leave the 2nd parameter data type unchanged as CLOB.  What I meant was that when you call your procedure, you  provide a character string for the 2nd parameter (which is what you did) and DB2 should automatically promote the character string to CLOB for you.

I also encourage you to try debugging your procedure using the debugger from Run SQL Script tool as described here :  https://www.itjungle.com/2022/04/11/guru-debugging-sql-stored-procedures-with-acs/


------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Original Message:
Sent: Tue August 23, 2022 08:59 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

OK.  Thanks.  I have tried it defining it as CLOB and passing in character, and I've tried explicitly casting it as CLOB in the call, and it still doesn't update the table.  I'm assuming it is something in my stored procedure.

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Wed August 24, 2022 09:35 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

>>>>   I changed the second parameter to character <<<<

It appears you misunderstood my response to you.  You should leave the 2nd parameter data type unchanged as CLOB.  What I meant was that when you call your procedure, you  provide a character string for the 2nd parameter (which is what you did) and DB2 should automatically promote the character string to CLOB for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Tue August 23, 2022 08:59 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

I have it defined as CLOB and pass in the data as character.  In debug I can see the data passed in and it looks correct.  However, when I use JSON_TABLE to split the JSON data, I always get NULL results.  I've rewritten the earlier code to use a cursor and I still get the same result - NULLS.  I have no idea what I'm doing wrong.  Do you have any suggestions on what I may be doing wrong?

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Thu August 25, 2022 05:49 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

OK.  Thanks.  I have tried it defining it as CLOB and passing in character, and I've tried explicitly casting it as CLOB in the call, and it still doesn't update the table.  I'm assuming it is something in my stored procedure.

------------------------------
Amy Vozza

Original Message:
Sent: Wed August 24, 2022 09:35 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

>>>>   I changed the second parameter to character <<<<

It appears you misunderstood my response to you.  You should leave the 2nd parameter data type unchanged as CLOB.  What I meant was that when you call your procedure, you  provide a character string for the 2nd parameter (which is what you did) and DB2 should automatically promote the character string to CLOB for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Tue August 23, 2022 08:59 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

Can someone point me to an example of an SQL stored procedure that accepts a CLOB input parameter for some JSON data and then uses JSON_TABLE to parse it and update a Db2 table?

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Wed August 31, 2022 03:39 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I have it defined as CLOB and pass in the data as character.  In debug I can see the data passed in and it looks correct.  However, when I use JSON_TABLE to split the JSON data, I always get NULL results.  I've rewritten the earlier code to use a cursor and I still get the same result - NULLS.  I have no idea what I'm doing wrong.  Do you have any suggestions on what I may be doing wrong?

------------------------------
Amy Vozza

Original Message:
Sent: Thu August 25, 2022 05:49 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

OK.  Thanks.  I have tried it defining it as CLOB and passing in character, and I've tried explicitly casting it as CLOB in the call, and it still doesn't update the table.  I'm assuming it is something in my stored procedure.

------------------------------
Amy Vozza

Original Message:
Sent: Wed August 24, 2022 09:35 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

>>>>   I changed the second parameter to character <<<<

It appears you misunderstood my response to you.  You should leave the 2nd parameter data type unchanged as CLOB.  What I meant was that when you call your procedure, you  provide a character string for the 2nd parameter (which is what you did) and DB2 should automatically promote the character string to CLOB for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Tue August 23, 2022 08:59 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

Dear Amy

If you do not receive any response to your latest question in a few days, I suggest you ask your question again in IBM i group instead as there are many more members in that group so you have more chance of getting an answer. 

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Original Message:
Sent: Wed August 31, 2022 06:17 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

Can someone point me to an example of an SQL stored procedure that accepts a CLOB input parameter for some JSON data and then uses JSON_TABLE to parse it and update a Db2 table?

------------------------------
Amy Vozza

Original Message:
Sent: Wed August 31, 2022 03:39 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I have it defined as CLOB and pass in the data as character.  In debug I can see the data passed in and it looks correct.  However, when I use JSON_TABLE to split the JSON data, I always get NULL results.  I've rewritten the earlier code to use a cursor and I still get the same result - NULLS.  I have no idea what I'm doing wrong.  Do you have any suggestions on what I may be doing wrong?

------------------------------
Amy Vozza

Original Message:
Sent: Thu August 25, 2022 05:49 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

OK.  Thanks.  I have tried it defining it as CLOB and passing in character, and I've tried explicitly casting it as CLOB in the call, and it still doesn't update the table.  I'm assuming it is something in my stored procedure.

------------------------------
Amy Vozza

Original Message:
Sent: Wed August 24, 2022 09:35 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

>>>>   I changed the second parameter to character <<<<

It appears you misunderstood my response to you.  You should leave the 2nd parameter data type unchanged as CLOB.  What I meant was that when you call your procedure, you  provide a character string for the 2nd parameter (which is what you did) and DB2 should automatically promote the character string to CLOB for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Tue August 23, 2022 08:59 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL

I got help outside this group and finally figured out my main issue, which was that "strict" or "lax" must be lowercase.  That one piece of information made a huge difference, so now I'm on my way to getting the rest of the code correct.

------------------------------
Amy Vozza
------------------------------

Original Message:
Sent: Wed August 31, 2022 08:06 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

If you do not receive any response to your latest question in a few days, I suggest you ask your question again in IBM i group instead as there are many more members in that group so you have more chance of getting an answer. 

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Wed August 31, 2022 06:17 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

Can someone point me to an example of an SQL stored procedure that accepts a CLOB input parameter for some JSON data and then uses JSON_TABLE to parse it and update a Db2 table?

------------------------------
Amy Vozza

Original Message:
Sent: Wed August 31, 2022 03:39 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I have it defined as CLOB and pass in the data as character.  In debug I can see the data passed in and it looks correct.  However, when I use JSON_TABLE to split the JSON data, I always get NULL results.  I've rewritten the earlier code to use a cursor and I still get the same result - NULLS.  I have no idea what I'm doing wrong.  Do you have any suggestions on what I may be doing wrong?

------------------------------
Amy Vozza

Original Message:
Sent: Thu August 25, 2022 05:49 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

OK.  Thanks.  I have tried it defining it as CLOB and passing in character, and I've tried explicitly casting it as CLOB in the call, and it still doesn't update the table.  I'm assuming it is something in my stored procedure.

------------------------------
Amy Vozza

Original Message:
Sent: Wed August 24, 2022 09:35 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

>>>>   I changed the second parameter to character <<<<

It appears you misunderstood my response to you.  You should leave the 2nd parameter data type unchanged as CLOB.  What I meant was that when you call your procedure, you  provide a character string for the 2nd parameter (which is what you did) and DB2 should automatically promote the character string to CLOB for you.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Tue August 23, 2022 08:59 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I changed the second parameter to character, and the third parameter to output.  It gets an error on the insert with a null error.  I've tried various changes and still get the null error on the first field from the JSON_TABLE that is to be inserted into a table.  I'm not sure if the issue is the parameters on the call or the code in the procedure.  Below are the parameters being passed on the call:

call xptest/AV620SP
('A','
{"CountQty": 11.0,
"CountReason": "BD",
"Discrepancy": 4.0,
"Location": "DF028-044-02-0",
"SKU": 623826.0,
"OrderNumber": 123456789.0,
"DateCountCreated": "2022-08-01",
"TimeCountCreated": "08:15:22",
"CountEmployee": "34523",
"ErrorDescription": "Duplicate",
"ErrorDate": 20220801,
"ErrorTime": 81522,
"Notes": "This is a test"}
',?)

Attached is the pertinent code from the SQL procedure.  Below is the error when it is called as above.

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable INSLTLOC.

------------------------------
Amy Vozza

Original Message:
Sent: Sat August 20, 2022 08:30 PM
From: Satid Singkorapoom
Subject: SQL Stored Procedure Accepting JSON Data

Dear Amy

You should try providing a character string value for the 2nd parameter to see if it works or not.  If so, this is what is called implicit casting (automatic data type conversion) for SQL data types. I found that DB2 can "promote" CHAR to CLOB data type : https://www.ibm.com/docs/en/i/7.2?topic=elements-promotion-data-types.  But if the call fails, then please try explicit casting by using  CLOB( <character string> ) function with the 2nd parameter to see if this works or not.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom

Original Message:
Sent: Fri August 19, 2022 04:47 PM
From: Amy Vozza
Subject: SQL Stored Procedure Accepting JSON Data

I'm creating an SQL stored procedure that accepts 3 parameters: input character, input CLOB for JSON record, and input/output integer.  What is the correct way to call that in Run SQL Scripts?

------------------------------
Amy Vozza
------------------------------
#SQL