On 2008 October 31 (Fri) 10:57:34pm PDT, yoky <[EMAIL PROTECTED]> wrote: >> Hi all, >> I create a table like this "create table tbl1 (ID integer primary >> key, name, addr)" , >> then insert a record: >> "insert into tbl1 values(1, 'aa', 'bb')", >> select the record: >> "select * from tbl1 where name ='aa' " ,can get this record : >> (1, 'aa', 'bb') >> I change insert way like this: >> char *pName = "aa"; >> sqlite3_prepare(db," insert into tbl1(1,?,'bb')",-1, &stat, 0); >> sqlite3_bind_blob( stat, 1, pName, strlen(pName)+1, 0 ); >> sqlite3_step(stat); >> Then select the record: >> sqlite3_prepare(db, "select * from tbl1 where name ='aa' ", -1, >> &stat, 0); >> sqlite3_step(stat); >> By this way, I can not get the record I want : (1, 'aa', 'bb'). >> Change the SQL statement by "like": >> sqlite3_prepare(db, "select * from tbl1 where name like 'aa' >> ", -1, &stat, 0); sqlite3_step(stat); >> I can get the record. >> Why? and How can I select record by "=" condition to a string? > > >>> You are binding a blob and specifying a length of *three*. strlen(pName) >>> returns 2 which is the proper length, but you're adding 1 to that. >>> Therefore the column contains three characters, 'a', 'a', '\0' which does >>> not equal "aa" but does begin with "aa". > >>> So you want to either use the correct length for the blob (assuming you >>> really need blobs) or bind a type other than blob. > Derrell > ------------------------------ > Thank you! > I have try to bind like this: > sqlite3_bind_blob( stat, 1, pName, strlen(pName), 0 ); > binding a blob and specifying a length of *two* and still can not select > the record I want by " select * from tbl1 where name ='aa' ", > I think maybe I should use: sqlite3_bind_text( ), > because this column type is a string. I will try last week! > > yoky
Yes, you should use sqlite3_bind_text(). If you want to compare it to a string, you need to insert it as a string. TEXT values don't compare equal to BLOB values. TEXT values always compare as less than BLOB values. See <http://sqlite.org/datatype3.html>. sqlite> select cast ('aa' as blob) = 'aa'; 0 sqlite> select cast ('aa' as blob) > 'aa'; 1 sqlite> create table tbl (id, col); sqlite> insert into tbl (id, col) values (1, 'aa'); sqlite> insert into tbl (id, col) values (2, cast ('aa' as blob)); sqlite> select * from tbl; 1|aa 2|aa sqlite> select * from tbl where col = 'aa'; 1|aa sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
