Re: #PERSONAL# Reg: date going as 01/01/0001

Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)com> wrote:

> Now my problem is that my bind variables are of string type and
> when the get a NULL value is converts it into an empty string in
> C++ programming.

> Example:
> insert into check_date values
> (to_date(:h1,'yyyymmddhh24miss'),
> 1,
> to_date(:h2,'yyyymmddhh24miss'));
>
> now h1 and h2 are of string datatype in my c++ program.
>
> If no value is there, it is passed on as a empty string into my
> query.
>
> How can I handle this to enter NULL values?

Well, arguably the best solution is to use a tool chain that
doesn't conflate any particular "magic value" with NULL. Where
that can't be done, you might want to look at the SQL standard's
NULLIF() construct.

insert into check_date values
(to_date(nullif(:h1, ''),'yyyymmddhh24miss'),
1,
to_date(nullif(:h2, ''),'yyyymmddhh24miss'));

If the two arguments to nullif() are equal, it returns NULL;
otherwise it returns the first argument.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company