[Zope-DB] Types between zsql methods and postgresql types

[Jim Penny]

On Tue, Sep 03, 2002 at 11:33:59PM -0400, Russell Hires wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hello all,
> 
> I'm doing a project for school, and rather than use Access, I thought I'd try 
> this stuff out in zope. So far, I'm basically following the examples in the 
> zope book about how to get the db to talk to zope and so forth. I'm okay 
> there...
> 
> It's the part where I have to identify the types: I've got postgresql 
> identifying a column's datatype as "money", but zope's zsql methods don't 
> have this type, nor do they seem to have the "boolean" type. The error 
> message I got says: [1]. I don't know how to cast one type to another, or 
> even where I'd do it. Anyone have any pointers on how to do this?
> 
> I appreciate your assistance. 
> 
> Russell
> 
> [1]
> Error, psycopg.ProgrammingError: ERROR: column "weekly_rate" is of type 
> 'money' but expression is of type 'integer' You will need to rewrite or cast 
> the expression insert into condo_tbl (unit_number, bedrooms, bathrooms, 
> sleeps, powder_room, linens, weekly_rate, owner_id) values ('100', 3, 1, 5, 
> 'yes', 'no', 500, 'RH01') 
> SQL used:
>  
> insert into condo_tbl (unit_number, bedrooms, bathrooms, sleeps, powder_room, 
> linens, weekly_rate, owner_id) 
> values
> ('100',
> 3,
> 1,
> 5,
> 'yes',
> 'no',
> 500,
> 'RH01')

Use an explicit cast.  E.g., 
insert into condo_tbl (unit_number, bedrooms, bathrooms, sleeps,
powder_room, linens, weekly_rate, owner_id) values
('100', 3, 1, 5, 
'yes'::boolean, 'no'::boolean, 500::money, 'RH01')

Notes:

Money has been deprecated in postgresql.  See section 3.2 of the
documentation.  I do not know if yes/no will cast to boolean, I always
use 1/0.

You will probably be using variables in the actual application.  Then it
might look like (variable names may be same as, or different from SQL
column names, both practices are shown below):

insert into condo_tbl (unit_number, bedrooms, bathrooms, sleeps,
powder_room, linens, weekly_rate, owner_id) values
(<dtml-sqlvar unit_number type=string>, 
<dtml-sqlvar bedrooms type=int>,
<dtml-sqlvvar baths type=int>, 
<dtml-sqlvar sleeps type=string>::boolean,
<dtml-sqlvar powder_room type=string>::boolean, 
<dtml-sqlvar linens type=string>::boolean, 
<dtml-sqlvar rate type=string>::money, 
<dtml-sqlvar owner type=string>)

Be sure to use sqlvar, or otherwise be sure that there are no SQL
Injection problems. 

Jim Penny

> 
> - -- 
> Linux -- the OS for the Renaissance Man 
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.0.6 (GNU/Linux)
> Comment: For info see http://www.gnupg.org
> 
> iD8DBQE9dX8nAqKGrvVshJQRAsy8AKCv71wFxzrF3TEBelvkauoeFyqEswCfWyyW
> Ym7VP3Apm0ZQ+0LB76OgkG4=
> =RrIM
> -----END PGP SIGNATURE-----
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db
>