You're 90% right. I had not noticed this behavior until now (with 7
years of 4GL experience) how embarrassing.
It is interesting however that insert #9 below will actually insert a
NULL value into the table, I had always assumed it would be the empty
string.
So it looks like 4GL is detecting that the string is empty in #7 and
actually inserting a NULL value, unlike DBD::Informix where you would
get " " as the value.
So I guess the answer to my question would be that if I wanted to
emulate what was being done in 4GL all that I would have to do is first
test for the empty string, and if it's empty simply insert a NULL
instead.
But is there an easier way? I'd hate to check every field, every time,
to see if it's empty and actually set it to NULL or undef. Any
suggestions?
Here is an example in 4GL:
database stores
main
define scratch char(10)
create temp table my_table (my_field char(10));
insert into my_table values(NULL); #1
insert into my_table values("a"); #2
insert into my_table values("a "); #3
insert into my_table values(" a "); #4
insert into my_table values(""); #5
insert into my_table values(" "); #6
insert into my_table values(" "); #7
let sctatch = NULL
insert into my_table values(scratch); #8
let scratch = ""
insert into my_table values(scratch); #9
let scratch = " "
insert into my_table values(scratch); #10
let scratch = " "
insert into my_table values(scratch); #11
# Let's check to see if any value is null
select count(*) into scratch from my_table where my_field is null
display scratch
unload to "mytable.unl" select * from my_table
end main
Yielding this output: 3
Yielding the following mytable.unl file:
|
a|
a|
a|
|
|
|
|
|
|
|
Ron
> -----Original Message-----
> From: Douglas Wilson [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, February 14, 2002 12:49 PM
> To: Ron MacNeil; Dbi-Users
> Subject: Re: Can't insert an empty string with DBD::Informix? Bug?
>
>
>
> ----- Original Message -----
> From: "Ron MacNeil" <[EMAIL PROTECTED]>
> To: "Dbi-Users" <[EMAIL PROTECTED]>
> Sent: Thursday, February 14, 2002 7:56 AM
> Subject: Can't insert an empty string with DBD::Informix? Bug?
>
>
> > Hi all, I'm having difficulty inserting empty strings into
> a CHAR(10)
> > field in my database.
> >
> > I'm trying to insert an empty string ('') into a database
> field. But in
> > every case I get a space (' ') as the value in the table. Using
> > DBD::Informix 1.00.PC1 with DBI 1.20.
>
> You would get the same thing if you inserted using dbaccess, ESQL/C,
> or 4GL. 'Empty' (but non-null) CHAR fields in Informix (&
> other databases?)
> are actually all spaces. You cannot store an actual 'empty
> string' unless
> you use a VARCHAR field.
>
> HTH,
> Douglas Wilson
>
