1

Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course :-(

Ref: https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_geomfromtext

Now I'd like to use this function in a way that is backwards compatible so it can run on very old MySQL server versions as much as possible. What is the best way to check if this function is defined and which of the two (if any) I can use? I'd like to prevent having to check the version number of the server to select the correct query, if at all possible.

1 Answer 1

1

You can use the IF() function, VERSION() function and Dynamic SQL:

SET @dynamic_func_name = CONCAT(IF(LEFT(VERSION(),3)>'5.6','ST_',''),'GeomFromText');
SET @sql = CONCAT('CALL ',@dynamic_func_name,'(wkt,srid)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

I used the left 3 character of the VERSION() to get the major release. Why ?

  • If LEFT(VERSION(),3) returns 5.7 or more, @dynamic_func_name will be ST_GeomFromText
  • If LEFT(VERSION(),3) returns 5.6 or less, @dynamic_func_name will be GeomFromText
3
  • Thanks, but as I said, I was looking for a way to do this without checking version numbers. That feels like 'user agent sniffing', which is frowned upon in the web dev world, for good reason.
    – MichielB
    Feb 14, 2018 at 7:46
  • @MichielB that's only because in the web world you're accommodating more than one client for your code. In this case, you're only accommodating MySQL. It's not like Trident is declaring itself "Mozilla." Aug 29, 2018 at 8:18
  • Well, with MariaDB on the scene, there are actually more clients for the code!
    – MichielB
    Aug 29, 2018 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.