db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: PersistenceBroker.getCount() -- Doesn't work for MSSQL Uniqueidentifiers!!!
Date Thu, 12 Feb 2004 18:07:32 GMT
hi andrew,

the distinct stuff works for single and multiple pk :

select count(distinct key1 || key2)

but the big problem i have now is the handling (translation)of the multi columns 
  during sql generation. the current implementation assumes that there's only 
one attribute to be translated :(

select count(distinct A0.pk_1 || key2)  from tab A0

the second attribute (key2) is not translated into it's column name.

jakob

Jakob Braeuchi wrote:
> hi andrew,
> 
> the count(*) stuff will be in 1.0. i'm not sure about the distinct 
> because this would require an api-change in platform.
> 
> jakob
> 
> Clute, Andrew wrote:
> 
>> Thank you!
>>
>> Is it safe to assume that this fix will be available in the 1.0 release?
>>
>>  
>>
>> -----Original Message-----
>> From: Jakob Braeuchi [mailto:jbraeuchi@gmx.ch] Sent: Sunday, January 
>> 25, 2004 10:54 AM
>> To: OJB Developers List
>> Subject: Re: PersistenceBroker.getCount() -- Doesn't work for MSSQL
>> Uniqueidentifiers!!!
>>
>> hi andrew,
>>
>> i fixed it using select count(*) which is ok for all dbms if the query
>> is not distinct. the syntax for count distinct differs between the dbms,
>> so we'll have to include it in the platform.
>>
>> hth
>> jakob
>>
>> Jakob Braeuchi wrote:
>>
>>> hi andrew,
>>>
>>> if the query is not distinct i can use count(*) for single and multiple
>>> keys:
>>>
>>> select count(*) from person_project;
>>>
>>> if it's distinct i'll have to use concatenation:
>>>
>>> select count (distinct(person_id || project_id)) from person_project;
>>>
>>> this syntax works with hsqldb and sapdb but fails with mysql :(
>>>
>>> jakob
>>>
>>> Jakob Braeuchi wrote:
>>>
>>>
>>>> hi andrew,
>>>>
>>>> according to some newsgroups there's no performance penalty for using
>>>> count(*) instead of count(pk). so we could use count(*) for queries 
>>>> not using DISTINCT.
>>>>
>>>> i'll have a look at it asap.
>>>>
>>>> hth
>>>> jakob
>>>>
>>>> Clute, Andrew wrote:
>>>>
>>>>
>>>>> The getCount function does a 'select count(<pk>)' where it inserts

>>>>> the primary keys for your return class into the count clause.
>>>>>
>>>>> That is great, until you are using, like we are, a field of type 
>>>>> 'uniqueidentifier' that is available in SQL 7 and up. MSSQL Server 
>>>>> does not support that using of uniqueidentifier's as the clause for
>>
>>
>> a count.
>>
>>>>> You will get the following error:
>>>>>
>>>>> 'The count aggregate operation cannot take a uniqueidentifier data 
>>>>> type as an argument.'
>>>>>
>>>>> So, this leaves me in a bad position -- by using that as my primary 
>>>>> key, I can no longer access length's of my result set (and secondary
>>
>>
>>
>>>>> be able to do paging).
>>>>>
>>>>> I don't see any workaround in the current code, and am wondering if 
>>>>> there is an appropriate fix for this problem for the codebase?
>>>>>
>>>>> Is there a reason why we couldn't have the ability to do 'select 
>>>>> count(*)' as a fallback?
>>>>>
>>>>> I will admit, I don't know the intricate details about SQL92 -- so I
>>
>>
>>
>>>>> don't know what the benefit of naming a column in a count clause 
>>>>> versus the '*'.
>>>>>
>>>>> I guess my other solution is to extend PersistenceBroker and make my
>>
>>
>>
>>>>> own implementation for that, and not use the pk's in the count 
>>>>> clause (what would the side effects be of that)?
>>>>>
>>>>> Thanks
>>>>>
>>>>> -Andrew
>>>>>
>>>>> --------------------------------------------------------------------
>>>>> - To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For 
>>>>> additional commands, e-mail: ojb-dev-help@db.apache.org
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For 
>>>> additional commands, e-mail: ojb-dev-help@db.apache.org
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For 
>>> additional commands, e-mail: ojb-dev-help@db.apache.org
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org For additional
>> commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message