Re: case sensitive object names via ODBC

On Sep 8, 5:59 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:

<snip>

> David Fitzjarrell likely found the source of the problem for you. It
> has been a while since I used DAO in VB4, but I recall that the Jet
> (aka MS Access) database drivers have authority to "customize" or
> translate the SQL statement before it is sent on to the database
> server. I remember occasionally receiving unexpected results due to
> this problem. The work around is to submit the query as a pass-
> through query, so that the Jet database drivers do not attempt to
> translate the SQL statement - this also helps improve performance.
>
> An example of a DAO passthrough in VB4:
> Dim dbMYDB As Database
> Dim snpRecordset As Recordset
> Dim strConnectString As String
> Dim strSQL as String
> strConnectString = "ODBC;DSN=MyODBCName;UID=MyUserName;PWD=MyPassword"
> Set dbMYDB = OpenDatabase("", False, False, strConnectString)
> strSQL = "Select * from Translation"
> Set snpRecordset = dbMYDB.OpenRecordset(strSQL, dbOpenSnapshot,
> dbSQLPassThrough)
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -

David, Charles,
Thank you for your time in replying.

It's also been a while since I've worked with vb4 and odbc. :)

I tried the dbSQLPassThrough arg and the query then worked (as I expected). But, this same code is working in production, so something else was afoot. (Good to know about the possible performance improvement, though - Thanks!)

I found that I've installed client 9.2.0.2.0 (according to the odbc release notes file). What's installed on the production clients is 8.1.7.0.0. Reviewing my current release notes (9.2.0.2.0), I see that in 8.1.7.3.0 an option for SQL_ATTR_METADATA_ID was added to the odbc setup. From this file (http://download-east.oracle.com/docs/cd/ B19306_01/server.102/b15658.pdf), under "Implementation of ODBC API Functions" (pg 168, appendix G):

<excerpt>
If the SQL_ATTR_METADATA_ID statement attribute is set to SQL_TRUE, then a string argument is treated as an identifier argument, and its case is not significant. [...] In contrast, if this attribute is set to SQL_FALSE, then it [...] is treated literally,
and its case is significant.
</excerpt>

When I change this odbc setup parm to true (click on the checkbox on the Workaround tab), then my queries work ok. So, I conclude that the effective behavior of the odbc clients prior to 8.1.7.3.0 is the same as for later clients with their SQL_ATTR_METADATA_ID parm set to true?

I should probably install the same odbc version on my dev machine as what's on the production clients. But, this is probable necessary info if it's decided to upgrade from the current 8.1.7.0.

Don't think that I have any questions at this point, I do welcome your comments if you think that I've misunderstood something or have something wrong here.

Thank you
Mark Received on Mon Sep 10 2007 - 11:41:52 CDT