Board index » delphi » String Parameters get changed to Text

String Parameters get changed to Text

The following SQL sentence has 27 parameters. They are in three groups of 9
parameters having the same name: Referencia1, Referencia2, Referencia3.
(I know that ADO cannot have parameters with the same name, but for the
purpose it's OK. Besides, I have tested the same sentence with different
names for the parameters and the problem was the same).

SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE
  (Telefono1 = :Referencia1) and (:Referencia1 is not null) and ('' <>
:Referencia1)
UNION
SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
  (Telefono2 = :Referencia1) and (:Referencia1 is not null) and ('' <>
:Referencia1)
UNION
SELECT * FROM Edisaco (INDEX (EmailWeb)) WHERE
  (EmailWeb LIKE :Referencia1 + '%') and (:Referencia1 is not null) and (''
<> :Referencia1)
UNION
SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE
  (Telefono1 = :Referencia2) and (:Referencia2 is not null) and ('' <>
:Referencia2)
UNION
SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
  (Telefono2 = :Referencia2) and (:Referencia2 is not null) and ('' <>
:Referencia2)
UNION
SELECT * FROM Edisaco (INDEX (EmailWeb))  WHERE
  (EmailWeb LIKE :Referencia2 + '%') and (:Referencia2 is not null) and (''
<> :Referencia2)
UNION
SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE
  (Telefono1 = :Referencia3) and (:Referencia3 is not null) and ('' <>
:Referencia3)
UNION
SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
  (Telefono2 = :Referencia3) and (:Referencia3 is not null) and ('' <>
:Referencia3)
UNION
SELECT * FROM Edisaco (INDEX (EmailWeb))  WHERE
  (EmailWeb LIKE :Referencia3 + '%') and (:Referencia3 is not null) and (''
<> :Referencia3)
ORDER BY ValidoHasta DESC, Id ASC

