• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Conditional Inserts with JDBC Prepared Statements

    July 16, 2008 Hey Mike

    I would like to write a conditional INSERT in SQL so I can use a prepared statement through JDBC. However, SQL supports IF only in SPL, the Stored Procedures Language. Is there another way to insert the record if doesn’t exist?

    –Avdhesh

    For the reader’s benefit, here is the SQL statement Avdhesh wants to run in JDBC:

    if not exists (select 1 from cotable where coekey = ?) then
        insert into cotable
        (coekey, cocode, coname, coactv)
        values (?, ?, ?, 1);
    end if
    

    Unfortunately DB2 for i5/OS doesn’t support dynamic scripting. Therefore conditional statements such as IF are not supported with the JDBC PreparedStatement.

    One way to handle the problem would be to wrap the SQL code into an SQL stored procedure and then pass appropriate parameter values to the stored procedure. But if you don’t want to hassle with writing a stored procedure, you can cram this conditional insert into one statement as follows:

    insert into cotable
    (coekey, cocode, coname, coactv)
    Select ?,?,?,1
      From SysDummy1
     Where Not Exists
        (Select 1
           From cotable
          Where coekey=?)
    

    The question marks represent parameter markers (roughly equivalent to host variables in pre-compiled SQL.) Values need to be assigned to each of these markers before the statement can execute successfully.

    Take note that SysDummy1 is a special IBM one row table that can be used as a trick for these one row operation situations! This is because inserting parameter values from a one row table is equivalent to the INSERT/VALUES statement. Placing the NOT EXISTS predicate in the WHERE clause instead of using an IF statement still allows us to condition if the row should be inserted by testing whether the row already exists.

    As a side note, SysDummy1 resides in the SysIBM schema so it should be part of the library list when using the *SYS naming convention or fully qualified (SYSIBM.SysDummy1) when using the *SQL naming convention. Alternatively, if you have a one row table in your own schema it can be substituted for SYSDUMMY1 as well.

    Finally, there is one more thing to consider about parameter markers. In some cases, the data types of parameter markers can be inferred from the SQL statement itself, such as when a parameter marker is compared to a column in the WHERE clause. In this case the parameter marker is assumed to have a data type that is compatible with the data column it is being compared with. However, parameter markers in a SELECT list need to have their data types explicitly defined for DB2. This can easily be done with the CAST function as follows:

    insert into cotable
    (coekey, cocode, coname, coactv)
    Select Cast(? As Int),
           Cast(? As VarChar(30)),
           Cast(? As Char(10)),
           1
      From SysDummy1
     Where Not Exists
        (Select 1
           From cotable
          Where coekey=?)
    

    If you do not provide DB2 with the data type of the parameter marker in a Select list, the SQL statement execution will fail with the message “SQL0418 Use of parameter marker not valid.” Notice in this case the fourth parameter marker (in the Where clause) does not need an explicit CAST because it is inferred from the data type of column COEKEY.

    Here is a snippet of Java code that illustrates usage of a PreparedStatement against a similar conditional Insert to the one shown above:

    Connection conn=DriverManager.getConnection("jdbc:as400://
    myas400/i3;transaction isolation=none;driver=native;
    naming=system","user","pwd");
    String sql="Insert Into i3/Test1 (C1,C2,C3) " +
      "Select Cast(? As Int)," +
      "       Cast(? As VarChar(30))," +
      "       Cast(? As Double)" +
      "  From SysIBM/SysDummy1 " +
      " Where Not Exists " + 
      " (Select * " + 
      "    From i3/Test1 " + 
      "   Where C1=?)";
    PreparedStatement ps=conn.prepareStatement(sql);
    ps.setInt(1, 3);               // Column 1
    ps.setString(2, "Test It");    // Column 2
    ps.setFloat(3,1.52f);          // Column 3
    ps.setInt(4, 3);               // Where Clause
    ps.execute();                  // Run the statement
    ps.close();
    conn.close();
    

    For those unfamiliar with JDBC, the PreparedStatement object has a variety of set methods (setInt, setString, etc.) that are used to assign values to the parameter markers. Parameter markers are evaluated in a SQL statement from left to right, top to bottom. The first parameter to these set methods is the parameter number in the statement and the second parameter is the value to be assigned to that parameter marker.

    So even though DB2 for i5/OS doesn’t support dynamic SQL, with a few tricks an acceptable substitute can be made.

    Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Email him at the IT Jungle Contact page.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Raz-Lee Security

    Start your Road to Zero Trust!

    Firewall Network security, controlling Exit Points, Open DB’s and SSH. Rule Wizards and graphical BI.

    Request Demo

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Vision Solutions:  System i Management Tips Blog - Free i5/OS Tips Each Week!
    Aldon:  Get ITIL compliant with Aldon's Application Lifecycle Management solutions
    COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    5250 Emulator for iPhone? Mochasoft is On It What the Heck Is the Midrange, Anyway?

    Leave a Reply Cancel reply

Volume 8, Number 26 -- July 16, 2008
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
COMMON

Table of Contents

  • API Fun Time
  • Conditional Inserts with JDBC Prepared Statements
  • Admin Alert: A Client Access Mystery Solved. . . with No-Prizes!!!

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle