• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Faking Create Or Replace Table

    January 20, 2015 Ted Holt

    The new CREATE OR REPLACE feature of SQL has been most helpful to me. It works for aliases, functions, masks, permissions, procedures, sequences, triggers, variables and views. It would be nice if it worked for tables, especially when I’m developing a new application. Here’s a workaround.

    The trick is to use a dynamic compound statement. If you’re not familiar with these, I recommend you read Michael Sansoterra’s excellent article on that subject.

    One of the things that dynamic compound statements let you do is include conditional logic, which is what we need. We need a way to drop a table if it exists, and not error out if the table does not exist.

    Here’s a typical script of the sort that I run under RUNSQLSTM:

    /* Delete the tables if they exist */
    begin
       declare continue handler for sqlexception
          begin end;
       drop table customers;
       drop table shipments;
       drop table invoices;
    end;
    
    create table customers 
    . . . code omitted . . .
    
    create table shipments
    . . . code omitted . . .
                          
    create table invoices 
    . . . code omitted . . .
    

    The dynamic compound statement is the begin-end block that spans the second through the eighth lines. I’ve defined a continue handler to run an empty begin-end block when an exception occurs. The continue handler takes control when a drop table ends abnormally.

    This technique is not exactly like Create or Replace. There is one big difference. When you delete an object, the system discards all object authority for that object. Create or Replace retains object authority.

    RELATED STORY

    Dynamic Compound Statements In DB2 For i

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    COMMON

    We Are Stronger Together!

    On behalf of the IBM i community, thank you to our valued COMMON members. We recognize and understand the challenges our members have faced this year, and we have faced many of the same challenges. As always, we are committed to our members and this community, and are working tirelessly on your behalf. Showing our commitment, we have decided not to increase dues for 2021. Through these unprecedented times, our top priorities remain serving the membership, providing benefits to support your career, and being the voice of our members and the industry.

    It’s Time to Renew

    Now more than ever, COMMON is essential to the IBM i community. We respectfully ask for your continued support by renewing your membership.

    Your continued commitment to COMMON enables us to:

    • Develop valuable in-person and on-demand education
    • Connect you with peers, industry experts, and vendor partners
    • Boost the visibility of your business as an IBM i leader and help you stay informed about the opportunities ahead
    • Reinforce the value of IBM i through educational programs, events, and services
    • Spotlight issues and gain visibility for the IBM i community
    • Provide timely communications about issues that impact our members
    • Provide resources such as online job postings, an expanded web presence through the membership directory, and digital advertising opportunities

    COMMON is strong because of YOU. Together, we will tackle these challenges, no matter what the future may bring, and ensure not only that the IBM i community will get through this, but will thrive.

    Renew Your Membership Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  Reach Your Modernization Goals. Register for the February 25 Webinar now!
    New Generation Software:  Ask us about Query, Reporting, and Analytics. Order a FREE Trial of NGS-IQ.
    System i Developer:  Upgrade your skills at the RPG & DB2 Summit in Dallas, March 17-19

    Strengthening Dollar Curtails Global IT Spending Growth IBM i Predictions For 2015 From Around The Community

    Leave a Reply Cancel reply

Volume 15, Number 01 -- January 20, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
SEQUEL Software
WorksRight Software

Table of Contents

  • Faking Create Or Replace Table
  • Everybody Likes Shortcuts! Part 1, Navigation
  • Regurgitating Data With DB2 For i Data Change Table References

Content archive

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

Recent Posts

  • 2021 Predictions for IBM i, Part 1
  • West Four Stands Out With On Demand Color Label Printing
  • HelpSystems Acquires Data Security, File Transfer Companies
  • Four Hundred Monitor, January 13
  • IBM i PTF Guide, Volume 23, Number 2
  • Seiden Group Unveils A PHP Distro For IBM i
  • Thoroughly Modern: DevOps Refactoring Of RPG Applications with RDi
  • Guru: Fall Brings New RPG Features, Part 2
  • More Vintage Power Systems Feature Withdrawals
  • IBM i PTF Guide, Volume 23, Number 1

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 © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.