We reduce and optimize the times of our activities

The first JDBC connection from C# to IBM i

This is the second episode of the article JTOpen for .Net environment in which we have learnt to create the .Net libraries for being able to work on our IBM i system from Windows in C# (or VisualBasic.NET)
In the folder ...\ikvm-7.2.4630.5\bin\ we find all the dlls which will allow us to use Java within a VisualStudio project, while, with this command run on our PC:
.\ikvm-7.2.4630.5\bin\ikvmc.exe -target:library \JTOpen\jtopen_10_6\lib\jt400.jar
We also have obtained the jt400.dll which will allow us to have full control of our IBM i system, including the chance to connect quickly.

  • Let’s open a new Visual Studio project named jdbcConnection (for example) and let’s load all the dlls in question as references (even if, for the basic example, they would be needed much less).

  • Indicate the references to the types we will use in the example
    using System;
    using com.ibm.as400.access;
    using java.sql;

  • Connect to the IBM i system named Penelope via JDBC
    Connection db2conn = null;
    AS400JDBCConnection db2connJT = null;
    DriverManager.registerDriver(new AS400JDBCDriver());
    db2conn = DriverManager.getConnection("jdbc:as400://penelope;queryTimeout=0;translate binary=true;date format=iso;time format=iso", , );
    db2connJT = (AS400JDBCConnection)db2conn;

  • Run a simple query to get the system’s date and hour using the dummy table sysibm.sysdummy1 and let’s print the result in console
    try
    {
        String query = @"SELECT REPLACE(cast(current_date as char(10)),'-',''),REPLACE(cast(current_time as char(8)),'.','') FROM sysibm.sysdummy1";
    
    
        ResultSet rst = db2connJT.createStatement().executeQuery(query);
        ResultSetMetaData rsmd = rst.getMetaData();
        while (rst.next())
        {
            for (int i = 1; i <= rsmd.getColumnCount(); i++)
            {
                Console.WriteLine(rsmd.getColumnName(i) + " " + rst.getString(i));
            }
            Console.WriteLine("-----------------------------------------------------------");
        }
    }
    catch (ThreadAbortException abortException)
    {
        Console.WriteLine((string)abortException.ExceptionState);
        db2connJT.close();
    }

  • Close the connection to the IBM i system
         db2connJT.close();

    With this simple example we have seen how to connect via JDBC to the IBM i system. This will allow us to take advantage of all the potential of DB2 for i and of the SQL services made available by the system, everything from a .NET program.
    Furthermore we have laid the groundwork to exploit the dlls of the open source package JTOpen, but this will be part of a next article 😉
    Previous article JHow to use JTOpen in .Net environment to develop client IBM i in Microsoft C# programming language


    Marco Moret Monitoring Project Manager at smeup ICS
    Alessandro Saviolo Monitoring Developer and Help Desk at smeup ICS
     You can also find the article on LinkedIn


    All articles
     Writers Manager: Monitor and manage printers with SQL
     Checking security on IBM i systems: the Audit Journal
     The first JDBC connection from C# to IBM i
     JTOpen for .Net environment: programming application in C# for IBM i
     DB2 for i: manage dates in SQL
     Get system info in SQL

    Manage, analyze and control your Systems IBM i
    Download, install and use in seconds
      os400 Monitoring Facility - Check is completely free until the end of the month

  • facebook