SQLite data extraction

[Illuma]

Hi

I am playing with the SQLite database functions in Autoit, I have worked out 99% of what I would like to do but I am stuck on how to extract data from the database.

I am recording when data is inserted into the database by having a date column / field next to this date field I am also recording a couple of numeric values in their own fields.

Where I am totally stuck is what way I need to go about extracting certain data between two given dates, I have attached an example script I am playing with.

Any help pointing me in the correct direction would be greatly appreciated.

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
_SQLite_Open()
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-01-01','123','10');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-02-01','111','20');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-03-05','158','30');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-05-01','188','30');") ; INSERT Data

;Here I need to extract the sum of column c ONLY between these two dates 2013-12-30 and 2013-01-05, hence the first line is dropped as it is out of range
;and the total returned for the sum of the selected data should be 80



_SQLite_Close()
_SQLite_Shutdown()

[jchd]

Try this and read my comments:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
_SQLite_Open()  ; this creates a (temporary) memory database
; please declare datatypes to avoid shooting yourself in the foot later
_SQLite_Exec(-1, "CREATE TABLE aTest (a text, b int, c int);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-01-01',123,10);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-02-01',111,20);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-03-05',158,30);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-05-01',188,30);") ; INSERT Data

;Here I need to extract the sum of column c ONLY between these two dates 2013-12-30 and 2013-01-05, hence the first line is dropped as it is out of range
;and the total returned for the sum of the selected data should be 80

Local $irows, $icols, $arows

_SQLite_GetTable2d(-1, "select sum(c) as Result from aTest where a between '2013-01-05' and '2013-12-30';", $arows, $icols, $icols)
_ArrayDisplay($arows)

_SQLite_Close()
_SQLite_Shutdown()

Edited December 30, 2013 by jchd

[Illuma]

Thanks very much jchd, I thought using the 2D array was the way to go but I had no idea I could create the complete formula and only extract the data required. I was looking at getting all the data as an array then taking only what I needed from that data.

In my actual program I have declared my data types, this was just one of my test programs I was playing with to see if I could work it out myself. 

your example works perfectly I can now fiddle with it and try it in my main program, once again thanks for the help.

 

 

Try this and read my comments:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
_SQLite_Open()  ; this creates a (temporary) memory database
; please declare datatypes to avoid shooting yourself in the foot later
_SQLite_Exec(-1, "CREATE TABLE aTest (a text, b int, c int);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-01-01',123,10);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-02-01',111,20);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-03-05',158,30);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-05-01',188,30);") ; INSERT Data

;Here I need to extract the sum of column c ONLY between these two dates 2013-12-30 and 2013-01-05, hence the first line is dropped as it is out of range
;and the total returned for the sum of the selected data should be 80

Local $irows, $icols, $arows

_SQLite_GetTable2d(-1, "select sum(c) as Result from aTest where a between '2013-01-05' and '2013-12-30';", $arows, $icols, $icols)
_ArrayDisplay($arows)

_SQLite_Close()
_SQLite_Shutdown()