Board index » delphi » Case sensitive problem of Paradox query engine

Case sensitive problem of Paradox query engine

Hi,

I am using Paradox 7 + Delphi 4.03. I pass a query string "Select * From
Products Where ProductName like 'Bec%'" to the Paradox, and I hope it can
return all products whose name are started with "Bec" or "bec". But the
query engine is Case Sensitive, and it returns only the uppercase ones. Is
there a configuration to let the query engine case insensitive? I have
created the Case-Insensitive index for the field.

I prefer to use the TQuery to do the search for this project since I joined
four tables in the SQL statements. To my surprise, it runed very fast
compared with Paradox 5 which let me to wait around 10 seconds to get result
from 2 joined tables. Good work! But the case problem might force me convert
back to use TTable. I can't imagine to search through four tables by using
TTable to get the result set. Help me...

Thanks,

Libo

 

Re:Case sensitive problem of Paradox query engine


Use the Upper SQL function and use only upper case.

For example

SELECT * FROM CUSTOMERS WHERE UPPER(NAME) LIKE "BOB%"

Re:Case sensitive problem of Paradox query engine


Quote
On Fri, 7 May 1999 17:24:36 +0800, "Libo" <l...@hol.com.sg> wrote:
>I am using Paradox 7 + Delphi 4.03. I pass a query string "Select * From
>Products Where ProductName like 'Bec%'" to the Paradox, and I hope it can
>return all products whose name are started with "Bec" or "bec". But the
>query engine is Case Sensitive, and it returns only the uppercase ones. Is
>there a configuration to let the query engine case insensitive? I have
>created the Case-Insensitive index for the field.

In SQL, string comparisons are by nature case-sensitive. That is the
default behavior. You can override this behavior, but through SQL syntax
and not a BDE configuration setting.

In the SQL statement, take measures to ensure that the strings on both
sides of the comparison operator (here the LIKE predicate) are all in the
same casing scheme (all upper case or all lower case). For string literals
this is merely a matter of typing them in upper case. For columns and
parameters, use the SQL function UPPER (or LOWER if you are going in that
direction).

  SELECT *
  FROM Products
  WHERE (UPPER(ProductName) LIKE "BEC%")

This is described in the local SQL online help file, LOCALSQL.HLP, found in
the main BDE directory.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Re:Case sensitive problem of Paradox query engine


Since a few times (BDE 32) SQL local "LIKE" operator is case sensitive.
When asking Borland why to change the behavior, they answered me "cause it
is more SQL 92 compliant"...
Well ... But they just forgot to provide a non case sensitive equivalent,
such as CONTAINTING in Interbase.
So, in particular against Memo fields, you can't use local SQL if you have
to search with a LIKE ! Incredible..
When you try to do the same with QBE, you can see that the latter is still
case insensitive.
So you can build a QBE query (some QBE Tquery exist).
The other solution (I choosed) : move your data to another database !

--
Olivier Dahan
oda...@{*word*104}cable.fr
Delphi 32b C/S certified engineer
Libo <l...@hol.com.sg> a crit dans le message :
7gub8d$d...@forums.borland.com...

Quote
> Hi,

> I am using Paradox 7 + Delphi 4.03. I pass a query string "Select * From
> Products Where ProductName like 'Bec%'" to the Paradox, and I hope it can
> return all products whose name are started with "Bec" or "bec". But the
> query engine is Case Sensitive, and it returns only the uppercase ones. Is
> there a configuration to let the query engine case insensitive? I have
> created the Case-Insensitive index for the field.

> I prefer to use the TQuery to do the search for this project since I
joined
> four tables in the SQL statements. To my surprise, it runed very fast
> compared with Paradox 5 which let me to wait around 10 seconds to get
result
> from 2 joined tables. Good work! But the case problem might force me
convert
> back to use TTable. I can't imagine to search through four tables by using
> TTable to get the result set. Help me...

> Thanks,

> Libo

Re:Case sensitive problem of Paradox query engine


Thanks. I will solve it by the way you suggested. But I can tell MS SQL
Server to be case insensitive when I install it. It is a valuable option,
right?

Another question is what is the purpose of Case-Insensitive index of
Paradox?

Libo

Quote
Steve Koterski <skoter...@NOSPAMinprise.com> wrote in message

news:373407ee.1716897@forums.inprise.com...
Quote
> On Fri, 7 May 1999 17:24:36 +0800, "Libo" <l...@hol.com.sg> wrote:

> >I am using Paradox 7 + Delphi 4.03. I pass a query string "Select * From
> >Products Where ProductName like 'Bec%'" to the Paradox, and I hope it can
> >return all products whose name are started with "Bec" or "bec". But the
> >query engine is Case Sensitive, and it returns only the uppercase ones.
Is
> >there a configuration to let the query engine case insensitive? I have
> >created the Case-Insensitive index for the field.

> In SQL, string comparisons are by nature case-sensitive. That is the
> default behavior. You can override this behavior, but through SQL syntax
> and not a BDE configuration setting.

> In the SQL statement, take measures to ensure that the strings on both
> sides of the comparison operator (here the LIKE predicate) are all in the
> same casing scheme (all upper case or all lower case). For string literals
> this is merely a matter of typing them in upper case. For columns and
> parameters, use the SQL function UPPER (or LOWER if you are going in that
> direction).

>   SELECT *
>   FROM Products
>   WHERE (UPPER(ProductName) LIKE "BEC%")

> This is described in the local SQL online help file, LOCALSQL.HLP, found
in
> the main BDE directory.

> //////////////////////////////////////////////////////////////////////////
> Steve Koterski                    "My problem lies in reconciling my gross
> Technical Publications            habits with my net income."
> INPRISE Corporation                             -- Errol Flynn (1909-1959)
> http://www.borland.com/delphi

Re:Case sensitive problem of Paradox query engine


Quote
On Mon, 10 May 1999 09:14:01 +0800, "Libo" <l...@hol.com.sg> wrote:
>Thanks. I will solve it by the way you suggested. But I can tell MS SQL
>Server to be case insensitive when I install it. It is a valuable option,
>right?

"Valuable" is a subjective term only you can estimate.

On the one hand, there is the SQL standard in which all string comparisons
are case-sensitive. On the other hand, you have this
implementation-specific (Microsoft SQL Server) setting that overrides this
default behavior. If you would only be using or programming for SQL Server,
such nonstandard features should never be a problem and would indeed be
valuable. But if you program for a number of different database back-ends,
some or most of which do not offer this feature (or offer it in the same
way), the value is diminished by the inconsistency.

Quote
>Another question is what is the purpose of Case-Insensitive index of
>Paradox?

Other than string comparisons in SQL, for one thing. Basically, these
indexes are for index-based search and filter methods as used by BDE API
functions. Typically, these functions are implemented in application
front-end tools like Paradox and Delphi.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Other Threads