I think this is a SQL query issue, but...

[fly]

So, I'm trying to use SQLite and I'm running into some issues...

Full disclosure: I'm a total SQL greenhorn

I have a SQL table. Once column is logging the time data was written to the table (in Ticks). I'm then trying to run a query that counts all rows written in the last 60 seconds. That gives me this:

$sTime = _TimeToTicks(@HOUR, @MIN, @SEC) - 60000
_SQlite_Query(-1, "SELECT count(Time) FROM Info WHERE Time < '" & $sTime & "';", $hQuery)
_SQLite_FetchData ($hQuery, $aOut)
ConsoleWrite("Less than 60s = " & $aOut[0]& @CRLF)

My ConsoleWrite output is constantly ZERO. What am I doing wrong?

[water]

Add some debugging code into your script:

$sTime = _TimeToTicks(@HOUR, @MIN, @SEC) - 60000
_SQlite_Query(-1, "SELECT count(Time) FROM Info WHERE Time < '" & $sTime & "';", $hQuery)
ConsoleWrite("Query: " & @error & @CRLF)
_SQLite_FetchData ($hQuery, $aOut)
ConsoleWrite("Fetch: " & @error & @CRLF)
ConsoleWrite("Less than 60s = " & $aOut[0]& @CRLF)

[Xenobiologist]

Try using Squirrel or Toad or DataStudio to firstly get your statement working outside from Autoit.

[fly]

Add some debugging code into your script:

$sTime = _TimeToTicks(@HOUR, @MIN, @SEC) - 60000
_SQlite_Query(-1, "SELECT count(Time) FROM Info WHERE Time < '" & $sTime & "';", $hQuery)
ConsoleWrite("Query: " & @error & @CRLF)
_SQLite_FetchData ($hQuery, $aOut)
ConsoleWrite("Fetch: " & @error & @CRLF)
ConsoleWrite("Less than 60s = " & $aOut[0]& @CRLF)

Here is what I get:

Query: 0
Fetch: 0
Less than 60s = 0

Try using Squirrel or Toad or DataStudio to firstly get your statement working outside from Autoit.

Are any of these free and easy to setup? I'm really a terrible SQL guy.

I guess the good news is that neither of you found something obviously wrong with my statement.

[saywell]

I use SQLite manager add-on for FireFox - free. You can look at the data to eyeball the expected results, and enter SQL code to see the results of the query.

William

[fly]

I use SQLite manager add-on for FireFox - free. You can look at the data to eyeball the expected results, and enter SQL code to see the results of the query.

William

This is probably a really dumb question, but is my AutoIt database stored on the system somewhere as a file? If so, where is it so I point this Firefox addon to it?

[saywell]

Maybe, maybe not!

SQLite can create in-memory databases, or files. If you use a file it might be easier to experiment/learn with.

The FF extension will create a file [ .SQlite ] for you.

William

[fly]

Maybe, maybe not!

SQLite can create in-memory databases, or files. If you use a file it might be easier to experiment/learn with.

The FF extension will create a file [ .SQlite ] for you.

William

Ah! I see that I can specify a file with _SQLite_Open(). Okay, thanks I'll try this...

[fly]

I figured it out, sorta. The only thing worse than my SQL is my logic.

The SQL query needs to see if Time is in between the current time (in ticks) and the current time - 60000 ticks. Now to figure out how to do that in the WHERE part of the SQL query...

[wolf9228]

So, I'm trying to use SQLite and I'm running into some issues...

Full disclosure: I'm a total SQL greenhorn

I have a SQL table. Once column is logging the time data was written to the table (in Ticks). I'm then trying to run a query that counts all rows written in the last 60 seconds. That gives me this:

$sTime = _TimeToTicks(@HOUR, @MIN, @SEC) - 60000
_SQlite_Query(-1, "SELECT count(Time) FROM Info WHERE Time < '" & $sTime & "';", $hQuery)
_SQLite_FetchData ($hQuery, $aOut)
ConsoleWrite("Less than 60s = " & $aOut[0]& @CRLF)

My ConsoleWrite output is constantly ZERO. What am I doing wrong?

#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
FileDelete("Database.db")
Dim $hQuery , $aOut , $count = 0
_SQLite_Startup ()
$DbHandle = _SQLite_Open("Database.db")
_SQLite_Exec(-1,"CREATE TABLE Info (Time,count);")
$count += 1
$sTime = _TimeToTicks(@HOUR, @MIN, @SEC)
_SQLite_Exec (-1, "INSERT INTO Info(Time,count) VALUES ('" & $sTime & "' , '" & $count & "');")
Sleep(1000)
$count += 1
$sTime = _TimeToTicks(@HOUR, @MIN, @SEC)
_SQLite_Exec (-1, "INSERT INTO Info(Time,count) VALUES ('" & $sTime & "' , '" & $count & "');")
Sleep(1000)
$count += 1
$sTime = _TimeToTicks(@HOUR, @MIN, @SEC)
_SQLite_Exec (-1, "INSERT INTO Info(Time,count) VALUES ('" & $sTime & "' , '" & $count & "');")
Sleep(1000)
$count += 1
$sTime = _TimeToTicks(@HOUR, @MIN, @SEC)
_SQLite_Exec (-1, "INSERT INTO Info(Time,count) VALUES ('" & $sTime & "' , '" & $count & "');")
Sleep(1000)
$count += 1
$sTime = _TimeToTicks(@HOUR, @MIN, @SEC)
_SQLite_Exec (-1, "INSERT INTO Info(Time,count) VALUES ('" & $sTime & "' , '" & $count & "');")

_SQlite_Query(-1, "SELECT Time FROM Info WHERE Time > '" & $sTime -  60000 & "';", $hQuery)


While 1
_SQLite_FetchData ($hQuery, $aOut)
if @error Then ExitLoop
ConsoleWrite("Less than 60s = " & $aOut[0]& @CRLF)
WEnd

Edited February 23, 2011 by wolf9228

[Skysnake]

https://www.sqlite.org/lang_aggfunc.html

SQLite query works. I keep on getting a [0]0  array in Autoit.

Expecting [0]2 on my data...

Edited May 7, 2015 by Skysnake

[jchd]

This one is 4-year old!