How to use stored procedures with linked tables
In MySQL I have the following table:
CREATE TABLE `info` ( `created` int(11) );
And it is linked (via JDBC SQL Gateway) to Cache table mysql.info. `created` field stores unix timestamp. So when I execute this SQL in SMP:
SELECT created FROM mysql.info
I receive the following output (which is expected):
created |
1435863691 |
1436300964 |
But I want to to display `created` field converted to ODBC timestamp format. To do that I call this SQL procedure
Class mysql.utils [ Abstract ]
{
/// Unix timestamp to ODBC
ClassMethod uto(unixstamp As %Integer) As %TimeStamp [ SqlName = uto, SqlProc ]
{
set startday = $zdh("1970-01-01 00:00:00",3)
set endday = (unixstamp \ (60 * 60 * 24))
set endtime = (unixstamp # (60 * 60 * 24))
set totalday = endday + startday
quit $zdt(totalday _ "," _ endtime, 3)
}
}
But if I execute this SELECT:
SELECT created, mysql.uto(created) As "ODBC" FROM mysql.info
I receive the following output:
created | ODBC |
1435863691 | 1435863691 |
1436300964 | 1436300964 |
Instead of the expected output:
created | ODBC |
1435863691 | 2015-07-02 19:01:31 |
1436300964 | 2015-07-07 20:29:24 |
Then, I tried to use MySQL conversion function from_unixtime for the same purpose:
SELECT created, from_unixtime(created) As "ODBC" FROM mysql.info
However, I receive the following error message:
User defined SQL function 'SQLUSER.FROM_UNIXTIME' does not exist
Which is true enough I suppose.
But a question is, how do I apply stored procedure to a linked table field?