How to export and import data to JSON format?

~ 0 min
2020-04-23 05:27

Enter below code to create store procedure “EXPORTJSON”. This stored procedure can export table to JSON format.

 

dmSQL> set block delimiter @@;

dmSQL> @@

CREATE OR REPLACE PROCEDURE EXPORTJSON(usr varchar(128),tab varchar(128),expath varchar(256),filter varchar(256))

LANGUAGE SQL

BEGIN

  DECLARE STR CHAR(128);

  DECLARE NCOL INT;

  DECLARE COLNAME varchar(128);

 

#get col num

  set STR='SELECT NUM_COL FROM SYSTABLE WHERE TABLE_NAME = upper(?) AND TABLE_OWNER = upper(?)';

  PREPARE stmt FROM str;

  EXECUTE stmt INTO NCOL USING tab,usr;

  DEALLOCATE PREPARE stmt;

  IF NCOL = NULL THEN

    return 6521,'table or view does not exist';

  END IF;

 

<lua>

   gbc={}

   collist={}

</lua>

 

#get col list

  set STR='SELECT TRIM(COLUMN_NAME) FROM SYSTEM.SYSCOLUMN WHERE TABLE_NAME = upper(?) AND TABLE_OWNER = upper(?) ORDER BY COLUMN_ORDER';

  PREPARE stmt FROM str;

  DECLARE cur1 CURSOR FOR stmt;

  OPEN cur1 USING tab,usr;

  WHILE SQLCODE = 0 DO  

    FETCH cur1 INTO COLNAME;

<lua>

   table.insert(collist,sv['COLNAME']:getval())

</lua> 

  END WHILE;

  CLOSE cur1;

  DEALLOCATE PREPARE stmt; 

 

  IF usr is not null and usr <> '' THEN

    set STR='SELECT * FROM ' || usr || '.' || tab ;

  ELSE

   set STR='SELECT * FROM ' || tab ;

  END IF;

 

  IF filter is not null and filter <> '' THEN

    set STR = STR || ' WHERE ' || filter;

  END IF;

 

  PREPARE stmt FROM STR;

  DECLARE cur2 CURSOR FOR stmt;

  OPEN cur2;

  WHILE SQLCODE = 0 DO

  <lua>

    precall(outcon)

    --FETCH Cursor

    if tblcur["CUR2"] ~= nil then

       rs_CUR = tblcur["CUR2"]:fetch({}, "n", "NEXT")end

    if type(rs_CUR) == "table" then  

      local tmp={}

      for k= 1,sv['NCOL']:getval(),1 do

      if type(rs_CUR[k]:getval()) == 'table' then

        tmp[collist[k]] = rs_CUR[k]:tostring()  --datatime

      else

        tmp[collist[k]] = rs_CUR[k]:getval()

      end

      end

      table.insert(gbc,tmp)

    end

    postcall(outcon)

  </lua>

  END WHILE;

  CLOSE cur2;

 

<lua>

  ff=io.open(sv['EXPATH']:getval(),'w')

  if not cjson then cjson = require 'cjson' end

  ff:write(cjson.encode_escape(gbc))

  ff:close()

</lua> 

  DEALLOCATE PREPARE stmt;

END;

@@

 

Enter below command to export table to JSON format.

dmSQL> call exportjson(owner_name, table_name, file_name, filter);

 

Enter below code to create stored procedure “IMPORTJSON”. This store procedure import JSON to table data.

 

dmSQL> set block delimiter @@;

dmSQL> @@

CREATE OR REPLACE PROCEDURE IMPORTJSON(usr varchar(128),tab varchar(128),expath varchar(256))

LANGUAGE SQL

BEGIN

  DECLARE STR CHAR(128);

  DECLARE NCOL INT;

  DECLARE VAR INT;

  DECLARE COLNAME varchar(128);

  DECLARE COLTYPE INT; 

 

