Does anyone have working syntax for CQL UPDATE with preparedStatement() they can share with me?

Question

When I do a INSERT things go well:

String insertText = "INSERT INTO alpha_screen (alpha, screen) VALUES (?,?)";
PreparedStatement preparedStatement = InsertWordCount.PrepareText(insertText);
BoundStatement boundStatement = preparedStatement.bind(keyvalue, newword);  //INSERT positions
getSession().execute(boundStatement);

cqlsh> SELECT * FROM rant.alpha_screen; ==> The INSERTS are done as expected.

alpha  | screen
-------+--------------
a      |   ['aboard']
c      | ['checking']
p      |     ['pull']
r      |  ['rotting']
t      |     ['time']

Not so much with PrepareText UPDATE:

String insertText = "UPDATE alpha_screen SET screen = screen + ['newword'] WHERE alpha = 'keyvalue' VALUES (?, ?)";
PreparedStatement preparedStatement = InsertWordCount.PrepareText(insertText);
BoundStatement boundStatement = preparedStatement.bind(newword, keyvalue);  // UPDATE positions
getSession().execute(boundStatement); 

com.datastax.driver.core.exceptions.SyntaxError: line 1:79 missing EOF at 'VALUES' (...] WHERE alpha = 'keyvalue' [VALUES] (...)
at com.datastax.driver.core.exceptions.SyntaxError.copy(SyntaxError.java:35)
at com.datastax.driver.core.DefaultResultSetFuture.extractCauseFromExecutionException(DefaultResultSetFuture.java:289)
at com.datastax.driver.core.AbstractSession.prepare(AbstractSession.java:79)
at playlist.model.InsertWordCount.PrepareText(InsertWordCount.java:13)
at playlist.model.CountDAO.screenWord(CountDAO.java:99)

It looks like it should work for this example in the documentation:

Append an element to the list by switching the order of the new element data and the list name in the UPDATE command.

UPDATE users
  SET top_places = top_places + [ 'mordor' ] WHERE user_id = 'frodo';

In fact it works just fine without VALUES:

String insertText = "UPDATE alpha_screen SET screen = screen + ['twoword'] WHERE alpha = 'keyvalue' ";
PreparedStatement preparedStatement = InsertWordCount.PrepareText(insertText);
BoundStatement boundStatement = preparedStatement.bind();  // No VALUES
getSession().execute(boundStatement); 

cqlsh> SELECT * FROM rant.alpha_screen;

alpha     | screen
----------+-----------------------------------
a         |                        ['aboard']
c         |                      ['checking']
p         |                          ['pull']
keyvalue  | ['newword', 'oneword', 'twoword']
r         |                       ['rotting']
t         |                          ['time']

score 4 · Answer 1 · answered Apr 25 '15 at 16:44

I'm not sure what documentation you are looking at (next time, please provide a link). Also, it helps to indicate which language you are using, as well as which driver and version. By reading through your error messages, I was able to ascertain that you are using the DataStax Java Driver, but I am still unsure as to which version you are using (I'm assuming 2.1). In any case VALUES is not a valid clause for the UPDATE statement.

This example shows a methond that inserts a List via a prepared statement with the DataStax Java 2.1 driver:

private static void insertAlphaScreen(Session _session, String _alpha, List<String> _screen)
{
    PreparedStatement statement = _session.prepare("UPDATE stackoverflow2.alpha_screen " +
        "SET screen=? WHERE alpha=?");

    BoundStatement boundStatement = statement.bind(_screen,_alpha);
    _session.execute(boundStatement);
}

When I prepare a list and call it from my main method:

    List<String> screen = new ArrayList<String>();
    screen.add("aboard");
    insertAlphaScreen(session, "a", screen);

This is the result in my CQL table:

 alpha    | screen
----------+-------------
        a |  ['aboard']

Unfortunately, if I just want to add an element to an existing List collection, there really isn't a good way to do it. There are two ways to accomplish this:

