How to do Counter updates using Prepared statements ?

[hey...@gmail.com]

I am not sure how to do counter update using prepared statements. There is only one test in the test suite that updates prepcachetest but it is not a counter column.

When I do this:

stmt := `UPDATE keyspace.table SET value = ? WHERE id = ? and type = ? and ts = ?`
args = []interface {}{"value + 1",id,type, ts}

casserr := c.session.Query(stmt, args).Exec()

I get "Cannot set the value of counter column value (counters can only be incremented/decremented, not set)"

If I have:

stmt := `UPDATE keyspace.table SET value = value + ? WHERE id = ? and type = ? and ts = ?`
args := []interface {}{1,id,type, ts}

I get query length mismatch error.

Any suggestions?


PS: I can do counter update using batch successfully, but that is not the use case. Why impose prepared statements only use for non-batch queries ?

[Chris Bannister]

The syntax for using counters in CQL is like this,

UPDATE keyspace.table SET value = value + 1 WHERE id = ? and type = ? and ts = ?

The reason that we have to prepare every statement is that we don't know how to convert the types the user passes in to the types of the columns in cassandra, and we only get this information after doing the prepare.

--
You received this message because you are subscribed to the Google Groups "gocql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gocql+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chris Bannister]

Hi,

After looking at it again it looks like the following is happening, the first statement fails at the prepare phase as the query is invalid. The second one fails when we try to convert the types to the column values but dont have the same number of arguments to query as the statement has according to Cassandra. The reason for this (if the statement is being executed as per your previous email) as far as I can see is that the arguments are being passed as a single argument not as the full number of query params.

Instead of session.Query(query, args) try using session.Query(query, args...).

The reason the first one gets passed as a single argument so we check that len(args) == 4 where args is passed into the query function as []interface{ []interface{ <args > } } where instead we expect the arguments to be a flat array (vararg).

To expand this the first method is passed like, session.Query(query, []interface {}{1,id,type, ts}) whereas the second one is passed as session.Query(query, 1, id, type,  ts)

Hope that clears it up, sorry for my earlier email which was not completely correct in the understanding of the problem.

Chris

[Ben Hood]

Hi Nick,

On Wed, Apr 29, 2015 at 5:11 PM, Chris Bannister <c.ban...@gmail.com> wrote:
> The reason for this (if the statement is being
> executed as per your previous email) as far as I can see is that the
> arguments are being passed as a single argument not as the full number of
> query params.

It's always helpful to have a complete (yet cut down) and runnable
example that demonstrates the behavior you are asking about.

In this case, Chris is having to assume stuff about your actual code.
On face value it looks like he might have done quite a good job:

1) "UPDATE keyspace.table SET value = ? WHERE id = ? and type = ? and
ts = ?” will not prepare because it is invalid CQL.

2) "UPDATE keyspace.table SET value = value + ? WHERE id = ? and type
= ? and ts = ?” is valid CQL, but you are passing in the wrong number
of arguments (assuming that you use the same code as the first
example, i.e. "session.Query(statement, args)" instead of
session.Query(statement, args...)

FWIW you can use bind variables to increment/decrement a counter
column, here is an example where this is done:
https://github.com/relops/cqlc/blob/master/cqlc/cqlc_test.go#L298

HTH,

Ben

[Nick]

Thanks a lot @Chris, that worked for me. 

@Ben I will try to provide more complete scenario next time. Thanks for the pointer. 

[Chris Bannister]

Hi Nick,

I'm glad that resolved your issue, if you have any other questions or problems please don't hesitate to ask.

Chris