ABC’s of Adobe ADBC

Note: The opinions expressed in this article are those of the
author and not of any other party. The discussion presented
here is offered in the context of personal opinion only.

People ask me all the time what’s the best way to learn Acrobat JavaScript. With any other programming language, I would say that a beginner can learn quickest by studying other people’s code whenever the opportunity arises. But with JavaScript, I frankly cannot (in good conscience) recommend this, because most of the JavaScript one encounters in the real world is, in my experience at least, scandalously bad, a frightful catalog of things-not-to-do. ?

One of the subjects that seminar attendees consistently showed
a great deal of interest in at the recent Seybold PDF
conference in New York was Acrobat Database Connectivity
(ADBC), a feature of Acrobat 5.0 that allows Windows users to
connect to relational database systems and issue SQL queries.
ADBC-related questions are also starting to show up in
frequently on the Planet PDF discussion forums. It’s obvious
that ADBC has a future. But what about the present? Is ADBC
ready for production usage right now?

The short answer is, only you can decide the answer to that,
based on your specific application requirements and your own
testing of ADBC-powered forms and applications. In my own
limited testing of ADBC on Windows NT and XP, I’ve discovered
limitations to the current API (Application Programming
Interface) that, frankly, would make me cautious about putting
ADBC to work in anything but a lightweight,
non-mission-critical application.

ADBC Basics

It’s important to understand a couple of basic limitations up
front, such as the fact that Acrobat (and therefore ADBC)
cannot legally be used on a server, due to licensing
restrictions. You will not be able, for example, to deploy
Web-facing applications that let clients ‘tunnel through’ to
back-end data sources via ADBC. That’s not part of the ADBC
game plan and probably never will be. If you need to connect
Web browsers to databases via the Web, you can certainly give
clients a PDF form for data entry, but you’ll still have to
provide your own behind-the-firewall RDBMS connectivity
solution (via CGI or servlets), the way you presently do.

A second limitation of ADBC is that it’s a Windows-only
solution. This is clearly documented in the AcroJS.pdf help
file.

Yet another thing to realize up front is that that ADBC
functionality is really only reachable from JavaScript.
Acrobat itself doesn’t offer any kind of direct (menu-command
or wizard-driven) interface to ADBC. If you can’t write
JavaScript, you can’t use ADBC.

But don’t give up yet. The JavaScript API for ADBC is not hard
to use. In fact, the most important development requirement
for ADBC is not JavaScript experience but SQL experience.

How to Connect with Your Data

Space precludes an in-depth discussion of ADBC scripting here,
but the following should be enough to get you started.

The main thing you’ll be wanting to do, of course, is to
connect to a database and issue SQL queries. In practical
terms, this means defining a DSN (data source name) in order
to expose the database, opening a connection, creating a
Statement object, and issuing a query through the Statement
object’s execute() method.

Defining the DSN is a Windows-specific thing that has nothing
to do with Acrobat, so I won’t discuss it here. Presumably, if
you’re a serious database user, you already know the drill. If
you’re a casual user or beginner, it may interest you to know
that you can reach into Excel documents this way. You can find
a lot of good tutorials on DSN setup on the web.

Once you know the name of the DSN you want to connect to, and
the name of the table you want to run queries against, it
takes only a few lines of JavaScript to establish a connection
and run a query:


// This code assumes a DSN of ‘MyData’ and a table
// name of ‘MyTable.’

var conn = ADBC.newConnection( ‘MyData’ );
if (conn == null)
throw ‘Null Connection Object’;

var stmt = conn.newStatement(); // create a Statement Object
if (stmt == null)
throw ‘Null Statement Object’;

stmt.execute( ‘SELECT * FROM MyTable’ ); // issue SQL query

Gotchas

You may encounter problems getting these lines to work. Sadly,
troubleshooting the situation is not easy.

If the connection can’t be opened, you will get a null object
back, which is easy to test for. But what does it mean? It
could mean any of a thousand things. The DSN name might be
spelled wrong, the DSN doesn’t exist at all, the file was in
an unexpected format, the file couldn’t be found, the moon is
in the wrong phase. Whatever. You’re on your own. Good luck.

The newStatement() method won’t work without a valid
Connection object, of course. When it works, it returns a
Statement object, but when it can’t do that (for reasons
unknown), it returns null. There’s a third possibility: In the
case where you call the newStatement() method,
inappropriately, on a null Connection object, you get a return
value of undefined.

