Thanks for your reply Andrew, No, the column is defined as "integer primary key". Regards, -shashikant
-----Original Message----- From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] Sent: Monday, July 23, 2007 7:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] how can i bound signed integer (negative) to an identifier in select query 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 > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
