IBM i Global

IBM i Global

Connect, learn, share, and engage with IBM Power.

 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