Solved

unable to open database file

Posted on 2014-03-24
16
2,226 Views
Last Modified: 2014-04-22
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [14] unable to open database file' in C:\wamp\www\POPP-edition4-code\9781430260318_Chapter_04_Code\generate_product_pdo.php:15 Stack trace: #0 C:\wamp\www\POPP-edition4-code\9781430260318_Chapter_04_Code\generate_product_pdo.php(15): PDO->__construct('sqlite:/C:\wamp...', NULL, NULL) #1 C:\wamp\www\POPP-edition4-code\9781430260318_Chapter_04_Code\listing4.03.php(134): getPDO() #2 {main} thrown in C:\wamp\www\POPP-edition4-code\9781430260318_Chapter_04_Code\generate_product_pdo.php on line 15


listing4.03.php
<?php
class ShopProduct {
    private $title;
    private $producerMainName;
    private $producerFirstName;
    protected $price;
    private $discount = 0; 
    private $id = 0;
    
    public function __construct(   $title, $firstName, 
                            $mainName, $price ) { 
        $this->title             = $title;
        $this->producerFirstName = $firstName;
        $this->producerMainName  = $mainName;
        $this->price             = $price;
    }

    public function setID( $id ) {
        $this->id = $id;
    }

    public function getProducerFirstName() {
        return $this->producerFirstName;
    }

    public function getProducerMainName() {
        return $this->producerMainName;
    }

    public function setDiscount( $num ) {
        $this->discount=$num;
    }

    public function getDiscount() {
        return $this->discount;
    }
    
    public function getTitle() {
        return $this->title;
    }

    public function getPrice() {
        return ($this->price - $this->discount);
    }

    public function getProducer() {
        return "{$this->producerFirstName}".
               " {$this->producerMainName}";
    }

    function getSummaryLine() {
        $base  = "$this->title ( $this->producerMainName, ";
        $base .= "$this->producerFirstName )"; 
        return $base;
    }

    public static function getInstance( $id, PDO $pdo ) {
        $query = "select * from products where id='$id'";
        $stmt = $pdo->prepare("select * from products where id=?");
        $result = $stmt->execute( array( $id ) );
        $row = $stmt->fetch( );
        if ( empty( $row ) ) { return null; }

        if ( $row['type'] == "book" ) {
            $product = new BookProduct( 
                                    $row['title'], 
                                    $row['firstname'], $row['mainname'], 
                                    $row['price'], $row['numpages'] ); 
        } else if ( $row['type'] == "cd" ) {
            $product = new CdProduct(
                                    $row['title'], 
                                    $row['firstname'], $row['mainname'], 
                                    $row['price'], $row['playlength'] ); 
        } else {
            $product = new ShopProduct(     
                                    $row['title'], 
                                    $row['firstname'], $row['mainname'], 
                                    $row['price'] ); 
        }
        $product->setId(            $row['id'] );
        $product->setDiscount(      $row['discount'] );
        return $product;
    }
}

class CdProduct extends ShopProduct {
    private $playLength = 0;

    public function __construct(   $title, $firstName, 
                            $mainName, $price, $playLength ) { 
        parent::__construct(    $title, $firstName, 
                                $mainName, $price );
        $this->playLength = $playLength;
    }

    public function getPlayLength() {
        return $this->playLength;
    }

    function getSummaryLine() {
        $base = parent::getSummaryLine();
        $base .= ": playing time - $this->playLength";
        return $base;
    }
 
}

class BookProduct extends ShopProduct {
    private $numPages = 0;

    public function __construct(   $title, $firstName, 
                            $mainName, $price, $numPages ) { 
        parent::__construct(    $title, $firstName, 
                                $mainName, $price );
        $this->numPages = $numPages;
    }

    public function getNumberOfPages() {
        return $this->numPages;
    }
   
    function getSummaryLine() {
        $base = parent::getSummaryLine();
        $base .= ": page count - $this->numPages";
        return $base;
    }

