Eonblast
Named parameters for prepared statements and as_json output util
If you have query with lots of parameteres you get code which is far far away from readability and maintainability. Using named parameteres ":param_name" instead of positional "?" can be very helpfull.
It could be implemented as a simple wrapper or util, which will get ":params" list from query string and build function to extract those values from proplist passed as argument.
Similar processing can be done on output side. Along with "as_record()" could be something like "as_json()" - each result row transformed to [{<<"key">>, <<"val">>}, ...] proplist.
There is at least one use case, where this little two utils will be realy useful - webservice with JSON api. JSON data parsed into erlang are represented in [{<<"key">>, <<"val">>}, ...] format so with those two utils you can directly pass parsed JSON as parameter to query and pass query result directly to JSON decoder.
I can do this if you like this idea.
I think these are both rather good ideas. I'd say go ahead and implement them.
I have make some work on named parameters here: 12ae453bf692714e4904b8cf8b0a7686104716e5
implementation is for now very simple:
emysql_util:named_parameters() will replace any :<parameter_name> occurences with ? and build list of all found parameter names.
List of found parameters can be later used for maping between named parameters and positional via emysql_util:map_parameters().
Extracting named parameters is done with simple "char-by-char" parser, which respect possible occurences of :<key> inside of string literals and ignore them (i didn't found how to solve this with regexp).
Mixing named parameters with positional is not permitted.
Example:
{ok, Stmt, Map} = emysql_util:named_parameters("INSERT INTO hello_table SET hello_text = :hello_text"),
Parameters = [{"hello_text", <<"'Hello World!'">>}],
Args = emysql_util:map_parameters(Parameters, Map),
emysql:execute(hello_pool, Stmt, Args),
I'm still beginner in erlang, so i will be very hapy if someone checks my work and correct me if i'm wrong. Problem with this solution is user responsibility for storing Maping between named parameters and positional. Better solution would be implementing this directly into main emysql parts (emysql_statements, emysql_conn) but that requires too much work (at least for me).
If this can be merged I will create pull request with added test case and hint in README.
I don't think regular expressions will work easily here, given the way regexes work. Better to have a parser which runs and replaces. I'll take a look at this later this week, I think. The idea seems really good and I would love to have this kind of functionality in the library.
I looked at the named parameters patch. I think it should go in. MySQL has no support for this, and the SQL spec neither. But Oracle has supported this statement type for years and it makes sense you can bind parameters like this in a statement. So I like having the utility function. It can be used by those who want and can be skipped by those who doesn't.
Ok. I will add some documentation and test cases too. Next week i shoul have some time for it.
Is this still ongoing, or is it dead? If it is dead, I'd rather close the ticket.
Mysql doesn't really support :param placeholders in the same way as Oracle does. It uses ? instead. Postgres uses $N for an integer N. So I am not too sure the patch would be needed in the first place. I think it is nice to have, but it seems odd that a driver should handle it.
In any way, we need to figure out a way to decouple the param handling from the rest of the code if we want to go forward with this change.
It was dead for a while. Sorry i got stuck with other work and than moved to next project. Althought in next weeks i should have some free time. If you are still interested, i can look again on this issue and either propose solution or close it by myself.
This is fine by me. I don't have a pressing need for this, and there are things far more important to work on for this driver to fix it. So I will probably attend those rather than handling other stuff.
