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.
HBJSON.zip
(2 MiB) Downloaded 180 times
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
Friends, this is the new executable file.

https://drive.google.com/file/d/1O0rfLx ... p=drivesdk