Re: case sensitive object names via ODBC

On Sep 7, 5:50 pm, Mark Lemoine <mark.a.lemo..._at_gmail.com> wrote:
> On Sep 7, 4:43 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> > On Sep 7, 3:36 pm, Mark Lemoine <mark.a.lemo..._at_gmail.com> wrote:
>
> > > I've been assigned to support a legacy app that we have that's coded
> > > in VB4 (16bit) that connects to an Oracle db via ODBC. Everything is
> > > fine in the production environment, my problem is in the test db
> > > environment that I'm trying to setup.
>
> > > I got a script from the dba from the production db to create the
> > > tables, etc. I run that script on my test db and that''s fine. But,
> > > when I try to perform a select from my code:
>
> > > Select * from Translation
>
> > > I get an error back something like "query or table not found" (sorry,
> > > I didn't write down the exact msg). When I change the query to:
>
> > > Select * from TRANSLATION
>
> > > It works fine. This is connecting thru an ODBC dsn using DAO (hey - I
> > > did say that it's legacy!). The table names in the script are not
> > > quoted (so they should be case-insensitive).
>
> > > If I run SQL*Plus, then the table name is not case sensitive (that is,
> > > Translation or TRANSLATION works), so this is something just with my
> > > ODBC setup, I conclude.
>
> > > Can anyone help? What's wrong with what I'm trying to do?
>
> > > Thanks
> > > Mark
>
> > One guess is that it may be how the ODBC/DAO piece 'transliterates'
> > the script. You'll know that by querying V$SQLTEXT and looking for
> > your queries; you may well find the table names wrapped in quotes even
> > though they aren't in the source script.
>
> > Just a thought.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Yup, that's it. When I look at V$SQLTEXT, I can see quotes around my
> table reference, ie 'Translation'.
>
> I'm using the Oracle v9 odbc driver. I don't see any options on the
> dsn setup to control this behavior.
>
> Any ideas on how I turn this off? Or a different odbc driver to use
> (at this stage of the app's life, converting from odbc to something
> else isn't an option)? (I am checking to see what's configured on
> the working-fine-in-production workstations).
>
> Thanks!
> Mark

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 passthrough  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. Received on Sat Sep 08 2007 - 16:59:53 CDT