Is it possible that the data type of the column in the CREATE TABLE
statement is not "INTEGER" but some other string?
This web page: <http://www.sqlite.org/datatype3.html> describes the data
type affinities applied for the various values of the data type in the
create table statement, and if the type affinity for your table mentioned
below were TEXT instead of INTEGER, that would explain what you are seeing,
as Sqlite would be doing an integer-to-text conversion prior to the where
clause execution.
On 7/23/07, Shashikant Shukla <[EMAIL PROTECTED]> wrote:
Hi,
I have a table with one of the columns storing negative integers in it.
There is one function that performs different query operations on this
table based on input range for those negative integers. When I try to
use a "select where" query with an inequality using negative number
semantics, the records fetched are wrong (the negative integers in the
where clause are bound using sqlite3_bind_int). However, if I ignore the
fact that the comparators in where clause are negative, I get proper
result.
Here is how it happens,
Assume that you have three records in table, with values -3, -4 and -5
in that negative-integer column (say column name is "num").
Now if you want to search all records between say -1 and -4 and say if
-1 and -4 were stored in integer identifiers limit1 and limit2; your
query will be:
char* pQuery= "select * from my_table where num <= :limit1 and num >=
:limit2;"
-----prepare statement here-
sqlite3_prepare(pDb, pQuery, -1, &pStmt, 0))-------
sqlite3_bind_int(pStmt, 1 , -1);
sqlite3_bind_int(pStmt, 2 , -4);
Now executing this sqlite statement should give you records with -3 and
-4, but it won't (value -1 and -4 are bound using sqlite3_bind_int
function).
However, if you try
"select * from my_table where num > :limit1 and number < :limit2;"
It will give you correct results (we are still binding the limit1 and
limit2 using sqlite3_bind_int function).
Why during bind call (sqlite3_bind_int), the negative number is not
bound to identifier instead it seems positive number got bound?
When I run the same queries from command line everything seems to work
fine and negative integers do need comparison semantics of negative
numbers.
Thanks,
Regards,
-shashikant
