Solved

can't connect to an sqlite database

Posted on 2015-01-04
12
424 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

738 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