6

Here is my CQL table:

CREATE TABLE user_login (
    userName varchar PRIMARY KEY,
    userId uuid,
    fullName varchar,
    password text,
    blocked boolean
);

I have this datastax java driver code

PreparedStatement prepareStmt= instances.getCqlSession().prepare("INSERT INTO "+ AppConstants.KEYSPACE+".user_info(userId, userName, fullName, bizzCateg, userType, blocked) VALUES(?, ?, ?, ?, ?, ?);");

batch.add(prepareStmt.bind(userId, userData.getEmail(), userData.getName(), userData.getBizzCategory(), userData.getUserType(), false));


PreparedStatement pstmtUserLogin = instances.getCqlSession().prepare("INSERT INTO "+ AppConstants.KEYSPACE+".user_login(userName, userId, fullName, password, blocked) VALUES(?, ?, ?, ?, ?) IF NOT EXIST");

batch.add(pstmtUserLogin.bind(userData.getEmail(), userId, userData.getName(), passwordEncoder.encode(userData.getPwd()), false));
            instances.getCqlSession().executeAsync(batch);

Here the problem is that if I remove IF NOT EXIST all work fine but if put it back it simply do not insert records in table nor throw any error.

So how will i know that i am inserting duplicate userName ?

I am using cassandra 2.0.1

Manish Kumar
  • 10,214
  • 25
  • 77
  • 147

2 Answers2

13

Use INSERT... IF NOT EXISTS, then you can use ResultSet#wasApplied() to check the outcome:

ResultSet rs = session.execute("insert into user (name) values ('foo') if not exists");
System.out.println(rs.wasApplied());

Notes:

  • this CQL query is a lightweight transaction, that carries performance implications. See this article for more information.
  • your example only has one statement, you don't need a batch
Olivier Michallat
  • 2,302
  • 11
  • 13
  • i have multiple statement thats why i am using batch. Here for simplicity i am showing just one statement – Manish Kumar Jul 13 '15 at 18:25
  • In my original question i made this changes `instances.getCqlSession().execute(batch).wasApplied()` and i am getting `com.datastax.driver.core.exceptions.UnavailableException: Not enough replica available for query at consistency QUORUM (2 required but only 1 alive)` I am running this app on my local system – Manish Kumar Jul 13 '15 at 18:32
  • You shouldn't use batch statements in this case (see [the DataStax documentation](http://docs.datastax.com/en/cql/3.1/cql/cql_using/useBatch.html)). And that won't work with conditional updates anyway, unless they apply to the same partition key (see [this other article](http://www.datastax.com/dev/blog/cql-in-2-0-6)). – Olivier Michallat Jul 13 '15 at 18:43
  • you mean batch should not be applied on two different table? Wht will be the possible solution in my scenario ? – Manish Kumar Jul 13 '15 at 18:47
  • Regarding your consistency error, check the `replication_factor` on your keyspace. If you only have one node it should be 1. Here it appears to be 3 (since quorum is 2). – Olivier Michallat Jul 13 '15 at 18:55
  • Re: solution in your scenario -- execute two separate queries (no batch). Use `IF NOT EXIST` only for the first table. If that fails, don't try to insert in the second table. If all clients do it in the same order you'll be fine. – Olivier Michallat Jul 13 '15 at 19:00
  • but if i do seperate query, and ist query is success and 2nd query is failed then there will be inconsistent record. Cos i want to keep user record in both table and that is mandatory. – Manish Kumar Jul 14 '15 at 03:06
-5

Looks like you need an ACID transaction and Cassandra, simply put, is not ACID. You have absolutely no guarantee that in the interval you check if username exists it will not be created from someone else. Besides that, in CQL standard INSERT and UPDATE do the same thing. They both write a "new" record marking the old ones deleted. If there are old records or not is not important. IF you want to authenticate or create a new user on the fly, I suppose you can work on a composite key username + password, and to your query as update where username =datum AND password = datum. IN this way, if the user gives you a wrong password your query fails. If user is new, he cant give a "wrong" password, and so his account is created. You can now test for a field like "alreadysubscribed" which you only set after the first login, so in case of a "just created" user will be missing

Alar
  • 760
  • 4
  • 11