Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 20 datatype mismatch' in /media/Data/Documenten/PHP/µBot/index.php:100

Question

I keep getting this error message. The datatype it's talking about is TEXT and the data to insert is TEXT so I don't see what could be the problem.

robin@robin-Latitude-D620:/media/Data/Documenten/PHP/µBot$ php index.php 
PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/lib/php5/20090626/sqlite.so' - /usr/lib/php5/20090626/sqlite.so: cannot open shared object file: No such file or directory in Unknown on line 0
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 20 datatype mismatch' in /media/Data/Documenten/PHP/µBot/index.php:100
Stack trace:
#0 /media/Data/Documenten/PHP/µBot/index.php(100): PDOStatement->execute()
#1 /media/Data/Documenten/PHP/µBot/index.php(73): addToLog('439', '*', 'irc.gmake.org', ':Please wait wh...')
#2 {main}
  thrown in /media/Data/Documenten/PHP/µBot/index.php on line 100

Block of code where the problem should lie (line 100 is the last line of this block):

function addToLog($type, $channel, $host, $message)
{
    global $database;
    $temp = explode('!', $host);
    $nick = $temp[0];
    unset($temp);
    $timestamp = time();
    $nickprefix = '';
    $query = $database->prepare("INSERT INTO log VALUES ('', :channel, :nick, :host, :message, :type, :time, '');");
    $query->bindParam(':channel', $channel);
    $query->bindParam(':nick', $nick);
    $query->bindParam(':host', $host);
    $query->bindParam(':message', $message);
    $query->bindParam(':type', $type);
    $query->bindParam(':time', $timestamp); 
    $query->execute();
}

Full code: http://pastebin.com/CXCQjqb0

--

SQLite version: sqlite 2.8.17-6.1ubuntu1
PHP version: php5-common 5.3.6-13ubuntu3.2

Solution

I don't know your schema but I'll guess that id is an auto-increment column and this:

INSERT INTO log (id, channel, nick, host, message, type timestamp, nickprefix)
VALUES ('', :channel, :nick, :host, :message, :type, :time, '');

is trying to put an empty string in the id. You probably want this:

INSERT INTO log (channel, nick, host, message, type timestamp, nickprefix)
VALUES (:channel, :nick, :host, :message, :type, :time, '');

And please, always specify the column list with your INSERTS and simply leave out any values that the database will supply values for (this includes both auto-incrementing columns and columns with other default values).

Comments

[RobinJ]

Thanks you Thanks you Thanks you Thanks you Thanks you!!! I spent 5 hours looking through documentation and contacted 4 different people without anyone seeing what the problem was! Most of the times it's just something simple like this :p I'm used to MySQL where you can just use '' for an auto_increment field.

[mu is too short]

@RobinJ: The worst thing about MySQL is that it encourages bad habits :) You can leave it out for MySQL too so leaving it out is a good habit to pick up. Props for using PDO though, much nicer than the usual pile of mysql_this and mysql_that noise.