Database stored procedures

[worldzerga]

Hi,

I am having a problem calling a database stored procedure from Teiid. Here are the steps showing how I build VDB:

1. I have created source model from JDBC datasource and in the wizard I have selected the database stored procedure

2. Then I have created a view model from the source model created in the step 1

3. The transformation script is:

CREATE VIRTUAL PROCEDURE

BEGIN

          EXEC PrepFundExchangeFromPifia.prepFundExchange(PrepFundExchangeView.prepFundExchange."date", PrepFundExchangeView.prepFundExchange.type_close, PrepFundExchangeView.prepFundExchange.regim, PrepFundExchangeView.prepFundExchange.debug, PrepFundExchangeView.prepFundExchange.CheckRegim);

END

4. After that I have created VDB from the view model and deployed to Teiid server

When I call virtual procedure from the client I get the followin error:

Error: TEIID30270 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30270 No valid criteria specified for procedure parameter PrepFundExchangeFromPifia.Pythoness_buf.dbo.prepFundExchange."date"

Could you please help me to understand what is it that I am doing wrong.

Much appreciated for your time and efforts!

I have also attached a printscreen

[rareddy]

Sergey,

It is indicating that you have not provided the "date" as the input parameter for execution of the procedure. Also make you use proper escaping when providing the date.

Ramesh..

[worldzerga]

Hi Ramesh,

Thank you for your response.

Here is the client code:

CallableStatement cs = connection.prepareCall("{call PrepFundExchangeView.Pythoness_buf.dbo.prepFundExchange(?,?,?,?,?)}");

cs.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));

cs.setByte(2, p1);

cs.setByte(3, p2);

cs.setByte(4, p3);

cs.setByte(5, p4);

cs.execute();

So I don't understand why it is complaining that the "date" is not being provided. My be there is a problem with the transformation script?

[shawkins]

Sergey,

That exception should only happen using the procedural relational style invocation, such as SELECT * FROM PrepFundExchangeFromPifia WHERE ...

Just calling PrepFundExchangeFromPifia through an EXEC/CALL should work as you expect.  Is there anything else going on in this scenario other than what is shown here?  If not, then you'll want to log a JIRA.

Thanks,

Steve

[worldzerga]

Hi Steve,

I have used relational style before but then moved to EXEC approach. I have cleaned all caches in JBOSS and it solved the problem.

Now I have a different problem. Although the procedure is being executed without problem it does not return the result set. Do I have to explicitly specify the result set that the database stored procedure returns?

[shawkins]

Sergey,

In many instances vendor's JDBC DatabaseMetadata does not show the columns for returned resultsets, in which case yes you do have to hand model what you expect.  If you are using a common DBMS and there is a way to use their system tables to get the result set information, then please open a designer JIRA to enhance the import process.

Steve