...

View Full Version : transliteration operator woes.



bazz
01-11-2010, 04:35 PM
Hi,

I am running a mysql query and I get the following error.

what does it mean and can nayone point me to a tutorial about it. I have googled but I don't see any mysql related answers (just oracle) and the perl one means nothing to me. http://perldoc.perl.org/perl581delta.html (see under hash randomisation).

here's the error message


Invalid range "t->" in transliteration operator at


bazz

FishMonger
01-11-2010, 04:41 PM
Please post the related Perl code when you get this error.

bazz
01-11-2010, 05:28 PM
Thanks Fishmonger,

It is erroring at a line in the mysql query yet it is not giving a my sql error. Instead, it is giving a compilation error.

I have highlighted in red, the line on which it says it is erroring.

FYI, it is a section of code which I got from the following link and ~whether I use this code or not ~ I need to be bale to find locations/records where they are within a set distance of the base.
http://snippets dot dzone dot com/posts/show/4991


my $sth = $dbhconnect->prepare("CREATE VIEW gpsGlb AS
SELECT
id
,6378 * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude)) AS x
,6378 * COS(RADIANS(Latitude)) * SIN(RADIANS(Longitude)) AS y
,6378 * SIN(RADIANS(Latitude)) AS z
FROM address
where address_type = 'actual';
);

my $sth2 = $dbhconnect->prepare("SELECT SQL_CALC_FOUND_ROWS

, qq2.id
, dist_mi
, CASE initBearingBoxed_deg
WHEN 22.5 THEN 'NNE' WHEN 45 THEN 'NE'
WHEN 67.5 THEN 'ENE' WHEN 90 THEN 'E'
WHEN 112.5 THEN 'ESE' WHEN 135 THEN 'SE'
WHEN 157.5 THEN 'SSE' WHEN 180 THEN 'S'
WHEN 202.5 THEN 'SSW' WHEN 225 THEN 'SW'
WHEN 247.5 THEN 'WSW' WHEN 270 THEN 'W'
WHEN 292.5 THEN 'WNW' WHEN 315 THEN 'NW'
WHEN 337.5 THEN 'NNW' ELSE 'N'
END AS bearing
FROM (
SELECT
id
,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi
FROM
(SELECT
SQRT(dx * dx + dy * dy + dz * dz) AS d
,id
FROM
(SELECT
p1.x - p2.x AS dx
,p1.y - p2.y AS dy
,p1.z - p2.z AS dz
,p2.id
FROM gpsGlb p1
JOIN gpsGlb p2 ON (p1.id = 405 AND p2.id != 405)
) t1
) t2
) qq1
JOIN (
SELECT
id
,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg
,(360 + ROUND((DEGREES(ATAN2(y, x))) / 22.5) * 22.5) % 360
AS initBearingBoxed_deg
FROM
(SELECT
SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude))
AS y
,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))
- SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))
* COS(RADIANS(s2.Longitude - s1.Longitude))
AS x
,s2.id
FROM address s1
JOIN address s2 ON (s1.id = 153 AND s2.id != 153)
) q1
) qq2 ON (qq1.id = qq2.id
AND qq1.dist_mi <= 60)
ORDER BY dist_mi
") or die "prepare statement failed: $DBI::errstr\n";

my $found_rows = $dbhconnect->prepare("select
FOUND_ROWS()
") or die "prepare statement failed:$DBI::errstr\n";

#my $total_entries;
my @results_returned;

#$sth->execute($business_type,$business_sub_type);
$sth->execute;
$sth2->execute;



I would welcome any advice tips or pointers on achieving the end result.

bazz

FishMonger
01-11-2010, 05:45 PM
When I run that under perl's -c flag, I get:

Bareword found where operator expected at C:\test\bazz.pl line 20, near "my $sth2 = $dbhconnect->prepare("SELECT"
(Might be a runaway multi-line "" string starting on line 20

A closer look reveals a problem with this line:

where address_type = 'actual';
The semi-colon should be a double quote.

bazz
01-11-2010, 06:09 PM
Oops, sorry. schoolboy error. :(

However, having changed that and having made a couple of other changes (permissions etc), I now get a different error and wonder if I have scripted the VIEW query and the main query correctly. should they be executed separately? I haven't made a view before.

here's the code followed by the error.



my $sth = $dbhconnect->prepare("CREATE VIEW gpsGlb AS
SELECT
id
,6378 * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude)) AS x
,6378 * COS(RADIANS(Latitude)) * SIN(RADIANS(Longitude)) AS y
,6378 * SIN(RADIANS(Latitude)) AS z
FROM address
where address_type = 'actual'
") or die "prepare statement failed: $DBI::errstr\n";

my $sth2 = $dbhconnect->prepare("SELECT SQL_CALC_FOUND_ROWS

qq2.id
, dist_mi
, CASE initBearingBoxed_deg
WHEN 22.5 THEN 'NNE' WHEN 45 THEN 'NE'
WHEN 67.5 THEN 'ENE' WHEN 90 THEN 'E'
WHEN 112.5 THEN 'ESE' WHEN 135 THEN 'SE'
WHEN 157.5 THEN 'SSE' WHEN 180 THEN 'S'
WHEN 202.5 THEN 'SSW' WHEN 225 THEN 'SW'
WHEN 247.5 THEN 'WSW' WHEN 270 THEN 'W'
WHEN 292.5 THEN 'WNW' WHEN 315 THEN 'NW'
WHEN 337.5 THEN 'NNW' ELSE 'N'
END AS bearing
FROM (
SELECT
id
,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi
FROM
(SELECT
SQRT(dx * dx + dy * dy + dz * dz) AS d
,id
FROM
(SELECT
p1.x - p2.x AS dx
,p1.y - p2.y AS dy
,p1.z - p2.z AS dz
,p2.id
FROM gpsGlb p1
JOIN gpsGlb p2 ON (p1.id = 153 AND p2.id != 153)
) t1
) t2
) qq1
JOIN (
SELECT
id
,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg
,(360 + ROUND((DEGREES(ATAN2(y, x))) / 22.5) * 22.5) % 360
AS initBearingBoxed_deg
FROM
(SELECT
SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude))
AS y
,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))
- SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))
* COS(RADIANS(s2.Longitude - s1.Longitude))
AS x
,s2.id
FROM address s1
JOIN address s2 ON (s1.id = 153 AND s2.id != 153)
) q1
) qq2 ON (qq1.id = qq2.id
AND qq1.dist_mi <= 60)
ORDER BY dist_mi
") or die "prepare statement failed: $DBI::errstr\n";





error


DBD::mysql::st execute failed: Table 'gpsGlb' already exists at line 157.

FishMonger
01-11-2010, 09:33 PM
I've never done a view either. You will need to ask about that from one of the mysql experts.

bazz
01-11-2010, 10:18 PM
OK, thanks.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum