Using UUIDs in Queries

[Paul Monday]

We jumped to Helenus from cassandra-client and are having great success with it so far. We are CQL 3 and it is super nice to be able to take full advantage of it.

BUT, there is one thing that we are having some issues with, UUID support. I would imagine we are making a simple mistake with formatting but, something just doesn't seem right.

Here is an example, I'm getting a v4 UUID as a request parameter. I've tried using this as a string or a uuid, here I'm storing it after with the built-in UUID support:

var provider_id = app.helenus.UUID(req.body.provider_id);

Now, I turn around and construct a query string .. the README says to do this with ? but it seems like the code likes format strings, I've tried both but %s seems right (since ? gives me an error of too many parameters)
var query = "SELECT verification_fields FROM providers WHERE provider_id=%s";
var params = [provider_id];

This results in a HelenusInvalidRequestException

The ONLY way I've been able to get the query to work is to do build the query by appending and treating the provider_id as a string:
var provider_id = req.body.provider_id;
var query = "SELECT verification_fields FROM providers WHERE provider_id=" + provider_id;
var params = [];

Unfortunately, there are so may UUIDs in our schema that this is unreasonable.

There must be some very simple thing I'm missing :( Obviously you've thought about UUIDs since there are serializers and such, I can only guess I missed a blurb of information somewhere :(

Thoughts?

Paul Monday

[Russell Bradberry]

The "?" is the correct way to do it, are you sure that the number of question marks is equal to the length of params?

Paul Monday

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

[Paul Monday]

Ahh, this is better (it accepts the query)... but still get the HelenusInvalidRequestException, I added the pool.query just in case:

         var provider_id = app.helenus.UUID(req.body.provider_id);

         var query = "SELECT verification_fields FROM providers WHERE provider_id=?";

        var params = [provider_id];

         app.pool.cql(query, params, function (err, rows) {

        if (err) {

            return next(err, null);

            }

I checked the provider_id and it is a valid UUID v4.

Is there a way to sniff the query being sent?

[Russell Bradberry]

There's no easy way to do that but here is where the code is generating the CQL: https://github.com/simplereach/helenus/blob/master/lib/connection.js#L61-L116

You might be able to add a log line in there or duplicate those methods to see what the output is.

[Paul Monday]

Very interesting, I simply put a console.log into the formatCQL function (console.log(cql);)

        var provider_id = req.body.provider_id;

                        var query = "SELECT verification_fields FROM providers WHERE provider_id=?";

                        console.log(provider_id);

                        var params = [provider_id];

Yields:

SELECT verification_fields FROM providers WHERE provider_id='756d529f-bbda-4308-bb34-871a6b1cf972'

This is a HelenusInvalidRequest (I think the '' give us a string that doesn't work for UUID comparison)

I try to use the helenus UUID how I thought it would work but provider_id is undefined and therefore results in formatCQL inserting a null

        var provider_id = app.helenus.UUID(req.body.provider_id);

undefined

SELECT verification_fields FROM providers WHERE provider_id=NULL

POST /verify/create 200 32ms - 46b

I try the node-uuid library but that gives me a JSON array of bytes that doesn't work at all.

Is UUID not parsing v4 UUIDs from strings?

Paul

[Russell Bradberry]

The UUID version should not matter.  Actually, i remember this in the issues.  https://github.com/simplereach/helenus/issues/98  Try that as a workaround for the time being.

[Paul Monday]

That did it.

Here is the final resolution for now:

1. Bring in the provider_id as a string from the request body:        var provider_id = req.body.provider_id;

2. Modify the connection.js / escapeCQL method and put these right up front ...

function escapeCQL(val) {

  if (typeof val === 'string' && val.match(/\w{8}\-\w{4}\-\w{4}\-\w{4}\-\w{12}/)) {

      return val;

  }

  if (typeof val === 'object' && typeof val.hex) {

      return val.toString();

  }

.....

3. Make the select clause unquoted (as expected):  

                        var query = "SELECT verification_fields FROM providers WHERE provider_id=?";

                        console.log(provider_id);

                        var params = [provider_id];

Thank you SO much for the quick help ... I apologize for not seeing the issue.  I thought I had mined as much as I could :(

Paul Monday

[Russell Bradberry]

no problem, we are working on a new version that uses the CQL native protocol as well. im just swamped so its getting delayed.  

[maxim.dom...@parsci.com]

I'd add one more condition - to avoid unnecessary UUID stringifying:

function escapeCQL(val) {
  if (val instanceof UUID) {

      return val;
  }
  if (typeof val === 'string' && val.match(/\w{8}\-\w{4}\-\w{4}\-\w{4}\-\w{12}/)) {
      return val;
  }

...

[Edwin McKain]

Does anybody know if we can use UUIDs without monkey-patching connection.js ?

I'm using helenus 0.6.8 and node-uuid 1.4.1 but I'm still having 'HelenusInvalidRequestException' issues, like this one:

HelenusInvalidRequestException: Invalid STRING constant (8e1e36b0-7931-11e3-999f-21973d0a8973) for id of type uuid


Thanks!.

[Russell Bradberry]

Is your value a string or an instance of helenus.UUID ?

We removed the regex check for a UUID in https://github.com/simplereach/helenus/pull/122 because it incorrectly assumed that all UUIDs were formatted that way and that all things formatted that way were UUIDs