fly

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

12 posts in this topic

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?

Share this post


Link to post
Share on other sites



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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by wolf9228

صرح السماء كان هنا

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by Skysnake

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

#12 ·  Posted

This one is 4-year old!


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)

Share this post


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