String getting converted to number when inserting it in database through Perl's DBI $sth->execute() function

Question

I'm using Perl's DBI and SQLite database (I have DBD::SQLite installed). I have the following code:

my $dbh = DBI->connect("dbi:SQLite:dbname=$db", "", "", { RaiseError => 1, AutoCommit => 1 });
...
my $q = "INSERT OR IGNORE INTO books (identica, book_title) VALUES (?, ?)";
my $sth = $dbh->prepare($q);
$sth->execute($book_info->{identica}, $book_info->{book_title});

The problem I have is when $book_info->{identica} begins with 0's they get dropped and I get a number inserted in the database.

For example, identica of 00123 will get converted to 123.

I know SQLite doesn't have types, so how do I make DBI to insert the identica as string rather than number?

I tried quoting it as "$book_info->{identica}" when passing to $sth->execute but that didn't help.

EDIT

Even if I insert value directly in query it doesn't work:

my $i = $book_info->{identica};
my $q = "INSERT OR IGNORE INTO books (identica, book_title) VALUES ('$i', ?)";
my $sth = $dbh->prepare($q);
$sth->execute($book_info->{book_title});

This still coverts 00123 to 123, and 0000000009 to 9...

EDIT

Holy sh*t, I did this on the command line, and I got this:

sqlite> INSERT INTO books (identica, book_title) VALUES ('0439023521', 'a');
sqlite> select * from books where id=28;
28|439023521|a|

It was dropped by SQLite!

Here is how the schema looks:

CREATE TABLE books (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  identica          STRING  NOT NULL,
);

CREATE UNIQUE INDEX IDX_identica       on books(identica);
CREATE INDEX IDX_book_title            on books(book_title);

Any ideas what is going on?

SOLUTION

It's sqlite problem, see answer by in the comments by Jim. The STRING has to be TEXT in sqlite. Otherwise it treats it as number!

Changing schema to the following solved it:

CREATE TABLE books (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  identica          TEXT  NOT NULL,
);

What is the declaration (i.e. type affinity) of that column in SQLite? According to the docs, if it's `TEXT` it should store it as a string; otherwise it will be a number. — Jim Garrison, Mar 19 '12 at 20:59
@JimGarrison Please see the updated question. I'm using `STRING`. Let me try `TEXT`. — bodacydo, Mar 19 '12 at 21:04
@JimGarrison You just saved my day. I converted it to `TEXT` and it works perfectly! — bodacydo, Mar 19 '12 at 21:05
Note also that in sqlite, `integer primary key autoincrement` is not needed--each table has an implicit `rowid` column. — piojo, Aug 05 '17 at 02:58

Glen Solsberry · Answer 1 · 2012-03-19T21:06:24.743

3

Use bind params

my $sth = $dbh->prepare($q);
$sth->bind_param(1, 00123, { TYPE => SQL_VARCHAR });
$sth->bind_param(2, $book_info->{book_title});
$sth->execute();

UPDATE:

Read about type affinity in SQLite. Because your column type is STRING (technically unsupported), it defaults to INTEGER affinity. You need to create your column as TEXT instead.

edited Mar 19 '12 at 21:06

answered Mar 19 '12 at 21:00

Glen Solsberry

11,960
15
69
94

Thanks for the answer. I tried this but it didn't help. I did more experiments and please see my updated question. It looks like sqlite3 is dropping leading 0's it all by itself! – bodacydo Mar 19 '12 at 21:02
Thanks. Please leave your original answer as it was insightful as well. Please combine both answers! – bodacydo Mar 19 '12 at 21:08
Added the original answer back in. Best of luck. – Glen Solsberry Mar 19 '12 at 21:10

score 1 · Answer 2 · answered Mar 19 '12 at 23:08

1

According to the docs, if the column type (affinity) is TEXT it should store it as a string; otherwise it will be a number.

answered Mar 19 '12 at 23:08

Jim Garrison

85,615
20
155
190

String getting converted to number when inserting it in database through Perl's DBI $sth->execute() function

2 Answers2