User defined SQL function in Query box fails.

[hiits100rav]

Hi,

I have created a function in MySQL which I want to call in my query box in data source tab. However it seems to be not working and fails. The same query works when run on sql prompt. I am an newbie to TI processes. Can anyone share their experience on this?

As a simple example:
SELECT 'a.txt' as fileid, getFileStatus('a.txt') file_status;

Many Thanks,
Saurabh

[Wim Gielis]

Not sure if a stored procedure can be set up and called from TI ?

[BrianL]

However it seems to be not working and fails.

How is it failing? Is there an error message?

[paulsimon]

Hi

I can confirm that stored procedures as data sources work fine with SQL Server. User Defined Functions are a little different in that in SQL they can't be a data source in their own right. However, used in the Select clause they work on SQL Server. I have never used MySQL so I can't comment on that but I don't see any reason why it should be any different.

Please post the actual SQL The example that you posted doesn't have From clause, which is needed, and you will probably find that you don't need the closing semi colon in an ODBC query

Regards

Paul Simon

[hiits100rav]

Hi Guys,

Apologies for the delay in response. The error that I am receiving is "SQL statement failed" while performing preview.

Paul SImon - The query is actual, that said, mysql doesn't force to use 'from' clause. ("Select 1" works in mysql).

Interesting that SQL server stored procedure does work for TI and not working for MySQL (atleast for me). Here is the function I defined in mysql and test query:
I am able to define the function in run the query correctly in SQL workbench.
Query:
select getDraftStatus('abc.csv')

Function:
DELIMITER $$
DROP FUNCTION getDraftStatus $$
CREATE FUNCTION getDraftStatus (p_file_name Varchar(50))
RETURNS int(4)
DETERMINISTIC
BEGIN
DECLARE file_status int(4);

SET file_status = -1;

SELECT status into file_status
FROM table1
WHERE file_id = p_file_name
ORDER BY created DESC
LIMIT 1;

RETURN (file_status);

END; $$

DELIMITER ;