Introduction
Sometimes, you'll want to store an entire JSON object in the database for history purposes.
Background
What Kind of Data Can I Store?
CLOB – NORMALLY TEXT
BLOB - Video, Audio, PDF, JSON… (Just convert it to byte[]
) - I’m going to convert my JSON to a string
and store it in a blob.
BLOB vs CLOB?
From what I’ve read, if you have reason to believe you’ll need to view the data in SQL plus, you’ll want to use CLOB.
When storing text, CLOB is bigger than BLOB because each character is stored as two bytes (to support different character sets).
I did not research how to insert CLOBs, because I want to store a large JSON obj, and don’t want to use up twice the space. I also do not have any reason for someone to view the column in SQL developer.
Using the Code
Storing in a Blob
insert into ORDERS.ORDER_HISTORY (ORDERNUM, CREATE_USER, JSON_VALUE) _
Values(5,'BLOB',utl_raw.cast_to_raw('a bunch of test with under a MAX 2000 char buffer.......'));
commit;
select Cust_PK, SUBMISSION_DATE, CUSTNUM, CREATE_USER, _
utl_raw.cast_to_varchar2(JSON_VALUE) from ORDERS.ORDER_HISTORY where ORDERNUM = 5;
For files or items larger than 2,000 chars, you’ll need to create a web service and convert the data to a byte array using C#.
You’ll also need to bind the data in your INSERT
statement.
Insertion
public string AddNewOrderHist(int ORDERNUM, string orderStr)
{
string SQLString = "";
byte[] toBytes = Encoding.ASCII.GetBytes(orderStr);
SQLString = "insert into ORDERS.ORDER_HISTORY " +
" (ORDERNUM, CREATE_USER, JSON_VALUE) " +
" Values(" + ORDERNUM + ",'" + me.USRID + "',:1) ";
using (var connection = new OracleConnection())
{
OracleCommand command = connection.CreateCommand();
try
{
connection.ConnectionString = gblSYSConnStr;
connection.Open();
command.CommandText = SQLString;
OracleParameter param = command.Parameters.Add("blobtodb", OracleDbType.Blob);
param.Direction = ParameterDirection.Input;
param.Value = toBytes;
command.ExecuteNonQuery();
connection.Close();
return "SUCCESS";
}
catch (Exception e)
{
return "ERROR";
}
}
}
Reading Blob Data
(You need to know what you are storing in the BLOB column so that you can accurately convert it back from BYTE data.)
Retrieval
public string GetORDERHistRec(int apk)
{
string SQLString = "";
SQLString = "BEGIN select JSON_VALUE into :tempblob from ORDERS.ORDER_HISTORY " +
" where ORDERNUM = " + apk + "; END; ";
using (var connection = new OracleConnection())
{
OracleCommand command = connection.CreateCommand();
try
{
connection.ConnectionString = gblSYSConnStr;
connection.Open();
command.Parameters.Add
(":tempblob", OracleDbType.Blob).Direction = ParameterDirection.Output;
command.CommandText = SQLString;
command.ExecuteNonQuery();
byte[] byteData = new byte[0];
byteData = (byte[])((OracleBlob)(command.Parameters[0].Value)).Value;
string orderStr = Encoding.ASCII.GetString(byteData);
connection.Close();
return orderStr;
}
catch (Exception e)
{
return "ERROR";
}
}
}