Board index » delphi » Parameterized query with Oracle 7.1....

Parameterized query with Oracle 7.1....

I've some troubles passing parameters to a D2 query on Oracle 7.1 on
Windows NT. When I create a parameter on a CHAR column, It is necessary
to pass a string padded to the length of the column on the database in
order to obtain the desired records.

Example:

Column CUSTOMER_CODE CHAR(3)

1) Query1.ParamByName('CustCode').AsString := 'A1'
In this case the query doesn't return any record (even when there are
records with this customer id);

2) Query1.ParamByName('CustCode').AsString := 'A1  ' // 2 spaces after
the code
In this case the query returns the correct record.

I've tried ODBC and SQLLinks (both the latest versions) but the problem
still arises. This problem doesn't show up with other databases (Sybase
SQL Anywhere, Interbase).

Please, reply also with e-mail.
Thanks!

-------------------------
    Armando Colombo
C/S & Intranet Consulting
  http://www.vol.it/ac

 

Re:Parameterized query with Oracle 7.1....


On Wed, 06 Nov 1996 11:40:35 +0100,
Armando Colombo  <actt...@mbox.vol.it> wrote:

Quote
>Example:

>Column CUSTOMER_CODE CHAR(3)

>1) Query1.ParamByName('CustCode').AsString := 'A1'
>In this case the query doesn't return any record (even when there are
>records with this customer id);

>2) Query1.ParamByName('CustCode').AsString := 'A1  ' // 2 spaces after
>the code
>In this case the query returns the correct record.

Change the column definition from CHAR to VARCHAR2.

Lito

Lito Dizon
adi...@us.net

Re:Parameterized query with Oracle 7.1....


Quote
Armando Colombo wrote:

> I've some troubles passing parameters to a D2 query on Oracle 7.1 on
> Windows NT. When I create a parameter on a CHAR column, It is necessary
> to pass a string padded to the length of the column on the database in
> order to obtain the desired records.

> Example:

> Column CUSTOMER_CODE CHAR(3)

> 1) Query1.ParamByName('CustCode').AsString := 'A1'
> In this case the query doesn't return any record (even when there are
> records with this customer id);

> 2) Query1.ParamByName('CustCode').AsString := 'A1  ' // 2 spaces after
> the code
> In this case the query returns the correct record.

> I've tried ODBC and SQLLinks (both the latest versions) but the problem
> still arises. This problem doesn't show up with other databases (Sybase
> SQL Anywhere, Interbase).

The problem isn't with Delphi but the use of the Char field type.  What
you probably want to use is VarChar2.  Char fields in Oracle are always
fixed length and that's regardless of whether you put half spaces, all
spaces, or whatever.  That's the definition of a Char field.

VarChar2 on the other hand is variable length and will only store up to
the end of your string.
--
John Parrott
Elysium Financial Systems
s...@interlog.com
http://www.interlog.com/~spo

Re:Parameterized query with Oracle 7.1....


Quote
Armando Colombo wrote:

> I've some troubles passing parameters to a D2 query on Oracle 7.1 on
> Windows NT. When I create a parameter on a CHAR column, It is necessary
> to pass a string padded to the length of the column on the database in
> order to obtain the desired records.

> Example:

> Column CUSTOMER_CODE CHAR(3)

> 1) Query1.ParamByName('CustCode').AsString := 'A1'
> In this case the query doesn't return any record (even when there are
> records with this customer id);

> 2) Query1.ParamByName('CustCode').AsString := 'A1  ' // 2 spaces after
> the code
> In this case the query returns the correct record.

Try to use VARCHAR2  instead CHAR. I think it is Oracle problem. You can read, what Oracle
compare in different way CHAR colums and VARCHAR2 colums.

--
Vidmantas Matelis
http://www1.omnitel.net/vidma

Re:Parameterized query with Oracle 7.1....


Quote
Vidmantas Matelis <86...@klaipeda.omnitel.net> wrote:
>Armando Colombo wrote:

>> I've some troubles passing parameters to a D2 query on Oracle 7.1 on
>> Windows NT. When I create a parameter on a CHAR column, It is necessary
>> to pass a string padded to the length of the column on the database in
>> order to obtain the desired records.

>> Example:

>> Column CUSTOMER_CODE CHAR(3)

>> 1) Query1.ParamByName('CustCode').AsString := 'A1'
>> In this case the query doesn't return any record (even when there are
>> records with this customer id);

>> 2) Query1.ParamByName('CustCode').AsString := 'A1  ' // 2 spaces after
>> the code
>> In this case the query returns the correct record.
>Try to use VARCHAR2  instead CHAR. I think it is Oracle problem. You can read, what Oracle
>compare in different way CHAR colums and VARCHAR2 colums.
>--
>Vidmantas Matelis
>http://www1.omnitel.net/vidma

In Oracle, columns of type CHAR are fixed length and the values are
padded with blanks. This is why you needed to pad your query
parameters since 'A1' and 'A1 ' aren't  really the same value.

Using a VARCHAR2 will solve this problem since VARCHAR2 columns are
not padded in the database.

If you really want (or need) to use CHAR columns, you can change your
query so that you do not need to pad the query parameters.  For
example:

SELECT *
FROM MYTABLE
WHERE RTRIM(ACHAR_COLUMN) = :PARAM1

Other Threads