Create a Function in MySQL via ODBC OT?

Started by JTaylor, March 11, 2014, 02:37:12 PM

Previous topic - Next topic

JTaylor

Needing to create a function in MySQL via ODBC.   Since MySql doesn't have an built-in way to determine if a value is a number and since if one does math on something like '3bears' and add it to the number '20' you will get 23, I need to create the following to avoid such problems.   The problem is that it generates an error saying the SQL is bad.  I'm fairly sure the issue is the DELIMITER statement but if I don't use it then it fails because it doesn't see it as a complete script.   Anyone run into this before?  Can't find any results from Googling as it likes the words involved in too many other contexts.  Suggestions?  Thanks.

Jim

     DELIMITER $$
     CREATE FUNCTION IFNUMERIC(myVal VARCHAR(1024), myVal_Sub VarChar(1024)) RETURNS VARCHAR(1024)
      BEGIN
      if myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$' = 0 Then Set myVal = myVal_Sub;
      end if;
      RETURN myVal;
      END $$

....IFICantBYTE

I know virtually nothing about MySQL or even SQL for that matter, but I found this documentation on line that looks like it's saying that to add a new Function, you need to modify a shared library file that is in the PlugIn directory.
http://dev.mysql.com/doc/refman/5.0/en/create-function-udf.html

Maybe you can create a Procedure instead?
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Like I said, I don't really know what I'm talking about, so forgive me if I'm just feeding you rubbish or stuff you already know.
(It's a bit crappy that MySQL doesn't have an IsNumeric type function in the first place!)
Regards,
....IFICantBYTE

Nothing sucks more than that moment during an argument when you realize you're wrong. :)

JTaylor

Appreciate the reply.  It prompted an idea but it didn't work  :(    I need a function rather than a procedure as it allows me to do something like the following, and it works if I'm not using ODBC.  Everything is great using Workbench (MySQL admin tool).    For what I'm doing I don't need the "plug-in" approach.  The Create Function statement works.   Just not via ODBC for some reason.

select item,  IfNumeric(IfNull(cost,0),0) as cost from widgets


Again, thanks.

Jim