Before was the sentence as it is written in the TADOQuery. Next is the
result of the sentence when it goes to the database:

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=NULL
set @P3=102401
set @P4=311300
set @P5=NULL
exec sp_cursoropen @P1 output, N' SELECT * FROM Edisaco (INDEX (Telefono1))
WHERE (Telefono1 = @P1) and (@P2 is not null) and ('''' <> @P3) UNION SELECT
* FROM Edisaco (INDEX (Telefono2)) WHERE (Telefono2 = @P4) and (@P5 is not
null) and ('''' <> @P6) UNION SELECT * FROM Edisaco (INDEX (EmailWeb)) WHERE
(EmailWeb LIKE @P7 + ''%'') and (@P8 is not null) and ('''' <> @P9) UNION
SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE (Telefono1 = @P10) and (@P11
is not null) and ('''' <> @P12) UNION SELECT * FROM Edisaco (INDEX
(Telefono2)) WHERE (Telefono2 = @P13) and (@P14 is not null) and ('''' <>
@P15) UNION SELECT * FROM Edisaco (INDEX (EmailWeb))  WHERE (EmailWeb LIKE
@P16 + ''%'') and (@P17 is not null) and ('''' <> @P18) UNION SELECT * FROM
Edisaco (INDEX (Telefono1)) WHERE (Telefono1 = @P19) and (@P20 is not null)
and ('''' <> @P21) UNION SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
(Telefono2 = @P22) and (@P23 is not null) and ('''' <> @P24) UNION SELECT *
FROM Edisaco (INDEX (EmailWeb))  WHERE (EmailWeb LIKE @P25 + ''%'') and
(@P26 is not null) and ('''' <> @P27) ORDER BY ValidoHasta DESC, Id ASC
', @P3 output, @P4 output, @P5 output, N'@P1 varchar(9),@P2 varchar(9),@P3
varchar(9),@P4 varchar(9),@P5 varchar(9),@P6 varchar(9),@P7 varchar(9),@P8
varchar(9),@P9 varchar(9),@P10 text,@P11 text,@P12 text,@P13 text,@P14
text,@P15 text,@P16 text,@P17 text,@P18 text,@P19 text,@P20 text,@P21
text,@P22 text,@P23 text,@P24 text,@P25 text,@P26 text,@P27 text',
'918912525', '918912525', '918912525', '918912525', '918912525',
'918912525', '918912525', '918912525', '918912525', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', ''
select @P1, @P3, @P4, @P5

(Sorry for not formatting).

Note how the parameters are parsed. The parameter number 10 (look for @P10),
which corresponds to Referencia2, and all the following are parsed to TEXT
type instead of VARCHAR, as it is made for Referencia1.

The parameters are defined with DataType set to dtString. I have even tried
to put a Size for each of them, but I get to nowhere.

This only happen when one the parameters do not have a value.
Referencia1..2..3 represent telefone numbers. The parameters are converted
to text when they are empty ('') or null. If I set a valur for every
parameters, the sentence works ok. The problem is that I don't want to do
that because the SQL sentence is constructed to be a selective search.

Have you observed a problem like this?.

The environment is:

DataBase = MS SQL 7
Delphi version = 6.2

 

Re:String Parameters get changed to Text


Felix,
    I had the same problem when we first switched to ADO.  I was using a
parameter in my WHERE clause that could potentially be an empty string when
the query was executed.  Lucky for me, we use a custom component for all of
our database transactions so I was able to create a new procedure that was
called before the statement was executed and I simply replaced the parameter
( :ParamName ) with the string "IS NULL" when the paramType was set to a
string type, and this worked for me.  I really didn't want to make the
change and have each dataset go through another procedure just to parse the
sql statement but I could not find any information about how to fix this
anywhere...  I kind of did it as a last resort.  I was seeing the exact same
thing that you are seeing when I ran a trace on the database.  Please let me
know if you find anything else about this or how to fix it...

jake

Quote
"FDS" <Felix.Du...@segundamano.es> wrote in message

news:3e492688@newsgroups.borland.com...
Quote
> The following SQL sentence has 27 parameters. They are in three groups of
9
> parameters having the same name: Referencia1, Referencia2, Referencia3.
> (I know that ADO cannot have parameters with the same name, but for the
> purpose it's OK. Besides, I have tested the same sentence with different
> names for the parameters and the problem was the same).

> SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE
>   (Telefono1 = :Referencia1) and (:Referencia1 is not null) and ('' <>
> :Referencia1)
> UNION
> SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
>   (Telefono2 = :Referencia1) and (:Referencia1 is not null) and ('' <>
> :Referencia1)
> UNION
> SELECT * FROM Edisaco (INDEX (EmailWeb)) WHERE
>   (EmailWeb LIKE :Referencia1 + '%') and (:Referencia1 is not null) and
(''
> <> :Referencia1)
> UNION
> SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE
>   (Telefono1 = :Referencia2) and (:Referencia2 is not null) and ('' <>
> :Referencia2)
> UNION
> SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
>   (Telefono2 = :Referencia2) and (:Referencia2 is not null) and ('' <>
> :Referencia2)
> UNION
> SELECT * FROM Edisaco (INDEX (EmailWeb))  WHERE
>   (EmailWeb LIKE :Referencia2 + '%') and (:Referencia2 is not null) and
(''
> <> :Referencia2)
> UNION
> SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE
>   (Telefono1 = :Referencia3) and (:Referencia3 is not null) and ('' <>
> :Referencia3)
> UNION
> SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
>   (Telefono2 = :Referencia3) and (:Referencia3 is not null) and ('' <>
> :Referencia3)
> UNION
> SELECT * FROM Edisaco (INDEX (EmailWeb))  WHERE
>   (EmailWeb LIKE :Referencia3 + '%') and (:Referencia3 is not null) and
(''
> <> :Referencia3)
> ORDER BY ValidoHasta DESC, Id ASC

> Before was the sentence as it is written in the TADOQuery. Next is the
> result of the sentence when it goes to the database:

> declare @P1 int
> declare @P3 int
> declare @P4 int
> declare @P5 int
> set @P1=NULL
> set @P3=102401
> set @P4=311300
> set @P5=NULL
> exec sp_cursoropen @P1 output, N' SELECT * FROM Edisaco (INDEX
(Telefono1))
> WHERE (Telefono1 = @P1) and (@P2 is not null) and ('''' <> @P3) UNION
SELECT
> * FROM Edisaco (INDEX (Telefono2)) WHERE (Telefono2 = @P4) and (@P5 is not
> null) and ('''' <> @P6) UNION SELECT * FROM Edisaco (INDEX (EmailWeb))
WHERE
> (EmailWeb LIKE @P7 + ''%'') and (@P8 is not null) and ('''' <> @P9) UNION
> SELECT * FROM Edisaco (INDEX (Telefono1)) WHERE (Telefono1 = @P10) and
(@P11
> is not null) and ('''' <> @P12) UNION SELECT * FROM Edisaco (INDEX
> (Telefono2)) WHERE (Telefono2 = @P13) and (@P14 is not null) and ('''' <>
> @P15) UNION SELECT * FROM Edisaco (INDEX (EmailWeb))  WHERE (EmailWeb LIKE
> @P16 + ''%'') and (@P17 is not null) and ('''' <> @P18) UNION SELECT *
FROM
> Edisaco (INDEX (Telefono1)) WHERE (Telefono1 = @P19) and (@P20 is not
null)
> and ('''' <> @P21) UNION SELECT * FROM Edisaco (INDEX (Telefono2)) WHERE
> (Telefono2 = @P22) and (@P23 is not null) and ('''' <> @P24) UNION SELECT
*
> FROM Edisaco (INDEX (EmailWeb))  WHERE (EmailWeb LIKE @P25 + ''%'') and
> (@P26 is not null) and ('''' <> @P27) ORDER BY ValidoHasta DESC, Id ASC
> ', @P3 output, @P4 output, @P5 output, N'@P1 varchar(9),@P2 varchar(9),@P3
> varchar(9),@P4 varchar(9),@P5 varchar(9),@P6 varchar(9),@P7 varchar(9),@P8
> varchar(9),@P9 varchar(9),@P10 text,@P11 text,@P12 text,@P13 text,@P14
> text,@P15 text,@P16 text,@P17 text,@P18 text,@P19 text,@P20 text,@P21
> text,@P22 text,@P23 text,@P24 text,@P25 text,@P26 text,@P27 text',
> '918912525', '918912525', '918912525', '918912525', '918912525',
> '918912525', '918912525', '918912525', '918912525', '', '', '', '', '',
'',
> '', '', '', '', '', '', '', '', '', '', '', ''
> select @P1, @P3, @P4, @P5

> (Sorry for not formatting).

> Note how the parameters are parsed. The parameter number 10 (look for
@P10),
> which corresponds to Referencia2, and all the following are parsed to TEXT
> type instead of VARCHAR, as it is made for Referencia1.

> The parameters are defined with DataType set to dtString. I have even
tried
> to put a Size for each of them, but I get to nowhere.

> This only happen when one the parameters do not have a value.
> Referencia1..2..3 represent telefone numbers. The parameters are converted
> to text when they are empty ('') or null. If I set a valur for every
> parameters, the sentence works ok. The problem is that I don't want to do
> that because the SQL sentence is constructed to be a selective search.

> Have you observed a problem like this?.

> The environment is:

> DataBase = MS SQL 7
> Delphi version = 6.2

Re:String Parameters get changed to Text


I have reported to Borland also.

I've testing arround to see what was happening and I discovered that this
extrange behaviour only happens the first time the query is run.

I've just a small change in my program in order to send a blanc string when
I detect an empty string in the parameter.

Simplu checking:

if (Edit1.Text = '') then

ADOQuery.Parameters.ParamByName ('Aname').Value := ' '

else

ADOQuery.Parameters.ParamByName ('Aname').Value := Edit1.Text;

OK, maybe the solution is not that smart, but it works.

Before getting to that solution, tried everything: nulls, parameter size,
etc. The problem is that my environment is a little bit more comlicated, as
I'm working in Multi Tier (Data Snap) application.

You can, however, try another solution. Think that you can put in an ADO
query more than a simple SQL select sentence (or whatever). So you can
probably handle this problem but doing something like this:

declare @N varchar(xx) -- The size should be the sive of the parameter

set @N=RTRIM(:ParameterName) -- Here is the actual parameter. I need to
remove blanks, what about you?

if (@N='') -- You can even work with the parameter value

set @N=null

select * from MyTable where MyField=@N

The script above makes the job for me. Just try it.

Other Threads