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