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
     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, develop applications in .Net for IMB 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
      IBM i Monitoring Facility - Check is completely free until the end of the month
    • Update cookies preferences