Re: [sqlite] problem with simple select
Aha! It turns out there was not a trailing space, but a trailing null character. The save code had a hard-coded size, something like: sqlite3_bind_text(g_objSaveStmt, 11, saveIdStr.c_str(), 16, SQLITE_STATIC); So when saveIdStr was less than 16 characters long, it was reading past the end of the string data when saving the value. Thanks for the help. > -Original Message- > From: [EMAIL PROTECTED] [mailto:sqlite-users- > [EMAIL PROTECTED] On Behalf Of Noah Hart > Sent: Friday, July 11, 2008 5:34 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problem with simple select > > My guess is that there is a trailing space in the record. > Try the following: > sqlite> select save_id ||'<' from ae_objects where save_id like 165; > 165< > > And see where the "sean" save_id field ends. > > Regards, Noah > > -Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley > Sent: Friday, July 11, 2008 5:04 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problem with simple select > > Thanks for the quick response. My application is using 3.4.1, but I > grabbed the 3.5.9 executable and got the same thing. > > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> select * from ae_objects; > 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 > sqlite> select * from ae_objects where save_id=165; > sqlite> select * from ae_objects where save_id like 165; > 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 > > So I tried what your code from below and it worked for me in a new > database. Strange thing though, if I do the insert from your code into > my existing database, then that new record shows up when I do: > > sqlite> select * from ae_objects where save_id=165; > > But the existing record (the "sean" one) does not! > > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If > you are not the addressee or authorized to receive this for the addressee, > you must not use, copy, disclose, or take any action based on this message > or any information herein. If you have received this message in error, > please advise the sender immediately by reply e-mail and delete this > message. Thank you for your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with simple select
Thanks for the quick response. My application is using 3.4.1, but I grabbed the 3.5.9 executable and got the same thing. SQLite version 3.5.9 Enter ".help" for instructions sqlite> select * from ae_objects; 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 sqlite> select * from ae_objects where save_id=165; sqlite> select * from ae_objects where save_id like 165; 20086|sean|1|0|5.43301269412041|4.43301269412041|0.0|0.0|1|0|165|2 So I tried what your code from below and it worked for me in a new database. Strange thing though, if I do the insert from your code into my existing database, then that new record shows up when I do: sqlite> select * from ae_objects where save_id=165; But the existing record (the "sean" one) does not! > -Original Message- > From: [EMAIL PROTECTED] [mailto:sqlite-users- > [EMAIL PROTECTED] On Behalf Of Noah Hart > Sent: Friday, July 11, 2008 2:24 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problem with simple select > > Sean, what version of sqlite are you using? > > > With the command line version it appears to work under 3.5.9 > > SQLite version 3.5.9 > Enter ".help" for instructions > sqlite> DROP TABLE if exists ae_objects ; > sqlite> CREATE TABLE ae_objects ( >...> oid INTEGER PRIMARY KEY, >...> nameVARCHAR(64), >...> template_id INTEGER, >...> template_module_id INTEGER, >...> pos_x FLOAT, >...> pos_y FLOAT, >...> pos_z FLOAT, >...> facing FLOAT, >...> sprite_id INTEGER, >...> sprite_module_idINTEGER, >...> save_id VARCHAR(16), >...> save_type INTEGER ); > sqlite> > sqlite> insert into ae_objects values > (40007,1000,1,0,3.43301269412041,12.4330126941204,0.0,0.0,11,0,165,2); > sqlite> select * from ae_objects; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id = 165; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id = '165'; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id like '165'; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> > > > > Regards, Noah > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Sean Riley > Sent: Friday, July 11, 2008 2:08 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] problem with simple select > > Hello, > > I have a strange issue with a seemingly simple query. > > The table schema: > > CREATE TABLE ae_objects ( > oid INTEGER PRIMARY KEY, > nameVARCHAR(64), > template_id INTEGER, > template_module_id INTEGER, > pos_x FLOAT, > pos_y FLOAT, > pos_z FLOAT, > facing FLOAT, > sprite_id INTEGER, > sprite_module_idINTEGER, > save_id VARCHAR(16), > save_type INTEGER ); > > Sequence of SQL statements executed: > sqlite> select * from ae_objects; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> select * from ae_objects where save_id = 165; > sqlite> select * from ae_objects where save_id = '165'; > sqlite> select * from ae_objects where save_id like '165'; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; > 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 > > Why does the "save_id = 165" fail to get any rows? I get zero rows when > I attempt to find the row by exact match of the "save_id" column, but > with a "like" or a "greater than 164 and less than 166" I get the one > row I am looking for. > > This only seems happens when that particular column is varchar(16). I > have gotten this behavior on windows and linux. Can provide a db file if > that helps. > > > --- > Sean Riley > Lead Programmer, Areae Inc. > "All problems in computer science can be solved by another level of > indirection", Butler Lampson, 1972 > > ___
[sqlite] problem with simple select
Hello, I have a strange issue with a seemingly simple query. The table schema: CREATE TABLE ae_objects ( oid INTEGER PRIMARY KEY, nameVARCHAR(64), template_id INTEGER, template_module_id INTEGER, pos_x FLOAT, pos_y FLOAT, pos_z FLOAT, facing FLOAT, sprite_id INTEGER, sprite_module_idINTEGER, save_id VARCHAR(16), save_type INTEGER ); Sequence of SQL statements executed: sqlite> select * from ae_objects; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> select * from ae_objects where save_id = 165; sqlite> select * from ae_objects where save_id = '165'; sqlite> select * from ae_objects where save_id like '165'; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 sqlite> SELECT * FROM ae_objects WHERE save_id<166 and save_id>164; 40007|1000|1|0|3.43301269412041|12.4330126941204|0.0|0.0|11|0|165|2 Why does the "save_id = 165" fail to get any rows? I get zero rows when I attempt to find the row by exact match of the "save_id" column, but with a "like" or a "greater than 164 and less than 166" I get the one row I am looking for. This only seems happens when that particular column is varchar(16). I have gotten this behavior on windows and linux. Can provide a db file if that helps. --- Sean Riley Lead Programmer, Areae Inc. "All problems in computer science can be solved by another level of indirection", Butler Lampson, 1972 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
