CODEWITHSUNDEEP
javacassandracql
Mewken
Mewken

Reputation: 45

Invalid STRING constant OR mismatched input ':' expecting '}' - Cassandra DB

I have two issues:

  1. When I am executing the code below:

    public void insertFarmers(int id, HashMap<String, String> the_farmer, List delivery) {

     Cluster cluster = Cluster.builder()
         .addContactPoints(serverIP)
         .build();
 try (Session session = cluster.connect("farm_db")) {
     String insert_query = "INSERT INTO farmers (farmer_id, delivery, the_farmer) VALUES ( "
             + "'" +id+ "',"
             + "'" +delivery + "',"
             + "'" +the_farmer + "')";

    session.execute(insert_query);
 }

    }

... along with this code in my main-class:

HashMap<String, String> the_farmer = new HashMap<>();
    the_farmer.put("Name ", " The name ");
    the_farmer.put("Farmhouse ", " Varpinge Gard");
    the_farmer.put("Foods ", " Fruits & Vegetables");

    List<String> delivery = new ArrayList<>();
    delivery.add("Malmo Hus parkering");
    delivery.add("Lund Golfbana Varpinge");


    employeeService.insertFarmers(1, the_farmer, delivery);

...I get this error:

InvalidQueryException: Invalid STRING constant ([Malmo Hus parkering, Lund Golfbana Varpinge]) for "delivery" of type list<text>
  1. When I try this instead, below I get this error:
        Cluster cluster = Cluster.builder()
                .addContactPoints(serverIP)
                .build();
        try (Session session = cluster.connect("farm_db")) {
            SimpleStatement insert_query = new
                    SimpleStatement("INSERT INTO farmers (farmer_id, delivery, the_farmer) VALUES " +
                    "(1, {'Name' : 'The name' , 'Farmhouse' : 'Varpinge Gard' : 'Foods' : 'Fruits & Vegetables'}, " +
                    "['Malmo Hus parkering','Lund Golfbana (Varpinge)']);", id, delivery, the_farmer);
              session.execute(insert_query);
        }
    } 


The error message: 
SyntaxError: line 1:118 mismatched input ':' expecting '}' (...'The name' , 'Farmhouse' : 'Varpinge Gard' [:] 'Food...)

Upvotes: 1

Views: 437

Answers (1)

Aaron
Aaron

Reputation: 57748

So type handling with collections can be tricky. I was able to reproduce your error.

I was able to get this to work by using a prepared statement:

String insert_query = "INSERT INTO farmers (farmer_id, delivery, the_farmer) VALUES (?,?,?)";
PreparedStatement pStatement = session.prepare(insert_query);
BoundStatement bStatement = pStatement.bind(id, delivery, the_farmer);
    
session.execute(bStatement);

For the second question, it looks like you have transposed variables for delivery and the_farmer.

What is the difference between 'PreparedStatement' and 'SimpleStatement'?

A SimpleStatement will parse the query on every execution, so it can be a little costly for statements that repeat. On the other hand, a PreparedStatement will parse the query only once, and then store it in the prepared statement cache (in the cluster) to optimize future uses.

PreparedStatement will also provide type checking on the client side, which I think is the core issue here. Basically, if you can get your Java code to compile using a PreparedStatement, it shouldn't run into type issues at execution time.

Upvotes: 1

Related Questions