Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

hex (3272)

hex
  (email not shown publicly)
http://downlode.org/

Perl, RDF and wiki hacker, London, UK. This is my former Perl blog; I now write at Earle's Notebook [downlode.org].

Journal of hex (3272)

Thursday April 14, 2005
01:21 PM

SQLite nightmare

[ #24191 ]
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?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I just scanned thru the documentation [sqlite.org] 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

  • 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);
  • 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 sh