CQL query working in DbVisualizer but not working in AdonisJS

Question

I am running a CQL query in DbVisualizer tool which is working fine, but same query when I am running through AdonisJS (nodejs) I am getting below error:

Error executing Redis command: ResponseError: line 1:74 no viable alternative at input '(' (...train_number = '95233' ORDER BY [(]...)
    at FrameReader.readError (/home/dinesh/Desktop/Crowdsource/hello-world/node_modules/cassandra-driver/lib/readers.js:389:17)
    at Parser.parseBody (/home/dinesh/Desktop/Crowdsource/hello-world/node_modules/cassandra-driver/lib/streams.js:209:66)
    at Parser._transform (/home/dinesh/Desktop/Crowdsource/hello-world/node_modules/cassandra-driver/lib/streams.js:152:10)
    at Parser.Transform._write (node:internal/streams/transform:171:8)
    at writeOrBuffer (node:internal/streams/writable:564:12)
    at _write (node:internal/streams/writable:493:10)
    at Parser.Writable.write (node:internal/streams/writable:502:10)
    at Protocol.ondata (node:internal/streams/readable:1007:22)
    at Protocol.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12) {
  info: 'Represents an error message from the server',
  code: 8192,
  coordinator: '107.80.45.9:9042',
  query: `SELECT * FROM train_activities_dump WHERE train_number = '95233' ORDER BY ((CAST("latitude" as float) - 19.061322371609194) * (CAST("latitude" as float) - 19.061322371609194)) + ((CAST("longitude" as float) - 72.874012291431427) * (CAST("longitude" as float) - 72.874012291431427)) ASC LIMIT 1`
}

exact query:

SELECT * FROM train_activities_dump WHERE train_number = '95233' ORDER BY ((CAST("latitude" as float) - 19.061322371609194) * (CAST("latitude" as float) - 19.061322371609194)) + ((CAST("longitude" as float) - 72.874012291431427) * (CAST("longitude" as float) - 72.874012291431427)) ASC LIMIT 1

when I run in DbVisualizer tool it is running.

This is my cassandra db schema:

CREATE TABLE 
    train_activities_dump 
    ( 
        train_number             TEXT, 
        time                     TIMESTAMP, 
        gps_status               TEXT, 
        latitude                 TEXT, 
        longitude                TEXT, 
        nearest_station_code     TEXT, 
        nearest_station_position TEXT, 
        only_time                TIME, 
        speed                    TEXT, 
        train_status             TEXT, 
        PRIMARY KEY (train_number, time) 
    )

and this is the code in my adonisJS controller:


import { cassandraClient } from 'Config/cassandra';

const cquery = `SELECT * FROM train_activities_dump WHERE train_number = '${train_number}' ORDER BY ((CAST("latitude" as float) - ${latitude}) * (CAST("latitude" as float) - ${latitude})) + ((CAST("longitude" as float) - ${longitude}) * (CAST("longitude" as float) - ${longitude})) ASC LIMIT 1`;

const history_data = await cassandraClient.execute(cquery);

console.log(history_data);

this is my Config/cassandra:

import { Client } from 'cassandra-driver';

export const cassandraConfig = {
  contactPoints: ['107.80.45.9','107.80.45.10'], // Cassandra node IPs
  localDataCenter: 'dc1', // Datacenter name
  keyspace: 'uat' // Keyspace name
};

export const cassandraClient = new Client(cassandraConfig);

Also I dont get it is saying Error executing Redis command while this is not a redis command.

if anyone has solution to this, is greatly appreciated :)

Erick Ramirez · Accepted Answer · 2024-06-27 08:09:36Z

0

DbVisualizer uses a JDBC driver to connect to Cassandra clusters. By definition, the JDBC API allows clients to send SQL queries to databases.

The SELECT statement you posted is a SQL query, not CQL. You are able to run it in DbVisualizer because the JDBC driver provides the compatibility layer that translates SQL to CQL.

When using the Datastax Node.js driver, it connects to a Cassandra cluster using the native CQL binary protocol and only understands valid CQL statements, not SQL.

I would recommend using the cqlsh tool that is included in the Apache Cassandra package to verify that your database queries are valid. Cheers!

answered Jun 27, 2024 at 8:09

Erick Ramirez

16.5k2 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CQL query working in DbVisualizer but not working in AdonisJS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related