-
1. Re: Error with in/out parameter
shawkins Mar 22, 2010 11:53 AM (in response to andeeptoor)Hi Andeep,
This is a different issue than the other thread. Is your procedure a source procedure or a virtual procedure? If it's a virtual, we don't yet support in/out, out, or return parameters in virtual procedures. The typical workaround is to change the in/out parameter to an in parameter and add the out value to the result set. I'm not sure if there is an issue currently open on supporting in/out, out, and return parameters in virtual procedures - I'll create one if not.
If it's a source procedure, then can you double check the metadata? If it was imported via a dyanmic vdb, then you can query System.ProcedureParams or this was imported via the designer you can look at the model.
Steve
-
2. Re: Error with in/out parameter
andeeptoor Mar 22, 2010 1:47 PM (in response to shawkins)Steven,
The procedure I am using is a virtual procedure that is mapped to a source procedure. The source procedure, as it turns out, has one in parameter and one out parameter. I had thought that there was an in / out parameter involved here, but it turns out that there is not. The virtual procedure lists the in parameter and on the result set, it has a column for the out parameter.
If I read your post correctly then you seem to be saying that this still will not work as Teiid does not support out parameters for virtual procedures? If this is incorrect and this should work, how should I call this stored procedure via SQL and get the out value? Can you provide an example that should work with a single in parameter and single out parameter in a virtual procedure?
-
3. Re: Error with in/out parameter
shawkins Mar 22, 2010 4:04 PM (in response to andeeptoor)Hi Andeep,
If you have a source procedure 'proc' that only returns in/out, out, or return parameters - and does not have have a resultset, then you're vitrual procedure should look like:
create virtual procedure
begin
exec proc(MY_PROC.param1)
end
This will return the out parameter as a single row in a resultset (in designer the resultset should automatically be created for you with a single column).
From your client you would then execute '{ call MY_VL.DB.MY_PROC(?) }' and get the out parameter from the resultset:
ResulSet rs = cs.getResultSet();
rs.next();
String result = rs.getString(1);
Hope this helps,
Steve
-
4. Re: Error with in/out parameter
andeeptoor Mar 24, 2010 11:26 AM (in response to shawkins)Steven,
Thanks, the example you provided cleared up my issue. Two quick followup questions:
1) Is there documentation that you can point to about calling stored procedures in this way?
2) When will there be a stable release of the 7.0.0 version of Teiid? We are looking to use this in a project that will be completed within the next month and wanted to know if there will be other major changes / fixes occurring between now and then.
Thanks again for your help!
-
5. Re: Error with in/out parameter
shawkins Mar 24, 2010 1:15 PM (in response to andeeptoor)Andeep,
Glad to hear that it worked. All the docbook docs are posted at http://www.jboss.org/teiid/docs.html. In particular you may be interested in the language reference covering the exec command http://docs.jboss.org/teiid/6.2/reference/en-US/html/sql_support.html#exec_command.
The next 7.0 milestone will be available shortly, but will contain major changes to the runtime/deployment model - we will run inside JBoss AS. 7.0 final won't probably be until May.
Steve