The execute() method takes an SQL query (as a string).
According to Adobe’s documentation (AcroJS.pdf), the execute()
method should return a boolean value indicating success or
failure. But it doesn’t work that way (for me, at least). I
have yet to see it return anything other than undefined on
success, and an exception on failure. (Which makes for an
awkward Seybold demo.)

If your SQL statement involves a SELECT, you will want to
inspect the result set that comes back (if indeed one comes
back). To do this, you call nextRow(), then getRow(), on the
Statement object that you used when executing the SQL query.
You must call these two methods, in the order just stated, for
every row of data you want to step over or inspect. The
nextRow() method takes no arguments and returns nothing. (But
it will throw an exception if there is no next row to get.)
The getRow() method returns a Row object on success. And on
failure? Well, apparently the method never fails. Adobe
doesn’t say anything about exception behavior or failure modes
in the documentation for this method.

One shortcoming of the nextRow()/getRow() technique is that it
means stepping through rows serially in order to access a
particular record in a result set. If you want to pluck out
just row number one thousand, you have to step through 999
rows to get at it. There is no random access, no way to jump
to a particular row, as far as I know. This isn’t a big deal
on small result sets, but on large result sets it could mean a
significant performance hit. (We are dealing with an
interpreted language here, after all.)

The way other languages (such as Java) handle this situation
is to define a ResultSet object to hold row data, with methods
like first(), last() and absolute() that the user can rely on
to move the ‘read location’ quickly to any part of the result
set. One hopes that Adobe will choose to provide
scrollable-result-set functionality in the next revision to
ADBC.

Error Checking and Debugging

Adobe provides little help for troubleshooting failed
execute() statements. In other connectivity APIs, such as Sun
Microsystems’ popular JDBC (Java Database Connectivity), an
SQL error causes an SQLException to be thrown. The
SQLException object, in turn, has properties on it that you
can inspect for things like the SQLCODE that resulted from the
failed query. To my knowledge (I could be wrong here), there
is no way to discover the values of status codes like SQLCODE
or SQLSTATE via ADBC. And there is no runtime-trace facility
that can be hooked into from ADBC, as far as I know. Microsoft
ODBC error codes are not exposed in ADBC, either. Or at least
it’s not documented.

In my work with ADBC, I’ve found that it’s easy to end up with
silent runtime errors unless a great deal of care is used in
applying error-checking and exception-trapping code in the
design phase; and even then, many return values and exception
messages are of little or no diagnostic value. This makes it
difficult for the forms designer to build a truly robust,
user-friendly data-entry/data-query form that comes with
online help that can steer the user toward solutions to errors
at runtime.

In my opinion, ADBC would benefit from the addition of the
following:

  1. A very simple SQL grammar-checker for preflighting queries

  2. A ResultSet object with scrollability

  3. A special SQLException object that exposes SQLCODE,
    SQLSTATE, and other status codes, as well as pinpoint-specific
    diagnostic messages

  4. Regular Acrobat UI features (menu commands and wizards) to
    help with the creation of ADBC-powered forms

  5. A method for taking down (or releasing) database connections when they are no longer needed. Right now, there is no documented way to release a connection programmatically, and Adobe doesn’t describe the conditions (if any) under which a connection might be closed automatically by Acrobat. This information is vital to enterprise users, since database connections are precious resources in the IT world.

It would also be nice to know what the implementation limits
are, if any, with respect to ADBC’s memory usage and
data-handling.

Conclusion

Bottom line, ADBC is a capable and robust (if lightweight)
connectivity solution for the in-house forms developer who
needs to give employees a way to connect to, say, an Oracle
back end. But the API needs to be fleshed out for it to be
taken seriously by professional database-forms developers. In
venturing into back-end connectivity, Adobe has waded into
some rather deep waters. Enterprise customers have strict
reliability requirements and high expectations from vendors of
connectivity solutions (because the price of failure is
correspondingly high). Right now, no one would seriously
contend that ADBC is on a par with something like (say) Java’s
JDBC for power, flexibility, robustness, etc. ADBC’s
acceptance in enterprise will be slow, if for no other reason
than that it is in its first release.

Nevertheless, Adobe is to be commended for taking on this
bold, ambitious task and providing a creditable ‘1.0 release’
of an easy-to-use connectivity API. It will be exciting to
watch ADBC mature in releases to come.

You May Also Like

About the Author: Kas Thomas

Leave a Reply