server side example, Invalid catalog name: 1046 No database selected

matt_riley

I'm trying to work with the "simple.html" file in the server side processing examples (http://datatables.net/examples/server_side/simple.html) and getting the error:

{"error":"An SQL error occurred: SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected"}

I've made sure the paths are correct for my server for the files simple.html references (namely, scripts/server_processing.php). I also edited the file that server_processing.php requires, ssp.class.php to connect to my test database.

If I load the server_processing.php, I get the aforementioned error. However, if I echo the $sql variable for the sql query it is using, it produces a valid query that runs fine in an app that deals directly with the mysql database:

SELECT SQL_CALC_FOUND_ROWS first_name, last_name, position, office, start_date, salary FROM datatables_demo

I'm at a loss as to why this won't work. I've loaded the mysql.sql file into a new database with only the table it generates in it (datatables_demo) and, as I said, the query being produced returns correct results when dealing with the database directly.

I edited the ssp.class.php file to connect to my database like so:

static function sql_connect ( $sql_details )
{
try {
$db = @new PDO(
"mysql:host={$sql_details['localhost']};dbname={$sql_details['dttest']}",
$sql_details['testuser'],
$sql_details['testpass'],
array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
);
}
catch (PDOException $e) {
self::fatal(
"An error occurred while connecting to the database. ".
"The error reported by the server was: ".$e->getMessage()
);
}

    return $db;
}

However, the error being returned when loading the page is from the sql_exec function part of the file, so does that mean it is actually connecting to the database correctly but the error is happening elsewhere?

What am I doing wrong?

Thanks!
Matt

matt_riley

Sorry for the formatting issues. I tried the markdown route but it also inserted weird things into the code blocks. :-(

larsonator

this is not a problem with dataTables, rather with your connection too your sql server.

i am not sure what your are doing when you are declaring the $db variable,
it should just be $db = new PDO(...)

matt_riley

The PDO statement was just like it was in the original file, except I've supplied the correct values for my database connection (host=localhost, dbname=dttest, user=testuser, password=testpass).

I'm not a PDO connection expert but it doesn't look like anything is wrong with code block. And, it came from the datatables download so I figured it was trustworthy.

Is the @ symbol in @new what you are saying is wrong? I tried removing it but it didn't make a difference.

-Matt

larsonator

Apologies, i see you changed your post, when i wrote that message it had HTML anchor tags around your 'new'.

I cant say i am and PDO expert either i use CodeIgniter which has its own wrapper around the database interactions.

At what point is the error occurring? is it in one of the PDO library files? or in one of the ones you made yourself?

double check that the user has sufficient rights on the database.

matt_riley

The error occurs in the ssp.class.php file, which is included by a required statement in the server_processing.php file. Both of these files are part of the examples provided in the DataTables download and I haven't changed them, other than to enter my db/account info where appropriate.

I did grant appropriate permissions to the database for the user I am trying to connect as. Just to make sure, I also changed my settings to connect as root but it doesn't make a difference. Same error.

The error happens during the stmt->execute part of this code block:

static function sql_exec ( $db, $bindings, $sql=null )
    {
        // Argument shifting
        if ( $sql === null ) {
            $sql = $bindings;
        }

        $stmt = $db->prepare( $sql );
        //echo $sql;

        // Bind parameters
        if ( is_array( $bindings ) ) {
            for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
                $binding = $bindings[$i];
                $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
            }
        }

        // Execute
        try {
            $stmt->execute();
        }
        catch (PDOException $e) {
            self::fatal( "An SQL error occurred: ".$e->getMessage() );
        }

        // Return all
        return $stmt->fetchAll();
    }

If I uncomment the echo $sql line from that code block, I get the query I posted earlier, which looks correct and does what is expected when run in, say, phpmysql.

Ugh. Any other ideas? This just has the feeling of something simple being wrong, especially since it is all from the included example files (which I assume work!).

-Matt

matt_riley

OK, I figured it out. I'm an idiot (no great surprise there!). :-)

It turns out I didn't need to edit the ssp.class.php file. I looked in that file and interpreted the code block from it as needing to fill in the variables for the connection (db, host, etc.).

However, the only editing that I needed to do was in the server_processing.php file. I looked right over the section that had the real place to fill in these variables. Once I did that, it started working.

I hope this saves someone else the trouble I went through. Thanks for trying to help.

-Matt

larsonator

Least you worked it out :)