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

prepared statement for select query with where clause in java
update query in jdbc using preparedstatement
difference between statement and preparedstatement in javatpoint
prepared statement dynamic where clause java
java prepared statement dynamic parameters
how to use prepared statement in jsp
how to pass multiple parameters in prepared statement
java code for updating records in database

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']

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:

  1. 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).
  2. 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);
}

A Java MySQL UPDATE example, The first thing we need for our Java UPDATE example is a sample MySQL database table. not null, date_created timestamp default now(), is_admin boolean, Create a SQL UPDATE statement, using the Java PreparedStatement syntax. After this Java MySQL UPDATE query runs, you can verify that it� Java SQL FAQ: Can you provide a Java PreparedStatement example that shows how to use a SQL UPDATE? Sure. I have quite a few examples on this website, just see the "Related" section for those. But for now, here's a short sample method that performs a JDBC SQL UPDATE using a Java PreparedStatement:

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

A Java PreparedStatement example with a SQL SELECT statement , Java/JDBC FAQ: Can you share an example of a PreparedStatement with a SQL For me, the hard part of this example is figuring out how to use wildcard executeQuery(); while ( rs.next() ) { Blog blog = new Blog(); blog. As you can see, the setup work is a lot like using a PreparedStatement with an� 8 jQuery .val() not working for input fields Dec 15 '16 1 Does anyone have working syntax for CQL UPDATE with preparedStatement() they can share with me? Apr 26 '15

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);

JDBC Statement vs PreparedStatement, Today we will look into JDBC Statement vs PreparedStatement and some SQL Injection Example. While working with JDBC for database connectivity, we can use Statement or I have Users table in my local MySQL database with following data. public static Connection getConnection() throws ClassNotFoundException,� 1 Kerberos authentication does not work in Mongodb Enterprise 3.2 Jan 14 '16 1 Does anyone have working syntax for CQL UPDATE with preparedStatement() they can share with me? Jul 19 '16

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.

DataStax Java Driver - Query builder, The query builder is a utility to generate CQL queries programmatically. try ( CqlSession session = CqlSession.builder().build()) { Select query thread safety : you can share built queries across threads, without any race condition or badly SELECT * FROM user WHERE id=? PreparedStatement preparedSelectUser� Inserting Records using the Prepared Statement: 32. Count Records using the Prepared Statement: 33. Deleting Records using the Prepared Statement: 34. Use PreparedStatement Twice: 35. Rows affected when updating data in database table: 36. Inserting with a prepared statement that uses the various setXXX() methods.

DataStax Java Driver - Prepared statements, When you prepare the statement, Cassandra will parse the query string, cache When you bind and execute a prepared statement, the driver will only send the To turn the statement into its executable form, you need to bind it to create a setString(1, "LCD screen"); // Named setters: BoundStatement bound = ps2.bind() . SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

JDBC PreparedStatement - Insert a row, By mkyong | Last updated: June 6, 2019 A JDBC PreparedStatement example to insert a row into the BigDecimal; import java.sql. will this work in a multithreaded environment ,having simultaneous when i insert data to database have Constraint key other table. i don't Can you help me, please. 0. As you can see, we are passing parameter (?) for the values. Its value will be set by calling the setter methods of PreparedStatement. Why use PreparedStatement? Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.

Java JDBC CRUD Tutorial: SQL Insert, Select, Update, and Delete , Java code examples to perform CRUD operations with JDBC: Create, because the query syntax used is standard SQL which is supported by all We will learn how to do insert, query, update and delete database In the above code, we have to close the connection explicitly after finish working with the� PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor