I guess we'll need to do some research as to how to call the function directly
via raw JDBC code with the same driver. If we are able to do that, we should be
able to do that via Cayenne.
Can't promise any specific time for that, but I put it on my TODO list.
Cheers,
Andrus
On Aug 7, 2012, at 2:58 PM, Сурнин Евгений wrote:
> I try to use Types.OTHER, but got the same exception:
> "java.sql.SQLException: The request for procedure 'test' failed because
> 'test' is a table valued function object."
>
> Types.OTHER maybe? (Just a guess, like I said I haven't tried this
> scenario myself)
>>
>> On Aug 7, 2012, at 11:46 AM, Сурнин Евгений wrote:
>>
>>> I try execute procedures&functions dynamically as:
>>> Procedure proc = new Procedure(procedureName);
>>>
>>> So if i want exec function, i add some code (parameter for returning value):
>>> proc.setReturningValue(true); // i think it means that i execute function
>>> ProcedureParameter returnValueParameter = new
>>> ProcedureParameter("RETURN_VALUE",
>>> TypesMapping.getSqlTypeByName(returnType),
>>> ProcedureParameter.OUT_PARAMETER);
>>> proc.addCallParameter(returnValueParameter);
>>>
>>> For Oracle tests I dynamically set type so:
>>> returnValueParameter.setType(OracleTypes.CURSOR);
>>>
>>> But which type I must set for SQL Server I'm not understand.
>>>
>>>> 2)
>>>>> How to execute this function? Or I have to execute it like this: select *
>>>>> from test('some') via performQuery method?
>>>>
>>>> If you have this option, I would certainly do the later. Or even better:
>>>>
>>>> Class queryType = .. ; // init this dynamically
>>>> SelectQuery query = new SelectQuery(queryType); // very ORM code
>>>>
>>>> Which is good ORM code.... Other than that... I know the following works
>>>> on SQLServer.
>>>>
>>>> CREATE PROCEDURE cayenne_tst_select_proc @aName VARCHAR(255),
>>>> @paintingPrice INT
>> AS
>>>> BEGIN
>>>> SELECT DISTINCT A.ARTIST_ID, A.ARTIST_NAME, A.DATE_OF_BIRTH
>>>> FROM ARTIST A, PAINTING P
>>>> WHERE A.ARTIST_ID = P.ARTIST_ID AND
>>>> A.ARTIST_NAME = @aName
>>>> ORDER BY A.ARTIST_ID
>>>> END
>>>>
>>>> Haven't tried functions with it though. Have you checked 'Return Value'
>>>> checkbox
>> when mapping
>>>> the procedure and created a parameter for the return value?
>>
>>
