Converting a string to a number when inserting it into the database via the Perl DBI function $ sth-> execute ()

Question

I am using Perl 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 encountered when $book_info->{identica} starts at 0, they are discarded, and I get the number inserted into the database.

For example, identica of 00123 converted to 123 .

I know that SQLite is typeless, so how do I make a DBI to insert an identica string as a string, not a number?

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

EDIT

Even if I insert the value directly into the query, it does not 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 hides 00123 to 123 and 0000000009 to 9 ...

EDIT

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

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

It has been deleted by SQLite!

Here's what the circuit looks like:

 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?

Decision

This is sqlite problem, see answer in Jim's comments. STRING must be TEXT in sqlite. Otherwise, it treats it as a number!

Change the circuit to the following solution:

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

Answer 1

Use bind options

 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 the affinity type in SQLite . Since your column type is STRING (technically unsupported), by default it is bound to INTEGER. You need to create a TEXT column instead.

Answer 2

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