Hi Bruce, I'm considering a switch from pymssql to pyodbc myself in the not-too-distance future, and this thread has me a bit curious about what's going on. This is a subject that may affect SQL more in the future when ODBC and JDBC drivers get more use.
I think there's two distinct questions that need to be answered to get to the bottom of this. The first question is "why are these queries being issued at all, and from where"? Like Mike says, SQLA is playing no part in constructing or issuing these queries. >From the bit of googling that I've done so far, it seems that the FMTONLY queries are issued behind the scenes by the data connector to fetch metadata regarding the query. While there's a lot of reasons a data connector might need to have metadata, there's two that seem especially likely when SQLA comes into play: a) There are un-typed bind parameters in the query, and the connector needs to know the data types for some reason. b) There is going to be a client-side cursor constructed, and result metadata is needed to allocate the cursor. From the description you give, I would bet that this is your main issue. If the cause is (a), a fix might be problematic, as SQLA issues all of its queries using bind parameters, and I'm not sure if type information is used for each. But if you're using explicit bind parameters, you may want to specify the type on those. As for the more likely cause (b) I would think this could be gotten around by making sure you specify "firehose" (read-only, forward-processing, non-scrollable) cursors for retrieval, but I'm not sure what the pyodbc settings for this might be. As a bonus, you'll probably see a bit of a performance boost using these types of cursors as well. The second question is more of a mystery to me: "ok, so the data connector issues a FMTONLY query........if it's just fetching metadata, why would that cause database locks?". This one I can't figure out. Unless you're calling stored procedures or UDF's that have locking side effects, It's got to be a bug in the data connector. From what I read a FMTONLY query should be pretty fast (other than the round-trip network time), and should lock nothing. Are you running on Windows, or on Unix? What's your ODBC connector? Please post to the list as you work through this and let us know what you find... Rick --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
