Re: Anybody use VB as a front end to Oracle?

From: Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
Date: Mon, 31 Dec 2001 09:34:16 -0900
Message-ID: <3C30AFA8.B76AE888@dced.state.ak.us>

I hate to admit this, but I don't know how to tell for sure. The app was written for Oracle 7 by another programmer. I think I'm fair in both VB & Oracle, but I'm pretty new at the 2 together. According to the notes, the connection used ADO.

I double-checked the Oracle OLE DB documentation & it looks like everything's right, but the samples in the documentation use the 'command' syntax which this project doesn't. Maybe that's the problem?

Project references:

    Visual Basic for Applications
    Visual Basic runtime objects and procedures     Visual Basic objects and procedures
    Microsoft Data Binding Collection
    Microsoft Data Formatting Object Library     OLE Automation
    Microsoft ActiveX Data Objects Recordset 2.1 Library     Microsoft ActiveX Data Objects 2.1 Library

The first 3 are pretty standard, and the next 2 as far as I know just deal with data controls that are used later in the program. I don't think they should affect the area where I'm having problem, which is the initial 'security' check. The Oracle database interfaces with a legacy application that uses different user IDs so we have a 'security' table that sets up the legacy user ID based on the Oracle user ID. If I can just get the app to select the legacy user ID from that table then I think/hope I can solve the rest of the problems on my own. Like I said, the user ID I'm using has access to that table-I copied the select statement from the immediate pane into SQL*Plus using the same user ID & it works fine.

Project code is as follows:

Public cn As ADODB.Connection 'connection to db Public rs As ADODB.Recordset
Public strProvider As String 'Provider string

' Oracle 7 client

    strProvider = "MSDAORA"
    On Error GoTo NewProvider
    With cn

        .Provider = strProvider
        .ConnectionString = "User ID =" & sUsername & _
                            ";Password=" & sPassword & _
                            ";Data Source=DB"
        .Open

End With

rs.Open "SELECT sec_legacy_id FROM db.security_tbl " & _

                "WHERE sec_user_id = user", cn

Debug.Print rs!sec_user_legacy_id
Exit Sub

NewProvider:

    'Oracle 8i Client
   strProvider = "ORAOLEDB.Oracle"
    With cn

        .Provider = strProvider
        .Open

End With
Resume Next
End Sub

result is ? (that's the character ? that the debug.print statement returns, not an indication that I don't know what the result is. In SQL*Plus the same SELECT returns the legacy user ID character string that matches the Oracle user ID I'm logged in with.)

By the way, is there any better way to check which client is installed than trying the 'lesser' and seeing if it generates an error? I do need to use both clients because we're not upgrading everybody at once. (Fortunately the people who actually use this haven't been upgraded yet-we caught the problem when I tried to perform some maintenance on the app after my client had been upgraded so I could upgrade the server. Now both the server & the client upgrades are on hold until we're sure they won't break the app.)

Thanks.

Jim Kennedy wrote:

> How are you connecting? (ODBC, ADO, Oracle's OLE objects)
> Maybe your method of connection isn't compatible with the Oracle 8 client.
> It changed since Oracle 7.
> Jim
> "Calvin Crumrine" <Calvin_Crumrine_at_dced.state.ak.us> wrote in message
> news:3C2BBAC0.4F577901_at_dced.state.ak.us...
> > We're having trouble with our VB front end since we upgraded from the
> > Oracle 7 client to Oracle 8i. First it wouldn't connect any more, then
> > we got it so it appeared to connect but it won't fetch any data. The
> > data's there & accessible-I can get it in SQL*Plus using the same logon
> > ID & Select statement, but it doesn't seem to come thru in the VB app.
> > No error messages, just no data.
> >
> > Anybody think they can help? I can post details if it would be useful.
> > Thanks.
> >
Received on Mon Dec 31 2001 - 12:34:16 CST