Board index » delphi » Passing Null in a parametrized Query

lbell...@dsuper.net (Luc Bellego)

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Passing Null in a parametrized Query

Is there a way to pass Null in a parametrized Query ?
We have to do that without reconstruct the SQL (and the prepare) and
sometimes we have to put the Null value for a field
(query like select * from Table where FieldValue = :FieldValue
FieldValue can have a value or Null).

Thanks,

Luc Bellego
lbell...@gbinaire.com
Gestion Binaire Inc.

 
squi

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing Null in a parametrized Query


depending on which SQL server you are talking about - the answer is
probably no.  That is because when specifying null, you often have to
use IS NULL and not = NULL.  = NULL will usually give you an error
because NULL is not a valid integer or string value.  The only thing you
might be able to do is to an = ''

This will work on some systems but not others

khariV

Quote
Luc Bellego wrote:
> Is there a way to pass Null in a parametrized Query ?
> We have to do that without reconstruct the SQL (and the prepare) and
> sometimes we have to put the Null value for a field
> (query like select * from Table where FieldValue = :FieldValue
> FieldValue can have a value or Null).

> Thanks,

> Luc Bellego
> lbell...@gbinaire.com
> Gestion Binaire Inc.

Luc Belle

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing Null in a parametrized Query


Thanks, we are trying that.
Quote
On Wed, 24 Feb 1999 16:51:31 GMT, squid <sq...@nospam.com> wrote:
>depending on which SQL server you are talking about - the answer is
>probably no.  That is because when specifying null, you often have to
>use IS NULL and not = NULL.  = NULL will usually give you an error
>because NULL is not a valid integer or string value.  The only thing you
>might be able to do is to an = ''

>This will work on some systems but not others

>khariV

>Luc Bellego wrote:

>> Is there a way to pass Null in a parametrized Query ?
>> We have to do that without reconstruct the SQL (and the prepare) and
>> sometimes we have to put the Null value for a field
>> (query like select * from Table where FieldValue = :FieldValue
>> FieldValue can have a value or Null).

>> Thanks,

>> Luc Bellego
>> lbell...@gbinaire.com
>> Gestion Binaire Inc.

Randy Fulle

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing Null in a parametrized Query


squid <sq...@nospam.com> wrote in article <36D42E0C.5419...@nospam.com>...

Quote
> depending on which SQL server you are talking about - the answer is
> probably no.  That is because when specifying null, you often have to
> use IS NULL and not = NULL.  = NULL will usually give you an error
> because NULL is not a valid integer or string value.  The only thing you
> might be able to do is to an = ''

This is only part of the issue.  The primary issue is that a parameter
defined as a string set to NULL is not the same as the SQL key word NULL.
The string parameter will be interpreted as 'NULL'.  

In SQL Anywhere I've never gotten this to work.  Usually we're trying
something like
INSERT INTO MYTABLE Values(:ITEM1, :ITEM2).
Sometimes a value might be need to intentionally be a NULL.

Randy Fuller
R Fuller Systems

Lieven Bogaer

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Passing Null in a parametrized Query


I've succesfully inserted null values through parameterized querys.
When you fill in your parameters using params[...] or ParamByName,
use .AsVariant := null.  This correctly inserted a null value in my app.
(Based on SQL Server 7 anyway).  The reason to use .AsVariant is
that only Variants allow a null assigned to it.

Hope this helps.

Lieven

Quote
Luc Bellego <lbell...@dsuper.net> wrote in message

news:36d52981.10775151@wingate...
Quote
>Is there a way to pass Null in a parametrized Query ?
>We have to do that without reconstruct the SQL (and the prepare) and
>sometimes we have to put the Null value for a field
>(query like select * from Table where FieldValue = :FieldValue
>FieldValue can have a value or Null).

>Thanks,

>Luc Bellego
>lbell...@gbinaire.com
>Gestion Binaire Inc.

Other Threads

1. Passing Null Values to Query's Parameters

2. Passing Null values in a query?

3. How to set Null values using parametrized SQL

4. Parametrized setting of NULL

5. Parametrized query against MS Access

6. ADO and Parametrized queries

7. Problems running parametrized query with LIKE operator against SQL server nvarchar datatype

8. Preparing/unpreparing parametrized query

9. Oracle:How to do parametrized query?

10. Q: Parametrized queries and explicit transaction: bug