    public function getPrice() {
        return $this->price;
    }
}

require_once("generate_product_pdo.php");
$pdo = getPDO();
$obj = ShopProduct::getInstance( 1, $pdo );
print_r( $obj );
$obj = ShopProduct::getInstance( 2, $pdo );
print_r( $obj );
$obj = ShopProduct::getInstance( 3, $pdo );
print_r( $obj );
?>

Open in new window


generate_product_pdo.php
<?php

function getPDO() {
    $create_products = "CREATE TABLE products ( 
                            id INTEGER PRIMARY KEY AUTOINCREMENT, 
                            type TEXT,
                            firstname TEXT,
                            mainname TEXT,
                            title TEXT,
                            price float,
                            numpages int,
                            playlength int,
                            discount int )";
    $dsn = "sqlite:/".dirname(__FILE__)."/products.db";    
    $pdo = new PDO( $dsn, null, null );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt= $pdo->query( "select count(*) from SQLITE_MASTER" );
    $row = $stmt->fetch( );
    $stmt->closeCursor();
    if ( $row[0] > 0 ) {
        $pdo->query( "DROP TABLE products" );
    }
    $pdo->query( $create_products );
    $pdo->query( "INSERT INTO products ( type, firstname, mainname, title, price, numpages, playlength, discount ) 
                                values ( 'book', 'willa', 'cather', 'my antonia', 4.22, 200, NULL, 0 )");
    $pdo->query( "INSERT INTO products ( type, firstname, mainname, title, price, numpages, playlength, discount ) 
                                values ( 'cd', 'the', 'clash', 'london calling', 4.22, 200, 60, 0 )");
    $pdo->query( "INSERT INTO products ( type, firstname, mainname, title, price, numpages, playlength, discount ) 
                                values ( 'shop', NULL, 'pears', 'soap', 4.22, NULL, NULL, 0 )");
    return $pdo;
}
?>

Open in new window


products.db is in the same folder but I can not attach.
all the files are recursive writable 777

from
matt zandstra php objects patterns and practice
0
Comment
Question by:rgb192
  • 8
  • 8
16 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39951570
Untested, but probably close to correct.  This is line 15-16 of the second code snippet.  Are you OK with using NULL for the user name and password?  I have little experience with SQLite and I'm not sure what goes there.

Note that you can use a similar construct for each of your PDO calls.  You don't have to be "blind" after $pdo->query() functions.  This article uses MySQL instead of SQLite for the PDO examples, but most of the same principles apply.  Have a look and see how error handling is done.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

try{    
    $pdo = new PDO( $dsn, null, null );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $exc){
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

Open in new window

0
 

Author Comment

by:rgb192
ID: 39951825
Fatal error: SQLSTATE[HY000] [14] unable to open database file in C:\wamp\www\POPP-edition4-code\9781430260318_Chapter_04_Code\generate_product_pdo.php on line 21

modified generate_product_pdo.php
<?php

function getPDO() {
    $create_products = "CREATE TABLE products ( 
                            id INTEGER PRIMARY KEY AUTOINCREMENT, 
                            type TEXT,
                            firstname TEXT,
                            mainname TEXT,
                            title TEXT,
                            price float,
                            numpages int,
                            playlength int,
                            discount int )";
    $dsn = "sqlite:/".dirname(__FILE__)."/products.db";    
    //$pdo = new PDO( $dsn, null, null );
    //$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    try{    
      $pdo = new PDO( $dsn, null, null );
      $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }catch(PDOException $exc){
      trigger_error($exc->getMessage(), E_USER_ERROR);
    }
    $stmt= $pdo->query( "select count(*) from SQLITE_MASTER" );
    $row = $stmt->fetch( );
    $stmt->closeCursor();
    if ( $row[0] > 0 ) {
        $pdo->query( "DROP TABLE products" );
    }
    $pdo->query( $create_products );
    $pdo->query( "INSERT INTO products ( type, firstname, mainname, title, price, numpages, playlength, discount ) 
                                values ( 'book', 'willa', 'cather', 'my antonia', 4.22, 200, NULL, 0 )");
    $pdo->query( "INSERT INTO products ( type, firstname, mainname, title, price, numpages, playlength, discount ) 
                                values ( 'cd', 'the', 'clash', 'london calling', 4.22, 200, 60, 0 )");
    $pdo->query( "INSERT INTO products ( type, firstname, mainname, title, price, numpages, playlength, discount ) 
                                values ( 'shop', NULL, 'pears', 'soap', 4.22, NULL, NULL, 0 )");
    return $pdo;
}
?>

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39951843
Here is my SQLite example.  Sorry - there's not much here, but maybe you could try it to see if you can adapt it for your server.
http://laprbass.com/RAY_sqlite_example.php

<?php // RAY_sqlite_example.php
error_reporting(E_ALL);
echo '<pre>';


// USE SQLITE
// REF: http://www.sqlite.org/about.html
// WHY: http://www.sqlite.org/mostdeployed.html



// SET UP OUR PHP DATA OBJECT USING SQLITE
$db_name = 'SqLite.db';
$db_drvr = 'sqlite';
try
{
    $pdo = new PDO("$db_drvr:$db_name");
}
catch(PDOException $e)
{
    var_dump($e);

}


// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


// SHOW THE (RATHER SPARSE) PDO OBJECT
echo "PDO OBJECT: ";
var_dump($pdo);
echo PHP_EOL;



// A QUERY TO CREATE A TABLE http://www.sqlite.org/datatype3.html
$sql
=
"
CREATE TABLE stock
( symbol TEXT
, price  REAL
, xtime  TEXT
, key    INTEGER PRIMARY KEY AUTOINCREMENT
)
"
;
// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($pdos);



// PREPARE THE QUERY WITH NAMED PLACEHOLDERS THAT MATCH PHP ARRAY KEYS
$sql
=
"
INSERT INTO stock
(  symbol,  price ) VALUES
( :symbol, :price )
"
;
// THIS PREPARES THE QUERY (ONLY NEEDED ONCE)
try
{
    $sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);



// SOME TEST DATA FOR OUR STOCK TABLE
$names = array
( array( 'symbol' => 'YHOO', 'price' =>   41.07 )
, array( 'symbol' => 'GOOG', 'price' => 1148.62 )
, array( 'symbol' => 'AAPL', 'price' =>  557.36 )
, array( 'symbol' => 'JCP',  'price' =>    6.92 )
)
;
// LOADING OUR ARRAYS OF TEST DATA INTO THE TABLE
foreach ($names as $name)
{
    // USE THE ARRAY OF KEYWORD => VALUE TO ATTACH symbol AND price
    try
    {
        // RUN THE QUERY TO INSERT THE ROW
        $sth->execute($name);
    }
    catch(PDOException $exc)
    {
        var_dump($exc);
        trigger_error($exc->getMessage(), E_USER_ERROR);
    }

    // GET THE SQLITE AUTOINCREMENT ID OF THE RECORD JUST INSERTED
    $rowid  = $pdo->lastInsertId();
    echo "PDO INSERTED A ROW CONTAINING <b>" . $name['symbol'] . ' ' . $name['price']. "</b> WITH AUTO_INCREMENT ID = $rowid" . PHP_EOL;
}



// A QUERY TO RETRIEVE INFORMATION FROM THE TABLE
$sql
=
"
SELECT rowid, symbol, price FROM stock WHERE price > :value LIMIT 3
"
;
// PREPARES THE QUERY
try
{
    $sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);

// USE ARRAY OF KEYWORD => VALUE TO ATTACH QUERY PARAMS
$parms = array( ':value' => 500 );
try
{
    // RUN THE QUERY TO SELECT THE ROWS
    $sth->execute($parms);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

// RETRIEVE THE RESULTS IN THE FORM OF ANONYMOUS OBJECTS
while ($obj = $sth->fetch(PDO::FETCH_OBJ))
{
    print_r($obj);
}



// A QUERY TO REMOVE INFORMATION FROM THE TABLE
$sql
=
"
DELETE FROM stock WHERE price > :value
"
;
// PREPARES THE QUERY
try
{
    $sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);

// USE ARRAY OF KEYWORD => VALUE TO ATTACH QUERY PARAMS
$parms = array( ':value' => 500 );
try
{
    // RUN THE QUERY TO DELETE THE ROWS
    $sth->execute($parms);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}



// A QUERY TO SHOW THE INFORMATION LEFT IN THE TABLE
$sql
=
"
SELECT rowid, symbol, price FROM stock ORDER BY symbol
"
;
// PREPARES THE QUERY
try
{
    $sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
}
// SHOW THE PREPARED STATEMENT HANDLE
var_dump($sth);

try
{
    // RUN THE QUERY TO SELECT THE ROWS (NO PARAMS NEEDED HERE)
    $sth->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

// RETRIEVE THE RESULTS IN THE FORM OF ANONYMOUS OBJECTS
while ($obj = $sth->fetch(PDO::FETCH_OBJ))
{
    print_r($obj);
}



// A QUERY TO CAUSE AN ERROR AND SHOW THE EXCEPTION OBJECT
$sql = "SELECT oopsie FROM stock ORDER BY symbol";
// PREPARES THE QUERY
try
{
    $sth = $pdo->prepare($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
}
// SHOW THAT THE PREPARED STATEMENT HANDLE IS MEANINGLESS AFTER THE EXCEPTION
var_dump($sth);

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:rgb192
ID: 39954878
nuspehere ide
PDO OBJECT: object(PDO)#1 (0) {
}

object(PDOException)#3 (8) {
  ["message":protected]=>
  string(60) "SQLSTATE[HY000]: General error: 1 table stock already exists"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  string(5) "HY000"
  ["file":protected]=>
  string(39) "C:\wamp\www\test\ray-testing-sqlite.php"
  ["line":protected]=>
  int(53)
  ["trace":"Exception":private]=>
  array(1) {
    [0]=>
    array(6) {
      ["file"]=>
      string(39) "C:\wamp\www\test\ray-testing-sqlite.php"
      ["line"]=>
      int(53)
      ["function"]=>
      string(5) "query"
      ["class"]=>
      string(3) "PDO"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(1) {
        [0]=>
        string(114) "
CREATE TABLE stock
( symbol TEXT
, price  REAL
, xtime  TEXT
, key    INTEGER PRIMARY KEY AUTOINCREMENT
)
"
      }
    }
  }
  ["previous":"Exception":private]=>
  NULL
  ["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "HY000"
    [1]=>
    int(1)
    [2]=>
    string(26) "table stock already exists"
  }
}

Fatal error:  SQLSTATE[HY000]: General error: 1 table stock already exists in C:\wamp\www\test\ray-testing-sqlite.php on line 58

Open in new window


wamp with browser
PDO OBJECT: 
object(PDO)[1]

object(PDOException)[3]
  protected 'message' => string 'SQLSTATE[HY000]: General error: 1 table stock already exists' (length=60)
  private 'string' (Exception) => string '' (length=0)
  protected 'code' => string 'HY000' (length=5)
  protected 'file' => string 'C:\wamp\www\test\ray-testing-sqlite.php' (length=39)
  protected 'line' => int 53
  private 'trace' (Exception) => 
    array (size=1)
      0 => 
        array (size=6)
          'file' => string 'C:\wamp\www\test\ray-testing-sqlite.php' (length=39)
          'line' => int 53
          'function' => string 'query' (length=5)
          'class' => string 'PDO' (length=3)
          'type' => string '->' (length=2)
          'args' => 
            array (size=1)
              ...
  private 'previous' (Exception) => null
  public 'errorInfo' => 
    array (size=3)
      0 => string 'HY000' (length=5)
      1 => int 1
      2 => string 'table stock already exists' (length=26)
  public 'xdebug_message' => string '<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> PDOException: SQLSTATE[HY000]: General error: 1 table stock already exists in C:\wamp\www\test\ray-testing-sqlite.php on line <i>53</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left''... (length=1244)


( ! ) Fatal error: SQLSTATE[HY000]: General error: 1 table stock already exists in C:\wamp\www\test\ray-testing-sqlite.php on line 58
Call Stack
#	Time	Memory	Function	Location
1	0.1055	278568	{main}( )	..\ray-testing-sqlite.php:0
2	0.4624	287264	trigger_error ( )	..\ray-testing-sqlite.php:58

Open in new window



so my php.ini may be missing something in both installations.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39955907
No, php.ini does not seem to be missing anything.  You have to read the information carefully to find out what is really happening.  Emphasis added below.  You can only create the table one time.  After that, it is already created and you can't have two tables with the same name.

["message":protected]=>string(60) "SQLSTATE[HY000]: General error: 1 table stock already exists"
0
 

Author Comment

by:rgb192
ID: 39965434
matt zandstra example:
Fatal error: SQLSTATE[HY000] [14] unable to open database file in C:\wamp\www\POPP-edition4-code\9781430260318_Chapter_04_Code\generate_product_pdo.php on line 21


php.ini does not seem to be missing anything.

In your example table was created twice
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39965550
your example table was created twice
What line caused the second CREATE TABLE statement?  Thanks, ~Ray
0
 

Author Comment

by:rgb192
ID: 39970883
What line caused the second CREATE TABLE statement?  Thanks, ~Ray

nusphere: line 52 of the output i received when I ran your code
    string(26) "table stock already exists"

wamp: line 27 of the output I received when I ran your code
     2 => string 'table stock already exists' (length=26)



I think the answer to your question is line 42 of your code creates a table twice.

currently, I do not understand similarity of
your code and
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28396166.html#a39951825
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39972050
You can only create a table of a given name one time.  After that, it's already created and you cannot create another table with the same name.  If you want to remove and recreate the table, you use the DROP TABLE command.
0
 

Author Comment

by:rgb192
ID: 39973907
Using mysql query workbench I have tried creating existing table.

but the zandstra error is
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [14] unable to open database file'

even when I make windows folder writable using cgywin

I do not understand how create table is similar to open database file
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39974885
This is a different error.

Old message:
SQLSTATE[HY000]: General error: 1 table stock already exists

New message:
SQLSTATE[HY000] [14] unable to open database file

I'm assuming you've read the man page here:
http://www.php.net/manual/en/ref.pdo-sqlite.php

Michelangelo van Dam has encountered this in Zend.  One possible issue goes to the write-ability of the data base.  Apparently SQLite has a "journal" file that is written, even for read-only access to the data base.
http://www.dragonbe.com/2014/01/pdo-sqlite-error-unable-to-open.html
0
 

Author Comment

by:rgb192
ID: 39983131
using browser search:
SQLSTATE[HY000]: General error: 1 table stock already exists
appears
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28396166.html#a39954878

after running your code


SQLSTATE[HY000] [14] unable to open database file
is in the first question

I do not understand how the error from your code is similar to the matt zandstra error.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39983250
It's not clear to me that these are similar.  This is just guessing on my part.

An attempt to create a duplicate table name should fail.  Perhaps nothing has been written to the database file at the time of the attempt to create the table (the DB engine could read and compare table names)?  But it would seem to me that it would have opened the database in order to do this.  Unable to open database file is not a very revealing message, but that may be all you can get with SQLite.
0
 

Author Comment

by:rgb192
ID: 40004862
Do you think I should keep trying this book example or call it a windows error and ignore and move on to another section of the book
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40004929
If you're finding yourself stuck on this, it might be worth skipping over it and moving on to a place where you can make progress.  Sometimes just taking a step back and later revisiting the task can give you "a fresh set of eyes."
0
 

Author Closing Comment

by:rgb192
ID: 40014777
ok.
thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question