Board index » delphi » not null string fields

not null string fields

Helo,

    I'm using IBXUPD4. This problem was also in FIB and IBX. When i use a
string field and i don't assign any value in edit mode, when i post the
changes i get a value like '|' in the field.

In FIB Juan Evora solves the problem with this code

{
TFIBCustomDataSet.SetFieldData
 in unit FIBDataSet , in line 2558 I have changed

if (Buffer = nil) or
     (Field is TFIBStringField)  and (PChar(Buffer)[0] = #0) then

with only

 if (Buffer = nil)

Now when the value is '' isn't stored a NULL ('' is not NULL)

Quote
}

--
Jess Angel Garca Zarco
Cointec
<coin...@cointec.es>
 

Re:not null string fields


With SQL backends empty string is traditionally stored as NULL.  This is not a
bug.  If you try to insert an empty string into a table using ISQL, or with
Oracle SQL PLus etc it will get inserted as a NULL, not an empty string.  This
is just the way SQL backends treat an empty string normally.

Jesus Angel Garcia Zarco wrote:

Quote

> Helo,

>     I'm using IBXUPD4. This problem was also in FIB and IBX. When i use a
> string field and i don't assign any value in edit mode, when i post the
> changes i get a value like '|' in the field.

> In FIB Juan Evora solves the problem with this code

> {
> TFIBCustomDataSet.SetFieldData
>  in unit FIBDataSet , in line 2558 I have changed

> if (Buffer = nil) or
>      (Field is TFIBStringField)  and (PChar(Buffer)[0] = #0) then

> with only

>  if (Buffer = nil)

> Now when the value is '' isn't stored a NULL ('' is not NULL)
> }

> --
> Jess Angel Garca Zarco
> Cointec
> <coin...@cointec.es>

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
If there is somebody up there could they throw me down a line.  Just a
little helping hand just a little understanding.  Just some answers to the
questions that surround me now.  If there's somebody up there could
they throw me down a line.               (Fish)

Re:not null string fields


I think the problem is with IBX

Ha have a field defined as varchar(10) default '' and not null.

If i insert or edit the field in a record, and i post the record with the
field empty, IBX puts a character less than ''.

If i insert it from ISQL or marathon de default rule fires and i get an
empty string.
if in the trigger before insert i compare the value that saves IBX with
null, is false, and if i do the compare with '' the result is true

( if new.FIELD_STRING = null ) ===> False
( if new.FIELD_STRING < '' ( not ' ' ) ) ===> True

--
Jess Angel Garca Zarco
Cointec
<coin...@cointec.es>
Jeff Overcash (TeamB) <overc...@onramp.net> escribi en el mensaje de
noticias 38D8D80E.77823...@onramp.net...
| With SQL backends empty string is traditionally stored as NULL.  This is
not a
| bug.  If you try to insert an empty string into a table using ISQL, or
with
| Oracle SQL PLus etc it will get inserted as a NULL, not an empty string.
This
| is just the way SQL backends treat an empty string normally.
|
| Jesus Angel Garcia Zarco wrote:
| >
| > Helo,
| >
| >     I'm using IBXUPD4. This problem was also in FIB and IBX. When i use
a
| > string field and i don't assign any value in edit mode, when i post the
| > changes i get a value like '|' in the field.
| >
| > In FIB Juan Evora solves the problem with this code
| >
| > {
| > TFIBCustomDataSet.SetFieldData
| >  in unit FIBDataSet , in line 2558 I have changed
| >
| > if (Buffer = nil) or
| >      (Field is TFIBStringField)  and (PChar(Buffer)[0] = #0) then
| >
| > with only
| >
| >  if (Buffer = nil)
| >
| > Now when the value is '' isn't stored a NULL ('' is not NULL)
| > }
| >
| > --
| > Jess Angel Garca Zarco
| > Cointec
| > <coin...@cointec.es>
|
| --
| Jeff Overcash (TeamB)
|       (Please do not email me directly unless  asked. Thank You)
| If there is somebody up there could they throw me down a line.  Just a
| little helping hand just a little understanding.  Just some answers to the
| questions that surround me now.  If there's somebody up there could
| they throw me down a line.               (Fish)

Re:not null string fields


Jeff Overcash (TeamB) <overc...@onramp.net> escribi en el mensaje de
noticias 38D8D80E.77823...@onramp.net...

Quote
> With SQL backends empty string is traditionally stored as NULL.  This is
not a
> bug.  If you try to insert an empty string into a table using ISQL, or
with
> Oracle SQL PLus etc it will get inserted as a NULL, not an empty string.
This
> is just the way SQL backends treat an empty string normally.

IB respects the meaning of NULL and EMPTY string. Oracle treats both the
same (Look at Oracle documentation and you will find explain to this
behaviuor).

Re:not null string fields


Jesus Angel Garcia Zarco wrote:

Quote

> I think the problem is with IBX

> Ha have a field defined as varchar(10) default '' and not null.

Yes, and the default will be used if you do not pass any value at all during the
insert statement.

Quote
> If i insert or edit the field in a record, and i post the record with the
> field empty, IBX puts a character less than ''.

All three IBX TDataset decendents all treat the empty string as null.  This is
consistant with how the BDE handles it.  The difference is that the BDE will
generate the Insert statement on only those columns that have non null values.
IBX's TIQuery and TIBDataset you have to write the Insert statement it is not
generated for you.  If you do an insert statement that includes your columns
with a default value then the normal behavior of translating empty string to
null will happen and Interbase will post the null since a value was passed.  

Remember the default only happens on the DB side when you don't pass any value
including null.  You need to write your SQL based on what you want to pass in
the Insert statement since you are writing that statement already.  If you try
this with the BDE and use an UpdateSQL component and tell the BDE the insert
statement and that statement includes the column with the default value you get
the exact same behavior as IBX (ie the '' is passed as null with the eror
message resulting).

Quote
> If i insert it from ISQL or marathon de default rule fires and i get an
> empty string.

Are you inserting a NULL value in the column, or leaving the column out of the
insert statement?

Under ISQL I just tried this against employee.gdb

  Insert into department (dept_no, department, budget) values (500, 'Test',
NULL)

and it raises an error that budget can't be null.  But if I do

  Insert into department (dept_no, department) values (500, 'Test')

that works.  The default value for budget is filled out.  If I try the exact
same statments as the InsertSQL for TIBDataset it works identically.

I also tried this with IBO and got the same behavior.  A blank edit field (empty
string) is interpreted as NULL and passed in the TIBOQuery to the Insert
statement I gave (which included the column) and I get the NULL value error
message.  Drop the column from the insert statement and it inserts with the
defaults being filled out on the server side just like IBX.

Quote
> if in the trigger before insert i compare the value that saves IBX with
> null, is false, and if i do the compare with '' the result is true

> ( if new.FIELD_STRING = null ) ===> False
> ( if new.FIELD_STRING < '' ( not ' ' ) ) ===> True

> --
> Jess Angel Garca Zarco
> Cointec
> <coin...@cointec.es>

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
If there is somebody up there could they throw me down a line.  Just a
little helping hand just a little understanding.  Just some answers to the
questions that surround me now.  If there's somebody up there could
they throw me down a line.               (Fish)

Re:not null string fields


But for Delphi since version 1, TStringfield = '' will end up getting mapped to
NULL when it internally sets the params.  This is consistant with the BDE way
and also IBO.  If you create a table like

create table test
  ( pkey varchar(5) not null)
and then write an insert query like

Insert into Test values (:pkey)

and assign it like

  IBQuery1.ParamByName('pkey').AsString := '';
  IBQuery1.ExecSQL;

IBX inserts '', not NULL.  Were are only talking about '' getting mapped to NULL
when the params are being pulled internally from the fields.  To suddenly start
mapping this to '' would break almost everyones code and would be different than
the other two major ways at getting at Interbase data.  If you are setting the
params then IBX passes whatever you set.  The only difference is when the BDE is
actually generating the InsertSQL then it will only generate non NULL columns in
the SQL.  IBX does not autogenerate InsertSQL and relies on your SQL.

Sergio Enrique Samayoa Solis wrote:

Quote

> Jeff Overcash (TeamB) <overc...@onramp.net> escribi en el mensaje de
> noticias 38D8D80E.77823...@onramp.net...
> > With SQL backends empty string is traditionally stored as NULL.  This is
> not a
> > bug.  If you try to insert an empty string into a table using ISQL, or
> with
> > Oracle SQL PLus etc it will get inserted as a NULL, not an empty string.
> This
> > is just the way SQL backends treat an empty string normally.

> IB respects the meaning of NULL and EMPTY string. Oracle treats both the
> same (Look at Oracle documentation and you will find explain to this
> behaviuor).

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
If there is somebody up there could they throw me down a line.  Just a
little helping hand just a little understanding.  Just some answers to the
questions that surround me now.  If there's somebody up there could
they throw me down a line.               (Fish)

Re:not null string fields


    Ok, but i have lot of fields that the user can leave in blank. As you
say i have to look the values that the user has leave in blank and
dinamically create the SQL insert or edit statement with the fields that are
not blank. I think is most interesting write an only Statement and that if
the user leave the value in blank, or i put '' in the field, IBX puts '' in
the field not null value.

--
Jess Angel Garca Zarco
Cointec
<coin...@cointec.es>
Jeff Overcash (TeamB) <overc...@onramp.net> escribi en el mensaje de
noticias 38DAE282.361FF...@onramp.net...
| Jesus Angel Garcia Zarco wrote:
| >
| > I think the problem is with IBX
| >
| > Ha have a field defined as varchar(10) default '' and not null.
| >
|
| Yes, and the default will be used if you do not pass any value at all
during the
| insert statement.
|
| > If i insert or edit the field in a record, and i post the record with
the
| > field empty, IBX puts a character less than ''.
| >
|
| All three IBX TDataset decendents all treat the empty string as null.
This is
| consistant with how the BDE handles it.  The difference is that the BDE
will
| generate the Insert statement on only those columns that have non null
values.
| IBX's TIQuery and TIBDataset you have to write the Insert statement it is
not
| generated for you.  If you do an insert statement that includes your
columns
| with a default value then the normal behavior of translating empty string
to
| null will happen and Interbase will post the null since a value was
passed.
|
| Remember the default only happens on the DB side when you don't pass any
value
| including null.  You need to write your SQL based on what you want to pass
in
| the Insert statement since you are writing that statement already.  If you
try
| this with the BDE and use an UpdateSQL component and tell the BDE the
insert
| statement and that statement includes the column with the default value
you get
| the exact same behavior as IBX (ie the '' is passed as null with the eror
| message resulting).
|
| > If i insert it from ISQL or marathon de default rule fires and i get an
| > empty string.
| >
|
| Are you inserting a NULL value in the column, or leaving the column out of
the
| insert statement?
|
| Under ISQL I just tried this against employee.gdb
|
|   Insert into department (dept_no, department, budget) values (500,
'Test',
| NULL)
|
| and it raises an error that budget can't be null.  But if I do
|
|   Insert into department (dept_no, department) values (500, 'Test')
|
| that works.  The default value for budget is filled out.  If I try the
exact
| same statments as the InsertSQL for TIBDataset it works identically.
|
| I also tried this with IBO and got the same behavior.  A blank edit field
(empty
| string) is interpreted as NULL and passed in the TIBOQuery to the Insert
| statement I gave (which included the column) and I get the NULL value
error
| message.  Drop the column from the insert statement and it inserts with
the
| defaults being filled out on the server side just like IBX.
|
| > if in the trigger before insert i compare the value that saves IBX with
| > null, is false, and if i do the compare with '' the result is true
| >
| > ( if new.FIELD_STRING = null ) ===> False
| > ( if new.FIELD_STRING < '' ( not ' ' ) ) ===> True
| >
| > --
| > Jess Angel Garca Zarco
| > Cointec
| > <coin...@cointec.es>
|
|
| --
| Jeff Overcash (TeamB)
|       (Please do not email me directly unless  asked. Thank You)
| If there is somebody up there could they throw me down a line.  Just a
| little helping hand just a little understanding.  Just some answers to the
| questions that surround me now.  If there's somebody up there could
| they throw me down a line.               (Fish)

Other Threads