SQLite Query returned as JSON array

Everything else that doesn't fall into one of the other PB categories.
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

SQLite Query returned as JSON array

Post 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
Simon White
dCipher Computing
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite Query returned as JSON array

Post by skywalk »

The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

Re: SQLite Query returned as JSON array

Post 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
Simon White
dCipher Computing
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: SQLite Query returned as JSON array

Post 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:
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite Query returned as JSON array

Post 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
User avatar
Kiffi
Addict
Addict
Posts: 1353
Joined: Tue Mar 02, 2004 1:20 pm
Location: Amphibios 9

Re: SQLite Query returned as JSON array

Post by Kiffi »

Please don't forget to escape the JSON values (and that the string concatenations in PureBasic are horribly slow).
Hygge
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite Query returned as JSON array

Post 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
swhite
Enthusiast
Enthusiast
Posts: 726
Joined: Thu May 21, 2009 6:56 pm

Re: SQLite Query returned as JSON array

Post 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      
Simon White
dCipher Computing
Post Reply