Solved

can't connect to an sqlite database

Posted on 2015-01-04
12
355 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
  • 6
  • 6
12 Comments
 
LVL 108

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
 
LVL 108

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 108

Expert Comment

by:Ray Paseur
ID: 40530508
Interesting.  I'm not seeing anything wrong.  Can you do the queries manually in SQLite Manager?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:derrida
ID: 40530524
yes
0
 
LVL 108

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 108

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 108

Expert Comment

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

Best regards, ~Ray
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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.

758 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

17 Experts available now in Live!

Get 1:1 Help Now