Page 1 of 3
Working on a JSON Project
Posted: Sat Aug 04, 2018 4:36 pm
by Rathinagiri
Friends,
I am working on a JSON navigator/browser/viewer Project.
Even though initially I have tried and struggled to use HB_Hash functions, it became now easy for me to start the project with SQLite JSON1 Extension and hmgsqlite bridge.
I have completed the basic functionality of navigating through a JSON file/Text.
I am working on exporting the tables to Excel. Please suggest other features also if you want.
The whole project is uploaded along with the Exe file here.
viewtopic.php?p=55926#p55926
Re: Working on a JSON Project
Posted: Sat Aug 04, 2018 4:45 pm
by serge_girard
Thanks Rathi,
At the moment I can't think of any extra functionality. I will let you know!
Serge
Re: Working on a JSON Project
Posted: Sat Aug 04, 2018 4:50 pm
by serge_girard
Rathi, some functions are missing ? : connect2db, miscsql, ...
Serge
Re: Working on a JSON Project
Posted: Sat Aug 04, 2018 5:33 pm
by Rathinagiri
It is HMG Connectivity HMGSQLite bridge. Otherwise, you can also use this code.
Code: Select all
#include <hmg.ch>
FUNCTION connect2db(dbname,lCreate)
local dbo1 := sqlite3_open(dbname,lCreate)
IF Empty( dbo1 )
msginfo("Database could not be connected!")
RETURN nil
ENDIF
RETURN dbo1
function sql( dbo1, qstr )
local table := {}
local currow := nil
local tablearr := {}
local rowarr := {}
local typesarr := {}
local current := ""
local i := 0
local j := 0
local type1 := ""
local stmt := nil
local cDate
if empty(dbo1)
msgstop("Database Connection Error!")
return tablearr
endif
table := sqlite3_get_table(dbo1,qstr)
if sqlite3_errcode(dbo1) > 0 // error
msgstop(sqlite3_errmsg(dbo1)+" Query is : "+qstr)
return nil
endif
stmt := sqlite3_prepare(dbo1,qstr)
IF ! Empty( stmt )
for i := 1 to sqlite3_column_count( stmt )
type1 := HMG_UPPER(alltrim(sqlite3_column_decltype( stmt,i)))
do case
case type1 == "INTEGER" .or. type1 == "REAL" .or. type1 == "FLOAT" .or. type1 == "DOUBLE"
aadd(typesarr,"N")
case type1 == "DATE" .or. type1 == "DATETIME"
aadd(typesarr,"D")
case type1 == "BOOL"
aadd(typesarr,"L")
otherwise
aadd(typesarr,"C")
endcase
next i
endif
SQLITE3_FINALIZE( stmt )
stmt := nil
if HMG_LEN(table) > 1
asize(tablearr,0)
for i := 2 to HMG_LEN(table)
rowarr := table[i]
for j := 1 to HMG_LEN(rowarr)
do case
case typesarr[j] == "D"
cDate := HB_USUBSTR(rowarr[j],1,4)+HB_USUBSTR(rowarr[j],6,2)+HB_USUBSTR(rowarr[j],9,2)
rowarr[j] := stod(cDate)
case typesarr[j] == "N"
rowarr[j] := val(rowarr[j])
case typesarr[j] == "L"
if val(rowarr[j]) == 1
rowarr[j] := .t.
else
rowarr[j] := .f.
endif
endcase
next j
aadd(tablearr,aclone(rowarr))
next i
endif
return tablearr
function miscsql(dbo1,qstr)
if empty(dbo1)
msgstop("Database Connection Error!")
return .f.
endif
sqlite3_exec(dbo1,qstr)
if sqlite3_errcode(dbo1) > 0 // error
msgstop(sqlite3_errmsg(dbo1)+" Query is : "+qstr)
return .f.
endif
return .t.
function C2SQL(Value)
local cValue := ""
local cdate := ""
if ( valtype(value) == "C" .or. valtype( value ) == "M" ) .and. HMG_LEN(alltrim(value)) > 0
value := HB_UTF8STRTRAN(value, "'", "''" )
endif
do case
case Valtype(Value) == "N"
cValue := AllTrim(Str(Value))
case Valtype(Value) == "D"
if !Empty(Value)
cdate := dtos(value)
cValue := "'"+HB_USUBSTR(cDate,1,4)+"-"+HB_USUBSTR(cDate,5,2)+"-"+HB_USUBSTR(cDate,7,2)+"'"
else
cValue := "''"
endif
case Valtype(Value) $ "CM"
IF Empty( Value)
cValue="''"
ELSE
cValue := "'" + value + "'"
ENDIF
case Valtype(Value) == "L"
cValue := AllTrim(Str(iif(Value == .F., 0, 1)))
otherwise
cValue := "''" // NOTE: Here we lose values we cannot convert
endcase
return cValue
Re: Working on a JSON Project
Posted: Sun Aug 05, 2018 4:14 am
by bpd2000
Thank you Rathi, It is very much required
Copy / Paste / Search / Replace and re-export json file
Also visit Freeware tools including json viwer
http://www.mitec.cz/jsonv.html
Re: Working on a JSON Project
Posted: Sun Aug 05, 2018 4:12 pm
by Rathinagiri
Thank you for the link. It is so cool!
However, these free viewers are just creating a big tree of all objects. What we require is to treat the Arrays and Objects in a different way.
Wow! The possibilities of HMG are unimaginable! Now I have improved a lot with some code changes to flatten all the tables in the JSON and show them in a single window with different tab pages.
Try to walk through the attached sample JSON file (a real Indian GST file) with my viewer. I think you will see the difference.
Now it is time to format and export.
Give your suggestions also.
Re: Working on a JSON Project
Posted: Sun Aug 05, 2018 4:23 pm
by serge_girard
Great tool Rathi!
Serge
Re: Working on a JSON Project
Posted: Sun Aug 05, 2018 5:24 pm
by jayadevu
Excellent Rathinagiri.
Warm regards,
Jayadev
Re: Working on a JSON Project
Posted: Mon Aug 06, 2018 10:04 am
by Anand
Great sample code for json parsing Rathinagiri Sir. Thanks for it.
Regards,
Anand
Re: Working on a JSON Project
Posted: Wed Aug 08, 2018 1:28 pm
by Rathinagiri