[FEATURE REQUEST] JSON Data Type support for Stored Procedure parameters

[matthewerwin]

Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it

All versions of MSSQL now support JSON data type. There is no TYPE.JSON in Tedious or support to leverage this and pass JSON objects using this native type instead of as varchar/nvarchar.

Describe the preferred solution
Addition of JSON data type for the parameters that correctly serializes a JSON object to SQL Server when calling a stored procedure.

Describe alternatives you've considered
I've considered writing the PR myself but don't have any experience with Tedious code-base. The other alternatives are to continue to use varchar/nvarchar types and manually serialize and re-parse within an SP at the server...none of these are great options.

[matthewerwin]

For anyone that's curious, I wrote up the below example of how support for the Json data type on stored procedures could be accomplished without waiting on Tedious team to integrate this PR. This will work with the node-mssql library as an interim solution.

const SqlJsonDataType = {
  type: {
    id: 0xF4,
    type: 'JSON',
    name: 'Json',

    declaration: (parameter) => {
      return 'json';
    },
    resolveLength: (parameter) => {
      if( (parameter.value instanceof String || typeof (parameter.value) == "string") )
        return parameter.value.length;
      else return JSON.stringify(parameter.value).length;
    },

    generateTypeInfo: (parameter) => {
      return Buffer.from([0xF4]);
    },

    generateParameterLength: (parameter, options) => {
      const MAX_NULL_LENGTH = Buffer.from([0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF, 0xFF]);
      const UNKNOWN_PLP_LEN = Buffer.from([0xfe, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff, 0xff]);
      const value = parameter.value as Buffer | null;

      if( value == null ) {
        return MAX_NULL_LENGTH;
      }
      return UNKNOWN_PLP_LEN;
    },

    generateParameterData: function* (parameter, options) {
      const PLP_TERMINATOR = Buffer.from([0x00, 0x00, 0x00, 0x00]);
      const value = parameter.value as Buffer | null;

      if( value == null || (value?.length ?? 0) === 0 ) {
        return;
      }

      const buffer = Buffer.alloc(4);
      buffer.writeUInt32LE(value.length, 0);
      yield buffer;
      yield value;
      yield PLP_TERMINATOR;
    },

    validate: (value, collation): Buffer | null => {
      if( value == null ) {
        return null;
      }

      if( !(value instanceof String || typeof (value) == 'string') )
        value = JSON.stringify(value);
      else JSON.parse(value as string); //validate parsing

      return Buffer.from(value, 'utf-8');
    }
  }
};

function async exampleExecute(spName)
{
    const query = new sql.Request(await getConnectionPool());
    query.input('json_meta', SqlJsonDataType, {json: {is: 'useful'}});
    await query.execute(spName);
}