Solved

can't connect to an sqlite database

Posted on 2015-01-04
12
374 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mimic google as my ip 11 53
wordpress url rewriting plugin 5 42
Passing variables to stored procedure 3 34
Animated .jpg? 13 57
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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 dynamically set the form action using jQuery.

932 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

7 Experts available now in Live!

Get 1:1 Help Now