Solved

unable to open database file

Posted on 2014-03-24
16
2,065 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Comment

by:rgb192
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

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

Author Comment

by:rgb192
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
ok.
thanks.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now