Cannot insert into Cassandra table, getting SyntaxError

Question

I have an assignment where I have to build a Cassandra database. I have connected Cassandra with IntelliJ, i'm writing in java and the output is shown in the command line.

My keyspace farm_db contains a couple of tables in wish i'm would like to insert data. I would like to insert the data with two columns and a list all in one row, in the table 'farmers'. This is a part of my database so far:

cqlsh:farm_db> use farm_db;
cqlsh:farm_db> Describe tables;

farmers              foods_dairy_eggs        foods_meat
foods_bread_cookies  foods_fruit_vegetables

cqlsh:farm_db> select * from farmers;

 farmer_id | delivery | the_farmer
-----------+----------+------------

This is what i'm trying to do:

[Picture of what i'm trying to do][1]

I need to insert the collection types 'list' and 'map' in 'farmers' but after a couple of failed attempts with that I tried using hashmap and arraylist instead. I think this could work but i seem to have an error in my syntax and I have no idea what the problem seem to be:

Exception in thread "main" com.datastax.driver.core.exceptions.SyntaxError: line 1:31 mismatched input 'int' expecting ')' (INSERT INTO farmers (farmer_id [int]...)

Am I missing something or am I doing something wrong?

This is my code:

public class FarmersClass {

public static String serverIP = "127.0.0.1";
public static String keyspace = "";

//Create db
public void crateDatabase(String databaseName) {
    Cluster cluster = Cluster.builder()
            .addContactPoints(serverIP)
            .build();

    keyspace = databaseName;
    Session session = cluster.connect();
    String create_db_query = "CREATE KEYSPACE farm_db WITH replication "
            + "= {'class':'SimpleStrategy', 'replication_factor':1};";
    session.execute(create_db_query);


}

//Create table
public void createFarmersTable() {

    Cluster cluster = Cluster.builder()
            .addContactPoints(serverIP)
            .build();

    Session session = cluster.connect("farm_db");
    String create_farm_table_query = "CREATE TABLE farmers(farmer_id int PRIMARY KEY, the_farmer Map <text, text>, delivery list<text>); ";
    session.execute(create_farm_table_query);

}

//Insert data in table 'farmer'.
public void insertFarmers(int id, HashMap< String, String> the_farmer, ArrayList <String> delivery) {

    Cluster cluster = Cluster.builder()
            .addContactPoints(serverIP)
            .build();
    Session session = cluster.connect("farm_db");
    String insert_query = "INSERT INTO farmers (farmer_id int PRIMARY KEY, the_farmer, delivery) values (" + id + "," + the_farmer + "," + delivery + ");";
    System.out.println(insert_query);
    session.execute(insert_query);

    }
}

public static void main(String[] args) {

    FarmersClass farmersClass = new FarmersClass();

    //  FarmersClass.crateDatabase("farm_db");

    //  FarmersClass.createFarmersTable();

    //Collection type map
    HashMap<String, String> the_farmer = new HashMap<>();
    the_farmer.put("Name", "Ana Petersen ");
    the_farmer.put("Farmhouse", "The great farmhouse");
    the_farmer.put("Foods", "Fruits & Vegetables");

    //Collection type list
    ArrayList<String> delivery = new ArrayList<String>();
    String delivery_1 = "Village 1";
    String delivery_2 = "Village 2";
    delivery.add(delivery_1);
    delivery.add(delivery_2);

    FarmersClass.insertFarmers(1, the_farmer, delivery);
}

Erick Ramirez · Accepted Answer · 2022-08-04 13:02:17Z

1

The problem is the syntax of your CQL INSERT query:

    String insert_query = \
        "INSERT INTO farmers (farmer_id int PRIMARY KEY, the_farmer, delivery)  \
        values (" + id + "," + the_farmer + "," + delivery + ");";

You've incorrectly added int PRIMARY KEY in the list of columns.

The correct format is:

INSERT INTO table_name (pk, col2, col3) VALUES ( ... )

For details and examples, see CQL INSERT. Cheers!

answered Aug 4, 2022 at 13:02

Erick Ramirez

16.5k2 gold badges22 silver badges33 bronze badges

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

4 Comments

Mewken Over a year ago

Hi Erick! Thanks for your answer! :) But i don't get it. Isn't that what i have written? I don't understand how i've added the primary key incorrectly because I have the table_name and the name of column1-datatype-primary key.

Erick Ramirez Over a year ago

Your query has int PRIMARY KEY in it -- INSERT INTO farmers (farmer_id int PRIMARY KEY, ... ) VALUES (...) which is wrong. It should just be INSERT INTO farmers (farmer_id, ... ) VALUES (...).

Mewken Over a year ago

Ah! Tank you! But, i still get a syntax error. This time it's this one: line 1:71 no viable alternative at input '=' (..., delivery) values (1,[{]Foods ...)

Madhavan Over a year ago

You need to follow the correct syntax, INSERT INTO table_name(column1_identifier, ..., columnn_identifier) VALUES (column1_value, ..., columnn_value);. See the documentation for syntax. What you've done instead here is that INSERT INTO table_name(column1_identifier column1_data_type PRIMARY KEY, ...) VALUES (...);

Collectives™ on Stack Overflow

Cannot insert into Cassandra table, getting SyntaxError

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related