Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

can't connect to an sqlite database

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
derrida
Asked:
derrida
  • 6
  • 6
1 Solution
 
Ray PaseurCommented:
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
 
derridaAuthor Commented:
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
 
derridaAuthor Commented:
obviously forgot the echo :)
i get:
SQLSTATE[HY000]: General error: 1 no such table: employees
but i have it.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ray PaseurCommented:
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
 
derridaAuthor Commented:
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
 
Ray PaseurCommented:
Interesting.  I'm not seeing anything wrong.  Can you do the queries manually in SQLite Manager?
0
 
derridaAuthor Commented:
yes
0
 
Ray PaseurCommented:
What does phpinfo() say about PDO and SQLite?  Also, this is a long shot, but do you have more than one SQLite database?
0
 
derridaAuthor Commented:
i share screen shots maybe something i miss?
PDO.png
sqlite.png
0
 
Ray PaseurCommented:
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
 
derridaAuthor Commented:
have no idea why but this morning everything works
0
 
Ray PaseurCommented:
Those are the scary ones!

Best regards, ~Ray
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now