[Zope-DB] ZSQL and update, what is the correct way to deal with nulls?

[Matthew T. Kromer]

David Wilbur wrote:

>
> ah... heh, sorry, i'm a python n00b... so is what people normally do 
> is when the form submits that they check all variables to see if they 
> are "" before they pass them on to the zsql method and convert them to 
> 'None'?
>

Most of the time they will end up inserting a zero length string into 
their databases.  Some databases used to interpret zero length strings 
as NULL columns, but not all do.

Generally an update will be conditional e.g.


INSERT INTO table (

    <dtml-if COLUMN1>column1</dtml-if>
) values (
    <dtml-if COLUMN1><dtml-sqlvar COLUMN1 type="string"></dtml-if>
)

etc... (with variations to handle all the input columns).  It isnt QUITE 
that easy, because of the need to put commas into the SQL statement.  I 
think at one point in time I made a dtml-comma operator tag to assist in 
the process, but I never did anything with it and I don't have it anymore.

Columns then have default values, such that if you never insert into 
that column, the value stays as the default (such as NULL).

-- 
Matt Kromer
Zope Corporation  http://www.zope.com/