| Subject: | Query failure with ?'s |
The db we're using isn't very big, nothing proprietary, so it's here: https://www.dropbox.com/s/3y1jd9x8x1ho6bp/stars.sqlt?dl=0
The query I'm using is a one-liner. Essentially, looking for rows where the x/y values are within a radius from a given center point:
perl5.18.2 -MData::Dumper -MDBI -E '$dbh=DBI->connect("dbi:SQLite:dbname=".shift); $x=110; $y=154; $distance=100; $sth=$dbh->prepare("select *,(x-110)*(x-110)+(y-154)*(y-154) as distance from stars where distance <= ? and owned_by is null and zone not in ('"'-1|-1', '-1|1', '1|-1', '1|1', '-3|0'"')"); $sth->execute($distance**2); while(my $s = $sth->fetchrow_hashref){ die Dumper $s if $s->{distance} >= 10000 }' ~/Dropbox/stars.sqlt
This returns back every row. To keep the output minimal, it dies on the first entry where the distance is too high. (Technically, distance == 10000 should be okay, but given the db and the center point, no row has exactly 10,000 distance.)
If we change "distance <= ?" to "distance <= 10000" and remove the parameter from execute, then it "works" (doesn't die, doesn't print anything out). However, then we have to prepare the statement every time we change the distance, ensure that it remains valid SQL, etc.