Cassandra is not retrieving the correct integer value

Question

I am using cql 3.0.0

I have executed the query:

INSERT INTO emp (empID, deptID, first_name, last_name)
VALUES (104, 15, 'jane', 'smith')

On retrieving this record, I get the following values:

empid = h
deptid =  (blank value)
first_name = 'jane'
last_name = 'smith'

On searching for it, I found that h is equivalent to utf-8 character 104. Also 15 in utf-8 is blank. (Reference Link: http://www.utf8-chartable.de/unicode-utf8-table.pl?utf8=dec&unicodeinhtml=dec )

I have set the column types to int during create table, but on retrieving I am not getting the int values.

How do I get the correct values to be retrieved. I do not want the utf-8 values.

Thanks

I am using cassandra 1.2.4

Below is my code written in phpcassa:

require_once(__DIR__.'/../lib/autoload.php');

use phpcassa\Connection\ConnectionPool;
use phpcassa\ColumnFamily;
use phpcassa\SystemManager;
use phpcassa\Schema\StrategyClass;
use cassandra\Compression;
use cassandra\ConsistencyLevel;


$pool = new ConnectionPool("prod",array('X.X.X.X','X.X.X.X'));

$raw = $pool->get();
$rows = $raw->client->set_cql_version("3.0.0");


$rows = $raw->client->execute_cql3_query('USE prod;', Compression::NONE,    ConsistencyLevel::ONE );
$rows = $raw->client->execute_cql3_query('CREATE TABLE emp (
  empID int,
  deptID int,
  first_name varchar,
  last_name varchar,
  PRIMARY KEY (empID, deptID));
  ', Compression::NONE, ConsistencyLevel::ONE );  


$rows = $raw->client->execute_cql3_query('INSERT INTO emp (empID, deptID, first_name,     last_name)
  VALUES (104, 15, \'jane\', \'smith\');
  ', Compression::NONE, ConsistencyLevel::ONE );  


$rows = $raw->client->execute_cql3_query('SELECT * FROM emp WHERE empID IN (2,104)     ORDER BY deptID ASC;', Compression::NONE, ConsistencyLevel::ONE );  

echo "<pre>";
print_r($rows);
echo "<pre>";

The output generated is:

cassandra\CqlRow Object
               (
                [key] => 
                [columns] => Array
                    (
                        [0] => cassandra\Column Object
                            (
                                [name] => empid
                                [value] => h
                                [timestamp] => 
                                [ttl] => 
                            )

                        [1] => cassandra\Column Object
                            (
                                [name] => deptid
                                [value] => 
                                [timestamp] => 
                                [ttl] => 
                            )

                        [2] => cassandra\Column Object
                            (
                                [name] => first_name
                                [value] => jane
                                [timestamp] => 
                                [ttl] => 
                            )

                        [3] => cassandra\Column Object
                            (
                                [name] => last_name
                                [value] => smith
                                [timestamp] => 
                                [ttl] => 
                            )

                    )

            )

can you share your cassandra version as well as your schema? — abhi, Apr 22 '13 at 05:35

Masum · Accepted Answer · 2013-05-15T18:28:43.007

Though a bit late answer, but I was desperately searching for the solution while writing my own cassandra-wrapper for Yii framework. I came up with something like the following class that eliminates the problem of incorrect binary to different data types in cassandra including integer types:

Yii::import('ext.phpcassa.autoload');
require_once('protected/extensions/phpcassa/autoload.php');

use phpcassa\Connection\ConnectionPool;
use phpcassa\ColumnFamily;
use phpcassa\ColumnSlice;
use phpcassa\SystemManager;
use phpcassa\Schema\StrategyClass;
use phpcassa\Schema\DataType;

class ACassandraConnection extends CApplicationComponent {

    protected $_pool = null;
    public $keyspace = null;
    public $servers = null; 

    /**
     * Establish connection to cassandra cluster
     * @return object 
     */
    private function _get_raw() {
        if ($this->_pool === null) {
            $this->_pool = new ConnectionPool($this->keyspace, $this->servers);
        }
        return $this->_pool->get();
    }


    public function cql_query($query) {
        $raw = $this->_get_raw();
        $cql_result = $raw->client->execute_cql3_query($query, cassandra\Compression::NONE, cassandra\ConsistencyLevel::ONE);
        $this->_pool->return_connection($raw); 
        return $cql_result;
    }

    public function cql_get_rows($cql_result) {
        if ($cql_result->type == 1) { 
            $rows = array(); 
            foreach ($cql_result->rows as $rowIndex => $cqlRow) { 
                $cols = array(); 
                foreach ($cqlRow->columns as $colIndex => $column) {
                    $type = DataType::get_type_for($cql_result->schema->value_types[$column->name]);
                    $cols[$column->name] = $type->unpack($column->value);
                }
                $rows[] = $cols;
            }
            return $rows; 
        } else {
            return null;
        }
    }


    /**
     * Perform garbage collection 
     */
    public function __destruct() {
        if($this->_pool !== null) {
            $this->_pool->close();
        }
    }

}

The solution works for integer types. But if a column type is counter, then instead of integer it considers it as byte. It does not work for counter column types. — user1058797, Jun 18 '13 at 09:36

abhi · Answer 2 · 2013-04-22T07:10:32.027

1

I will say please review your solution, i have tried exactly the same thing you described as your problem, but its working normal for me. Might be at the time of creation you have used utf-8 as their type.

Schema

CREATE COLUMNFAMILY test(empID int, deptID int, first_name text, last_name text, PRIMARY KEY(empID));

Insert

INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (104, 15, 'jane', 'smith');

Retrieve

SELECT * FROM test ;

empid  | deptid | first_name | last_name
-------+--------+------------+-----------
104    |     15 |       jane |     smith

edited Apr 22 '13 at 07:10

answered Apr 22 '13 at 05:43

abhi

4,762
4
29
49

I am using the below create table syntax. I have not used create columnfamily: ` CREATE TABLE emp ( empID int, deptID int, first_name varchar, last_name varchar, PRIMARY KEY (empID, deptID));` – user1058797 Apr 22 '13 at 10:06
no i am working with phpcassa library. I will update the code i am using above. – user1058797 Apr 22 '13 at 13:52
Yes I am able to view in cqlsh. But it is not showing in php using phpcassa library. – user1058797 Apr 23 '13 at 06:53
In that case, may be @Tyler Hobbs can give you a perfect answer – abhi Apr 23 '13 at 10:51

Cassandra is not retrieving the correct integer value

2 Answers2

Linked