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

[David Wilbur]

hi,  

i was hoping that someone could describe to me what is the right way to 
deal with null and zsql methods.  i have come up with the following 
_EXAMPLE_ below that works... but, this table only has one field that 
allows null and i'm sitting here saying to my self... wow, what if it 
had like 10 permutations of nulls?  i just cant envision that i would 
have to write 10 zsql methods.  i figure that i must be missing 
something in the rather sparse documentation on zsql...

is  'None' the normal way that zsql indicates null when you do a select 
[colums] from [table name]?  How do you distinguish between the string 
'None' and when it is indicating that the value is null?

also, feel free to rip this apart and tell me a better way to do this 
whole example cause i really think that update issues are something that 
is for the most part totally ignored in the documentation and that the 
documentation needs a good example.

dave
ps:  pls don't point me at the two forms packages, i can't use them 
cause the minimalistic browser that i am having to conform to barfs when 
it sees the forms generated by it.  ie: < html 1.0, no cookies, 
javascript, tables, frames, etc, etc, etc...  also, i would like to 
_understand_ what is a good way to do updates.

if  updateNeedAction just does a <dtml-call updateNeed> with no <dtml-if 
quantity> then updateNeed gives this error:

Error Type: ProgrammingError
Error Value: ERROR: ExecReplace: rejected due to CHECK constraint integrity


however if you put in updateNeedAction

<dtml-if quantity>
   <dtml-call updateNeed>
<dtml-else>
   <dtml-call updateNeedWithoutQuantity>
</dtml-if>

then every thing works fine


--- postgres table definitions

create table need_type (type varchar(32)primary key);

create table need (
   sid integer default nextval('need_sid_seq') primary key,
   name varchar(64),
   quantity integer,
   need_type varchar(32),
   constraint integrity check (name is not null and (quantity > 0 or 
quantity is null)),
   constraint need_type_exists foreign key (need_type) references 
need_type (type) on update cascade
);


---  updateNeedIndex

<dtml-var need_html_header>
<h2><dtml-var title></h2>
<dtml-in getNeed>
<p>
<a href=
"updateNeedForm?need_sid=<dtml-var sid fmt="%d">&need_name=<dtml-var 
name>&need_quantity=<dtml-var quantity>&need_type=<dtml-var need_type>"
 >
<dtml-var name></a>
</dtml-in>
<dtml-var need_html_footer>

---  updateNeedForm ---

<dtml-var need_html_header>
<h2><dtml-var title></h2>
<form action="updateNeedAction">
<input type="hidden" name="sid" value="<dtml-var need_sid>">
Name: <input type="text" name="name" value="<dtml-var need_name>">
<br>
Type:
<select name="need_type">
   <dtml-in getNeedType>
      <option value="<dtml-var type>"><dtml-var type></option>
   </dtml-in>
</select>
<br>
<dtml-if expr="need_quantity == 'None'">
   Quantity: <input type="text" name="quantity">
<dtml-else>
   Quantity: <input type="text" name="quantity" value="<dtml-var 
need_quantity>">
</dtml-if>
<br>
<input type="submit" value=" update ">
</form>
<dtml-var need_html_footer>

---  updateNeedAction

<dtml-var need_html_header>
<dtml-if quantity>
   <dtml-call updateNeed>
<dtml-else>
   <dtml-call updateNeedWithoutQuantity>
</dtml-if>
<dtml-call expr="RESPONSE.redirect('updateNeedIndex')">
<dtml-var need_html_footer>

--- ZSQL Method  getNeed

select * from need

--- ZSQL Method  getNeedType

select * from need_type order by type

--- ZSQL Method  updateNeed(sid,name,need_type,quantity)

update
   need
set
   name = <dtml-sqlvar name type="string">,
   quantity = <dtml-sqlvar quantity type="string">,
   need_type = <dtml-sqlvar need_type type="string">
where
   sid = <dtml-sqlvar sid type="string">

--- ZSQL Method  updateNeedWithoutQuantity(sid,name,need_type)

update
   need
set
   name = <dtml-sqlvar name type="string">,
   quantity = NULL,
   need_type = <dtml-sqlvar need_type type="string">
where
   sid = <dtml-sqlvar sid type="string">