#get col num

  set STR='SELECT NUM_COL FROM SYSTABLE WHERE TABLE_NAME = ? AND TABLE_OWNER = ?';

  PREPARE stmt FROM str;

  EXECUTE stmt INTO NCOL USING tab,usr;

  DEALLOCATE PREPARE stmt;

  IF NCOL = NULL THEN

    return 6521,'table or view does not exist';

  END IF;

 

<lua>

  collist={}

  coltype={}

  ff=io.open(sv['EXPATH']:getval(),'r')

  if not cjson then cjson = require 'cjson' end

  gbc = cjson.decode(ff:read('a'))

  ff:close()

</lua>

 

#get col list

  set STR='SELECT TRIM(COLUMN_NAME),DATA_TYPE FROM SYSTEM.SYSCOLUMN WHERE TABLE_NAME = ? AND TABLE_OWNER = ? ORDER BY COLUMN_ORDER';

  PREPARE stmt FROM str;

  DECLARE cur1 CURSOR FOR stmt;

  OPEN cur1 USING tab,usr;

  WHILE SQLCODE = 0 DO  

    FETCH cur1 INTO COLNAME,COLTYPE;

<lua>

   table.insert(collist,sv['COLNAME']:getval())

   table.insert(coltype,sv['COLTYPE']:getval())

</lua>

  END WHILE;

  CLOSE cur1;

  DEALLOCATE PREPARE stmt; 

 

  IF usr = '' THEN

    SET STR= 'INSERT INTO ' || tab || ' VALUES(';

  ELSE

    SET STR= 'INSERT INTO ' || usr || '.' || tab || ' VALUES(';

  END IF;

 

  set VAR = 0;

  WHILE VAR < NCOL DO

    IF VAR = 0 THEN

      SET STR= STR || '?';

    ELSE

      SET STR= STR || ',?';

    END IF;

    SET VAR = VAR + 1;

  END WHILE;

  SET STR= STR || ')';

 

  PREPARE stmt FROM STR;

 

# EXECUTE stmt USING VAR,COLNAME;

<lua>

  for k=1,rawlen(gbc),1 do

  local slice=gbc[k]

  precall(outcon)

  --EXECUTE 

      local exec_args_table={}

      for i=1,sv['NCOL']:getval(),1 do

        if coltype[i] == 1 or coltype[i] == 12 or coltype[i] == -1 or

           coltype[i] == 9 or coltype[i] == 10 or coltype[i] == 11  or coltype[i] == -4 then

          table.insert(exec_args_table,DTO.varchar(rawlen(slice[collist[i]]),slice[collist[i]]))

      elseif coltype[i] == 3 or coltype[i] == 6 or  coltype[i] == 7 or coltype[i] == 8 then

          table.insert(exec_args_table,DTO.double(slice[collist[i]]))

        elseif coltype[i] == 4 or coltype[i] == 5 or coltype[i] == -5 then

          table.insert(exec_args_table,DTO.bigint(slice[collist[i]]))

        elseif coltype[i] == -8 or coltype[i] == -9 or coltype[i] == -10 then

          table.insert(exec_args_table,DTO.nvarchar(rawlen(slice[collist[i]]),slice[collist[i]]))

        elseif coltype[i] == -2 or coltype[i] == -3  then

          table.insert(exec_args_table,DTO.binary(rawlen(slice[collist[i]]),slice[collist[i]]))

        else

          table.insert(exec_args_table,DTO.int(nil))

      end

      end  

    sv("STMT", outcon:execute(sv["hstmt_STMT"], exec_args_table, nil, nil))

  postcall(outcon)

  end

</lua>

  DEALLOCATE PREPARE stmt;

END;

 

Enter below command to import data from JSON format.

dmSQL> call importjson(owner_name, table_name, file_name);

 

Version: DBMaker 5.4.3

Product: Normal/Bundle

Platform: Windows/Linux

Average rating 0 (0 Votes)

You cannot comment on this entry

Tags