Solved

can't connect to an sqlite database

Posted on 2015-01-04
12
455 Views
Last Modified: 2015-01-05
hi
i am trying to work with sqlite but cannot connect to it. i have this code:
            // Create (connect to) SQLite database in file
            $file_db = new PDO("sqlite:C:/sqlite/company.sqlite;dbname=company", "", "");
            // Set errormode to exceptions
            $file_db->setAttribute(PDO::ATTR_ERRMODE, 
                                    PDO::ERRMODE_EXCEPTION);
            
            $result = $file_db->query('SELECT * FROM employees');
            var_dump( $result );

Open in new window


with firefox sqlite manager i have created a database named "company" in the folder where my sqlite3.exe is located.
i have an employees table.
then i try to run this and get:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 no such table: employees' in C:\xampp\htdocs\ronnyran\testsqlite.php on line 15

what am i doing wrong?
0
Comment
Question by:derrida
[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
  • 6
  • 6
12 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40530247
You want to change this code so that you're using a try{} catch{} block to catch the PDOException and visualize the error information.  You can see the errors in PDOException::getmessage().  Once we know what the error is, we can start debugging.  Documentation here:
http://php.net/manual/en/class.pdoexception.php
0
 
LVL 1

Author Comment

by:derrida
ID: 40530254
hi thanks for answering.
i put it inside a try catch like so:
        try{
            // Create (connect to) SQLite database in file
            $file_db = new PDO("sqlite:C:/sqlite/company.sqlite;dbname=company", "", "");
            // Set errormode to exceptions
            $file_db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
            
            $result = $file_db->query('SELECT * FROM employees');
            var_dump( $result );
            
        } catch (Exception $ex) {
            $ex->getmessage();
        }

Open in new window


and now i get nothing, but also the dump doesn't return anything.
0
 
LVL 1

Author Comment

by:derrida
ID: 40530282
obviously forgot the echo :)
i get:
SQLSTATE[HY000]: General error: 1 no such table: employees
but i have it.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40530382
Doesn't SQLIte have a "Show tables" command?  Something like this:
http://www.sqlite.org/faq.html#q7

Also, while we're debugging, it might be good to use multiple try{}catch{} blocks.  And I think you want PDOException, not just general Exception.  I think I would try it like this.

try{
    $file_db = new PDO("sqlite:C:/sqlite/company.sqlite;dbname=company", "", "");
} 
catch (PDOException $ex){
    var_dump( $ex->getmessage() );
}


try{
    $file_db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} 
catch (PDOException $ex){
    var_dump( $ex->getmessage() );
}


try{
    $result = $file_db->query('SELECT * FROM employees');
    var_dump( $result );
} 
catch (PDOException $ex){
    var_dump( $ex->getmessage() );
}

Open in new window

0
 
LVL 1

Author Comment

by:derrida
ID: 40530403
hi
i still get:
string 'SQLSTATE[HY000]: General error: 1 no such table: employees' (length=58)

sqlitemanager
maybe you'll see there something i miss?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40530508
Interesting.  I'm not seeing anything wrong.  Can you do the queries manually in SQLite Manager?
0
 
LVL 1

Author Comment

by:derrida
ID: 40530524
yes
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40530537
What does phpinfo() say about PDO and SQLite?  Also, this is a long shot, but do you have more than one SQLite database?
0
 
LVL 1

Author Comment

by:derrida
ID: 40530556
i share screen shots maybe something i miss?
PDO.png
sqlite.png
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40530730
This script works for me on my server.
http://iconoun.com/demo/sqlite_example.php

I can't see much different between our installations.  Maybe you need to prepare the query?  But I do not see any variables in the query, so I would guess that prepare is not needed.

<?php // demo/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 DELIBERATELY 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
 
LVL 1

Author Closing Comment

by:derrida
ID: 40531064
have no idea why but this morning everything works
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40532471
Those are the scary ones!

Best regards, ~Ray
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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.

696 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