On 5/19/08, Skip Evans <[EMAIL PROTECTED]> wrote:
> Even this statement
>
> SELECT * FROM bsp_options WHERE modelID=351 and
> startyear=1990
>
> Is returning no rows and I can clearly see dozens
> that meet the criteria!
If you are not getting rows returned, how can you clearly see dozens
of rows that meet your criteria? What you are "seeing," from what I
can tell from your earlier emails, is what is being shown in whatever
program you are using to "look" at SQLite data. It could be that this
program that you are using is showing you something other than what is
really in the db.
Since, in another email you mention that you don't have access to the
command line tool because you are accessing the database on someone
else's server, is it possible for you to download that database on to
your own computer where you can access it from the command line shell?
>
> Here is the table def. I've recreated and
> repopulated them using int(11) for the year fields:
>
>
> CREATE TABLE bsp_options (
> optionsID INTEGER NOT NULL PRIMARY KEY,
> modelID INT(11) NOT NULL,
>
> startyear INT(11) NOT NULL,
> endyear INT(11) NOT NULL,
>
> options TEXT NOT NULL,
> productcodesize VARCHAR(10),
> productdesc VARCHAR(200),
> pattern VARCHAR(10) );
>
>
> AAAaaarrrgghhh!!! I'm eating my head from the
> inside out!!!!
nah. Don't do that. See below... you can insert pretty much anything
into any column. SQLite is reasonably smart about treating numbers and
strings accordingly. But you have something else going on, and you can
get much better diagnosis by looking at the data directly from the
command line rather than some third party tool.
sqlite> CREATE TABLE bsp_options (
...> optionsID INTEGER NOT NULL PRIMARY KEY,
...> modelID INT(11) NOT NULL,
...> startyear INT(11) NOT NULL,
...> endyear INT(11) NOT NULL,
...> options TEXT NOT NULL,
...> productcodesize VARCHAR(10),
...> productdesc VARCHAR(200),
...> pattern VARCHAR(10) );
sqlite> .s
CREATE TABLE bsp_options (
optionsID INTEGER NOT NULL PRIMARY KEY,
modelID INT(11) NOT NULL,
startyear INT(11) NOT NULL,
endyear INT(11) NOT NULL,
options TEXT NOT NULL,
productcodesize VARCHAR(10),
productdesc VARCHAR(200),
pattern VARCHAR(10) );
sqlite> insert into bsp_options (modelID, startyear, endyear, options,
productdesc) values ('', 'nineteen twenty eight', '2000', '', 23);
sqlite> select * from bsp_options;
1||nineteen twenty eight|2000|||23|
sqlite> select * from bsp_options where endyear > 1;
1||nineteen twenty eight|2000|||23|
sqlite> select typeof(startyear), typeof(endyear) from bsp_options
where optionsid = 1;
text|integer
sqlite>
>
> Skip--
>
> Skip Evans
> Big Sky Penguin, LLC
> 503 S Baldwin St, #1
> Madison, WI 53703
> 608-250-2720
> http://bigskypenguin.com
> =-=-=-=-=-=-=-=-=-=
> Check out PHPenguin, a lightweight and versatile
> PHP/MySQL, AJAX & DHTML development framework.
>
> http://phpenguin.bigskypenguin.com/
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
