Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts

    transliteration operator woes.

    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
    Code:
    Invalid range "t->" in transliteration operator at
    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,915
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Please post the related Perl code when you get this error.

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    Code:
     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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,915
    Thanks
    2
    Thanked 164 Times in 159 Posts
    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:
    Code:
    where address_type = 'actual';
    The semi-colon should be a double quote.

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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.

    Code:
     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
    Code:
    DBD::mysql::st execute failed: Table 'gpsGlb' already exists at line 157.
    Last edited by bazz; 01-11-2010 at 07:16 PM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,915
    Thanks
    2
    Thanked 164 Times in 159 Posts
    I've never done a view either. You will need to ask about that from one of the mysql experts.

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    OK, thanks.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •