Board index » delphi » SQL Server provider ClientDataSet Filtering Exception (Demo Uploaded to b.p.attachments)

SQL Server provider ClientDataSet Filtering Exception (Demo Uploaded to b.p.attachments)

Hi all

Firstly I'll point out that I've tested the following with a Paradox &
Interbase back end and neither had this problem.
I've tried D6 - Update Pack 1, Update Pack 2 & Update Pack 2 with Variants
beta 2.
All versions experience the same problem with MSSQL as the back end.

Now the problem...

I have a string field in a client dataset that I want to filter on...

If I filter on this field with a value that begins with a character, all is
fine and it works as expected.
If I filter on this field with a value that begins with a number I get an
Access Violation in kernel32.dll.

The AV occurs in DBClient.pas in TCustomClientDataSet.AddExprFilter on the
line
    Check(FDSCursor.AddFilter(FilterData, DataSize, FExprFilter));

I've uploaded a demo project that demonstrates the problem. A script
included will create a table in tempdb with a small amount of data.

If anyone has any ideas/comments I'd like to hear them.

Regards
Clint.

 

Re:SQL Server provider ClientDataSet Filtering Exception (Demo Uploaded to b.p.attachments)


Forgot to mention that I'm accessing SQL Server through BDE 5.2.0.2

Re:SQL Server provider ClientDataSet Filtering Exception (Demo Uploaded to b.p.attachments)


Clint -

Did you ever find a solution to this problem? I've got a similar problem.
I've narrowed it down to where it only happens if my CDS contains certain
records. But there doesn't appear to be anything strange about those
records. I'd really appreciate it if you could let me know if you find a
solution.

The only work-around I could find is to use the OnFilterRecord event instead
of a filter string. For whatever reason, this does not encounter the error.

By the way, my problem happens with MSSQL 6.5 and 2000. I had transferred
the data from 6.5 to 2000 using BCP.

Dan Thomas

Quote
"Clint Good" <cg...@compfleet.com.au> wrote in message

news:3cdf49a7$1_1@dnews...
Quote
> Forgot to mention that I'm accessing SQL Server through BDE 5.2.0.2

Re:SQL Server provider ClientDataSet Filtering Exception (Demo Uploaded to b.p.attachments)


Hi Dan

Quote
"Dan Thomas" <Filter...@dagware.com> wrote in message

news:3ce29931_2@dnews...

Quote
> Did you ever find a solution to this problem? I've got a similar problem.
> I've narrowed it down to where it only happens if my CDS contains certain
> records.

No solution yet :(
I'm assuming it's a problem with the SQL Server SQL Link. I suspect it
incorrectly treats some string fields that begin with numbers, but to add to
the weirdness I actually have some records that begin with numbers that it
works for.

Quote
> The only work-around I could find is to use the OnFilterRecord event
instead
> of a filter string. For whatever reason, this does not encounter the

error.

I'm actually using the DevExpress grids autofilter, I'll have to check later
if it is possible to use OnFilterRecord instead. Thanks for this tip...

Regards
Clint.

Re:SQL Server provider ClientDataSet Filtering Exception (Demo Uploaded to b.p.attachments)


I have found a workaround. First, let me give all the details of my problem:

Delphi 6, Update pack #1
Not sure what BDE version -- don't remember how to find it!

Using a TClientDataSet, with the data coming from a TQuery (also happens
with a TStoredProc), connected to MSSQL 6.5, and 2000. The problem does NOT
appear to happen if I use ADO instead of BDE, but I only did limited testing
with that. Please don't ask why I don't just use ADO -- that's too long a
story to deal with here. Just think "Dilbert" and Pointy-Headed-Boss, and
you'll get the idea.

Under certain circumstances, setting cds.Filter to a value that includes an
"=" condition and then setting cds.Filtered to TRUE will cause an Access
Violation.

Upon further investigation, I can narrow the problem down to specific fields
in specific rows of my result set. In other words, cds.Filter :=
'client_name = ''test''' when record for client_no 1234 exists in the result
set. When I examine the offending records, there appears to be nothing wrong
with the data. I did extensive checking of this. I can't see any pattern to
the problem.

WORKAROUND:

Since the problem only happens when there's an "=" condition, we only need
to work around that type of condition. All other conditions appear to work
fine.

Instead of using an "=", I use a "like" with no wild card characters, and
cds.FilterOptions contains foNoPartialCompare. This works fine, and there's
no AV's.

The one problem I've found with the workaround is if you want to test for an
empty string. You can't do that with a "like". So here's the really
convoluted code I use for testing for empty strings:

cds.Filter :=
    '(substring(trimright(' + sFieldName + ')+''x'',1,2) = ''x'')'
    + ' or (' + sFieldName + ' IS NULL)'

What I'm basically doing is appending an 'x' character to the end of the
field (it doesn't matter what character, I just chose 'x'), then checking to
see if the first 2 characters are equal to 'x'. The only time this will be
true is if the field was blank. I'm not sure why I don't get an AV with this
method, but I don't, so let's not complain!

I discovered that I needed to add the test for NULL also, so that's why it's
there.

I've tried this solution with lots of complex filter strings, and it appears
to work in all cases. Understand, though, that I just came up with this
yesterday, so we may discover some problems as we go on.

That's it!

-Dan Thomas

Quote
"Clint Good" <cg...@compfleet.com.au> wrote in message

news:3ce2d459$1_2@dnews...
Quote
> Hi Dan

> "Dan Thomas" <Filter...@dagware.com> wrote in message
> news:3ce29931_2@dnews...
> > Did you ever find a solution to this problem? I've got a similar
problem.
> > I've narrowed it down to where it only happens if my CDS contains
certain
> > records.

> No solution yet :(
> I'm assuming it's a problem with the SQL Server SQL Link. I suspect it
> incorrectly treats some string fields that begin with numbers, but to add
to
> the weirdness I actually have some records that begin with numbers that it
> works for.

> > The only work-around I could find is to use the OnFilterRecord event
> instead
> > of a filter string. For whatever reason, this does not encounter the
> error.

> I'm actually using the DevExpress grids autofilter, I'll have to check
later
> if it is possible to use OnFilterRecord instead. Thanks for this tip...

> Regards
> Clint.

Other Threads