0

I'm using php5 and PDO to create a sqlite database.

Here is the php code :

    <?php
    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    try {
      $dbh = new PDO("sqlite:/srv/www/htdocs/lowi.sqlite");
    } catch (PDOException $e) {
      die ($e->getMessage());
    }
    $query = "CREATE TABLE setting (
      id INTEGER AUTOINCREMENT PRIMARY KEY, 
      title TEXT,
      subTitle TEXT,
      ssid TEXT,
      defaultUrl TEXT,
      imageBannerPath TEXT,
      imageProfilePath TEXT,
      loginAdmin TEXT,
      passwordAdmin TEXT,
      moderationActive TEXT CHECK(moderationActive IN ('T','F'))
    )";
    try {
      $dbh->exec($query);
    } catch (PDOException $e) {
      echo $e->getMessage();
    }
    echo "Table 'setting' created successfully<br/>";
    $dbh=NULL;
    ?>

It just outputs :

Table 'setting' created successfully

I checked the permissions and my webserver runs under the user/group : lighttpd (for both)

The directory in which I create the sqlite database has those permission :

drwxrwxr-x 4 root lighttpd 4096 Sep  9 16:15 /srv/www/htdocs/

and the database file is created and has those persmissions :

-rw------- 1 lighttpd lighttpd 0 Sep  9 15:48 /srv/www/htdocs/lowi.sqlite

The problem is that it remains an empty file and when I try to execute other queries like insert or select, it executes them without error (like the create table) but those queries have no effect.

NOTE : I know I'm not making good choices here in term of security (the database file in a public directory ...) No need to tell me such things as I'm trying to debug I changed losts of things and I'm just giving the current situation I'm in as close to as it is in my development machine to have better answers. :)

Damiens Robert
  • 41
  • 3
  • Is the problem no rows added or is it not table is actually created? – Can O' Spam Sep 09 '15 at 14:31
  • 2
    You didn't enable exceptions in PDO. By default it fails silently and returns boolean false. Since there's no exceptions, your try/catch blocks are useless. Therefore the query can still fail and not trigger any error messages. Read this: http://stackoverflow.com/questions/8992795/set-pdo-to-throw-exceptions-by-default – Marc B Sep 09 '15 at 14:31
  • Also, have you tried putting the echo into the `try` section and see if it prints there? – Can O' Spam Sep 09 '15 at 14:31
  • Ditto what @MarcB said *and* the permissions on the database file iself should be `-rwxrw-rw-` I have also found that the directory containing the SQLite database is going to have to be writable. Here is one example: http://stackoverflow.com/questions/1122885/why-cant-dbdsqlite-insert-into-a-database-through-my-perl-cgi-script – Jay Blanchard Sep 09 '15 at 14:32
  • Also you may want to use an integer type for `moderationActive` and use it as a C-style boolean (zero is false, nonzero is true), since that's how SQLite does it. – Colonel Thirty Two Sep 09 '15 at 14:59
  • Thanks to Marc B, I found out the problem ... Indeed by enabling exceptions in PDO with this code : $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); I got error reporting. The error was actually with the AUTOINCREMENT keyword which is not supported on my platform. I read that INTEGER PRIMARY KEY do autoincrement automatically. – Damiens Robert Sep 09 '15 at 19:02

0 Answers0