DBI Question

Jocko has asked for the wisdom of the Perl Monks concerning the following question:

I have a value that I'm assigning to a variable that is the result of timelocal($sec,$min,$hour,$day,$month,$year). If I print the variable I get the correct value in seconds since 1970. However if I attempt to use an Insert Into statement using DBI, the value is increased by 16 seconds. If I directly assign the variable the same value (instead of getting it from timelocal) it does not change. More strangely, if I enclose the variable derived from timelocal in double quotes in the bind_param statement it does not change and inserts the correct value. I'm using Informix. So I've gotten it to work but I don't understand why I'd have to enclose an integer variable in double quotes if I've received the value from timelocal. If anybody can enlighten me that would be great. I've researched this all day but am stumped. Thanks, John

Comment on DBI Question

Replies are listed 'Best First'.
Re: DBI Question by lo_tech (Scribe) on Aug 30, 2001 at 08:28 UTC
It's been my experience that the DBD will attempt to convert (mangle?) the DTS when attempting an insert in Informix if the formats are not defined in exactly the same format, for example if the field is defined in the table as 'year to second' vs. 'year to fraction' it will still accept, but YMMV. I've been able to get past some of these pitfalls by 1) checking the field format via dbaccess, and making sure my discrete values are valid for that field definition, or 2) doing an insert using the Informix keyword CURRENT eg. 'INSERT INTO foo_table (field1,field2) VALUES (?, CURRENT) Note that the current time on the host running perl might not be the db servers current time. We run everything UTC so sometimes I forget others dont. I've also had success selecting from the database using EXTEND(field2, year to second). While I know that extending the field doesn't help your question, it has saved me a lot of regexes when selecting elements of the DTS from the db. Onward!	[reply]
Re (tilly) 1: DBI Question by tilly (Archbishop) on Aug 30, 2001 at 05:01 UTC
At a guess that is a bug at the C level in DBD::Informix. One reason why the quotes could make a difference is that you are forcing the internal representation of the scalar into string form, and so the driver might wind up using a different conversion function on the number.	[reply]