Jump to content
Sign in to follow this  
testmeout

Sqlite Help WHERE <= Not Working

Recommended Posts

testmeout

So I have tried everything and searched through the forum without luck, so I am needing some help.

I have 2 seperate scripts, here is the sqlite portion of the first one which is creating the database.

Local $sDatabaseFile = @ScriptDir & "\Test.Db", $sOutputFile
    _SQLite_Startup()
    _SQLite_Open($sDatabaseFile)
_SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS "&$symbol&"_"&$date&"_"&$ExpMon&" (CDelta NUMERIC,CGamma NUMERIC,CTheta NUMERIC,CVega NUMERIC,CIV NUMERIC,CTheoPrice NUMERIC,COI NUMERIC,CVol NUMERIC,CMark NUMERIC,CBid NUMERIC,CAsk NUMERIC,Exp TEXT,Strike NUMERIC,PBid NUMERIC,PAsk NUMERIC,PDelta NUMERIC,PGamma NUMERIC,PTheta NUMERIC,PVega NUMERIC,PIV NUMERIC,PTheoPrice NUMERIC,POI NUMERIC,PVol NUMERIC,PMark NUMERIC);")
    _SQLite_Exec(-1, "Begin")
_SQLite_Exec(-1, "INSERT INTO "&$symbol&"_"&$date&"_"&$ExpMon&" values ('" &$arraystring& "');")
_SQLite_Exec(-1, "END ;")

In my second script accessing the database

$sSQL = "SELECT CDelta, CIV, CMark, COI FROM "&$SQLSym&"_"&$SQLDate&"_"&$ExpMon&" WHERE Strike='"&$StrikePrice&"';"
ConsoleWrite ( "$sSQL: "&$sSQL)
$get = _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns)
_SQLite_Display2DResult($aResult)
$CallDeltaCond = .11
$sSQL = "SELECT CDelta, CIV, CMark, COI FROM "&$SQLSym&"_"&$SQLDate&"_"&$ExpMon&" WHERE CDelta <= '"&$CallDeltaCond&"';"
ConsoleWrite ( "$sSQL: "&$sSQL)
$get = _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns)
_SQLite_Display2DResult($aResult)

I am fairly new to sqlite, but have tried everything and i am stuck. My first select function works using strike column in where statement but my second select does not work if I use <= or = I have tried with and without '', but intresting thing is when I use sqlite manager in firefox the sql statement works just fine

SELECT CDelta, CIV, CMark, COI, STRIKE FROM SPX_2009_01_30_MAR09 WHERE CDelta <= '0.11';

CDelta CIV CMark COI

".11","32.48","4.8","2700","955"

Any help would be greatly appreciated, I am sure it is something simple.

Share this post


Link to post
Share on other sites
Clark

What is the error you are getting? It should output one into the console window telling you where abouts in the SQL statement it is borking.

Share this post


Link to post
Share on other sites
jchd

You shouldn't be using single quotes around your floating-point values.

Try getting rid of them in both your insert and select statements.


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
testmeout

Thanks for the responses.

I have tried both with and without single quotes, both seem to work in sqlite manager but neither work with autoit.

clark,

It doesn't exactly produce an error that I know of, it only shows my column names that I selected with _ArrayDisplay but no data.

So technically it is working but not finding data which meets the where criteria.

Share this post


Link to post
Share on other sites
testmeout

Update - so yall got me thinking and I played with it some more and got it working sort of, now I just need to figure out a work around.

It is working just fine as long as the number is greater than 1 if it is less then one it doesn't work so

WHERE >= 1 works but if I use WHERE <= 1 it doesn't work which in my case I was looking for <= .11

My original database has numbers formated like the following

.99

.98

.97

etc

When I go back and edit the database and make it

0.99

0.98

0.97

etc

It works like a champ.

Any way to fix this without completely redoing my database?

Share this post


Link to post
Share on other sites
jchd

Please reread my post. I strongly suspect you're inserting data as text (strings) not as numeric.

Can you please run the following statement and inspect the result:

select typeof(cdelta) where typeof(cdelta) not like 'real';

Edit: it would be very easy to force conversion should you need so. I stay tuned.

Edited by jchd

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
Sign in to follow this  

×