[Zope-dev] Calendar tag and MS-Access Database fails on date type

25 May 1999

      I'm using the contributed Calendar  product and trying to lookup records from an 
MS-Access database, but I'm having trouble getting calendar's date variable to 
work correctly with the SQL Method.

I've tried all iterations in the SQL method, having the target date be passed as a 
type 'date', or type 'string', and/or using PrincipiaTime() (when passed as a 
string), No matter which way I try, I either get all records in my database, or an 
error in PrincipiaTime or an error in DA.py.

I even managed to corrupt an SQL method some how such that all operations on 
it fail with Error, exceptions.AttributeError: self 

(weird, eh).

So that particular method got renamed to *.old and I moved on.

Anyway, has anyone gotten Calendar to work with an SQL Method?

My curent test form looks like this:

<!--#var standard_html_header-->
<!--#calendar date='2/7/99' mode='week'-->
<!--#call "REQUEST.set('date',getCalendar('datestr'))"-->
<!--#var date--><br>
<!--#in CalendarLookup-->
<!--#var Description-->
<!--#/in-->
<!--#/calendar-->
<!--#var standard_html_footer-->

You can see that I'm trying to force "date" to be a string type when passed to 
CalendarLookup (that's just this particular iteration).

One comment, although mode='week', Calendar still shows it as a month view to 
start with when I "view" it.

My current SQL Method CalendarLookup is like this (yes, its complicated):

Args: identifier:string="" city:string="" county:list:string="" region:string="" 
unapproved:string="" date:string=""

select *
from [Calendar]
<!--#if identifier-->
<!--#sqlgroup where required-->
<!--#sqltest identifier column=Identifier type=string-->
<!--#/sqlgroup-->
<!--#else-->
<!--#sqlgroup where-->
   <!--#if region-->
       <!--#sqltest region column=Region type=int optional-->
   <!--#elif county-->
      <!--#if "_.len(county[0]) > 0"-->
      <!--#sqltest county column=County type=string multiple optional--><!--#endif-
->
   <!--#endif-->
<!--#and--><!--#if city-->
  <!--#sqltest city column=City type=string multiple optional--><!--#endif-->
<!--#and-->
  <!--#if date-->
  DateValue(StartDateTime) >= DateValue(<!--#var date-->)<!--#endif-->
<!--#and-->
  <!--#if "date"-->
  DateValue(EndDateTime) <= DateValue(<!--#var date-->)<!--#endif-->
<!--#/sqlgroup-->
<!--#endif-->
order by StartDateTime asc

DateValue is an MS-Access SQL function that converts the passed in arg to 
just a date serial value. It can take a string or a date. If the date has a time, it 
removes it.

For some reason, this particular setup returns all records in the database for 
EVERY DAY.

If I change args to date:date, and DateValue(<!--#var date-->) to just <!--#var date-
->,
than the SQL Method returns no results, which is also a failure.

If I try changing date to type date, and change the SQL lookup to be.

<!--#and-->
  <!--#if date-->
  StartDateTime >= <!--#var date--><!--#endif-->
<!--#and-->
  <!--#if "date"-->
  EndDateTime <= <!--#var "PrincipiaTime(date+1)"--><!--#endif-->

I get this:

Error, exceptions.TypeError: __div__ nor __rdiv__ defined for these 
operands 

Any ideas? 

Brad Clements,                bkc@murkworks.com   (315)268-1000
http://www.murkworks.com                          (315)268-9812 Fax
netmeeting: ils://ils.murkworks.com               ICQ: 14856937