Read the collection out first, and then write the entire collection via a prepared statement. Of course, this means you would have to read-in the collection, add the value, and then write it back to Cassandra (possibly using the above method).
The other option is to create your update statement by parsing the string together. This of course doesn't protect you from a SQL (CQL) Injection attack.

For the second option, the method looks similar to how you built your CQL UPDATE statement above:

private static void updateAlphaScreen(Session _session, String _alpha, String _screen)
{
    PreparedStatement statement = _session.prepare("UPDATE stackoverflow2.alpha_screen " +
        "SET screen=screen+['" + _screen + "'] WHERE alpha=?");

    BoundStatement boundStatement = statement.bind(_alpha);
    _session.execute(boundStatement);
}

score 1 · Answer 2 · edited Nov 11 '15 at 19:53

Thank you for taking the time to answer this question. Yes, VALUES is used with INSERT only, never with UPDATE.

I went with your second suggestion but testing showed I could not use prepare/bind.

Also, the SQL injection issue was a real eye opener. What a hoot!

This code does exactly what I need:

String source = "UPDATE alpha_screen SET screen = screen + ['newWord'] WHERE alpha = 'keyvalue'";
String update = source.substring(0,42) + newWord + source.substring(53,69) +  keyvalue + source.substring(77,78) + ";";
getSession().execute(update);

For completeness I am running Cassandra-2.1.2 DataStax Java Driver 2.1.

CQLSH shows [cqlsh 5.0.1 | Cassandra 2.1.2 | CQL spec 3.2.0 | Native protocol v3]

CQL for Cassandra 2.x http://docs.datastax.com/en/cql/3.1/cql/cql_using/use_list_t.html

For the prepare statement: https://cassandra.apache.org/doc/cql3/CQL.html#preparedStatement

score 1 · Answer 3 · answered Jul 19 '16 at 00:20

Here is a java sample for a prepared update statement, Datastax java driver 3, Cassandra 3.6

This is a copy paste from my code, variable names changed. Let me know if there is a typo. I could not find a single example that does not require massaging a string, not even in any Datastax documentation. :(

Declaration:

private PreparedStatement statement_update;

In a startup method when the application starts (you have to connect to a cluster and open a session first, maybe in the same startup method):

Clause filter1= QueryBuilder.eq("column1", QueryBuilder.bindMarker("column1"));
Assignment assignment1 = QueryBuilder.set("column2",QueryBuilder.bindMarker("column2"));
Assignment assignment2 = QueryBuilder.set("column3",QueryBuilder.bindMarker("column3"));

Update update1 = (Update) QueryBuilder.update("keyspace1", "table1")
//                  .where(filter1) // does not work here
//                  .with(assignment1).and(assignment2)  // does not work here
                    .setConsistencyLevel(ConsistencyLevel.LOCAL_QUORUM)
                    ;
update1.where(filter1).ifExists();
update1.with(assignment1).and(assignment2);

statement_update = session.prepare(update1);

Later in a method:

BoundStatement statement = new BoundStatement(this.statement_update);
statement.setString("column1", "some value for filter");
statement.setString("column2", "some value to set/update");
statement.setString("column3", "some other value to set/update");

ResultSet rs = session.execute(statement);

score 0 · Answer 4 · answered Nov 19 '19 at 06:25

Here's the working code you are looking for:

String strCQL = "update EMPLOYRK set Employee_Name=?, Employee_Title=?, Employee_Phone=?, Business_Address=?, Branch_ID=?, Department_ID=? where Employee_ID=?";
PreparedStatement preparedStatement = session.prepare(strCQL);
BoundStatement boundStatement = new  BoundStatement(preparedStatement);
boundStatement.bind(Employee_Name,Employee_Title,Employee_Phone,Business_Address,Branch_ID,Department_ID,Employee_ID);
session.execute(boundStatement);

EMPLOYRK is my table name. I am trying to update 6 columns of my table here.

PS: I used datastax driver for Eclipse to run this program.

Does anyone have working syntax for CQL UPDATE with preparedStatement() they can share with me?

4 Answers4