Add support for prepared statements

[joerivanruth]

Using prepared statements can be more efficient but PEP 249 does not provide an API for it. Instead, it mentions that

A reference to the operation will be retained by the cursor. If the same operation object is passed in again, then the cursor can optimize its behavior. This is most effective for algorithms where the same operation is used, but different parameters are bound to it (many times).

However, it is not safe for pymonetdb to simply prepend PREPARE to an arbitrary block of SQL code and expect it to work. For example, if there is more than one sql statement in there, only the first would get prepared, the rest would get executed. There could also be EXPLAIN, or ddl statements, misleading comments, or other things that would break.

Pymonetdb just cannot be sure that the user has taken into account that cursor.execute(code_with_percent_s, args) might actually be transformed into cursor.execute('PREPARE ' + code_with_question_marks); ...; cursor.execute('EXEC 42(...)', args).

Instead of having pymonetdb try to guess, I propose we add a .prepare() method to Cursor and maybe Connection. This method would prepare the statement and return an object that knows about the parameter- and result types, and which you can invoke .execute() and .executemany() on.

Alternatively, if we also want applications that are not pymonetdb-aware to benefit from prepared statements, we could augment the responses from the server with information on whether the given statements would have been suitable for PREPARE. Pymonetdb could remember this and if it encounters the same SQL code again, use it to decide whether or not to PREPARE this time.

It has also been suggested to add a connect parameter that says "use prepared statements, I promise not to pass any sql code that cannot be PREPAREd". I am a little uneasy with this because it seems very error prone. The person who writes the Connect statement (or MAPI URL parameter) may not be the same as the person who writes the individual execute statements.

[joerivanruth]

Note that currently, cursor.execute("PREPARE SELECT ? + 42") crashes pymonetdb because it cannot handle the resulting response. At the very least, we should probably fix that crash, and provide access to the number of the prepared statement so the user can then do cursor.execute("EXEC %s(%s)", [prepnr, 99]) and cursor.execute("DEALLOCATE %s", [prepnr]).

Should this be a separate issue?

[joerivanruth]

Also note that MonetDB destroys all existing prepared statements when a transaction aborts due to an error or a ROLLBACK. If we go the way of the .prepare method, it would be nice if the returned object would have the capability to reregister itself when this happens.

[njnes]

on the server side work has started to fix this by recompiling the prepared statements

[njnes]

the server now handles :name paramstyle='named' formated queries. The multi statement remains an issue.

[joerivanruth]

Note that currently, cursor.execute("PREPARE SELECT ? + 42") crashes pymonetdb because it cannot handle the resulting response. At the very least, we should probably fix that crash, and provide access to the number of the prepared statement so the user can then do cursor.execute("EXEC %s(%s)", [prepnr, 99]) and cursor.execute("DEALLOCATE %s", [prepnr]).

This crash has already been fixed in pymonetdb 1.6.4. In 1.8.2 we document how to use PREPARE and EXEC.