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.
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:
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?
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.
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.
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
http://www.redbooks.ibm.com/Abstracts/tips0801.html?Open