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.


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)');

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
  • 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 '18 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." – Evan Carroll Aug 29 '18 at 8:18
  • Well, with MariaDB on the scene, there are actually more clients for the code! – MichielB Aug 29 '18 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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