PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

SQLite Query returned as JSON array

https://www.purebasic.fr/english/viewtopic.php?t=77915

Page 1 of 1

SQLite Query returned as JSON array

Posted: Fri Sep 17, 2021 2:54 pm
by swhite
sqlite3 -json myDatabase.db "select * from MyTableName" > query.json
The above command allows you to extract data from an SQLite table and receive the result as a formatted JSON array. My question is it possible to use the PB Databasequery() to accomplish the same thing? I suppose the alternative option is to use RunProgram(). Any suggestions about the most efficient way to query SQLite and receive the response in JSON format would be appreciated.

Note the data in the table is not in JSON format it just the standard columns and rows. I just want to receive the results in JSON format. Postgres has functions that allow you to use the Select command and receive the results in JSON format that work perfectly with PB so I am looking for a similar solution for SQLite.

Thanks,
Simon

Re: SQLite Query returned as JSON array

Posted: Fri Sep 17, 2021 3:10 pm
by skywalk
Use JSON SQLite extension

Re: SQLite Query returned as JSON array

Posted: Fri Sep 17, 2021 3:37 pm
by swhite
Hi

I read through the JSON SQLite extension before posting because all the examples showed how to extract JSON from data that was already stored in JSON format or how to insert JSON into a table. What I needed was a method to extract data that was not in JSON format and convert the result to JSON which is what the command I quoted accomplishes.

In the mean time I created a function that runs the command and reads the program string from stdout which appears to work quite nicely. Now I am just looking for the fastest method.

Simon

Re: SQLite Query returned as JSON array

Posted: Sat Sep 18, 2021 10:31 am
by Marc56us
What I needed was a method to extract data that was not in JSON format and convert the result to JSON which is what the command I quoted accomplishes.
(Edited version without use of json lib. This version output the same thing as SQLite3 -json ...)

Code: Select all

; Quick and dirty but full sample for output SQLite (array) to Json file
; Marc56 - 2021/09/18
; in reply to https://www.purebasic.fr/english/viewtopic.php?f=7&t=77915

UseSQLiteDatabase()

Enumeration 
  #DB
  #Json
EndEnumeration

DB_File$ = "myDatabase.db"
Json_File$ = "query.json"

