There was a recent query about prepared statements on the db-sig mailing list, too. Apparently thought is being given to adding such functionality to Psycopg. If such functionality is added, it could be useful to support, I suppose. Some SQL engines apparently benefit from the techinque. MS SQL Server is not one of them, so I have not bothered to add support for them to adodbapi. If I do so, it will use the same api as mxodbc uses now (a copy of the SQL statement is kept with the cursor). [note: my reading of Microsoft's recommendation is not "don't do that", it is "why bother?".]
Pep-0249 is silent on the subject of how to support prepared statements, so any existing systems are likely to do so differently. In particular, there can be no expectation that there is any support whatsoever for the concept, so it will have to be emulated where not present (-- i.e. almost everywhere). On Tuesday, March 25, 2014 9:17:13 AM UTC-6, Michael Manfre wrote: > > Is falling back to a direct queries being considered? Not all backends > support prepared statements or recommend using them. The native mssql > drivers support prepared statements, but the other drivers django-mssql > supports do not. Also, "In SQL Server, the prepare/execute model has no > significant performance advantage over direct execution, because of the way > SQL Server reuses execution plans." [1] > > Given Microsoft's recommendation against using prepared statements, my > motivation is geared toward having this be an truly optional feature that > will not prevent using 3rd party apps with django-mssql. I do like the > proposal with an explicit .execute() and think documenting corner cases is > the sane way to proceed. > > Regards, > Michael Manfre > > [1] http://technet.microsoft.com/en-us/library/ms175528(v=sql.105).aspx > > > > On Tue, Mar 25, 2014 at 10:22 AM, Anssi Kääriäinen > <[email protected]<javascript:> > > wrote: > >> On Tuesday, March 25, 2014 2:53:42 PM UTC+2, Curtis Maloney wrote: >>> >>> ps = MyModel.objects.filter(foo__lt=Param('a').prepare() >>> >>> The result is now a callable that accepts one parameter - "a". To >>> invoke the query: >>> >>> results = ps(a=1000) >>> >>> >>> Clearly it's early days yet - I've written no code. And akaariai has >>> pointed out already there's some corners cases which won't work well with >>> existing behaviours (e.g. foo=None being silently translated to >>> foo__isnull=True), but it's best to get this idea under wider public >>> scrutiny earlier, rather than later. >>> >> >> I like this style of prepared statements. It is explicit, and >> implementation should be achievable without too much added code complexity. >> I prefer ps.execute(a=1000) personally, but the exact syntax isn't that >> important at this stage. >> >> There will be a couple of corner cases that will be hard to solve. The >> problems are around value preparation during .filter() calls and how >> certain special values are dealt with. Quickly thinking the value >> preparation (basically get_prep_lookup() call) shouldn't be that much of a >> problem - it is currently done during .filter() calls, but it should be >> possible to defer it to execution time. >> >> The foo=None case is something that likely can't be solved. The problem >> here is that foo=None translates to WHERE foo IS NULL, while foo=1 >> translates to WHERE foo = 1. These are syntactically different queries, and >> thus single prepared statement can't handle both of these. There are also >> cases where isnull=True/False require different join promotion depending if >> True or False is supplied. These again can't be handled. >> >> I am OK for just documenting these corner cases. They aren't something >> that should happen too often. The implementation for prepared statements is >> relatively straightforward (generate SQL, prepare it once, execute using >> given values), but if the corner case needs to be handled the >> implementation will be more complex, likely so much more complex that >> nobody will implement this feature. >> >> In short: +1 for implementing this with documentation of the corner cases. >> >> - Anssi >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django developers" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to >> [email protected]<javascript:> >> . >> Visit this group at http://groups.google.com/group/django-developers. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-developers/3123fda6-d7b3-46d3-82ec-28ed3003e837%40googlegroups.com<https://groups.google.com/d/msgid/django-developers/3123fda6-d7b3-46d3-82ec-28ed3003e837%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/9b0bbfb4-a43d-4fb8-be2f-063314eb9ca8%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
