Board index » delphi » D2/BDE-SQL UPPER() question, and Union query question

D2/BDE-SQL UPPER() question, and Union query question

Hi,

(1)

I know I asked this last week but I cannot believe that it can be true!

Working with D1 and version 2.5 of the BDE I can use the following no
problem

'Select * from MyTable where Upper(Address) Like :xAddress';
MyQuery.ParamByName(xAddress).AsString := '%' + 'new york' + '%';

And it locates all occurances of "New York", "new YoRk" etc etc .... does
what its told!

HOWEVER!!

In Delphi 2 and the newer BDE (most recent upgrade) I cannot preform this
query!

The only answer I got was telling me that they confirmed this was true
.... does this then mean that Borlands latest BDE is not backward
compatiable????

(2) I have a clients table, then a phone table. Clients can have many
phones. The Link is using a ClientID field in the CLients table as auto-
increment, and a ClientID field in the phone table as Long Integer.

Can anyone tell me the syntax for selecting all clients where the phone
number equals *variable*

Thanks,

Allen.

--
(To reply remove the NO from my address!)

 

Re:D2/BDE-SQL UPPER() question, and Union query question


Quote
On Wed, 4 Mar 1998 11:00:11 -0000, tec...@cargo.iol.ie (Allen) wrote:
>I know I asked this last week but I cannot believe that it can be true!

>Working with D1 and version 2.5 of the BDE I can use the following no
>problem

>'Select * from MyTable where Upper(Address) Like :xAddress';
>MyQuery.ParamByName(xAddress).AsString := '%' + 'new york' + '%';

>And it locates all occurances of "New York", "new YoRk" etc etc .... does
>what its told!

>HOWEVER!!

>In Delphi 2 and the newer BDE (most recent upgrade) I cannot preform this
>query!

>The only answer I got was telling me that they confirmed this was true
>... does this then mean that Borlands latest BDE is not backward
>compatiable????

The above operation works under Delphi 3. Using the sample Paradox table
CUSTOMER.DB, the SQL:

  SELECT C.COMPANY, C.ADDR1
  FROM "CUSTOMER.DB" C
  WHERE UPPER(C.ADDR1) LIKE UPPER(:xAddress)

And, in Delphi the code:

  procedure TForm1.Button1Click(Sender: TObject);
  begin
    with Query1 do begin
      Close;
      ParamByName('xAddress').AsString := '%' + Edit1.Text + '%';
      Open;
    end;
  end;

What part of the process is not working for you? The SQL statement or the
Delphi code setting the parameter? In what way is it not working? Error
message? No error, but incorrect or no results?

Quote
>(2) I have a clients table, then a phone table. Clients can have many
>phones. The Link is using a ClientID field in the CLients table as auto-
>increment, and a ClientID field in the phone table as Long Integer.

>Can anyone tell me the syntax for selecting all clients where the phone
>number equals *variable*

It depends on what you mean by "variable". A parameter in an SQL statement
is a form of variable. Delphi memory variables cannot be explicitly named
in SQL statements, but can be used in constructing an SQL statement in the
TQuery.SQL property. Or, the value of a Delphi memory variable can be
passed to an SQL statement through a parameter.

Your description above does not sound like a UNION situation (per the
subject line). It sounds more like a simple join with WHERE clause
filtering.

**************************************************************************
Steve Koterski
Borland International, Inc.
http://www.borland.com/delphi
(Remove the "SPICEDHAM2" from the address. Death to spam-bots!)

Other Threads