Sqlite Help WHERE <= Not Working

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

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

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

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

[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?

[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 February 22, 2012 by jchd