The Crystal Programming Language Forum

Escape in Crystal DB

Hi, I’m writing an DB application in Crystal and got stuck at providing an array as parameter to a query:

db.query "SELECT ... FROM ... WHERE x IN (?)", my_array

It seems that it is not supported. As an alternative, does Crystal DB API expose functionality for escaping parameters so that I can prepare the query manually?

Try like db.query "SELECT ... FROM ... WHERE x IN (?)", args: my_array.

Alas, that gives me:
Unhandled exception: Incorrect arguments to mysqld_stmt_execute (Exception)

Ah right, yea what you have should be correct. I can’t seem to get it to work with the pg shard either, might be worth making an issue for it?

I’m not sure if the base drivers support such a features (Bind an array to a parameter in prepared statement).

I found a workaround, though. I dynamically generate the query with the number of ‘?’ equal to the number of arguments in the array. Not very beautiful but works.

What database?

Looks like MySQL based on the error give above

Yes, that’s MariaDB, version 5.5.57-MariaDB.

I don’t think mysql supports arrays. You’ll need to interpolate the values and join them using commas.

I as said, I’m dynamically generating the query to contain as many comma-joined '?'s in the “IN” part as the needed.
On the other hand a routine for DB-escaping a string would be quite useful in the more general context.

Yeah, it would be nice if crystal-db abstract this away. Please open an issue in crystal-db.

There you are: https://github.com/crystal-lang/crystal-db/issues/132