Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

unable to open database file

Posted on 2014-03-24
16
Medium Priority
?
2,876 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 111

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 111

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 111

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 111

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 111

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 111

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 111

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 111

Accepted Solution

by:
Ray Paseur earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

722 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