[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_id166 and save_id164; 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
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_id166 and save_id164; 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_id166 and save_id164; 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 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
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_id166 and save_id164; 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_id166 and save_id164; 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 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
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
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
