Jump to content

SQLite returning query as JSON


Recommended Posts

#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 :) 

Link to post
Share on other sites

Nice, but as I said in another thread, you have to build your own formatting depending on each query, just to put column names in the resulting json.

An ideal solution would be a function like this:

select jsonify(select ...) whatever the inner select is.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

...same here @dmob, but I've got lucky. :P 
 

1 hour ago, jchd said:

An ideal solution would be a function like this: select jsonify(select ...) whatever the inner select is.

yes. And that is what I did  something along the idea is what I did for my testing tho, is not a general purpose func. , just a hack to get it out of my way:

Func JsonIze_Query($s, $sQuery)
    Local $s_json_object_Str = "", $sSQL = "SELECT json_group_array( json_object("
    Local $a_json_object_Str = StringSplit($s, ",")
    For $n = 1 To $a_json_object_Str[0]
        $s_json_object_Str &= ",'" & $a_json_object_Str[$n] & "'," & $a_json_object_Str[$n]
    Next
    $sSQL &= StringTrimLeft($s_json_object_Str, 1) & ")) AS json_result FROM (" & $sQuery & ");"
    ConsoleWrite($sSQL & @CRLF)
    Return $sSQL
EndFunc

 

Edited by argumentum
clarify a bit
Link to post
Share on other sites

I'm not trying to belittle your good code, not at all. What I find unpractical but inevitable is that you still have to setup both $s and $sQuery in paired terms for every distinct query. Either this way, or merge both when typing the query as in the other thread.

