Following hours of trying painfully to solve the problem from my previous post, teejay on #london.pm suggests trying to update the values manually. It demonstrates clearly that the problem is SQLite trying to be smart, not Perl (and that I should have tried it earlier).
Thanks a lot, whoever it was days ago in #perl who tried to come off as knowledgeable by saying "How do you know it's not Perl doing it", when I was asking about SQLite's behavior, because I was right in the first place. Now I've spent all my coding time in something like four days wasting my time trying to force Perl to do something when it was probably already doing it.
So. How on Earth do I get SQLite to behave? Anyone?
Defaults?
phillup on 2005-04-14T18:54:40
I just scanned thru the
documentation for column types.
First, this looks like it may be affecting you.
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored.
Then, a few more lines down...
2.1 Determination Of Column Affinity
...
4. Otherwise, the affinity is NUMERIC.
So, I'm wondering if the column type is defaulting to numeric... and thus stripping the leading zero.
SQL_BLOB
marcel on 2005-04-14T19:15:55
I've had the same problem. Although the field was of type TEXT, when I wanted to insert international phone number-style data (like '+4369912345678'), the '+' got cut off.
The solution was to bind the data, not with SQL_VARCHAR, but with SQL_BLOB:
$sth->bind_param(3, $data, SQL_BLOB);
You are a superstar
hex on 2005-04-16T13:20:20
Thank you. I owe you an ice cold beer or other beverage of your choice!
Similar experience, DBI+SQLite3 aggrave problem
lukhnos on 2005-06-02T22:07:51
I had a similar experience, and it turned out that use of bind_param and bind_col is the only solution, as a previous reply suggested. I found Perl was also part of the blame -- if a string can be treated as a number, DBI did indeed insert it as a number. Unfortunately, SQLite3 does the same thing. That "type affinity" thing is only for reference use only. In theory, if you say in the table schema that you want a column to be text, and indeed you insert the column and retrieve it as text, then 0, 01, 0.1 shouldn't matter. If you are doing it in C, a careful schema is really all you need. However we're talking about DBI and Perl...
Conclusion: type disaster ensues when the two, which happen to be type-relaxed both of them, meet...
I found this journal entry while browsing We-Hates-Software, indeed a hateful thing.
