Eduard Lebedyuk · May 18, 2016

How to use stored procedures with linked tables

In MySQL I have the following table:

   `created` int(11)

And it is linked (via JDBC SQL Gateway) to Cache table  `created` field stores unix timestamp. So when I execute  this SQL in SMP:


I receive the following output (which is expected):


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

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

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?

0 784
Discussion (2)0
Log in or sign up to continue

2 things:

           1) What are you using for a Select Mode (Display, Logical, or OBDC)?  

           2) If you want to use a MySQL Stored Procedure, you can link it with the Linked Procedure Wizard.

1) I tried all three with the same result.

2) I linked the procedure, and it does not work when I select data from a linked table. When I check the int code for a query, there is no call to my procedure or linked procedure to be found.