cancel
Showing results for 
Search instead for 
Did you mean: 

JSON to BYTEA

SOLVED
Level 2 Adventurer

JSON to BYTEA

Hello,

 

I want my code function to convert the JSON data to BYTEA and then inserts into the table. Following is the code,

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
DECLARE
    v1 "TBL1"."COL1"%TYPE;
    v2 "TBL1"."COL2"%TYPE;
BEGIN
v1 := utl_raw.cast_to_raw('{
 "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""              
     }');

 v2 := utl_raw.cast_to_raw('{
    "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""  
                    }');

 INSERT INTO "TBL1" ("SN","COL1","COL2") values(1,v1, v2);
END;
$$

LANGUAGE 'plpgsql';
COMMIT;
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
EDB Team Member

Re: JSON to BYTEA

Hi neiljo10,

 

Could you please remove the utl_raw.cast_to_raw function and execute the below function, we have tested the same and it works in our local environment.

 

Please find the attached function for your reference.

 

edb=# CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
edb$# BEGIN
edb$# INSERT INTO "TBL1" ("SN","COL1","COL2")
edb$# values(1,
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}',
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}');
edb$# END;
edb$# $$
edb-# LANGUAGE plpgsql;
CREATE FUNCTION
edb=# COMMIT;
COMMIT

4 REPLIES 4
Level 3 Adventurer

Re: JSON to BYTEA

Hi  neiljo10,

 

Please provide us with below-required information.

1. Which EPAS version you are using.

2. Is the function code you have pasted executing successfully, if not can you paste an error you are facing.

3. Table structure in which trying to insert data after manipulation. 

Highlighted
EDB Team Member

Re: JSON to BYTEA

Hi neiljo10,

 

Could you please remove the utl_raw.cast_to_raw function and execute the below function, we have tested the same and it works in our local environment.

 

Please find the attached function for your reference.

 

edb=# CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
edb$# BEGIN
edb$# INSERT INTO "TBL1" ("SN","COL1","COL2")
edb$# values(1,
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}',
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}');
edb$# END;
edb$# $$
edb-# LANGUAGE plpgsql;
CREATE FUNCTION
edb=# COMMIT;
COMMIT

Level 2 Adventurer

Re: JSON to BYTEA

Hi,

It is actually working fine.

But what I wanna confirmis that wether the data getting stored in BYTEA column is being converted to BYTEA data type.

 

@kapil  @Ranjan 

Level 3 Adventurer

Re: JSON to BYTEA

Hi neiljo10,

 

Bytea data type is accepting data to get stored means it is bytea type data otherwise it would have through error while storing data in bytea type column.

You will see the data stored in bytea column which is in binary format.