RES: [Zope-DB] Insert DATE filed in Firebird with sqlvar

[Valmir Pimenta]

Thank you Ian.

Unfortunately that is not the answer but after digging the Firebird
documentation i found that i should use 
a CAST function to make things hapen. So the query will becom something
like this...

     insert into MEDIAITM
       values(
         <dtml-sqlvar cd_media    type="int">,
         CAST( <dtml-sqlvar dt_purchase type="string"> AS DATE)
       )

Witch does the job.

I Believe this is a weaknes in the design of the DA, as it makes the
code less portable, requiring you to review all your functions as you
move from one DB engine to another (by switching DA's).
Maibe someone more gifted as a programer may do something about this.

Regards,

Valmir Pimenta

-----Mensagem original-----
De: Ian Bicking [mailto:ianb at colorstudy.com] 
Enviada em: quinta-feira, 20 de novembro de 2003 17:24
Para: valmir at leme.com.br
Cc: zope-db at zope.org
Assunto: Re: [Zope-DB] Insert DATE filed in Firebird with sqlvar


On Nov 20, 2003, at 2:17 PM, Valmir Pimenta wrote:
> Hi all,
>
> I'am a novice with Zope and to be honest, not very confortble with 
> programming too, since i quit doing programs for living a long time 
> ago, and things change a lot in this area with time...
>
> I've browsed this list and found this subject came by so many times
> that
> i'am amazed it is not covered in zope book yet. Anyway since i could 
> not
> find the answer in a idiot proof maner anywhere, here it goes...
>
> Currently i'am trying to make a simple insert form in a SQL Table and 
> i can´t figure how to insert a DATE field from a form into the 
> Database. I´ve seen in the list that i should use the string type, but

> it seens that there is some kind of type casting problem between the 
> Database and the DA.
>
> I currently use Firebird 1.0.3 with the gvibDA 0.9.6 and when i use
> this
> ...
>
>     insert into MEDIAITM
>       values(
>         <dtml-sqlvar cd_media    type="int">,
>         <dtml-sqlvar dt_purchase type="string">
>       )
>
> I get this nasty message...
>
> Error, Products.gvibDA.gvib.gvibExceptions.DataError: - conversion
> error
> from string ""
>
> And this is the generated SQL...
>
>      insert into MEDIAITM
>        values(
>      100,
>      '20/11/2003'
>      )
>
> If someone could please put some light in my path...

You can use an expression in a dtml-sqlvar, so you could potentially do 
something like <dtml-sqlvar "dt_purchase.strftime('%d/%m/%Y')" 
type=string>, thus making it explicit how you wanted to format your 
string.  (I'm sure that formatting isn't what you want -- you'll have 
to look at the way Firebird wants dates formatted, and there's a 
reference for the format string for strftime in the Python time module 
documentation)

You can also use <dtml-var> in a SQL method too, if you want to format 
something in a way that dtml-sqlvar doesn't allow.

--
Ian Bicking | ianb at colorstudy.com | http://blog.ianbicking.org