Skip to main content

[archive] Adodb

  • September 1, 2006
  • 24 replies
  • 0 views
D

[Migrated content. Thread originally posted on 01 September 2006]

Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

Thanks,
Rob

    24 replies

    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob

               CREATE  Connection       OF ADODB
                       HANDLE           IN hConnection.
               Create  Recordset        OF ADODB
                       HANDLE           IN hRecordset.
               STRING  "Driver={Microsoft Access Driver (*.mdb)};"
                       "Dbq=apiv2db.mdb;"
                       "DefaultDir=F:\\APIV2\\DocExamples\\MSADO;"
                       "ReadOnly=False"
                       DELIMITED        BY SIZE INTO ConnStr.

               MODIFY  hConnection      @Open(ConnStr).
               INQUIRE hConnection      State IN WS-ConnStat.

               IF      WS-ConnStat      NOT = adStateOpen
                       GO TO            MAIN-900
                       END-IF.

          * This variant uses the connection as a basis for the selection
          *    MODIFY  hConnection      Execute("Select * from Contacts",
          *            WS-RecCount,     adCmdText)
          *            GIVING           hRecordset.
         
           
               MODIFY  hRecordset       MaxRecords = 100.
               MODIFY  hRecordset       @Open(
                       BY NAME Source   
                       "Select * FROM Contacts ORDER BY LastName",
                       BY NAME ActiveConnection hConnection,
                       BY NAME Options adCmdText).
               MODIFY  hRecordset       MoveFirst().
               INQUIRE hRecordset       EOF IN WS-ConnStat.
               PERFORM UNTIL            WS-ConnStat = -1
                                       
                       INQUIRE          hRecordSet
                                        fields::item("FirstName")::value
                                        IN WS-First
                                        fields::item("LastName")::value
                                        IN WS-Last

                       INITIALIZE       WS-String

                       STRING           WS-Last DELIMITED BY SPACE
                                        ", " DELIMITED BY SIZE
                                        WS-First DELIMITED BY SPACE
                                        INTO WS-String

                       DISPLAY          WS-String(1:62)

                       IF               WS-ConnStat NOT = -1
                                        MODIFY  hRecordset MoveNext()
                                        END-IF

                       INQUIRE          hRecordset EOF IN WS-ConnStat

               END-PERFORM.
               ACCEPT  OMITTED.
               MODIFY  hRecordset       @Close().
               MODIFY  hConnection      @Close().

           MAIN-900.   
          *Finish up.
           DESTROY hRecordset.
           DESTROY hConnection.
               GOBACK.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Gisle,

    Thanks for the example! What control should I use to generate the "def" file? I am using MSADODC.OCX, but it doesn't have a "Connection" class...

    Thanks,
    Rob
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Gisle,

    Ok, I figured out that I have issue with the alphabet occasionally... I need ADODB, not ADODC... Would I use something like msado15.dll?

    Rob
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Use the Microsoft ActiveX Data Objects (on the library tab), which version is really up to you, I have versions 2.0, 2.1, 2.5, 2.6, 2.7 and 2.8.
    Except of 2.8 the others come from type library files (.tlb), but I suspect it is msado15.dll behind it all.

    Good luck!
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Thanks, Gisle. I'll give this a try. You've saved me a lot of time.

    Rob
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    ADO is part of a package of Microsoft Data Access Components known as MDAC: http://msdn.microsoft.com/data/ref/mdac/default.aspx

    Most windows PC's with IE explorer 5.5 or higher and/or MS Office 2000 or higher should have it installed already. You can also redistribute the MDAC with your application: http://www.microsoft.com/downloads/details.aspx?familyid=6C050FE3-C795-4B7D-B037-185D0506396C&displaylang=en

    I could see a few uses for it at Southware. I have used it with Visual Basic, but not with Acucobol as of yet. You might eventually have compatability issues if users have different versions than what you developed with, so keep an eye out for that.


    A set of ADO samples in VB is located here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadocodeexamplesinvisualbasic.asp
    ADO Programmers Reference here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscsection1_ado.asp
    ADO Programmers Guide here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscsection1_ado.asp


    I look forwarding to seeing you at the Acucorp conference this week. :D
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hello Gisle,
    I tried the approach You suggested to ADO: it works fine, but how trapping errors from the activeX? When I produce an error (as "column not found" or "tabel_x doesnt' exists on database") it's always fatalk for the runtime too.
    Thanks In Advance Giovanni
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    You would need to use an exception procedure to handle any errors and use C$EXCEPTINFO to determine what the error is.

    CALL "C$EXCEPINFO"
    USING ERROR-INFO,
    ERR-SOURCE,
    ERR-DESCRIPTION,
    ERR-HELP-FILE,
    ERR-HELP-CONTEXT,
    ERR-OBJECT-HANDLE,
    ERR-CONTROL-ID

    Then, you can display a message or whatever other action you need to take based on the error.

    Rob
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Thanks Rob. Just to be a bit more precise; You can program a declaratives section that captures COM exceptions just like you capture file exceptions. Rob shows you how to get information about the exception.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hello Everyone,
    I've implemented the method You both, Rob & Gisle suggested: it work fine for intercepting AdoErrores, but after I catch one of them, as for example, if I launch a

    modify hConnection Execute("truncate table my_table"
    , , adCmdText)
    giving hRecordset

    and my_table doesn't exists on the DB, then if I try to destroy hRecordset handle I always have a MAV.
    (runtime is Wrun32 7.0.0)
    Any Ideas?
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    I'm certainly not the expert Gisle would be on this subject, but I have had situations where I destroy an ActiveX handle and I get a MAV when the handle was never really created. So, for example, if your hRecordset handle never really gets a value, you don't need to destroy it.

    I'll let Gisle confirm or deny this... ;-)
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hi Rob,
    I checked: my handle (hRecordset) is not null and has a value (123...).
    So...let's hope Gisle'll make some light in the fog...:)
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Some more: occasionally I get MAV in standard execution (so without an ActiveX's error occurrence) either if I run my proc in a thin client (linux server) environment.
    The same proc run with wrun32 or Thin on Windows server works fine.
    (ver. 7.0.0)
    Another Bug? :mad:
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Giovanni,

    You may want to report this to technical support. They can see if you've coded something wrong and can also test on other versions of the runtime to see if it's something that's been resolved by an ECN...
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    If you get an exception on executing a method that is supposed to return a handle to the object. You can certainly expect that handle not to be returned. Remember that COBOL does not by default initialize variables, so do an INITIALIZE hRecordset prior to the call should allow you to make a test NOT = 0. In short, when there is an exception, the returnvalue is not set, so it will retain whatever it already has.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Also, you are much to humble Rob, you know COM pretty well I would say :-).
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Thanks for the kind words, Gisle...
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hello and Thanks Everyone,
    1) Gisle tomorrow I'll check the code and I'll report back to the forum.
    2) some more info's: I reported the MAV from Thin on Linux server to support, but, as usual they've got no answer. I tried procedure on another client machine with WinXp instead of Win98 and now it works!...BUT it's performances are a bluff: wrun32 execution is quite 50 to 100 times quicker!
    3) two more problems:
    a) if I gather a recordset fetched thru a "SELECT ..." issued with a command object the returned recordset is not open for update so I can't call AddNew method. This behaviour in opposition of getting recordset with a call to Recordset's Open method.
    b)If a try to fill a datetime nullable field value with
    modify hHecordset, fields(index)||value = my_value
    where my_value = spaces I get an error exception "of type imcompatibliity" from AdoDb. I tried to fill it with low-values nor null but nothing works.
    Many Thanks In Advance Giovanni.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    1) If you suspect a bug, you should report to Tech support.
    2) Tech support is responsible for a vast area of products, platforms and technology. Your problem is specific to you, may not easily reproduce elsewhere, it is not quite fair to expect an answer to everything there and then. Some issues are complex, some are not.
    As for it working on XP, may be a coincidence, may not. XP may for instance have a bug fix of the component from Microsoft, that Win98 does not. Win98 is for instance not supported by Microsoft anymore...
    As for performance. The big difference makes sense. Remember you do the ActiveX portion on the client, but your cobol manipulation of the individual items are on the server, so for each MODIFY, INQUIRE, data goes back and forth over the network. That is *expensive*.
    3.a) Ask Microsoft
    3.b) This used to be a bug. I don't remember the ecn, but I believe we now set the VT_EMPTY datatype, if this applies to you or not is dependent on the version you use, at any rate, you will have to report to Tech Support for this.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hello Everyone, here are my test results:
    1) I adapted the code to initialize handles, and it seemes now work: I think you caught the bear!
    2) The MAV on Acuthin I confirm it fails on Win98: on Xp and W2K it works fine. I know MS doesn't support W98 anymore, however is strange to me it must due to a Win98 bug, 'cos the same proc with Wrun32 works fine in W98 too, and on the same platform VB&VC proc usings MsAdo2.8 works fine too. On tech support: I know some questions are complex and some are not, but I think it's fair to expect an answer not always but at least the most of the times, and in a reasonable time.
    On performance: I suspected something as You explained, in fact my test revealed that disabling
    AGS_COCKET_COMPRESS ZLIB
    on cblconfi I had activated, made me regain a bit of speed, but performance are still (10 times slower) far from Wrun32. And what about if I'll go via Msado15.dll in spite of *.ocx (on Win servers of course...)? Do you think it can work?
    3.a)Ok, I asked if someonelse Acu's customer had experienced this...
    3.b)I workarounded: correct constant value is 0(zero), but be careful: it rejects 0 as "0"=pic x(01), so you have to use the numeric item.
    TIA Giovanni.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    I am happy to hear you make progress. I doubt switching to ado 15 will make any performance improvement. You just cannot expect a remote connection to perform anywhere near the numbers of a local execution.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hi Gisle,
    my idea on using MSADO15.dll was:
    in a Thin client environment a can invoke libraries both on client (using
    @[display]: notation) but on server side too (or I'm wrong?) so if server is Win and DB is on the same server if I use MsAdo15 calling it on Server side I avoid client-server traffic and elaboration. Don't you think it could works on Win servers?
    TIA Giovanni.
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Hi Gisle. I tried your ADO example with AcuODBC as my datasource but I keep getting a "system error 182 (AcuODBC Driver)" on the Connection Open. My connection string is simply "DSN=MYDATA". MYDATA is a System DSN using the AcuODBC driver. I used the AcuODBC Sample Query Program with MYDATA and it connects just fine and returns data in a query. Has anyone else tried ADO with an AcuODBC datasource succesfully?

    My AcuODBC driver version is 7.02.00.1986 (7.2.0).
    The comment at top of my 7.0 generated .DEF files says:
    >>IMP(ACU-CBLFLAGS=-Sa)
    * G:\\DAN\\ADODB\\MSADOXP.def - OLE object definitions for ADODB
    * Generated: Wednesday, December 12, 2007
    * Typelib..: C:\\Program Files\\Common Files\\system\\ado\\msado15.dll
    D

    [Migrated content. Thread originally posted on 01 September 2006]

    Has anyone attempted to use Microsoft's ADODB to access information? Anyone made it work? If so, would you be willing to share an example of how you did this?

    Thanks,
    Rob
    Ok, let me update a bit. Apparently, when I use a 7.0.1 runtime and a 7.2.0 AcuODBC driver they don't get a long. It may have something to do with dll depedencies and non-matching versions which results in a system error 182. This kind of suprises me since I though the only thing the two of them have in common are the vision dll and perhaps some MS VC runtime dll's or something, but who knows.

    So, I changed my runtime to 7.2.1 and now Gisle's ADO COM sample runs great with an AcuODBC datasource using the 7.2.1 driver.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement