WinBatch® Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: JTaylor on March 11, 2014, 02:37:12 PM

Title: Create a Function in MySQL via ODBC OT?
Post by: JTaylor on March 11, 2014, 02:37:12 PM
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 $$
Title: Re: Create a Function in MySQL via ODBC OT?
Post by: ....IFICantBYTE on March 11, 2014, 06:12:30 PM
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 (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 (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!)
Title: Re: Create a Function in MySQL via ODBC OT?
Post by: JTaylor on March 11, 2014, 07:11:18 PM
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