rgb192
asked on
unable to open database file
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [14] unable to open database file' in C:\wamp\www\POPP-edition4- code\97814 30260318_C hapter_04_ Code\gener ate_produc t_pdo.php: 15 Stack trace: #0 C:\wamp\www\POPP-edition4- code\97814 30260318_C hapter_04_ Code\gener ate_produc t_pdo.php( 15): PDO->__construct('sqlite:/ C:\wamp... ', NULL, NULL) #1 C:\wamp\www\POPP-edition4- code\97814 30260318_C hapter_04_ Code\listi ng4.03.php (134): getPDO() #2 {main} thrown in C:\wamp\www\POPP-edition4- code\97814 30260318_C hapter_04_ Code\gener ate_produc t_pdo.php on line 15
listing4.03.php
generate_product_pdo.php
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
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 );
?>
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;
}
?>
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
ASKER
Fatal error: SQLSTATE[HY000] [14] unable to open database file in C:\wamp\www\POPP-edition4- code\97814 30260318_C hapter_04_ Code\gener ate_produc t_pdo.php on line 21
modified generate_product_pdo.php
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;
}
?>
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
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);
ASKER
nuspehere ide
wamp with browser
so my php.ini may be missing something in both installations.
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
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
so my php.ini may be missing something in both installations.
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]=>str ing(60) "SQLSTATE[HY000]: General error: 1 table stock already exists"
["message":protected]=>str
ASKER
matt zandstra example:
Fatal error: SQLSTATE[HY000] [14] unable to open database file in C:\wamp\www\POPP-edition4- code\97814 30260318_C hapter_04_ Code\gener ate_produc t_pdo.php on line 21
In your example table was created twice
Fatal error: SQLSTATE[HY000] [14] unable to open database file in C:\wamp\www\POPP-edition4-
php.ini does not seem to be missing anything.
In your example table was created twice
your example table was created twiceWhat line caused the second CREATE TABLE statement? Thanks, ~Ray
ASKER
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
https://www.experts-exchange.com/questions/28396166/unable-to-open-database-file.html?anchorAnswerId=39951825#a39951825
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.
ASKER
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
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
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
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
ASKER
using browser search:
SQLSTATE[HY000]: General error: 1 table stock already exists
appears
https://www.experts-exchange.com/questions/28396166/unable-to-open-database-file.html?anchorAnswerId=39954878#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.
SQLSTATE[HY000]: General error: 1 table stock already exists
appears
https://www.experts-exchange.com/questions/28396166/unable-to-open-database-file.html?anchorAnswerId=39954878#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.
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.
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok.
thanks.
thanks.
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.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
Open in new window