You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by "Clute, Andrew" <An...@osn.state.oh.us> on 2004/01/21 22:49:47 UTC
PersistenceBroker.getCount() -- Doesn't work for MSSQL Uniqueidentifiers!!!
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
Re: PersistenceBroker.getCount() -- Doesn't work for MSSQL Uniqueidentifiers!!!
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
Re: PersistenceBroker.getCount() -- Doesn't work for MSSQL Uniqueidentifiers!!!
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
Re: PersistenceBroker.getCount() -- Doesn't work for MSSQL Uniqueidentifiers!!!
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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