Written by

Senior Cloud Architect at InterSystems

STAFF

Question Eduard Lebedyuk · May 18, 2016

How to use stored procedures with linked tables

#Caché #JDBC #ODBC #SQL

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?

Discussion (3)0

Add reply

Comments

Kyle Baxter · May 19, 2016

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.

0 0

Eduard Lebedyuk May 19, 2016 to Kyle Baxter

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.

0 0

Jean Millette Feb 7 to Eduard Lebedyuk

It's been almost 9 years @Eduard Lebedyuk :-) Have ever figured out how to call a linked procedure when running a select for data from a linked table?

When I execute a select statement with only the call to the linked stored procedure, all works ok:

select CRMBI.getPlanningRate(11,2023)

Expression_1
0.94

1 row(s) affected

But if I try to make that same call within a query to another table in that same package, I get errors:

select ISCurrencyID, CRMBI.getPlanningRate(ISCurrencyID,2024) from CRMBI.CurrencyRate

  [SQLCODE: <-223>:<Gateway query Prepare() failed>]

  [%msg: <Connection 'CRMBILINKTABLES': Prepare(select T1.ISCurrencyID AS C1,(T1.ISCurrencyID...): SQLState: (42000) NativeError: [1] Message: [Iris ODBC][State : 42000][Native Code 1] [c:\intersystems\iris2021v1\bin\irisdb.exe] [SQLCODE: <-1>:<Invalid SQL statement>] [Location: <Prepare>] [%msg: < ) expected, , found ^SELECT T1 . ISCurrencyID AS C1 , ( T1 . ISCurrencyID ,>] >]

Other queries to the linked table, without the stored procedure, also works just fine:

select ISCurrencyID, USDRate, effectiveDate from CRMBI.CurrencyRate where ISCurrencyID = 11 and effectiveDate > '2022-12-31' and effectiveDate < '2023-01-31'

ISCurrencyID	USDRate	effectiveDate
11	0.9392000	2023-01-01

1 row(s) affected

Might be user error or a later message to the WRC, but thanks in advance if you have any ideas.

0 0