[SOLVED] How can I store a JSON file in a SQL Server database using ballerina?

How can I store a JSON file in a SQL Server database using ballerina?

I have a SQL Server database table with this structure:

CREATE TABLE json_file_table 
(
    file_id VARCHAR(50) PRIMARY KEY,
    json_value VARBINARY(MAX)
);

How to write a SQL query to insert value into the database and bind values to that query in Ballerina?

Solution

First you need to convert your json value into a byte[]

json a = { "name": "John", "age": 30 };
byte[] b = a.toString().toBytes();

Then you needs to create a sql:varBinaryValue instance using above bytes. sql:VarBinaryValue varBinaryValue = new sql:VarBinaryValue(b).

Then use that varBinaryValue in your sql query.

import ballerina/sql;
public function main() {
    json a = { "name": "John", "age": 30 };
    byte[] b = a.toString().toBytes();
    sql:VarBinaryValue varBinaryValue = new sql:VarBinaryValue(b);

    // use this varBinaryValue in a SQL query
}