On Sat, Sep 26, 2009 at 2:23 AM, Alexey Pechnikov <pechni...@mobigroup.ru> wrote: > Hello! > > On Friday 25 September 2009 22:29:55 Tom Jackson wrote: >> Personally I would use [string is double -strict] and quote anything > > Tcl and PostgreSQL types are not equal. > > tclsh8.5 [/tmp]string is double -strict 9999999999 > 1 > template1=# create temp table test(value integer); > CREATE TABLE > template1=# insert into test (999999999999999999999); > template1=# insert into test values (9999999999); > ERROR: integer overflow >
How does '9999999999999999999'::int solve the problem? (Hint: it doesn't) If a value is numeric it doesn't need surrounding quotes, the test [string is -strict double $string] is only used to check if the value looks like a number, there is no way it can guarantee the number is valid for the particular query (the integer may be an id, which might need to satisfy a referential constraint, for instance). > So you send to PostgreSQL overflowed values without strict typing > which are potentially dangerous. Defaults type conversions in > PostgreSQL depends of version and so application behaviour is > undefined with other PostgreSQL version. If you need strict type checking in Tcl, try out the type code in tWSDL. It has very good numeric type definition and validation support. Basic examples: http://www.junom.com/gitweb/gitweb.perl?p=twsdl.git;a=blob;f=packages/wsdl/ns/ns-xsd.tcl;h=1482a Some comparisons with Tcl int: http://junom.com/document/twt/view/www/decimal2.tcl code: http://junom.com/document/twt/view/www/decimal2.tcl~ > Database and application can be placed on hosts with different > arch (32 and 64 bit). In this case you can't check integer type size > in application because database can have the different type size. > > On Friday 25 September 2009 22:29:55 Tom Jackson wrote: >> What >> is better is a type system which handles each type in a separate API >> and rejects unfound types. > > The better is mapping in the driver and DBMS with dynamic typing. As > example for SQLite (the forms $value and :value are equal): > > db eval {create table test(value int)} > db eval {insert into test (value) values (999999999999999999999)} > insert into test (value) values (9999999999.999999) > > set value 1 > db eval {insert into test (value) values ($value)} > set value {The test value with '} > db eval {insert into test (value) values (:value)} > > sqlite> select typeof(value),value from test; > real|1.0e+21 > real|10000000000.0 > integer|1 > text|The test value with ' > > And strict field typing can be released by constraints or triggers or > collations. Dynamic typing? My database tables/columns don't change types. This last example of yours is strange. Why would you have multiple different types in one column? Why use a database at all? tom jackson -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <lists...@listserv.aol.com> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
