JSON2DBF adapter

[Otto]

Dear Mr. Rao,

Perhaps you could create a JSON2DBF adapter for us?

I think it would be practical to have a function to fill JSON data into a DBF file in the following manner:

json2dbf(dbfFile, jsonData, {aFields})

However, perhaps such a function already exists and I am just not aware of it.

-----------------------------------------------------------------------------------------------------

It would also be beneficial to align the functions of mod harbour and Fivewin somewhat.
I often need functions from, for example, filename.prg in mod harbour, and conversely, the ValToChar() from mod harbour would be useful in Fivewin.

Best regards,
Otto

[nageswaragunupudi]

Right now, we have this function.

Code: Select all  Expand view

FW_DbfToJson ( [cFieldList], [bFor], [bWhile], [nNext], [nRec], [lRest] )
// The parameters are like DBEVAL(...)
 

but not JsonToDbf()

We will make it available soon.

[Cgallegoa]

A first attempt. Maybe someone can help improve it:

Code: Select all  Expand view

#include "FiveWin.ch"

REQUEST DBFCDX

Function Main()
LOCAL cJsonFile := "_Data.json", nHandle

SET EXCLUSIVE OFF

dbUseArea(.T., "DBFCDX", "d:\fwh\samples\customer", "customer")
COPY STRUCTURE TO "_NEWTBL.DBF"

hRet := FW_DbfToJson()  // *** FWH Function *** //

nHandle := FCreate(cJsonFile)
FWrite(nHandle, hRet)
FClose(nHandle)

dbCloseArea()

JsonToDbf()     // *** New Function *** //

dbCloseAll()
Return(NIL)
//------------------------------------------------//


// *** New Function *** //
Function JsonToDbf()  
LOCAL cJsonFile := "_Data.json"
LOCAL cResp, hResp := {=>}, uRet
LOCAL aData1 := {}, aData2 := {} , aData3 := {}, aData4 := {}
LOCAL aStruc

dbUseArea(.T., "DBFCDX", "_NEWTBL.DBF", "NewTbl")
aStruct := dbStruct()

cResp := MemoRead( cJsonFile )  // *** hResp is an array that has a Hash *** //
hb_jsonDecode( cResp, @hResp )

aFields := HGetKeys(hResp[1])

aPosStruct := {}
nPos := 0
for i := 1 TO Len(aStruct)
    nPos := ASCAN(aFields,{|x| x = aStruct[i,1]})
    AADD(aPosStruct,{aStruct[i,1], i, nPos})
next

hHasha := {=>}
for i := 1 TO Len(aPosStruct)
    cField := aPosStruct[i,1]
    hHasha["&cField"] := aPosStruct[i,2]
next

for each uRet IN hResp
    aData1 := HGetValues(uRet)
    aData2 := {}
    for i:=1 TO Len(aData1)
        nPosCol := HScan(hHasha,i)
        AADD(aData2,aData1[nPosCol])
    next

    aData3 := {}
    for i := 1 TO Len(aData2)
            AADD(aData3,aData2[i])
        next

    AADD(aData4, aData3 )

next

FW_ArrayToDBF( aData4 )
dbGoTop()
xBrowse(, Alias() + "   --   JSonToDBF() Result")

Return(NIL)
//------------------------------------------------------------//

[Otto]

Hello Carlos,

Thank you for your help. The function is working fine as long as there are no dates in the DBF.

I am concerned about what happens if the DBF and JSON do not have the same structure. We also need an assigning part within this function. You should be able to assign a key/value pair from the hash you receive from JSON to a specific DBF column with the correct value.

Regarding the function `FW_DbfToJson()`, it is perfect for a test when programming with html.

However, we need a simpler function only to create the array strings for the json. When preparing a JSON for HTML, there can be styling, etc., as well. For example, if you have values and they are negative, you can present these in HTML in red instead of black.

Thus, you have the `do while/enddo` not passing parameters like `[bFor]`, `[bWhile]`, `[nNext]`, `[nRec]`, `[lRest]`.

Programming the loop and conditions is more readable, maintainable, and generally more understandable. I would leave it as is.


But the lines would be fine if you could create them with:

`addresponse(address->name, 'start/end/nil')`

The `addresponse` should include a `Val2Char()` function.

```
h['response'] += "{" + '"name":' + '"' + ALLTRIM(address->name) + '",'
h['response'] += '"street":' + '"' + ALLTRIM(address->street) + '",'
h['response'] += '"city":' + '"' + ALLTRIM(address->city) + '"},'
```

Best regards,
Otto