Cassandra Prepared Statements error with datastax php driver

Question

I'm facing strange error with Prepared Statements with DataStax php driver 1.0.0-rc and Cassandra 2.2.3. I got an Exception on this line:

$statement = $this->session->prepare("SELECT ? FROM ? WHERE ? = ?");

I am seeing this error:

"error_code":33562624
"error_message":"Bind variables cannot be used for keyspace names"

Below a stub of class used to comunicate with Cassandra:

class ClsCassandra extends ClsDbObject
{
    private $hostname="";
    private $username="";
    private $password="";
    private $keyspace="";
    private $poi_table="";
    private $poi_table_key_field="";
    private $poi_table_content_field="";

    private $cluster = NULL;
    private $session = NULL;

    private $threads = 1;

    function __construct()
    {
        ...
        ...
        //
        // i set up all the properties above
        //
        ...
        ...
    }

    public function runQuery(&$error)
    {
        try 
        {           
            $this->cluster   = Cassandra::cluster()
            ->withContactPoints($this->hostname)
            ->withCredentials($this->username, $this->password)
            ->withIOThreads($this->threads)
            ->build();

            $this->session = $this->cluster->connect($this->keyspace);

            // error on next line...
            $statement = $this->session->prepare("SELECT ? FROM ? WHERE ? = ?");
            $results = $this->session->execute($statement, new Cassandra\ExecutionOptions(array(
                    'arguments' => array($this->poi_table_content_field, $this->poi_table, $this->poi_table_key_field, $keypattern)
            )));

        }
        catch(Cassandra\Exception $ce)
        {
            $error->setError($ce->getCode(), $ce->getMessage(), $ce->getTraceAsString());
            $this->log(LOG_LEVEL, $error->getErrorMessage(), __FILE__, __LINE__, __CLASS__);
            return false;
        }
        return true;
    }

    ...
    ...
    ...
}

If I use Simple Statemetn with standard select query instead it works.

Any suggestions?

score 2 · Accepted Answer · answered Nov 27 '15 at 03:53

2

You are seeing this error because you can only bind variables to the WHERE clause. The prepared statement mechanism isn't just a glorified string formatter. It has rules about what can and cannot be bound, and how you have to bind things to remove any ambiguity when sending to Cassandra.

You'll need to try something like this:

$statement = $this->session->prepare(
    "SELECT key1, key2, col1, col2 FROM yourKeyspaceName.yourTableName WHERE key1 = ? AND key2 = ?");
$results = $this->session->execute($statement, new Cassandra\ExecutionOptions(array(
    'arguments' => array($key1,$key2)
    )));

answered Nov 27 '15 at 03:53

Aaron

55,518
11
116
132

Dear Aaron, many thanks for your help. Following your hints i solved. is there website where i can see detailed rule to be applied for prepared statement? Thx in advance. – omambe Nov 27 '15 at 10:00
@omambe Here is a link to the Java Driver documentation (http://docs.datastax.com/en/developer/java-driver/2.0/java-driver/quick_start/qsSimpleClientBoundStatements_t.html) that states: "use prepared statements and bind new values to the columns each time before execution." The idea behind a prepared statement, is that you are building the CQL ahead of time, and only sending the different column values with each query (for performance). How can Cassandra save a query for future use, if you're going to change the columns, table or keyspace name each time? – Aaron Nov 29 '15 at 14:01
@omambe You might also find these two blog posts helpful: http://www.datastax.com/dev/blog/4-simple-rules-when-using-the-datastax-drivers-for-cassandra and https://ahappyknockoutmouse.wordpress.com/2014/11/12/246/ – Aaron Nov 29 '15 at 14:02

Cassandra Prepared Statements error with datastax php driver

1 Answers1