Deriving $s from the analysis of $sQuery automagically is way beyond doable for the general case (SQL grammar isn't simple!).

Passing the result array to an extension function in plain C in order to do the json formatting quickly is hard due to AutoIt using Variant type.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

..basically this is just "SELECT json_group_array( json_object(" & "'name', name, 'etc', etc" & ")) AS json_result FROM (" & $sSQL & ");"
Coding a table_names_generator() can get cumbersome and mainly, slow, just to get the "'name', name, 'etc', etc" part. And that string will be used for every call to the same query.
I love automagical functions but given the time cost of AutoIt executions, in the aim of speed, coding such would defeat the purpose of a speedy script.
The loop I made to take an array and generate a json string was practical but slow, so one has to choose: hard code or magical function. What is faster.

Finding a query that makes the resultant faster, that in itself I'd call "magical query" ( as I did not come up with it solely on my own ), so, I saved around 200 ms. ( in my query ) by having SQLite formatting the return value.

And I feel I know you better than believing that you would aim to belittle anyone. Moreover, you strive to enlighten everyone in every posting, so, thanks for taking the time to evaluate possibilities.

Now, if an "ASM god" comes up with a _SQLite_GetTable2d_array_to_json(), now that would be vary much welcomed :) 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By TheXman
      This UDF brings the power and flexibility of jq to AutoIt scripts.  jq is an open-source, powerful, and flexible command-line based JSON processor.  As it says on their website, jq is like 'sed' for JSON.  jq can be used for the simplest of tasks like retrieving JSON objects and values (parsing), to very advanced JSON processing using its numerous built-in functions and conditional processing.  Its built-in functions can handle math, selection, conditional processing, mapping, object and array manipulation, flattening, reduction, grouping, and much more.  You can even create your own jq functions.  You can learn more about jq and even play with it in real-time, using jq's online jq playground, all on their website.
      Here and some helpful links to get you more familiar with jq, what can be done with it, its built-in functions, and its syntax.
      jq Website: https://stedolan.github.io/jq/ jq Manual: https://stedolan.github.io/jq/manual/ jqWiki (FAQ, Cookbook, Advanced Topics) https://github.com/stedolan/jq/wiki jq Online Testing Playground: https://jqplay.org/ jq is a single 32 or 64 bit executable that has no other dependencies.  Just like using the SQLite UDF, the only requirement to use this UDF is that the jq executable reside in a location in which the UDF can execute it.  The latest win32 & win64 versions have been included in the UDF download.  You can always get newer versions from the jq website.
      I don't consider this UDF as a replacement for some of the other JSON UDFs like the one based on JSMN.  If speed and simple JSON parsing are your primary goals, then other UDFs may be a better choice.  However, if you like having the power to do just about anything related to JSON processing/manipulation, using a single UDF, then jq may be worth checking out.  It can be used by novices and experts alike.  Below, is a brief explanation of how jq works, how to use the jq UDF, and a few examples of how to do some simple tasks.  If you want to learn more about jq and what it can do, I would highly suggest checking out the jq website and some of the other resources listed above.
      jq at a high level
      Like 'sed', jq reads JSON in, either through STDIN or one or more files, processes it thru one or more "filters", and outputs the results.  You can, optionally, supply "options" that affect how it reads the input, where it gets its "filters", and how it writes its output.  It looks a little like this:
      JSON ---> jq processor (using supplied filters and options) ---> Output
      So in jq lingo, you basically use "Filters" to tell jq what you want it to do.  So in the UDF file, that is why the main functions ( _jqExec() and _jqExecFile() ) refer to filters and options.  Please make note that jq works with relatively strict JSON.  This means that all JSON read must be conform to the standard.  Luckily, jq is pretty good at identifying where a format error exists in non standard JSON.
      The jq UDF
      There are 2 main funtions in the UDF file, _jqExec and jqExecFile.  With these 2 functions, you can pretty much do anything that jq can do.  The only difference between to two functions is whether the JSON is supplied by a string or a file.  The 2 primary functions simply call the jq executable with the supplied information, after properly formatting the parameters.  There are additional functions in the UDF to easily pretty-print your json, compact-print your json, dump the json data with its associated paths, and see if specific JSON keys exist, but they all just execute the _jqExec or _jqExecFile function with the proper filter.  There are also a couple of extra functions to display what version of the UDF and jq executable you are currently using.  There are also a couple of functions to enable and disable logging of jq information for debugging purposes.  Most of the jq UDF file functions return an @error if unsuccessful.  Some also include @extended info.  Please see the actual function headers for more information on their usage and return values.
      The 2 primary functions below just format your jq request and pass it on the jq executable.  The functions will also properly escape double quotes (") that are used in the filter.  For most simple tasks, you just need to supply the JSON source and a filter.
      _jqExec($sJson, $sFilter, $sOptions = Default, $sWorkingDir = Default) Or _jqExecFile($sJsonFile, $sFilter, $sOptions = Default, $sWorkingDir = Default) Using jq in your script
      As stated earlier, the jq executable must reside somewhere where the script can locate and execute it.  The _jqInit() function always has to be executed before any jq processing occurs.  _jqInit() merely locates the executable or uses the supplied path.  It also clears any previous debug log.  The jq UDF folder contains a jq example script that has several examples to how to do some of the most common JSON processing tasks.  Here are a few examples to get you started:

      How to pretty-print some JSON
      #include "jq.au3" ;_jqInit is only needed if the jq executale is not in the PATH or @ScriptDir _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"fruits":[{"Apple":{"color":"Red","season":"Fall"}}, {"Banana":{"color":"Yellow","season":"Summer"}}]}' $sCmdOutput = _jqPrettyPrintJson($sJson) ConsoleWrite(@CRLF & "Pretty-Print JSON" & @CRLF & $sCmdOutput & @CRLF) How to compact-print some JSON
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{ "fruits" : [{"Apple" : {"color":"Red","season":"Fall"}}, {"Banana":{"color":"Yellow","season":"Summer"}}]}' $sCmdOutput = _jqCompactPrintJson($sJson) ConsoleWrite(@CRLF & "Compact-Print JSON" & @CRLF & $sCmdOutput & @CRLF) Dump JSON data (paths and values)
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{ "fruits" : [{"Apple" : {"color":"Red","season":"Fall"}}, {"Banana":{"color":"Yellow","season":"Summer"}}]}' $sCmdOutput = _jqDump($sJson) ConsoleWrite(@CRLF & "Dump JSON paths and values" & @CRLF & $sCmdOutput & @CRLF) How to GET JSON values
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple" : {"color":"Red","season":"Fall"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = '.Banana.color' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Get color of banana" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF) or
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple" : {"color":"Red","season":"Fall"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = 'getpath(["Banana", "color"])' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Get color of banana" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF)  
      Check for the existence of a key
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple":{"color":"Red","season":"Fall"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = '.Banana | has("color")' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Check for existence of color key within Banana object" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF) Count of how many Items in an object
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple":{"color":"Red"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = '.Banana | length' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("How many items in the Banana object" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF) How to PUT/Create/Modify JSON
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sInput = "" $sFilter = 'setpath(["Apple","color"];"Red") | setpath(["Banana","color"];"Yellow") | setpath(["Banana","season"];"Summer")' $sOptions = '-n' ;required if no input supplied $sCmdOutput = _jqExec($sInput, $sFilter, $sOptions) ConsoleWrite("Update/Create JSON" & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & @CRLF & $sCmdOutput & @CRLF) List all of the fruits (top-level keys)
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple":{"color":"Red"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = 'keys | .[]' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("List all top-level keys (fruits)" & @CRLF) ConsoleWrite("Input : " & $sJson & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & @CRLF & $sCmdOutput & @CRLF) Calculate the sum of all of the objects' price * qty
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '[{"id":1,"price":20.00,"qty":10},{"id":2,"price":15.00,"qty":20.25},{"id":3,"price":10.50,"qty":30}]' $sFilter = 'map(.price * .qty) | add' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Calculate the sum of all of the objects' price * qty" & @CRLF) ConsoleWrite("Input : " & $sJson & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF)
      The examples above, and the ones in the example files, merely scratch the surface of what jq can do.  It may look intimidating at first but it really isn't that bad once you start playing with it.
      If you have any questions regarding the UDF, or how to perform a certain task using jq, I'll try my best to answer them.  Since jq has been around for a while now, there's also several jq-related questions and answers on StackOverflow.
      If you work with JSON, I hope you find this UDF useful as I do.
      >>> Download in the Files Section <<<
       
    • By TheXman
      This UDF brings the power and flexibility of jq to AutoIt scripts.  jq is an open-source, powerful, and flexible command-line based JSON processor.  As it says on their website, jq is like 'sed' for JSON.  jq can be used for the simplest of tasks like retrieving JSON objects and values (parsing), to very advanced JSON processing using its numerous built-in functions and conditional processing.  Its built-in functions can handle math, selection, conditional processing, mapping, object and array manipulation, flattening, reduction, grouping, and much more.  You can even create your own jq functions.  You can learn more about jq and even play with it in real-time, using jq's online jq playground, all on their website.
      Here and some helpful links to get you more familiar with jq, what can be done with it, its built-in functions, and its syntax.
      jq Website: https://stedolan.github.io/jq/ jq Manual: https://stedolan.github.io/jq/manual/ jqWiki (FAQ, Cookbook, Advanced Topics) https://github.com/stedolan/jq/wiki jq Online Testing Playground: https://jqplay.org/ jq is a single 32 or 64 bit executable that has no other dependencies.  Just like using the SQLite UDF, the only requirement to use this UDF is that the jq executable reside in a location in which the UDF can execute it.  The latest win32 & win64 versions have been included in the UDF download.  You can always get newer versions from the jq website.
      I don't consider this UDF as a replacement for some of the other JSON UDFs like the one based on JSMN.  If speed and simple JSON parsing are your primary goals, then other UDFs may be a better choice.  However, if you like having the power to do just about anything related to JSON processing/manipulation, using a single UDF, then jq may be worth checking out.  It can be used by novices and experts alike.  Below, is a brief explanation of how jq works, how to use the jq UDF, and a few examples of how to do some simple tasks.  If you want to learn more about jq and what it can do, I would highly suggest checking out the jq website and some of the other resources listed above.
      jq at a high level
      Like 'sed', jq reads JSON in, either through STDIN or one or more files, processes it thru one or more "filters", and outputs the results.  You can, optionally, supply "options" that affect how it reads the input, where it gets its "filters", and how it writes its output.  It looks a little like this:
      JSON ---> jq processor (using supplied filters and options) ---> Output
      So in jq lingo, you basically use "Filters" to tell jq what you want it to do.  So in the UDF file, that is why the main functions ( _jqExec() and _jqExecFile() ) refer to filters and options.  Please make note that jq works with relatively strict JSON.  This means that all JSON read must be conform to the standard.  Luckily, jq is pretty good at identifying where a format error exists in non standard JSON.
      The jq UDF
      There are 2 main funtions in the UDF file, _jqExec and jqExecFile.  With these 2 functions, you can pretty much do anything that jq can do.  The only difference between to two functions is whether the JSON is supplied by a string or a file.  The 2 primary functions simply call the jq executable with the supplied information, after properly formatting the parameters.  There are additional functions in the UDF to easily pretty-print your json, compact-print your json, dump the json data with its associated paths, and see if specific JSON keys exist, but they all just execute the _jqExec or _jqExecFile function with the proper filter.  There are also a couple of extra functions to display what version of the UDF and jq executable you are currently using.  There are also a couple of functions to enable and disable logging of jq information for debugging purposes.  Most of the jq UDF file functions return an @error if unsuccessful.  Some also include @extended info.  Please see the actual function headers for more information on their usage and return values.
      The 2 primary functions below just format your jq request and pass it on the jq executable.  The functions will also properly escape double quotes (") that are used in the filter.  For most simple tasks, you just need to supply the JSON source and a filter.
      _jqExec($sJson, $sFilter, $sOptions = Default, $sWorkingDir = Default) Or _jqExecFile($sJsonFile, $sFilter, $sOptions = Default, $sWorkingDir = Default) Using jq in your script
      As stated earlier, the jq executable must reside somewhere where the script can locate and execute it.  The _jqInit() function always has to be executed before any jq processing occurs.  _jqInit() merely locates the executable or uses the supplied path.  It also clears any previous debug log.  The jq UDF folder contains a jq example script that has several examples to how to do some of the most common JSON processing tasks.  Here are a few examples to get you started:

      How to pretty-print some JSON
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"fruits":[{"Apple":{"color":"Red","season":"Fall"}}, {"Banana":{"color":"Yellow","season":"Summer"}}]}' $sCmdOutput = _jqPrettyPrintJson($sJson) ConsoleWrite(@CRLF & "Pretty-Print JSON" & @CRLF & $sCmdOutput & @CRLF) How to compact-print some JSON
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{ "fruits" : [{"Apple" : {"color":"Red","season":"Fall"}}, {"Banana":{"color":"Yellow","season":"Summer"}}]}' $sCmdOutput = _jqCompactPrintJson($sJson) ConsoleWrite(@CRLF & "Compact-Print JSON" & @CRLF & $sCmdOutput & @CRLF) Dump JSON data (paths and values)
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{ "fruits" : [{"Apple" : {"color":"Red","season":"Fall"}}, {"Banana":{"color":"Yellow","season":"Summer"}}]}' $sCmdOutput = _jqDump($sJson) ConsoleWrite(@CRLF & "Dump JSON paths and values" & @CRLF & $sCmdOutput & @CRLF) How to GET JSON values
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple" : {"color":"Red","season":"Fall"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = '.Banana.color' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Get color of banana" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF) or
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple" : {"color":"Red","season":"Fall"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = 'getpath(["Banana", "color"])' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Get color of banana" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF)  
      Check for the existence of a key
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple":{"color":"Red","season":"Fall"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = '.Banana | has("color")' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Check for existence of color key within Banana object" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF) Count of how many Items in an object
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple":{"color":"Red"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = '.Banana | length' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("How many items in the Banana object" & @CRLF) ConsoleWrite("Input: : " & _jqCompactPrintJson($sJson) & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF) How to PUT/Create/Modify JSON
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sInput = "" $sFilter = 'setpath(["Apple","color"];"Red") | setpath(["Banana","color"];"Yellow") | setpath(["Banana","season"];"Summer")' $sOptions = '-n' ;required if no input supplied $sCmdOutput = _jqExec($sInput, $sFilter, $sOptions) ConsoleWrite("Update/Create JSON" & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & @CRLF & $sCmdOutput & @CRLF) List all of the fruits (top-level keys)
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '{"Apple":{"color":"Red"}, "Banana":{"color":"Yellow","season":"Summer"}}' $sFilter = 'keys | .[]' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("List all top-level keys (fruits)" & @CRLF) ConsoleWrite("Input : " & $sJson & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & @CRLF & $sCmdOutput & @CRLF) Calculate the sum of all of the objects' price * qty
      #include "jq.au3" ;Initialize jq environment _jqInit() If @error Then Exit ConsoleWrite("ERROR: Unable to initialize jq - @error = " & @error & @CRLF) $sJson = '[{"id":1,"price":20.00,"qty":10},{"id":2,"price":15.00,"qty":20.25},{"id":3,"price":10.50,"qty":30}]' $sFilter = 'map(.price * .qty) | add' $sCmdOutput = _jqExec($sJson, $sFilter) ConsoleWrite("Calculate the sum of all of the objects' price * qty" & @CRLF) ConsoleWrite("Input : " & $sJson & @CRLF) ConsoleWrite("Filter : " & $sFilter & @CRLF) ConsoleWrite("Output : " & $sCmdOutput & @CRLF)
      The examples above, and the ones in the example files, merely scratch the surface of what jq can do.  It may look intimidating at first but it really isn't that bad once you start playing with it.
      If you have any questions regarding the UDF, or how to perform a certain task using jq, I'll try my best to answer them.  Since jq has been around for a while now, there's also several jq-related questions and answers on StackOverflow.
      If you work with JSON, I hope you find this UDF useful as I do.
    • By diepfeile
      I'm using the following:
      Autoit 3.3.14.5
      newly installed Beta 3.3.15.5
      SQlite version 3380000 aka 3.38.0
      I put sqlite3.dll and sqlite3_x64.dll in C:\Windows\System32 since many scripts depend on them.


      I extended the output of _SQLite_Startup()
      with:
      ConsoleWrite("@AutoItX64 " & @AutoItX64 & @CRLF) ConsoleWrite("$sDll_Filename " & $sDll_Filename & @CRLF) ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

      Also using the script from https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm for testing.

       
      >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3.exe "R:\Download\aasdf.au3" @AutoItX64 0 $sDll_Filename sqlite3.dll _SQLite_LibVersion=0 >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "R:\Download\aasdf.au3" @AutoItX64 1 $sDll_Filename sqlite3_x64.dll _SQLite_LibVersion=3.38.0 >Running:(3.3.15.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3.exe "R:\Download\aasdf.au3" @AutoItX64 0 $sDll_Filename sqlite3.dll _SQLite_LibVersion=0 >Running:(3.3.15.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3_x64.exe "R:\Download\aasdf.au3" @AutoItX64 1 $sDll_Filename sqlite3_x64.dll _SQLite_LibVersion=3.38.0


      Why doesn't it work in 32bit, despite me having the 32bit sqlite.dll? Autoit urges running scripts in 32bit mode and Scite starts scripts just in 32bit mode without the flag?
      With #AutoIt3Wrapper_UseX64=Y it just works, both normal Autoit and beta!
      sqlite3.dll sqlite3_x64.dll
    • By rot3r
      Hi friends,
      I'm using json.au3 to register some data in my server, everything work fine except the data that i send in utf8 format like names saved as ????? .
      i think something mess with inetget cause this problem
      can you help me please?
      $sAdditionalData = "secret_key=" & $secret_key_verify & "&slm_action=slm_check&license_key=" & $license_key&"&first_name="&$first_name $data = _INetGetSource($sDomain&'/?'&$sAdditionalData) $object = json_decode($data) Json_Dump($data) $response = json_get($object, '.first_name')  
    • By t0nZ
      I was searching for a way to highlight zones (regions, provinces, counties, etc) on a map, and I don't need  super precise maps so I wrote this script, based on picking up black and white maps (2 colors BW .png or .gif tested) and filling them with colors, writing down a sqlite database to associate zones with names (and other data as well), and reuse the map and the DB to display data, in my example reading a simple .txt file.
      It's all based on this thread  and this other thread.

      So I have two modes:
      The Map "creation mode" : you provide a map image and you start to pick up colors, set "upper level" region/state, and by clicking on a region you fill it and you name it, and all the data are saved on a sqlite DB (auto-created) when you have the map image and a DB with the correct associations, you can switch the "mode" to "show" (as by .ini file) and the script tries to read a "datafile" showing the zone names listed in datafile. The code:
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=Icone\mapFlooder.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** ;MAP Flooder ;(C) NSC 2021 #include <GUIConstants.au3> #include <_GOLLOG.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <Misc.au3> #include <GDIPlus.au3> Opt("mousecoordmode", 2) Global $prgname = "MAP Flooder", $ver = "V.0.7", $Buttoncolor = "0xFF00FF", $MPini = @ScriptDir & "\MapFlooder.ini", $btest Global $dbfullpath, $dbtable, $dbFields, $mapfile, $FloodMode, $datafile Global $HDC, $hBrush, $hGraphics, $obj_orig Global $Pic1, $gui, $width, $height, $bColor, $realtimeCoords, $lastclickcoords, $inputSup, $zonecountNum,$labeltest #Region program Gollog(">>>>>> Start MAP Flooder " & $ver) ctrlini() Gui() SQLiteDBcreate() If $FloodMode = "createdb" Then Gollog("CreateDB Mode") DBFlooder() Else Gollog("Show MAP mode") MapShow("show") EndIf Close() #EndRegion program #Region funcS Func Gui() _GDIPlus_Startup() $Pic1 = _GDIPlus_BitmapCreateFromFile($mapfile) $width = _GDIPlus_ImageGetWidth($Pic1) $height = _GDIPlus_ImageGetHeight($Pic1) If $FloodMode = "createdb" Then $gui = GUICreate($prgname & " " & $ver, $width + 150, $height) $labelLoadedMap = GUICtrlCreateLabel("Loaded Map", $width + 10, 5) $labelLoadedMap2 = GUICtrlCreateLabel(_FileToFileName($mapfile), $width + 10, 25) $labeldim = GUICtrlCreateLabel("Width*Height", $width + 10, 45) $labeldim2 = GUICtrlCreateLabel($width & " * " & $height, $width + 10, 65) $lastclickcoordslabel = GUICtrlCreateLabel("Last Click Coords", $width + 10, 100) $lastclickcoords = GUICtrlCreateLabel("xx - xx", $width + 10, 120, 180, 20) $realtimeCoordslabel = GUICtrlCreateLabel("Real Time Coords", $width + 10, 140) $realtimeCoords = GUICtrlCreateLabel("Real Time Coords", $width + 10, 160, 80, 20) $SuPzonelabel = GUICtrlCreateLabel("Supzone (region-state)", $width + 10, 200) $inputSup = GUICtrlCreateInput("sup", $width + 10, 220, 80, 20) $bColor = GUICtrlCreateButton($Buttoncolor, $width + 10, 250, 130, 30) GUICtrlSetBkColor($bColor, $Buttoncolor) $zonecountlabel = GUICtrlCreateLabel("Done Zone Count:", $width + 10, 320, 100, 20) $zonecountNum = GUICtrlCreateLabel("x", $width + 10, 340, 100, 20) $btest = GUICtrlCreateButton("TEST MAP", $width + 10, 380, 130, 30) $labeltest = GUICtrlCreateLabel("", $width + 10, 420, 130, 30) Else $gui = GUICreate($prgname & " " & $ver, $width, $height) EndIf GUISetState() $HDC = _WinAPI_GetDC($gui) $hGraphics = _GDIPlus_GraphicsCreateFromHDC($HDC) _GDIPlus_GraphicsDrawImageRect($hGraphics, $Pic1, 0, 0, $width, $height) EndFunc ;==>Gui Func MapShow($showmode) ; reading a simple text file with zone names, searching for names in DB and fill the map using stored coordinates If $showmode = "show" Then Local $aLines = FileReadToArray($datafile) Local $iLineCount = @extended EndIf If $showmode = "test" Then $iLineCount = 1 If @error Then MsgBox(48, "MapFlooder", "There was an error reading the data file. @error: " & @error) ; Gollog("There was an error reading the data file. @error: " & @error) Close() Else Gollog("start filling zones") _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database with zone definitions Local $hQuery, $aRow For $i = 0 To $iLineCount - 1 If $showmode = "show" Then _SQLite_Query(-1, "SELECT * FROM " & $dbtable & " where zone = '" & $aLines[$i] & "' ORDER BY zone ASC;", $hQuery) ; the query EndIf If $showmode = "test" Then _SQLite_Query(-1, "SELECT * FROM " & $dbtable & " ORDER BY zone ASC;", $hQuery) ; the query EndIf While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $aRow[2]) ; fill color read from DB $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $aRow[3], "int", $aRow[4], "int", 0x000000) If $showmode = "test" Then GUICtrlSetData($labeltest,$aRow[0]) Sleep(200) GUISetState() EndIf WEnd _SQLite_QueryFinalize($hQuery) Next _SQLite_Close() _SQLite_Shutdown() EndIf While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop WEnd EndFunc ;==>MapShow Func DBFlooder() $zonecount = 0 While 1 $mp = MouseGetPos() GUICtrlSetData($realtimeCoords, $mp[0] & " - " & $mp[1]) $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop If $msg = $bColor Then colorP() If $msg = $btest Then MapShow("Test") If $mp[0] < $width And $mp[1] < $height And _IsPressed("01") And WinActive($gui) Then $mp = MouseGetPos() GUICtrlSetData($lastclickcoords, $mp[0] & " - " & $mp[1]) $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $Buttoncolor) ; fill color ok $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $mp[0], "int", $mp[1], "int", 0x000000) Local $Zone = InputBox("Map Floode", "Zone ?") If $Zone = "" Or @error = 1 Then ; when manage wrong click, possibility to repeat ; set 'temp' color to highlight the 'wrong' click $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", 0x4ccfc6) ; fill color wrong $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $mp[0], "int", $mp[1], "int", 0x000000) ; restore color $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $Buttoncolor) ; fill color ok $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) Else _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database Local $SupZone = GUICtrlRead($inputSup) Local $data = '"' & $Zone & '","' & $SupZone & '","' & $Buttoncolor & '",' & $mp[0] & "," & $mp[1] _SQLite_Exec(-1, "INSERT INTO " & $dbtable & "(" & $dbFields & ") VALUES (" & $data & ");") If @error = -1 Then GOLLOG("Error insert record") MsgBox(48, "Error", "insert record") EndIf $zonecount += 1 GUICtrlSetData($zonecountNum, $zonecount) _SQLite_Close() _SQLite_Shutdown() EndIf EndIf WEnd EndFunc ;==>DBFlooder Func Close() Gollog("<<<<<<< closing...") _WinAPI_ReleaseDC($gui, $HDC) _GDIPlus_GraphicsDispose($hGraphics) _GDIPlus_Shutdown() Exit EndFunc ;==>Close Func SQLiteDBcreate() ;complete path e filename If Not FileExists($dbfullpath) Then GOLLOG("perform SQLite DB creation") Local $dbfolder = _FileToFilePath($dbfullpath) ;Local $dbfile = _FileToFileName($dbfullpath) If Not FileExists($dbfolder) Then DirCreate($dbfolder) ; =====================>>>>> START SQL DLL _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database ; creating first table If _SQLite_Exec(-1, "CREATE TABLE " & $dbtable & " (" & $dbFields & ");") = $SQLITE_OK Then GOLLOG("DB table - " & $dbtable & " - creation ok") Else GOLLOG("Error creating DB table : " & @error) EndIf _SQLite_Close() _SQLite_Shutdown() Else Gollog("DB already exist") EndIf EndFunc ;==>SQLiteDBcreate ; #FUNCTION# ==================================================================================================================== ; Name ..........: _FileToFilePath ; Description ...: Returns a folder path from a FQPN (Fully Qualified Path Name) ; Syntax ........: _FileToFilePath($sPath) ; Parameters ....: $sPath - a string value. ; Return values .: Success - String ; Failure - Empty string as returned from StringLeft() ; Author ........: Sam Coates ; =============================================================================================================================== Func _FileToFilePath($sPath) Local $sReturn = StringLeft($sPath, StringInStr($sPath, "\", 0, -1) - 1) Return ($sReturn) EndFunc ;==>_FileToFilePath ; #FUNCTION# ==================================================================================================================== ; Name ..........: _FileToFileName ; Description ...: Returns a filename from a FQPN (Fully Qualified Path Name) ; Syntax ........: _FileToFileName($sPath[, $bIncludeExtension = True]) ; Parameters ....: $sPath - a string value. ; $bIncludeExtension - [optional] a boolean value. Default is True. ; Return values .: Success - String ; Failure - Empty string as returned from StringLeft() ; Author ........: Sam Coates ; =============================================================================================================================== Func _FileToFileName($sPath, $bIncludeExtension = True) Local $sReturn = StringTrimLeft($sPath, StringInStr($sPath, "\", 0, -1)) If $bIncludeExtension = False Then $sReturn = StringLeft($sReturn, StringInStr($sReturn, ".", 0, -1) - 1) Return ($sReturn) EndFunc ;==>_FileToFileName Func colorP() ; modified for BGR color GOLLOG("Color Picker") Local $color = _ChooseColor(2) If $color = -1 Then GOLLOG("no color selected") Else Local $sCr = Hex($color, 6) Local $RGB_Buttoncolor = '0x' & StringMid($sCr, 1, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 5, 2) GUICtrlSetBkColor($bColor, $RGB_Buttoncolor) ; BGR color $Buttoncolor = '0x' & StringMid($sCr, 5, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 1, 2) GUICtrlSetData($bColor, $Buttoncolor) GOLLOG("new color " & $Buttoncolor & " selected") EndIf EndFunc ;==>colorP Func ctrlini() ;ini read If FileExists($MPini) Then GOLLOG("found: " & $MPini) $mapfile = IniRead($MPini, "map", "mapfile", "") $datafile = IniRead($MPini, "map", "datafile", "") $dbfullpath = IniRead($MPini, "db", "dbfullpath", "") $dbtable = IniRead($MPini, "db", "dbtable", "") $dbFields = IniRead($MPini, "db", "dbfields", "") $FloodMode = IniRead($MPini, "mode", "mode", "") Else GOLLOG($MPini & " NOT found..") Close() EndIf EndFunc ;==>ctrlini #EndRegion funcS All the needed files plus some example (image maps and DBs)
      Link to all demo files
      To test, copy all in a single folder and adjust the mapflooder.ini, also you can add to you includes the _gollog.au3 (used for log, you can avoid it deleting all Gollog() lines)
       
       
       
       
       
×
×
  • Create New...