Working on a JSON Project

You can share your experience with HMG. Share with some screenshots/project details so that others will also be benefited.

Moderator: Rathinagiri

User avatar
Rathinagiri
Posts: 5234
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 160 times
Been thanked: 154 times
Contact:

Working on a JSON Project

Post by Rathinagiri » Sat Aug 04, 2018 4:36 pm

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
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
serge_girard
Posts: 2092
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 432 times
Been thanked: 92 times
Contact:

Post by serge_girard » Sat Aug 04, 2018 4:45 pm

Thanks Rathi,

At the moment I can't think of any extra functionality. I will let you know!

Serge

User avatar
serge_girard
Posts: 2092
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 432 times
Been thanked: 92 times
Contact:

Post by serge_girard » Sat Aug 04, 2018 4:50 pm

Rathi, some functions are missing ? : connect2db, miscsql, ...

Serge

User avatar
Rathinagiri
Posts: 5234
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 160 times
Been thanked: 154 times
Contact:

Post by Rathinagiri » Sat Aug 04, 2018 5:33 pm

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         
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
bpd2000
Posts: 1024
Joined: Sat Sep 10, 2011 4:07 am
Location: India
Has thanked: 165 times
Been thanked: 72 times

Post by bpd2000 » Sun Aug 05, 2018 4:14 am

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
BPD
Convert Dream into Reality through HMG

User avatar
Rathinagiri
Posts: 5234
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 160 times
Been thanked: 154 times
Contact:

Post by Rathinagiri » Sun Aug 05, 2018 4:12 pm

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 85 times
Now it is time to format and export.

Give your suggestions also.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
serge_girard
Posts: 2092
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Has thanked: 432 times
Been thanked: 92 times
Contact:

Post by serge_girard » Sun Aug 05, 2018 4:23 pm

Great tool Rathi!

Serge

jayadevu
Posts: 213
Joined: Tue May 19, 2009 7:10 am
Been thanked: 8 times

Post by jayadevu » Sun Aug 05, 2018 5:24 pm

Excellent Rathinagiri.

Warm regards,

Jayadev

User avatar
Anand
Posts: 302
Joined: Tue May 24, 2016 4:36 pm
DBs Used: DBF
Has thanked: 58 times
Been thanked: 42 times

Post by Anand » Mon Aug 06, 2018 10:04 am

Great sample code for json parsing Rathinagiri Sir. Thanks for it.

Regards,

Anand
Image

User avatar
Rathinagiri
Posts: 5234
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 160 times
Been thanked: 154 times
Contact:

Post by Rathinagiri » Wed Aug 08, 2018 1:28 pm

Friends, this is the new executable file.

https://drive.google.com/file/d/1O0rfLx ... p=drivesdk
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

Post Reply