Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Anyone Using SQL CLI for iseries IBM i
has no ratings.
Published: 12 Mar 2014
Revised: 13 Mar 2014 - 3641 days ago
Last viewed on: 01 Mar 2024 (5598 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Anyone Using SQL CLI for iseries IBM i Published by: Bob Cozzi on 12 Mar 2014 view comments(3)

I'm wondering if anyone is using CLI (SQL Call-level Interface aka "ODBC") natively on IBM i?

I've built a wrapper that allows me to effectively displace embedded SQL when necessary. Aside from a few benefits, there is really no significant advantage to CLI over embedded SQL, but I needed to be able to manipulate the resulting datasets for a report writer, so i wrote a wrapper that mimics the openSQL, fetchSQL, CloseSQL functions of embedded SQL.

This SQLLIB service progam also returns the data in any of (currently) three formats:

  1. Native format (packed, char, zone, etc.)
  2. Text format (all data converted to plain text)
  3. HTML format (all data converted to plain text and wrapped in HTML

Eventually I'm going to add CSV, JSON and XML to the resulting formats. But I'm still working on the kinks, while using extensively in a new Report Writer I built just for fun.

So, do you see any use for something like this, or should I put it in the vault of cool stuff I keep to myself?


Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: Anyone Using SQL CLI for iseries IBM i
Posted: 9 years 11 months 20 days 4 hours 32 minutes ago

Don't use it, but there are some advantages of CLI over embedded SQL. Since you're calling an API, you're not using the precompiler, so you don't need the SQL licensed program installed. Since they're based on ODBC, CLI may allow your application to connect to a database product other than DB2. There are one or two other things that could tip a decision to CLI, but if you're strictly DB2, you're probably better off sticking with what you know.

Posted by: bobcozzi
Site Admin ****
Comment on: Anyone Using SQL CLI for iseries IBM i
Posted: 9 years 11 months 20 days 4 hours 14 minutes ago

Hi Dale, yes I think so too.

I think Commercial Products (like report writers or code generators) need a standard interface to write to so they can port their apps to/from IBM i. I also think it is far too complex to use in anything other than C or C++ development, although not Rocket Science. That's why I built a SRVPGM that handles the rough stuff for the RPG developer.

It is pretty cool though, how you can interface directly with the I/O buffer and tell it to convert the data to Character for you.

Posted by: Ringer
Premium member *
Comment on: Anyone Using SQL CLI for iseries IBM i
Posted: 9 years 11 months 20 days 2 hours 19 minutes ago

We have a few SQL stored procs that return a result set to the caller. Sometimes the caller is web (java) and sometimes the caller is RPG. If the caller is RPG, someone here wrote code to read the Result Set with CLI. Me? I'd just stuff data into QTEMP if the caller is RPG. The CLI stuff is a bit complicated. I think in v7.1, RPG can easily read a Result Set. 

Chris Ringer