IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
 View Only
Back to discussions
Expand all | Collapse all

Database driver from System i to external DB

  • 1.  Database driver from System i to external DB

    Posted Tue June 20, 2023 10:23 AM
    Edited by JIE LIU Tue June 20, 2023 10:34 AM

    HI,

    I'd like to enable RPG programs on System i to access external DB (Access, MySQL, Oracle etc) directly. Is it available for system i?

    • Where to get the data source driver (like ODBC, JDBC) to achieve the target to access external DB.
    • How to configure the connection for the driver?

    If there is no driver available to acess external DB directly, is there any other solution?

    Many Thanks

    Jie Liu

    ------------------------------
    Jie Liu
    ------------------------------



  • 2.  RE: Database driver from System i to external DB

    Posted Wed June 21, 2023 12:02 AM
    Edited by Satid Singkorapoom Wed June 21, 2023 12:14 AM

    The great Scott Klement used to write a few articles about using JDBC as a bridge for RPG to access remote DB in the distant past and he has a presentation on this on his web site.  Go here : https://www.scottklement.com/presentations/  and select the hot link named "Accessing External Databases from RPG" to download the presentation. 

    I will search for his past articles on this and let you know when I find them. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: Database driver from System i to external DB

    Posted Wed June 21, 2023 12:11 AM

    I attach herewith Scott Klement's past articles and a few others on this topic.  



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 4.  RE: Database driver from System i to external DB

    Posted Wed June 21, 2023 09:53 PM
      |   view attached

    I forgot to add that as of IBM i 7.1, RPG and COBOL can receive a result set and I attach an article on how to enable RPG to receive result set. 



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Attachment(s)

    pdf
    DB-V7R1 Result Set Support for RPG-COBOL-C.pdf   163 KB 1 version
    Original Message


  • 5.  RE: Database driver from System i to external DB

    Posted Wed June 28, 2023 03:31 AM

    Hi, Satid

    Thanks a lot for your help. We are going to try the solution of Scott you provided.

    Regards,
    Jie



    ------------------------------
    JIE LIU
    ------------------------------

    Original Message


  • 6.  RE: Database driver from System i to external DB

    Posted Wed June 21, 2023 04:39 AM

    You should take a look at the presentation of exactly this topic by RPG Guru Scott Klement:

    External Databases from RPG.pdf

    For the JDBC driver, all database providers have a JDBC driver to download. The driver should be of type 4, pure Java, so it can run on IBM i.

    This technique has been available for more than 20 years - where I used it to connect to MS SQL Server directly from ILE RPG.

    Best regards,

    Christian



    ------------------------------
    Christian Jorgensen
    IT System Administrator
    Network of Music Partners A/S
    ------------------------------

    Original Message


  • 7.  RE: Database driver from System i to external DB

    Posted Wed June 21, 2023 11:06 AM

    You can use Dieter Benders Ardgate as well.

    Link: https://sourceforge.net/projects/appserver4rpg/



    ------------------------------
    [Karl] [Fritz]
    [sr. Consultant]
    ------------------------------

    Original Message


  • 8.  RE: Database driver from System i to external DB

    Posted Wed June 21, 2023 11:40 AM

    The most „application transparent" would be the use of a DRDA connection. This is the same protocol and method that DDM-files use, for database connections between two IBM i systems/partitions. 

    AFAIK there are products for Microsoft's SQL Server and Oracle available. And of course for DB2. Maybe there are other, that I don't know. 

    Just search for "DRDA" (Distributed Relational Database Architecture). 

    HTH



    ------------------------------
    Daniel Gross
    ------------------------------

    Original Message


  • 9.  RE: Database driver from System i to external DB

    Posted Thu June 29, 2023 07:23 AM

    If you want the simplest where you code your RPG like this:

    exec sql CONNECT to mysqlserver;

    exec sql ...;

    Then it's simply a matter of adding mysqlserver to WRKRDBDIRE with the ADDRDBDIRE command.

    ADDRDBDIRE RDB(MYSQLSERVER) RMTLOCNAME(mysqlserver *IP) ARDPGM(MYARDLIB/MYARDPGM)

    If you want a commercial version of the ARDPGM look at Dieter Bender's ARDGATE



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 10.  RE: Database driver from System i to external DB

    Posted Thu June 29, 2023 09:37 AM

    Another option if your team is into the open-source technologies on IBM i is to create an app in Python, PHP, Node, etc that connects to your remote database and returns resulting data that can be consumed via RPG.  JSON tends to be a popular interchange format these days. 

    The HTTPCLOB stuff for RPG is another way to consume data that may come from a web service.  Or you can use curl and other open source utilities to get data and then consume from an RPG app.  



    ------------------------------
    Richard Schoen
    ------------------------------

    Original Message


Related Content