Unable to call stored procedure with array json parameter from C#

Help JSON
1

how do we can call stored procedure with json array as input to store procedure from .Net ? I tried below code but it doesnt work.

sql:
CREATE OR REPLACE PROCEDURE ReportData(jsonArray ARRAY(json)) AS
DECLARE

C# code

var connMySQL = new MySql.Data.MySqlClient.MySqlConnection();
int Timeout = 1900;
System.Data.IDbCommand cmd;
string sSql;
object rowCnt;
string stored_procedure = “ReportData”;

        string myConnectionString = "Server=s;Port=3306;Uid=;Pwd=;Database=;checkparameters=false";

        var jsonRequest = new { items = new[] {
             new
             {
                case_name = "tes", view = "gg", acct_account_code = "888-0000", busn_business_code = "888", func_function_code = "000" , enty_entity_code = "000", proj_project_code = "000" , time_month = "JAN",
                time_year = "2021" , layr_layer_code = "kk" ,  dimension1_code = "77" , cellid ="9"
             }
            }
        };

        try
        {
             var json1 = System.Text.Json.JsonSerializer.Serialize(jsonRequest);
            connMySQL.ConnectionString = myConnectionString;
            connMySQL.Open();
            if (connMySQL.State == System.Data.ConnectionState.Open)
            {
                connMySQL.Open();
                MySqlCommand cmd1 = connMySQL.CreateCommand(); 
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.CommandText = stored_procedure;
                cmd1.Parameters.Add(new MySqlParameter("@jsonArray", MySqlDbType.JSON) { Value = json1 });
                MySqlDataReader rdr = cmd1.ExecuteReader();
                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " --- " + rdr[1]);
                }
                rdr.Close();
                connMySQL.Close();

            }
        }
        catch(Exception ex)
        {
            string ex1 = ex.ToString();
        }
2

Hi @sgorantla!

Just in case – you have defined your function (ReportData) to accept an array of JSON objects, not a single JSON array.

Can you try this:

USE testdb;

DELIMITER //
CREATE OR REPLACE PROCEDURE simple_procedure(json_arr_of_objs json not null) AS
DECLARE
  arr_of_objs ARRAY(json);
  res text = '';
BEGIN
  arr_of_objs = JSON_TO_ARRAY(json_arr_of_objs);
  FOR i IN 0 .. LENGTH(arr_of_objs) - 1 LOOP
    res = concat(res, i, ': ', arr_of_objs[i], '   ');
  END LOOP;
  ECHO SELECT res;
END //

DELIMITER ;

CALL simple_procedure('[1, 2, {"three": 3}, [456, 789]]');

So in your case your procedure can be defined as

CREATE OR REPLACE PROCEDURE ReportData(jsonArray json) AS

and then you can unpack jsonArray using JSON_TO_ARRAY:

  arr_of_objs = JSON_TO_ARRAY(json_arr_of_objs);

If that doesn’t work you can try defining procedure as the one taking blob or text and then convert it to json array this way:

CREATE OR REPLACE PROCEDURE ReportData(jsonArrayAsBlob BLOB) AS
...
BEGIN
    arr_of_objs = JSON_TO_ARRAY(jsonArrayAsBlob :> JSON);
    ...
END //

And then simply call this stored procedure with json1 passed as BLOB (or TEXT):

cmd1.Parameters.Add(new MySqlParameter("@jsonArrayAsBlob", MySqlDbType.Blob) { Value = json1 });

If BLOB doesn’t work (for whatever reason) can you also try TEXT?

3
  1. Define an array type and a procedure:
CREATE or replace PACKAGE Testpackage AS 
  TYPE Areas_t is table of VARCHAR(100) index by BINARY_INTEGER;
  PROCEDURE TESTPROCEDURE(Areas IN Areas_t);       
END Testpackage;
  1. C# routine prepaidgiftbalance:
public void InsertQuestion(IEnumerable<string> area_list)
{
    var connect = new OracleConnection("YOUR CONNECTION STRING");

    var command = new OracleCommand("BEGIN Testpackage.Testprocedure(:Areas); END;", connect);

    connect.Open();

    var arry = command.Parameters.Add("Areas", OracleDbType.Varchar2);

    arry.Direction = ParameterDirection.Input;
    arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    arry.Value = area_list.ToArray();
    arry.Size = area_list.Count();
    arry.ArrayBindSize = area_list.Select(_ => _.Length).ToArray();
    arry.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, area_list.Count()).ToArray();

    command.ExecuteNonQuery();

    connect.Close();
}