cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Stored procedure question ?
Date Tue, 15 May 2007 12:22:46 GMT
> Is it a problem from the modeler?

Per SQLServer docs:

http://support.microsoft.com/kb/285295

"Every SQL Server stored procedure has a return value parameter  
(whether it is explicitly used or not)" which is called  
@return_value. I suspect that JDBC blows on implicit mapped  
@return_value. We can probably do something smarter about reverse  
engineering of SQLServer procedures to account for that (like  
removing an implicit return). Would you mind opening an improvement  
request in Jira?

http://issues.apache.org/cayenne


> The exception:
> Caused by: java.sql.SQLException: Operand type clash: int is
> incompatible with cursor
> ...

I am not sure about this handling a refcursor OUT parameter - I need  
to try it for myself to be able to comment intelligently.  
Unfortunately I don't have time to look at it now either :-( For now  
you can try the same trick with removing the return value?

Andrus



On May 15, 2007, at 10:53 AM, Marc Gabriel-Willem wrote:

> Hello,
>
> I removed the first parameter and the "returningValue" flag ... and
> indeed it is working properly. I'm really surprised because I used the
> "reengineer database schema" function to create the mapping file.
>
> Is it a problem from the modeler?
>
> I've another question for you.
>
> In MS sqlserver, we have the following stored procedure:
>
> CREATE procedure [dbo].[spTestGet2] @rs_cursor CURSOR VARYING OUTPUT
> AS
> SET  @rs_cursor = CURSOR FOR select id, data, lastupdate, description
> from tTest
> OPEN @rs_cursor
> GO
>
> Following mapping has been done using the modeler:
> <procedure name="spTestGet2" schema="dbo" catalog="spp"
> returningValue="true">
> 		<procedure-parameter name="@RETURN_VALUE" type="INTEGER"
> length="4"/>
> 		<procedure-parameter name="@rs_cursor" type="INTEGER"
> length="4" direction="in_out"/>
> </procedure>
>
> By the way, for the "@return_value", I've the following warning:  
> missing
> direction.
>
> I removed the first parameter in order to fix that warning.
> Nevertheless, the performGenericQuery function throws an exception:
> ...
> ProcedureQuery query = new ProcedureQuery("spTestGet2");
> QueryResponse result = context.performGenericQuery(query);
> ...
>
> The exception:
> Caused by: java.sql.SQLException: Operand type clash: int is
> incompatible with cursor
> ...
>
> For information, I'm using the "JTDS 1.2" driver.
>
> Thank you for your help.
>
> Regards,
> Marc
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Monday, May 14, 2007 4:04 PM
> To: user@cayenne.apache.org
> Subject: Re: Stored procedure question ?
>
> This doesn't make sense to me either. So what happens of you remove
> the first parameter and set "returningValue" to false?
>
> Andrus
>
> On May 14, 2007, at 4:58 PM, Marc Gabriel-Willem wrote:
>
>> <procedure name="spViewCustomer" schema="dbo" returningValue="true">
>>   <procedure-parameter name="returnValue" type="INTEGER"
>> direction="in"/>
>>   <procedure-parameter name="id" type="INTEGER" direction="in"/>
>>   <procedure-parameter name="user" type="VARCHAR" length="25"
>> direction="in"/>
>> </procedure>
>>
>> ...
>>
>>
>>
>> I've no problem with the "id" and "user" parameters. But I really
>> do not
>> understand why I have to set my "returnValue" as INTEGER with the
>> direction "IN"... I tried with other options but without any success.
>
>
>
>


Mime
View raw message