By
argumentum
#include <SQLite.au3>
;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools
;-- for manipulating JSON data stored in the database.
;-- This is a gist showing SQLite return query as a JSON object.
;-- https://www.sqlite.org/json1.html
Example()
Func Example()
_SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm
_SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/
If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4
If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _
'(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _
'(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _
'(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5
; -- Get query data as a JSON object using the
; -- json_group_object() [1] and json_object() [2] functions.
_SQLite_GetTable2d_ArrayToConsole("SELECT" & _
" json_group_object(" & _
" email," & _
" json_object('full_name', full_name, 'created', created)" & _
" ) AS json_result" & _
" FROM (SELECT * FROM users WHERE created > ""02-01-01"");")
; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}}
; -- Get query data as a JSON object using the
; -- json_group_array() function to maintain order.
_SQLite_GetTable2d_ArrayToConsole("SELECT" & _
" json_group_array(" & _
" json_object('full_name', full_name, 'created', created)" & _
" ) AS my_json_result_OrAnythingReally" & _
" FROM (SELECT * FROM users ORDER BY created);")
; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}]
;-- Links
;-- [1] https://www.sqlite.org/json1.html#jgroupobject
;-- [2] https://www.sqlite.org/json1.html#jobj
; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2
EndFunc ;==>Example
Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1)
Local $aResult, $iRows, $iColumns
If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then
ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF)
Else
_SQLite_Display2DResult($aResult)
EndIf
ConsoleWrite(@CRLF)
EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole
Based on this example, you can build your own query.
The code has all the explanations.
Enjoy
By Colduction
By argumentum
By jresine
By Dwalfware