; Create sample database and fill it
If CreateFile(#DB, DB_File$)
  Debug "New file created"
  CloseFile(0)
Else
  Debug "Can't create DB file"
  End  
EndIf

If OpenDatabase(#DB, DB_File$, "", "")
  Debug "DB open"
  If DatabaseUpdate(#DB, "CREATE TABLE MyTableName ( Colors TEXT );"   +
                         "INSERT INTO  MyTableName VALUES ('red');"    +
                         "INSERT INTO  MyTableName VALUES ('green');"  +
                         "INSERT INTO  MyTableName VALUES ('blue')")
    Debug "Query OK"
  Else
    Debug DatabaseError()
  EndIf
  CloseDatabase(#DB)
Else
  Debug "Error" : End
EndIf

; Read database and create Json output file
If OpenDatabase(#DB, DB_File$, "", "")
  Debug "DB open"
  If DatabaseQuery(#DB, "SELECT * FROM MyTableName")
    Txt$ = "["
    While NextDatabaseRow(#DB)
      Txt$ + "{" + Chr(34) + DatabaseColumnName(#DB, 0) + Chr(34) + ":" +
              Chr(34) + GetDatabaseString(#DB, 0) + Chr(34) + "}," + #CRLF$
    Wend  
    
    If FileSize(Json_File$) : DeleteFile(Json_File$) : EndIf
    Txt$ = RemoveString(Txt$, ","+#CRLF$, 0, Len(Txt$)-2) + "]"
    OpenFile(#Json, Json_File$)
    WriteString(#Json, Txt$)
    CloseFile(#Json)
    
    CloseDatabase(#DB)
  Else
    Debug DatabaseError()
  EndIf
EndIf

; expected output of
; sqlite3 -json myDatabase.db "select * from MyTableName" > query3.json
; 
; [{"Colors":"red"},
; {"Colors":"green"},
; {"Colors":"blue"}]


; This will open default json viewer (ie: web navigator)
; RunProgram(Json_File$)
RunProgram("notepad", Json_File$, "")
Can be adapted as command line tool by extration of parameters in command line.
sqlite3 -json myDatabase.db "select * from MyTableName" > query.json

Code: Select all

For i = 0 To CountProgramParameters() - 1
  Debug "Parameter #" + i + " : " + ProgramParameter(i)
Next

Code: Select all

Parameter #0 : sqlite3
Parameter #1 : -json
Parameter #2 : myDatabase.db
Parameter #3 : select * from MyTableName
Parameter #4 : >
Parameter #5 : query.json
:!: Note that PB is very well done because it automatically takes as a single parameter the one between quotation marks

:wink:

Re: SQLite Query returned as JSON array

Posted: Mon Sep 20, 2021 10:29 am
by infratec

Code: Select all

; Quick and dirty but full sample for output SQLite (array) to Json file
; Marc56 - 2021/09/18
; Extended by infratec 2021/09/20
; in reply to https://www.purebasic.fr/english/viewtopic.php?f=7&t=77915

UseSQLiteDatabase()

Enumeration 
  #DB
  #Json
EndEnumeration



Procedure.s DatabaseQueryJSON(DB.i, SQL$)
  
  Protected JSON$
  
  If DatabaseQuery(DB, SQL$)
    JSON$ = "[" + #CRLF$
    While NextDatabaseRow(DB)
      JSON$ + "{"
      For i = 0 To DatabaseColumns(DB) - 1
      JSON$ + #DQUOTE$ + DatabaseColumnName(DB, i) + #DQUOTE$ + ":"
      If DatabaseColumnType(DB, i) = #PB_Database_String
        JSON$ + #DQUOTE$ + GetDatabaseString(DB, i) + #DQUOTE$ + ","
      Else
        JSON$ + GetDatabaseString(DB, i) + ","
      EndIf
    Next i
    JSON$ = Left(JSON$, Len(JSON$) - 1)
    JSON$ + "}," + #CRLF$
    Wend
    
    JSON$ = Left(JSON$, Len(JSON$) - 3) + #CRLF$ + "]"
    
    ;Debug JSON$
    
    FinishDatabaseQuery(DB)
    
  EndIf
  
  ProcedureReturn JSON$
  
EndProcedure



If OpenDatabase(#DB, ":memory:", "", "")
  Debug "DB open"
  If DatabaseUpdate(#DB, "CREATE TABLE MyTableName ( Colors TEXT, num INTEGER, flo FLOAT );"   +
                         "INSERT INTO  MyTableName VALUES ('red', 1, 1.1);"    +
                         "INSERT INTO  MyTableName VALUES ('green', 2, 2.2);"  +
                         "INSERT INTO  MyTableName VALUES ('blue', 3, 3.3)")
    
    Debug DatabaseQueryJSON(#DB, "SELECT * FROM MyTableName")
  Else
    Debug DatabaseError()
  EndIf
  CloseDatabase(#DB)
Else
  Debug "Error"
EndIf

Re: SQLite Query returned as JSON array

Posted: Mon Sep 20, 2021 10:55 am
by Kiffi
Please don't forget to escape the JSON values (and that the string concatenations in PureBasic are horribly slow).

Re: SQLite Query returned as JSON array

Posted: Mon Sep 20, 2021 12:32 pm
by infratec
More extended version:

Code: Select all

; Quick and dirty but full sample for output SQLite (array) to Json file
; Marc56 - 2021/09/18
; Extended by infratec 2021/09/20
; in reply to https://www.purebasic.fr/english/viewtopic.php?f=7&t=77915


EnableExplicit


Procedure.s JSONEscape(JSON$)
  
  JSON$ = ReplaceString(JSON$, "\", "\\") ; needs to be first ;)
  JSON$ = ReplaceString(JSON$, #DQUOTE$, "\" + #DQUOTE$)
  JSON$ = ReplaceString(JSON$, #CR$, "\r")
  JSON$ = ReplaceString(JSON$, #LF$, "\n")
  JSON$ = ReplaceString(JSON$, #TAB$, "\t")
  
  ProcedureReturn JSON$
  
EndProcedure



Procedure.s DatabaseQueryJSON(DB.i, SQL$)
  
  Protected JSON$, String$, i.i, Cols.i
  
  JSON$ = "{" + #DQUOTE$ + "sql" + #DQUOTE$ + ":" + #DQUOTE$ + SQL$ + #DQUOTE$ + ","
  If DatabaseQuery(DB, SQL$)
    JSON$ + #DQUOTE$ + "error" + #DQUOTE$ + ":" + #DQUOTE$ + #DQUOTE$ + ","
    JSON$ + #DQUOTE$ + "result" + #DQUOTE$ + ":[" + #CRLF$
    
    While NextDatabaseRow(DB)
      Cols = DatabaseColumns(DB) - 1
      JSON$ + "{"
      For i = 0 To Cols
        JSON$ + #DQUOTE$ + DatabaseColumnName(DB, i) + #DQUOTE$ + ":"
        If DatabaseColumnType(DB, i) = #PB_Database_String
          JSON$ + #DQUOTE$ + JSONEscape(GetDatabaseString(DB, i)) + #DQUOTE$ + ","
        Else
          JSON$ + GetDatabaseString(DB, i) + ","
        EndIf
      Next i
      JSON$ = Left(JSON$, Len(JSON$) - 1)
      JSON$ + "}," + #CRLF$
    Wend
    
    JSON$ = Left(JSON$, Len(JSON$) - 3) + #CRLF$ + "]}"
    
    FinishDatabaseQuery(DB)
  Else
    JSON$ + #DQUOTE$ + "error" + #DQUOTE$ + ":" + #DQUOTE$ + JSONEscape(DatabaseError()) + #DQUOTE$ + ","
    JSON$ + #DQUOTE$ + "result" + #DQUOTE$ + ":[]}"
  EndIf
  
  ProcedureReturn JSON$
  
EndProcedure



UseSQLiteDatabase()

If OpenDatabase(0, ":memory:", "", "")
  If DatabaseUpdate(0, "CREATE TABLE MyTableName ( Colors TEXT, num INTEGER, flo FLOAT );"   +
                         "INSERT INTO  MyTableName VALUES ('red\', 1, 1.1);"    +
                         "INSERT INTO  MyTableName VALUES ('green" + #DQUOTE$ + "', 2, 2.2);"  +
                         "INSERT INTO  MyTableName VALUES ('blue', 3, 3.3)")
    
    Debug DatabaseQueryJSON(0, "SELECT * FROM MyTableName")
    Debug DatabaseQueryJSON(0, "SELECT Colors, num FROM MyTableName WHERE num < 3")
    Debug DatabaseQueryJSON(0, "SELECT nothing FROM nowhere")
  Else
    Debug DatabaseError()
  EndIf
  CloseDatabase(0)
Else
  Debug DatabaseError()
EndIf

Re: SQLite Query returned as JSON array

Posted: Mon Sep 20, 2021 4:39 pm
by swhite
Hi

This version allows me to use PB native JSON escaping strings. It is actually faster than using all the JSON functions to create the array.

Code: Select all

lcJSBuf.s = ""
lndBHnd.i = OpenDatabase(#PB_Any,myDatabase,"","",#PB_Database_SQLite)
If lndBHnd
   If DatabaseQuery(lndBHnd,"select from mytable",#PB_Database_StaticCursor)
      lcComma1.s=""
      CreateJSON(0)
      lnO.i = SetJSONObject(JSONValue(0))
      While NextDatabaseRow(lndBHnd)
         lcJSON.s = lcComma1 +"{"
         lcComma2.s = ""
         For ln = 0 To DatabaseColumns(lndBHnd) - 1
            Select DatabaseColumnType(lndBHnd,ln)
               Case #PB_Database_String, 0 ; SQLite returns 0 for any column not based on a schema and treats it like text.
                  SetJSONString(AddJSONMember(lnO,DatabaseColumnName(lndBHnd,ln)),GetDatabaseString(lndBHnd,ln))
               Case #PB_Database_Long
                  lcJSON + lcComma2 + #DQUOTE$ + DatabaseColumnName(lndBHnd,ln) + #DQUOTE$+":"+GetDatabaseString(lndBHnd,ln)
                  lcComma2 = ","
               Case #PB_Database_Double
                  lcJSON + lcComma2 + #DQUOTE$ + DatabaseColumnName(lndBHnd,ln) + #DQUOTE$+":"+GetDatabaseString(lndBHnd,ln)
                  lcComma2 = ","
               Case #PB_Database_Quad
                  lcJSON + lcComma2 + #DQUOTE$ + DatabaseColumnName(lndBHnd,ln) + #DQUOTE$+":"+GetDatabaseString(lndBHnd,ln)
                  lcComma2 = ","
               Case #PB_Database_Float
                  lcJSON + lcComma2 + #DQUOTE$ + DatabaseColumnName(lndBHnd,ln) + #DQUOTE$+":"+GetDatabaseString(lndBHnd,ln)
                  lcComma2 = ","
            EndSelect
          Next
          If Len(lcJSON) <= 2
              lcJSBuf + lcJSON + LTrim(ComposeJSON(0),"{")
          Else
              lcJSBuf + lcJSON + RTrim(","+LTrim(ComposeJSON(0),"{"),",")
         EndIf
         lcComma1 = ","
      Wend
      FreeJSON(0)
      FinishDatabaseQuery(lndBHnd)
   EndIf
   CloseDatabase(lndBHnd)
   lcJSBuf = "["+lcJSBuf+"]"
EndIf
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited