How to export and import data to JSON format?
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