IN operator

[Jacob]

The C* java driver has the querybuilder class that lets you give the IN Operator a list of things to compare against. Is there a way to use the use the IN operator in a similiar fashion using the C++ driver? Putting a question mark for everything in the list, and use statement_bind_*, probably isn't practical for the number of things we would be binding to the statement. Being able to use the IN operator on a partition key is something that my team finds really useful in C* 3+, and we're hoping to be able to harness this ability using the C++ driver.

[Michael Fero]

Jacob,

The DataStax C/C++ driver does not have a query builder like the Java driver; however you can use a format string to accomplish the same goal and replace the formatter with your IN clause and VALUES and execute with a simple statement.

Below is an example using sprintf to create a query string that can be used with a zero bound parameter simple statement. This example will generate 50,000 records and run two different sized IN clause examples using the SELECT_QUERY_FORMAT string.

#include <limits.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>

#include <cassandra.h>

#define CREATE_KEYSPACE "CREATE KEYSPACE IF NOT EXISTS in_clause " \
"WITH replication = { " \
"'class': 'SimpleStrategy', " \
"'replication_factor': 1 " \
"}"

#define CREATE_TABLE "CREATE TABLE in_clause.example " \
"(" \
"cluster text, date text, time text, value int, " \
"PRIMARY KEY ((cluster, date), time)" \
")"

#define INSERT_QUERY "INSERT INTO in_clause.example " \
"(cluster, date, time, value) VALUES (?, ?, ?, ?)"

#define SELECT_QUERY_FORMAT "SELECT * FROM in_clause.example " \
"WHERE cluster IN (%s) " \
"AND date = '%s' " \
"AND time >= '%s' " \
"AND time <= '%s'"

void print_error(CassFuture* future) {
const char* message;
size_t message_length;
cass_future_error_message(future, &message, &message_length);
fprintf(stderr, "Error: %s: %.*s\n",
cass_error_desc(cass_future_error_code(future)),
(int)message_length, message);
}

void create_keyspace(CassSession* session) {
CassStatement* statement = cass_statement_new(CREATE_KEYSPACE, 0);
CassFuture* future = cass_session_execute(session, statement);
CassError rc = cass_future_error_code(future);

if (rc != CASS_OK && rc != CASS_ERROR_SERVER_ALREADY_EXISTS) {
print_error(future);
}

cass_statement_free(statement);
cass_future_free(future);
}

int create_table(CassSession* session) {
int created = 0;
CassStatement* statement = cass_statement_new(CREATE_TABLE, 0);
CassFuture* future = cass_session_execute(session, statement);
CassError rc = cass_future_error_code(future);

if (rc == CASS_OK) {
created = 1;
} else if (rc != CASS_ERROR_SERVER_ALREADY_EXISTS) {
print_error(future);
}

cass_statement_free(statement);
cass_future_free(future);
return created;
}

void generate_records(CassSession* session) {
CassFuture* prepared_future = cass_session_prepare(session, INSERT_QUERY);
if (cass_future_error_code(prepared_future) == CASS_OK) {
const CassPrepared* prepared = cass_future_get_prepared(prepared_future);
size_t i;
srand(time(NULL));

printf("Inserting 50,000 records into in_clause.example\n");
for (i = 0; i < 50000; ++i) {
CassStatement* statement = NULL;
CassFuture* future = NULL;
char cluster[10];
int cluster_suffix;
char date[10];
int date_suffix;
char time[5];
int hour;
int minute;
cass_int32_t value;

cluster_suffix = rand() % 10 + 1;
sprintf(cluster, "cluster_%02d", cluster_suffix);
date_suffix = rand() % 30 + 1;
sprintf(date, "2016-10-%02d", date_suffix);
hour = rand() % 10 + 10;
minute = rand() % 59;
sprintf(time, "%d:%02d", hour, minute);
value = rand() % USHRT_MAX;

statement = cass_prepared_bind(prepared);
cass_statement_bind_string_n(statement, 0, cluster, 10);
cass_statement_bind_string_n(statement, 1, date, 10);
cass_statement_bind_string_n(statement, 2, time, 5);
cass_statement_bind_int32(statement, 3, value);
future = cass_session_execute(session, statement);
if (cass_future_error_code(future) != CASS_OK) {
print_error(future);
}
cass_statement_free(statement);
cass_future_free(future);
}

cass_prepared_free(prepared);
} else {
print_error(prepared_future);
}

cass_future_free(prepared_future);
}

void print_results(CassFuture* future) {
if (cass_future_error_code(future) == CASS_OK) {
const CassResult* result = cass_future_get_result(future);
CassIterator* iterator = cass_iterator_from_result(result);

while (cass_iterator_next(iterator)) {
const CassRow* row = cass_iterator_get_row(iterator);
const char* cluster;
size_t cluster_length;
const char* date;
size_t date_length;
const char* time;
size_t time_length;
cass_int32_t value;

cass_value_get_string(cass_row_get_column(row, 0), &cluster, &cluster_length);
cass_value_get_string(cass_row_get_column(row, 1), &date, &date_length);
cass_value_get_string(cass_row_get_column(row, 2), &time, &time_length);
cass_value_get_int32(cass_row_get_column(row, 3), &value);

printf("Cluster: %.*s | Date: %.*s | Time: %.*s | Value: %d\n",
(int)cluster_length, cluster,
(int)date_length, date,
(int)time_length, time,
value);
}

cass_result_free(result);
cass_iterator_free(iterator);
} else {
print_error(future);
}
}

int main(int argc, char* argv[]) {
CassFuture* connect_future = NULL;
CassCluster* cluster = cass_cluster_new();
CassSession* session = cass_session_new();
char* hosts = "127.0.0.1";
if (argc > 1) {
hosts = argv[1];
}

cass_cluster_set_contact_points(cluster, hosts);
connect_future = cass_session_connect(session, cluster);

if (cass_future_error_code(connect_future) == CASS_OK) {
CassStatement* statement = NULL;
CassFuture* future = NULL;
CassFuture* close_future = NULL;
char query[256];

create_keyspace(session);
if (create_table(session)) {
generate_records(session);
}

sprintf(query, SELECT_QUERY_FORMAT,
"'cluster_01', 'cluster_03'",
"2016-10-06",
"12:00",
"14:00");
statement = cass_statement_new(query, 0);
future = cass_session_execute(session, statement);
print_results(future);
cass_statement_free(statement);
cass_future_free(future);

sprintf(query, SELECT_QUERY_FORMAT,
"'cluster_02', 'cluster_04', 'cluster_06'",
"2016-10-07",
"14:00",
"16:00");
statement = cass_statement_new(query, 0);
future = cass_session_execute(session, statement);
print_results(future);
cass_statement_free(statement);
cass_future_free(future);

close_future = cass_session_close(session);
cass_future_wait(close_future);
cass_future_free(close_future);
} else {
print_error(connect_future);
}

cass_future_free(connect_future);
cass_cluster_free(cluster);
cass_session_free(session);

return 0;
}

~Fero