Announcement

Collapse
No announcement yet.

Problem using ADO on Win/2000 with Oracle DBMS

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem using ADO on Win/2000 with Oracle DBMS

    Running on: Windows 2000
    DBMS: Oracle 8.1 (client)
    Using: PB/CC 4.03 , relevant code below
    Symptoms: Program starts Ok, creates the oConn Connection object OK, Open method fails.
    Open method failure returns nothing for the error description (error count = 0) .

    Other systems:
    Same executable runs perfectly on
    Windows XP/Pro SP2 and Windows/XP SP3 with Oracle 10g
    Windows XP/Pro SP2 with Oracle 8.1

    On these systems when the connection string is deliberately cheesed up to induce an error, the error message when oConn.Open fails is returned, eg:
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [invalid Data Source (DB) name]
    or
    ORA-01017: invalid username/password; logon denied [um, self-evident]
    Suspect: Target system does not have adequate level of ADO installed. The only "tlb" files I can find on target system are with names msado15.tlb, msado20.tlb and msado21.tlb

    Questions
    1. Does this sound like my suspicions are correct, that an insufficient level of ADO is installed?

    2. Is it safe to install ADO 2.8 on target system (there are no other applications on this system using ADO). MS says the redistributable may be installed on Windows/2000. Reference:
    http://www.microsoft.com/downloads/d...n#Instructions

    3. Can multiple levels of ADO exist on same system? If not, is ADO "backward compatible?", that is, does each new release of ADO support all the properties and methods supported by prior versions?

    4. How should one determine which version(s) of ADO are installed on a system?

    Any/all suggestions appreciated

    Thanks,
    MCM

    #INCLUDE Library file in use (created by someone else):
    Code:
    ' ------------------------------------------------------------
    ' Library Name:      Microsoft ActiveX Data Objects 2.5 Library
    ' Library File:      C:\Program Files\Common Files\system\ado\msado25.tlb
    ' ------------------------------------------------------------
    ' Version Info:
    ' -------------
    ' Company Name:      Microsoft Corporation
    ' File Description:  Microsoft Data Access - ActiveX Data Objects 2.5 Type Library
    ' File Version:      2.81.1117.0 (xpsp_sp2_rtm.040803-2158)
    ' Internal Name:     msado25.rc
    ' Legal Copyright:   © Microsoft Corporation. All rights reserved.
    ' Original Filename: msado25.rc
    ' Product Name:      Microsoft Data Access Components
    ' Product Version:   2.81.1117.0
    ' ------------------------------------------------------------
    ' ProgID:            ADODB.Command.2.8
    ' Interface Name:    ADODBCommand
    '
    ' ProgID:            ADODB.Connection.2.8
    ' Interface Name:    ADODBConnection
    '
    ' ProgID:            ADODB.Parameter.2.8
    ' Interface Name:    ADODBParameter
    '
    ' ProgID:            ADODB.Record.2.8
    ' Interface Name:    ADODBRecord
    '
    ' ProgID:            ADODB.Recordset.2.8
    ' Interface Name:    ADODBRecordset
    '
    ' ProgID:            ADODB.Stream.2.8
    ' Interface Name:    ADODBStream
    '
    ' Interface Prefix:  ADODB
    
    '$PROGID_ADODBCommand28 = "ADODB.Command.2.8"
    '$PROGID_ADODBConnection28 = "ADODB.Connection.2.8"
    '$PROGID_ADODBParameter28 = "ADODB.Parameter.2.8"
    '$PROGID_ADODBRecord28 = "ADODB.Record.2.8"
    '$PROGID_ADODBRecordset28 = "ADODB.Recordset.2.8"
    '$PROGID_ADODBStream28 = "ADODB.Stream.2.8"
    
    ' versionless created 8/29/08 by MCM
    $PROGID_ADODBCommand28 = "ADODB.Command"
    $PROGID_ADODBConnection28 = "ADODB.Connection"
    $PROGID_ADODBParameter28 = "ADODB.Parameter"
    $PROGID_ADODBRecord28 = "ADODB.Record"
    $PROGID_ADODBRecordset28 = "ADODB.Recordset"
    $PROGID_ADODBStream28 = "ADODB.Stream"

    Relevant Source code
    Code:
    FUNCTION SelectTesterFunction (sConnect AS STRING, sQuery AS STRING, sResultFile AS STRING) AS LONG
    
      LOCAL oConn AS ADODBConnection
      LOCAL vConn AS VARIANT       ' connection string so I can try multiple
      LOCAL vState AS VARIANT
      LOCAL vProvider AS VARIANT   ' get after connection
      ' to create a recordset on the connnection
      LOCAL oRs AS ADODBRecordset
      LOCAL oFields  AS  ADODBFields, vFields AS VARIANT
      LOCAL oField   AS ADODBField,   vField  AS VARIANT
      LOCAL vSql AS VARIANT, vnRow AS VARIANT
      LOCAL vW   AS VARIANT  ' working var
      LOCAL nCol AS LONG, iCol AS LONG
      LOCAL sColName() AS STRING
      LOCAL vColIndex AS VARIANT, vColName AS VARIANT
      LOCAL vEOF AS VARIANT
      LOCAL vItem     AS VARIANT
      LOCAL nRow      AS LONG
      LOCAL vValue    AS VARIANT
      LOCAL sValue    AS STRING
      LOCAL hOut      AS LONG, sBUff AS STRING
      LOCAL iRet      AS LONG
      LOCAL vErrIndex AS VARIANT
      LOCAL vErrDesc  AS VARIANT
      LOCAL vErrCount AS VARIANT
      LOCAL nErr      AS LONG
      LOCAL iErr      AS LONG
    
    
      hOut = FREEFILE
      OPEN sResultFile FOR OUTPUT AS hOut
      PRINT #hOut, "ADO TESTING " & DATE$ & $SPC & TIME$
      PRINT #hOut,
    
      PRINT #hout, USING$("COnnection:&", sConnect)
      PRINT #hout, USING$("Query:&", sQuery)
      STDOUT       USING$("COnnection:&", sConnect)
      STDOUT       USING$("Query:&", sQuery)
    
      PRINT #hOut,
    
    
    
    
      SET oConn  = NEW ADODBConnection IN "ADODB.Connection"
    
      IF ISOBJECT(oConn) THEN
         STDOUT "created ADODBConnectionObject OK, setting connection string to:"
         STDOUT  sConnect
    
         'vConn       = "Provider=OraOLEDB.Oracle;Data Source=XE;User ID=HR;Password=hrpass"
    
         vConn       = sConnect
         OBJECT CALL   oConn.Open (vConn)
         iRet = OBJRESULT   ' most recent
         IF iRet <> %S_OK THEN
             ' for
             STDOUT "Object error on oConn.Open"
             OBJECT GET   oconn.Errors.Count  TO vErrCount
             nErr   = VARIANT#(vErrCount)
             STDOUT "Error Count is " & FORMAT$(nErr)   '<<< Returns count of zero here on problem system
    
             FOR iErr = 0 TO nErr-1
                STDOUT       "Getting Error index " & FORMAT$(iErr)
                PRINT #hOut, "Getting Error index " & FORMAT$(iErr)
                vErrIndex =  iErr
                OBJECT GET oConn.Errors.Item(vErrIndex).Description TO vErrDesc
                PRINT #hOut, "ErrorMessage:" &  VARIANT$(vErrDesc)
                STDOUT       "Error Message:" & VARIANT$(vErrDesc)
             NEXT
             GOTO  TesterExit
         END IF
         .....
    Last edited by Michael Mattias; 17 Sep 2008, 07:27 AM.
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

  • #2
    1. Does this sound like my suspicions are correct, that an insufficient level of ADO is installed?
    The Open method of the Connection object exists in all versions of ADO, but could be that older versions don't know how to connect with the database manager you're using.

    2. Is it safe to install ADO 2.8 on target system (there are no other applications on this system using ADO).
    Should be. I've used ADO 2.8 with Windows 2000 in the past.

    3. Can multiple levels of ADO exist on same system? If not, is ADO "backward compatible?", that is, does each new release of ADO support all the properties and methods supported by prior versions?
    It is backward compatible, but only one version of ADO will exist. No matter how many msadoxx.tlb you will find in your system, the only valid is msado15.tlb. The ADO installation leaves the old .tlb files, adds a .tlb for its version, and rewrites msado15.tlb. Don't ask me why they have chosen to do it that way, but this is what happens. Explore msado15.tlb and you will see that it is not the .tlb for version 1.5, but for the latest ADO version installed in your computer.

    4. How should one determine which version(s) of ADO are installed on a system?
    Call the Version property of the Connection object.
    Forum: http://www.jose.it-berater.org/smfforum/index.php

    Comment


    • #3
      Thank you very much!

      I will be testing that version thing on client's system this evening. That should tell me if I have made a good guess and installing updated ADO is worth the effort. (Works great here, returning "2.8").


      MCM
      Michael Mattias
      Tal Systems (retired)
      Port Washington WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        OPEN method still fails, same symptoms.

        ADO installed version comes back on client system with "2.7".

        Any reason the connect ("open") should fail and I should not be able to get an error message?

        Bad/ancient OLEDB provider? Provider should have different name?

        I should maybe try to generate a 2.7 Interface #INCLUDE file? I have a "2.7" type lib file on my system (but curriously enough, no "2.8")

        Thanks,
        MCM
        Michael Mattias
        Tal Systems (retired)
        Port Washington WI USA
        [email protected]
        http://www.talsystems.com

        Comment


        • #5
          OR.... (hope not...) ....

          Did I just stumble into...... DLL HELL ???




          MCM
          Michael Mattias
          Tal Systems (retired)
          Port Washington WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            Just to make sure:

            ORA-12514: TNS:listener does not currently know of service requested in connect descriptor [invalid Data Source (DB) name]
            or
            ORA-01017: invalid username/password; logon denied [um, self-evident]
            The (DB-)user you're using in your connection string does exist and has the appropriate rights to access the table?

            Also, a look at http://www.connectionstrings.com/?carrier=oracle might give you some ideas as what variations of the connection string you could try.

            Comment


            • #7
              My problem is, I get a connect failure but I can't get an error message.

              When program works, I know if I have invalid user, password or database.

              Maybe I need to look at the actual VALUE of "OBJRESULT" instead of simply testing for for S_OK or not S_OK. My desktop is in the shop for an upgrade today, so I don't have my 'on-disk' SDK but I guess I can use the MS online version.

              oops, I don't have Oracles installed on the laptop either.... well there's always Saturday.

              For now, I received my PB/CC 5 and Win/9 Compilers yesterday. Maybe I'll install those on the laptop and play with them a little.




              MCM
              Michael Mattias
              Tal Systems (retired)
              Port Washington WI USA
              [email protected]
              http://www.talsystems.com

              Comment


              • #8
                Maybe I need to look at the actual VALUE of "OBJRESULT" instead of simply testing for for S_OK or not S_OK.
                It is advisable, because it could be an ADO runtime error or even a Windows error. This is what I'm using with the new compilers:

                Code:
                ' ########################################################################################
                ' Helper functions to retrieve error information.
                ' ########################################################################################
                
                ' ========================================================================================
                ' Returns the description of an ADO runtime error
                ' ========================================================================================
                FUNCTION AdoGetRuntimeErrorInfo (BYVAL wError AS WORD) AS STRING
                
                   LOCAL s AS STRING
                
                   SELECT CASE AS LONG wError
                      CASE 3000 ' &HBB8 - %adErrProviderFailed
                         s = "Provider failed to perform the requested operation."
                      CASE 3001 ' &HBB9 - %adErrInvalidArgument
                         s = "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
                      CASE 3002 ' &HBBA - %adErrOpeningFile
                         s = "File could not be opened."
                      CASE 3003 ' &HBBB - %adErrReadFile
                         s = "File could not be read."
                      CASE 3004 ' &HBBC - %adErrWriteFile
                         s = "Write to file failed."
                      CASE 3021 ' &HBCD - %adErrNoCurrentRecord
                         s = "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."
                      CASE 3219 ' &HC93 - %adErrIllegalOperation
                         s = "Operation is not allowed in this context."
                      CASE 3220 ' &HC94 - %adErrCantChangeProvider
                         s = "Supplied provider is different from the one already in use."
                      CASE 3246 ' &HCAE - %adErrInTransaction
                         s = "Connection object cannot be explicitly closed while in a transaction."
                      CASE 3251 ' &HCB3 - %adErrFeatureNotAvailable
                         s = "Object or provider is not capable of performing requested operation."
                      CASE 3265 ' &HCC1 - %adErrItemNotFound
                         s = "Item cannot be found in the collection corresponding to the requested name or ordinal."
                      CASE 3367 ' &HD27 - %adErrObjectInCollection
                         s = "Object is already in collection. Cannot append."
                      CASE 3420 ' &HD5C - %adErrObjectNotSet
                         s = "Object is no longer valid."
                      CASE 3421 ' &HD5D - %adErrDataConversion
                         s = "Application uses a value of the wrong type for the current operation."
                      CASE 3704 ' &HE78 - %adErrObjectClosed
                         s = "Operation is not allowed when the object is closed. "
                      CASE 3705 ' &HE79 - %adErrObjectOpen
                         s = "Operation is not allowed when the object is open. "
                      CASE 3706 ' &HE7A - %adErrProviderNotFound
                         s = "Provider cannot be found. It may not be properly installed."
                      CASE 3707 ' &HE7B - %adErrBoundToCommand
                         s = "Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."
                      CASE 3708 ' &HE7C - %adErrInvalidParamInfo
                         s = "Parameter object is improperly defined. Inconsistent or incomplete information was provided."
                      CASE 3709 ' &HE7D - %adErrInvalidConnection
                         s = "The connection cannot be used to perform this operation. It is either closed or invalid in this context."
                      CASE 3710 ' &HE7E - %adErrNotReentrant
                         s = "Operation cannot be performed while processing event. "
                      CASE 3711 ' &HE7F - %adErrStillExecuting
                         s = "Operation cannot be performed while executing asynchronously."
                      CASE 3712 ' &HE80 - %adErrOperationCancelled
                         s = "Operation has been cancelled by the user."
                      CASE 3713 ' &HE81 - %adErrStillConnecting
                         s = "Operation cannot be performed while connecting aynchronously. "
                      CASE 3714 ' &HE82 - %adErrInvalidTransaction
                         s = "Coordinating transaction is invalid or has not started."
                      CASE 3715 ' &HE83 - %adErrNotExecuting
                         s = "Operation cannot be performed while not executing."
                      CASE 3716 ' &HE84 - %adErrUnsafeOperation
                         s = "Safety settings on this computer prohibit accessing a data source on another domain."
                '      CASE 3717 ' &HE85 - %adwrnSecurityDialog
                '         For internal use only. Don't use.
                '      CASE 3718 ' &HE86 - %adwrnSecurityDialogHeader
                '         For internal use only. Don't use.
                      CASE 3719 ' &HE87 - %adErrIntegrityViolation
                         s = "Data value conflicts with the integrity constraints of the field."
                      CASE 3720 ' &HE88 - %adErrPermissionDenied
                         s = "Insufficent permission prevents writing to the field."
                      CASE 3721 ' &HE89 - %adErrDataOverflow
                         s = "Data value is too large to be represented by the field data type."
                      CASE 3722 ' &HE8A - %adErrSchemaViolation
                         s = "Data value conflicts with the data type or constraints of the field."
                      CASE 3723 ' &HE8B - %adErrSignMismatch
                         s = "Conversion failed because the data value was signed and the field data type used by the provider was unsigned."
                      CASE 3724 ' &HE8C - %adErrCantConvertvalue
                         s = "Data value cannot be converted for reasons other than sign mismatch or data overflow. For example, conversion would have truncated data."
                      CASE 3725 ' &HE8D - %adErrCantCreate
                         s = "Data value cannot be set or retrieved because the field data type was unknown, or the provider had insufficient resources to perform the operation."
                      CASE 3726 ' &HE8E - %adErrColumnNotOnThisRow
                         s = "Record does not contain this field."
                      CASE 3727 ' &HE8F - %adErrURLDoesNotExist
                         s = "Either the source URL or the parent of the destination URL does not exist."
                      CASE 3728 ' &HE90 - %adErrTreePermissionDenied
                         s = "Permissions are insufficient to access tree or subtree. "
                      CASE 3729 ' &HE91 - %adErrInvalidURL
                         s = "URL contains invalid characters. Make sure the URL is typed correctly."
                      CASE 3730 ' &HE92 - %adErrResourceLocked
                         s = "Object represented by the specified URL is locked by one or more other processes. Wait until the process has finished and attempt the operation again."
                      CASE 3731 ' &HE93 - %adErrResourceExists
                         s = "Copy operation cannot be performed. Object named by destination URL already exists. Specify adCopyOverwrite to replace the object."
                      CASE 3732 ' &HE94 - %adErrCannotComplete
                         s = "Server cannot complete the operation."
                      CASE 3733 ' &HE95 - %adErrVolumeNotFound
                         s = "Provider cannot locate the storage device indicated by the URL. Make sure the URL is typed correctly."
                      CASE 3734 ' &HE96 - %adErrOutOfSpace
                         s = "Operation cannot be performed. Provider cannot obtain enough storage space."
                      CASE 3735 ' &HE97 - %adErrResourceOutOfScope
                         s = "Source or destination URL is outside the scope of the current record."
                      CASE 3736 ' &HE98 - %adErrUnavailable
                         s = "Operation failed to complete and the status is unavailable. The field may be unavailable or the operation was not attempted."
                      CASE 3737 ' &HE99 - %adErrURLNamedRowDoesNotExist
                         s = "Record named by this URL does not exist."
                      CASE 3738 ' &HE9A - %adErrDelResOutOfScope
                         s = "URL of the object to be deleted is outside the scope of the current record."
                      CASE 3739 ' &HE9B - %adErrPropInvalidColumn
                         s = "Cannot apply property to field"
                      CASE 3740 ' &HE9C - %adErrPropInvalidOption
                         s = "Attribute property invalid"
                      CASE 3741 ' &HE9D - %adErrPropInvalidValue
                         s = "Invalid property value"
                      CASE 3742 ' &HE9E - %adErrPropConflicting
                         s = "Property values conflict with each other"
                      CASE 3743 ' &HE9F - %adErrPropNotAllSettable
                         s = "Cannot set property or read-only"
                      CASE 3744 ' &HEA0 - %adErrPropNotSet
                         s = "Optional property value not set"
                      CASE 3745 ' &HEA1 - %adErrPropNotSettable
                         s = "Read-only property cannot be set"
                      CASE 3746 ' &HEA2 - %adErrPropNotSupported
                         s = "Property not supported by provider"
                      CASE 3747 ' &HEA3 - %adErrCatalogNotSet
                         s = "Operation requires a valid ParentCatalog."
                      CASE 3748 ' &HEA4 - %adErrCantChangeConnection
                         s = "Connection was denied. New connection you requested has different characteristics than the one already in use."
                      CASE 3749 ' &HEA5 - %adErrFieldsUpdateFailed
                         s = "Fields update failed. For further information, examine the Status property of individual field objects."
                      CASE 3750 ' &HEA6 - %adErrDenyNotSupported
                         s = "Provider does not support sharing restrictions."
                      CASE 3751 ' &HEA7 - %adErrDenyTypeNotSupported
                         s = "Provider does not support the requested kind of sharing restriction."
                      CASE 3753 ' 3753 - %adErrProviderNotSpecified
                         s = "Provider not specified"
                      CASE 3754 ' &HEAA - %adErrConnectionStringTooLong
                         s = "Connection string too long"
                   END SELECT
                
                   FUNCTION = s
                
                END FUNCTION
                ' ========================================================================================
                
                ' ========================================================================================
                ' Retrieves rich information from the ADO Errors collection.
                ' When an ADO call returns a result code you can call your function. If pErrors is true,
                ' then it is an error returned by the OLE DB provider else it must be an ADO runtime error.
                ' ========================================================================================
                FUNCTION AdoGetErrorInfo (BYVAL pConnection AS ADOConnection, BYVAL dwError AS DWORD) AS STRING
                
                   LOCAL hr             AS DWORD        ' // Result code
                   LOCAL pErrors        AS ADOErrors    ' // Reference to the Errors collection
                   LOCAL pError         AS ADOError     ' // Reference to the Error object
                   LOCAL wError         AS WORD         ' // ADO runtime error
                   LOCAL i              AS LONG         ' // Loop counter
                   LOCAL nCount         AS LONG         ' // Number of errors
                   LOCAL eNumber        AS LONG         ' // Error number
                   LOCAL eSource        AS STRING       ' // Source of the error
                   LOCAL eDescription   AS STRING       ' // Description of the error
                   LOCAL eHelpFile      AS STRING       ' // Help file
                   LOCAL eHelpContext   AS LONG         ' // Help context
                   LOCAL eSQLState      AS STRING       ' // SQlState
                   LOCAL eNativeError   AS LONG         ' // Native error
                   LOCAL strErrorText   AS STRING       ' // String to return the result
                   LOCAL ncbBuffer      AS DWORD        ' // Buffer for Windows error messages
                   LOCAL pBuffer        AS ASCIIZ PTR   ' // Pointer to the buffer
                   LOCAL strDescription AS STRING       ' // Description of the ADO runtime error
                
                   IF ISNOTHING(pConnection) THEN EXIT FUNCTION
                   IF dwError = 0 THEN EXIT FUNCTION
                
                   ' Get a reference to the errors collection
                   pErrors = pConnection.Errors
                   IF ISOBJECT(pErrors) THEN
                      ' Get the number of errors
                      nCount = pErrors.Count
                      ' The errors collection is zero based
                      FOR i = 0 TO nCount - 1
                         ' Get a reference to the error object
                         pError = pErrors.Item(i)
                         IF ISOBJECT(pError) THEN
                            eNumber = pError.Number
                            eDescription = ACODE$(pError.Description)
                            eSource = ACODE$(pError.Source)
                            eSqlState = ACODE$(pError.SQLState)
                            eNativeError = pError.NativeError
                            eHelpFile = ACODE$(pError.HelpFile)
                            eHelpContext = pError.HelpContext
                            strErrorText = strErrorText & "OLE DB provider error" & $CRLF
                            strErrorText = strErrorText & "Error number: " & FORMAT$(eNumber) & " [&H" & HEX$(eNumber, 8) & "]" & $CRLF
                            IF LEN(eDescription) THEN strErrorText = strErrorText & "Description: " & eDescription & $CRLF
                            IF LEN(eSource) THEN strErrorText = strErrorText & "Source: " & eSource & $CRLF
                            IF LEN(eSqlState) THEN strErrorText = strErrorText & "SQLState: " & eSqlState & $CRLF
                            IF eNativeError THEN strErrorText = strErrorText & "Native error: " & FORMAT$(eNativeError) & " [&H" & HEX$(eNativeError, 8) & "]" & $CRLF
                            IF LEN(eHelpFile) THEN strErrorText = strErrorText & "Help file: " & eHelpFile & $CRLF
                            IF eHelpContext THEN strErrorText = strErrorText & "Help context: " & FORMAT$(eHelpContext)
                            ' Release the error object
                            pError = NOTHING
                         END IF
                      NEXT
                      ' Clear the errors collection
                      pErrors.Clear
                      ' Release the errors collection
                      pErrors = NOTHING
                      IF nCount THEN
                         FUNCTION = strErrorText
                         EXIT FUNCTION
                      END IF
                   END IF
                
                   ' Must be an ADO runtime error
                   wError = dwError
                   strDescription = AdoGetRuntimeErrorInfo(wError)
                   IF LEN(strDescription) THEN
                      strErrorText = strErrorText & "ADO runtime error" & $CRLF
                      strErrorText = strErrorText & "Error code: " & FORMAT$(dwError) & " [&H" & HEX$(dwError, 8) & "]" & $CRLF
                      strErrorText = strErrorText & "Description: " & strDescription
                   ELSE
                      ' If it is not a Provider or an ADO runtime error then must be a Windows error
                      ncbBuffer = FormatMessage(%FORMAT_MESSAGE_ALLOCATE_BUFFER OR _
                         %FORMAT_MESSAGE_FROM_SYSTEM OR %FORMAT_MESSAGE_IGNORE_INSERTS, _
                         BYVAL %NULL, dwError, BYVAL MAKELANGID(%LANG_NEUTRAL, %SUBLANG_DEFAULT), _
                         BYVAL VARPTR(pBuffer), 0, BYVAL %NULL)
                      IF ncbBuffer THEN
                         strErrorText = strErrorText & "Windows error" & $CRLF
                         strErrorText = strErrorText & "Error code:" & FORMAT$(dwError) & " [&H" & HEX$(dwError, 8) & "]"
                         strErrorText = strErrorText & "Description: " & @pBuffer
                         LocalFree pBuffer
                      END IF
                   END IF
                
                   FUNCTION = strErrorText
                
                END FUNCTION
                ' ========================================================================================
                Forum: http://www.jose.it-berater.org/smfforum/index.php

                Comment


                • #9
                  Michael,

                  These actions recommended for error ORA-12514:

                  Action:
                  - Wait a moment and try to connect a second time.

                  - Check which services are currently known by the listener by executing: lsnrctl services <listener name>

                  - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

                  - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

                  - Check for an event in the listener.log file.
                  Might be helpful...

                  Comment


                  • #10
                    Update....

                    I finally got that error handler code to compile and more-or-less work. That must be PBWin 9x code... very 'friendly' vis-a-vis handling conversions to VARIANTs and using direct assignment statements rather than "OBJECT GET .... TO variant_var" as compared with the 8x compiler I am using.

                    On my system I can't figure out how to generate either an ADO runtime error or a Windows system error, so I guess I will have to wait and see. (I can get lots of Oracle errors without hardly trying).

                    That code is much appreciated and I should FINALLY be able to test on the balky system the first part of next week.

                    MCM
                    Michael Mattias
                    Tal Systems (retired)
                    Port Washington WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment


                    • #11
                      Update...

                      The error is reported as -2147352567 (as LONG) 2147614729 (as DWORD) 80020009 (hex) (That's the value of iRet).

                      HOWEVER... we have some more info.....

                      The function above gets the connection string and an SQL statement to execute... it prints a report of the query results

                      I ran the same program with a Microsoft Jet connect string and ACcess database....
                      Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Mercator\eprofile.mdb

                      ...and the program executed perfectly!


                      Here are what I am setting (UID and password edited):
                      CONNECT=Provider=OraOLEDB.Oracle;Data Source=PTMN;User ID=XXXX;Password=XXXX
                      QUERY=Select * from batch_proc

                      I ran with datasource PTMN and TEST, both services ARE running (SQL Plus runs against either) but I got the same error condition on Connection.Open


                      So... it sounds like... there is maybe something in the Oracle setup which is out of whack?

                      I've been doing 'generic Googles' to try to find soemthing... next stop the Oracle support forums.

                      But anyone here with an idea it will be much appreciated.

                      ( I see above where in Jose's code he was converting from Unicode?
                      Is converting my stuff to unicode worth a try or is that grasping at straws?)

                      The same program works on another system and I don't have to do Unicode nuthin.'


                      MCM
                      Michael Mattias
                      Tal Systems (retired)
                      Port Washington WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        The error is reported as -2147352567 (as LONG) 2147614729 (as DWORD) 80020009 (hex) (That's the value of iRet).
                        This error is %DISP_E_EXCEPTION, returned when there is an exception, but it is not the real error code. With the new compilers, you have the IDISPINFO pseudo object that will return you rich error information when %DISP_E_EXCEPTION is returned as the result of a call.

                        info& = IDISPINFO.CODE
                        info& = IDISPINFO.CONTEXT
                        info$ = IDISPINFO.DESC$
                        info$ = IDISPINFO.HELP$
                        info$ = IDISPINFO.SOURCE$

                        With previous versions of the compilers you're without clue.

                        ( I see above where in Jose's code he was converting from Unicode?
                        Is converting my stuff to unicode worth a try or is that grasping at straws?)

                        The same program works on another system and I don't have to do Unicode nuthin.'
                        With direct interface calls you use standard types, just like with standard functions. With Automation, you're using variants and PB converts strings to unicode automatically when you assign it to a variant, Therefore, if the parameter is a variant you don't have to use UCODE$.
                        Forum: http://www.jose.it-berater.org/smfforum/index.php

                        Comment


                        • #13
                          As long as it's backward compatble except for the "SET object = NEW type IN... " being changed to NEWCOM or something close to that I guess it is time to grab the CD, install 9x and port this program from 8x.

                          (I can guarantee the 9x syntax in your error handler above does not compile in 8x).

                          I really appreciate your patience.
                          Michael Mattias
                          Tal Systems (retired)
                          Port Washington WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            As long as it's backward compatble except for the "SET object = NEW type IN... " being changed to NEWCOM or something close to that I guess it is time to grab the CD, install 9x and port this program from 8x.
                            It is backward compatible. Even "NEW type IN..." is still supported. Additionally, it features the much needed IDISPINFO pseudo object to retrieve rich error information when the result of a call is %DISP_E_EXCEPTION.

                            (I can guarantee the 9x syntax in your error handler above does not compile in 8x).
                            I advised you that it was what I'm using with the new compilers. Even if you use PBWIN 9 or PBCC 5, it won't compile unless you generate interface definitions for direct interface call usage, and with the same names that I'm using, or changing the ones that I have used (AdoErrors and AdoError) for the ones of your include file, if they aren't the same.

                            If you are going to use Automation instead of direct interface calls, you will have to change instructions like pErrors = pConnection.Errors to OBJECT GET oConnection.Errors TO vErrors, and so on.

                            Keep in mind that the new support for direct interface calls is not an easier to use syntax, but a different technology.
                            Forum: http://www.jose.it-berater.org/smfforum/index.php

                            Comment


                            • #15
                              Progress: I have the program working with Pb/CC 5.0. I had to re-generate the #INCLUDE with that "dispatch" option, and actually RTFM to see what that "IDBIND" thing in the INTERFACE defintion meant....

                              BUT... the program now works the same using the 5x program as it did with the 4x program with both Oracle and Jet databases on my system.

                              I think all I have to do now I think is query 'iret' for DISP_E_EXCEPTION and go get that newly available info, and run it on the client's machine (since I can't get that error here).

                              I am getting there.....

                              This is kind of fun, as I haven't really had a chance to do 'new things' for a while.

                              MCM
                              Michael Mattias
                              Tal Systems (retired)
                              Port Washington WI USA
                              [email protected]
                              http://www.talsystems.com

                              Comment


                              • #16
                                Ok, I got the new IDISPINFO pesudo-object in ....and have learned something...

                                When Connect fails, it returns DISP_E_EXCEPTION.

                                I did not know this before as I was only testing for "NOT S_OK."

                                Curious that I *WAS* able to get the error info "the traditional" way (errors property) on my machine , even when the error was DISP_E_EXECPTION, but I could not get it on the balky machine.

                                I hope this does not mean I am going to get nada on the other machine; that is, the "PB pseudo objects" are not just the compiler doing what I can do for myself.

                                FWIW, when you specify an Invalid Oracle database you get:
                                Code:
                                ADO TESTING 10-12-2008 12:49:08
                                
                                COnnection:Provider=OraOLEDB.Oracle;Data Source=MM;User ID=prod;Password=prod
                                Query:Select * from batch_proc
                                
                                ADO Version 2.8
                                Object error -2147352567 (0009) on oConn.Open
                                DispInfo Code '-2147467259'
                                DispInfo Context '0'
                                DispInfo Description 'ORA-12154: TNS:could not resolve the connect identifier specified'
                                DispInfo Help(file) ''
                                DispInfo Source'OraOLEDB'
                                end of error messages for Error DISP_E_EXCEPTION
                                *** END OF REPORT ***

                                Hmmm... seems I also get DISP_E_EXCEPTION for bad SQL too..
                                Code:
                                ADO TESTING 10-12-2008 13:01:01
                                
                                COnnection:Provider=OraOLEDB.Oracle;Data Source=XE;User ID=prod;Password=prod
                                Query:Select * from batch_procX
                                
                                ADO Version 2.8
                                Object error -2147352567 (x'0009') on oConn.Execute
                                ORA-00942: table or view does not exist
                                *** END OF REPORT ***
                                I did not put the pseudo-object code in the program for this error... this is just the error message using "traditional" means.

                                Yes, I will be cleaning up this program and posting it for all. But let me test it on the offending machine first.

                                MCM
                                Last edited by Michael Mattias; 12 Oct 2008, 01:05 PM.
                                Michael Mattias
                                Tal Systems (retired)
                                Port Washington WI USA
                                [email protected]
                                http://www.talsystems.com

                                Comment


                                • #17
                                  THANK YOU THANK YOU THANK YOU!!

                                  IDISPNFO.DESCRIPTION reports:

                                  "Provider cannot be found. It may be improperly installed."
                                  "Help file : C:=WinNT\Help\ADO27.CHM"



                                  MCM
                                  Michael Mattias
                                  Tal Systems (retired)
                                  Port Washington WI USA
                                  [email protected]
                                  http://www.talsystems.com

                                  Comment


                                  • #18
                                    The rest of the story.....

                                    FIRST, here is the actual output of the program, in which I learned the problem:
                                    Code:
                                    ADO TESTING 10-21-2008 11:20:56
                                    
                                    COnnection:Provider=OraOLEDB.Oracle;User ID=XXXXXX;Password=XXXXXX;Data Source=TEST
                                    Query:select 'X' from dual
                                    
                                    ADO Version 2.7
                                    Object error -2147352567 (0009) on oConn.Open
                                    DispInfo Code '-2146824582'
                                    DispInfo Context '1240655'
                                    DispInfo Description 'Provider cannot be found. It may not be properly installed.'
                                    DispInfo Help(file) 'C:\WINNT\HELP\ADO270.CHM'
                                    DispInfo Source'ADODB.Connection'
                                    end of error messages for Error DISP_E_EXCEPTION
                                    *** END OF REPORT ***
                                    SECOND, now knowing the problem was the Provider, I searched the registry for "OLE DB Provider" (Search in "KEYS")

                                    I found a provider installed name MSDAORA, identified as "Microsoft OLE DB Provider For Oracle" - which is NOT the name of the provider officially supported by Oracle (in their OLE DB Developer manual), but I figured, "what the hell, why not give it a shot?"

                                    So I tried and got this result...
                                    Code:
                                    ADO TESTING 10-21-2008 11:35:43
                                    
                                    COnnection:Provider=MSDAORA;User ID=XXXXXX;Password=XXXXXX;Data Source=PTMN
                                    Query:select * from batch_proc 
                                    
                                    ADO Version 2.7
                                    PROC_ID_NO,DATE_REQ,NUM_COPIES,SEG_NO,REQ_BY_PERS_NO,REQ_TYPE_CD,PRINTER_INFO,STATUS_INFO,PRIOR_PROC_ID_NO,RUN_BEG_DT_TIME,RUN_END_DT_TIME
                                    
                                    2442,2008-10-20,1,252,4629,PRNPICKLIST,DISKFILE,RUN COMPLETE,2441,2008-10-20,2008-10-20
                                    2443,2008-10-20,1,252,4629,BOOKSO,<NULL>,RUN COMPLETE,<NULL>,2008-10-20,2008-10-20
                                    2444,2008-10-20,1,252,4629,ALLOCPICK,<NULL>,RUN COMPLETE,2443,2008-10-20,2008-10-20
                                    2445,2008-10-20,1,252,4629,PRNPICKLIST,DISKFILE,RUN COMPLETE,2444,2008-10-20,2008-10-20
                                    2446,2008-10-20,1,252,4629,BOOKSO,<NULL>,RUN COMPLETE,<NULL>,2008-10-20,2008-10-20
                                    2447,2008-10-20,1,252,4629,ALLOCPICK,<NULL>,RUN COMPLETE,2446,2008-10-20,2008-10-20
                                    ...
                                    I don't think I need to show all 2233 rows returned by the query do I?

                                    Bottom line... the new feature in CC5 (IDISPINFO) was a HUGE help.

                                    AND (more importantly)....

                                    This client can benefit from some new software I am creating. Well, I am pretty sure he can, at least that is how I am going to present it when attempting to make a sale.

                                    Yes, now I can clean up this program and post it. Give me a couple days, OK?

                                    What it does: START ProgramName.exe commandFile

                                    Looks for the "CONNECT=" string and the "QUERY=" string in "commandFile." Attempts to connect using Connectstring and execute the query. Writes output to both the console and a log file.

                                    Might be really handy for those of you who need to see if you can connect to and read from any database for which an OLE provider is available (yes, you can get at Access DBs with this, I have a connect string for that here somewhere and will include it in post).

                                    MCM
                                    Michael Mattias
                                    Tal Systems (retired)
                                    Port Washington WI USA
                                    [email protected]
                                    http://www.talsystems.com

                                    Comment


                                    • #19
                                      Generic 'ADO' Connection and Query Tester 11-02-08

                                      I kind of went one better... I reduced the "connect and perform SELECT" function to return the results of the query to a dynamic string array and made the call syntax a little more usable.

                                      FWIW:
                                      I went back and made it compilable 'as is' with EITHER PB/CC 5x or PB/WIN 9x.

                                      Should also work with PB/CC 4x and PB/Win 8x if you tweak up the "IDISPINFO" stuff. But I did not try that myself. (Unless the format of the file created by the COM Browser is not compatible with earlier compiler). But since this program did work minus the IDISPINFO function with CC4/Win8 using an #INCLUDE generated by those versions of COM Browser, I know this is doable.

                                      Enjoy.

                                      MCM
                                      Last edited by Michael Mattias; 2 Nov 2008, 04:47 PM. Reason: Comment re using either compiler
                                      Michael Mattias
                                      Tal Systems (retired)
                                      Port Washington WI USA
                                      [email protected]
                                      http://www.talsystems.com

                                      Comment

                                      Working...
                                      X