Java IoT Authors: Elizabeth White, Liz McMillan, Yeshim Deniz, Pat Romanski, William Schmarzo

Related Topics: Java IoT

Java IoT: Article



Several fun and important secrets of SQLJ will be unlocked today. Our show will include the following numbers:

  • Some magic tricks for taming the SQLJ translator to do your bidding.
  • Some incantations to turn you into a SQLJ debug-mon.
  • An initiation to the mysteries of execution contexts - for getting full control over executing SQL statements - and of connection contexts.
  • A happy story of brotherly love between JDBC and SQLJ.
  • A map of the hidden location containing all of the remaining SQLJ details that the wonderful folks from Java Developer's Journal neither would or could let me fit into this column.
Little space and much to say - let's get started right away!

Your Own Private Translator...Is Talking Back
Don't fret if you're stuck and need help - the SQLJ translator may just be able to give you what you need. Enjoy the following translator command-line options:

  • sqlj -help shows a short help message with the important SQLJ command line options. And when you just say sqlj by itself this is also recognized as a cry for help.
The Translator-Chameleon Is Serving Up Bugs
How I wish that SQLJ translation were built into the javac Java compiler. But tough luck - it ain't! The translator strives hard to make it appear however as if the only difference is saying sqlj instead of javac.

Nice: You can freely mix .sqlj and .java source files on the command line. The translator also has some implicit make capability similar to javac. Oh yes, it automatically invokes your Java compiler - but you already knew that!

Nice: The translator reports error messages from your Java compiler on the original SQLJ file and not on the generated Java file.

Not Nice: When your program throws exceptions at runtime, line numbers - such as those issued by printStackTrace() - are shown in terms of the generated Java files.

Incantation #1
Add the flag -linemap to your command line during translation. Then the translator will fix up file names and the line numbers in those class files to show the original SQLJ files. If you want this on by default (which it probably should have been in the first place), then create an environment variable SQLJ_OPTIONS with the value "-linemap". Or you can add the line sqlj.linemap to a sqlj.properties file.

Still Not Nice: If you use Sun's Java debugger jdb to debug your SQLJ program, you'll see that...this tip does not work: jdb refuses to show the SQLJ source. No wonder — they only taught it about .java source files!

Magic spell #2
Shout -jdblinemap instead of -linemap whenever you must trick that silly little (de)bugger.

God Mode: Well, not quite...You can trace the goings-on in the SQLJ runtime by installing a profile auditor in your SQLJ profile files (remember those pesky little .ser files that hold the static SQL information of your program). After the usual translation and compilation you add tracing with the following command:
sqlj -P-debug *.ser
Have fun drinking from the fire hose!

More Dizzying Debugging Spells
Remember the JDBC Genie? On many platforms (such as Oracle's), SQLJ runtime calls turn into calls to JDBC at the end of the day. You can trace JDBC calls with DriverManager.setLogStream() (or setLogWriter).

Don't count out them IDEs: Oracle's JDeveloper has been supporting SQLJ programming and debugging for a while. Other IDEs may offer SQLJ support Real Soon Now.

Smokey Bear Says: Only you can prevent runtime bugs. You should always provide the translator with the

option, so that it can check your SQL code for real.

Become a Control Freak - with ExecutionContexts
At times you need to obtain additional information about a SQL statement that just ran or you need to control exactly how you want SQL statements to be executed. Take the following examples:

  • The statement might have resulted in a warning (not an exception) that you want to inspect.
  • An UPDATE or DELETE statement reports the number of rows that was changed or - respectively - removed.
  • You want to set a timeout or designate a prefetch size for queries.
  • You want to batch several SQL statements and have them all executed together, rather than pay a round trip to the database for each one. In particular, you want to combine the repeated execution of a DML statement, such as an INSERT, an UPDATE or a DELETE that takes on different values for its bind-variables.

All of this - and more - is available on the sqlj.runtime.ExecutionContext. Every connection context (remember, this is the SQLJ equivalent to JDBC connections) has an associated ExecutionContext which can be accessed with getExecutionContext(). Your static DefaultContext also has one. Consider this example:

import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ExecutionContext;
#sql { UPDATE emp SET sal = sal * 2 };
ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext();
System.out.println( ec.getUpdateCount() + " employees are rejoicing.");
Or, you can terminate a whole bunch of bad guys in a single batch as follows.
String[] badGuys = { "HORRIBLE", "TERRIBLE", "NOGOOD", "VERYBAD" };
for (int i=0; i<badGuys.length; i++) {
#sql { DELETE FROM emp WHERE ename = :(badGuys[i]) }; }
If we hadn't issued executeBatch() explicitly, then the badGuys' execution would happen implicitly when a different #sql statement is encountered. Alternatively, we could have used ec.setBatchLimit(4); to tell SQLJ to always flush a batch implicitly once 4 rows have accumulated. This convenience feature is not (yet?) offered by JDBC.

Being Well Connected - Explicitly
Until now you've been brainwashed. You were led to believe that there's only a single, static connection in your SQLJ program, set once with DefaultContext.setDefaultContext(...) and then forgotten about. Even though this makes great marketing copy, it's not the (only) way the world works.

If you're connecting to more than one schema, if you're running an applet in a browser or if you're connecting to the database in a multithreaded program, then you should - nay, you must - use explicit SQLJ connections.

Don't worry: it's really easy. You just put the connection context instance (or an expression evaluating to one) in those square brackets:

An example:
import sqlj.runtime.ref.DefaultContext;
DefaultContext ctx =
new DefaultContext("jdbc:oracle:oci8:@", "scott", "tiger", false);
#sql [ctx] { UPDATE emp SET sal = sal * 2 };
Tip: Always use explicit connection contexts, unless you know that your program owns the world and requires only a single, static database connection.

Rolling Your Own Connection
Sometimes you need to distinguish between different schemas or databases. Consider a program that establishes connections to two different database schemas. The PILOTS schema has personnel data, flight hours and so on for pilots, and the JETS schema contains maintenance and flight data of aircraft. Your program is working on two different sets of tables, views and stored procedures. Now you need to verify your SQLJ statements against both of these schemas. Typed connection contexts are what let you do this. First, declare two different context types:

#sql context Pilots;
#sql context Jets;

At runtime, you connect to each of the schemas using the appropriate type:

Pilots pconn = new Pilots("jdbc:oracle:oci8:@","pilots","ace",false);
Jets jconn = new Jets("jdbc:oracle:oci8:@","jets","stratos",false);
Then the connection context type of the #sql statement clearly shows whether you want a Pilots or a Jets connection:
#sql [pconn] { INSERT INTO pilot VALUES ( .... ) }; // Pilots context
#sql [jconn] { UPDATE maintenance SET status = Checkup( .... ) }; // Jets context
Of course at translate time you also need to explain how to connect to the database for both connection context types:
sqlj [email protected]=pilots/ace [email protected]=jets/stratos MyFile.sqlj
SQLJ and JDBC: Living in Perfect Harmony
SQLJ works just fine and dandy with static SQL - where you know the shape of SQL statements and queries beforehand. But what if your application has to make up the WHERE clause in a SELECT statement on the fly - guess you'd better forget all about SQLJ, right?

Not so quick - SQLJ and JDBC are actually close-knit buddies. JDBC connections and SQLJ connection contexts are mutually convertible and so are java.sql.ResultSets and SQLJ iterators. Let's look at the specifics.

Connecting from JDBC to SQLJ
All connection context constructors and initializers can take an existing JDBC connection. Example:

java.sql.Connection conn = DriverManager.getConnection(....);
DefaultContext ctx = new DefaultContext(conn);
Now SQLJ and JDBC share the same session. Closing the SQLJ context will also clean up the JDBC connection.

Connecting from SQLJ to JDBC
All SQLJ connection contexts have the getConnection() method that allows you to retrieve an underlying JDBC connection. For example, if your program uses the static default context, the following will do:

java.sql.Connection conn =
Passing Result Sets from JDBC to SQLJ
If you want to pass off a JDBC result set as a SQLJ iterator, you can do so with a SQLJ CAST statement:
SomeIterator iter;
java.sql.ResultSet rs = stmt.executeQuery();
#sql iter = { CAST :rs };
Why that CAST statement and not simply a Java constructor? So that any vendor's SQLJ runtime implementation can scrutinize that result set very closely.

Passing Iterators fromSQLJ to JDBC
This one's a breeze. You just call the iterator's getResultSet() method and - voilˆ - your JDBC ResultSet.

Exercise 1: Why might it be useful to convert a JDBC result set into a SQLJ iterator or vice versa?

Jump into the Fray
Hope you enjoyed the journey, even though we've not yet seen all the vistas. We stopped short of covering the new fun JDBC 2.0 features that made it into SQLJ, such as support for various SQL LOB types, named SQL types, DataSources and scrollable iterators. Nor did we look at how to put iterator subclassing to use or how connection caching meshes with SQLJ or even speculate what directions SQLJ might take in the future. Is there a place to learn more? In my totally biased opinion (Oracle pays my bills, after all) the vastest amount of information on SQLJ is on the Oracle Technology Network site at http://technet.oracle.com. You'll find the world's most humongous SQLJ manual that answers more questions than you could ever dream of, as well as demos, samples, papers, an FAQ and so on. Beware, though, the Oracle-specific is happily stirred in with the generic.

Better yet, do it. If you want to get involved on the standards side, subscribe to the SQLJ partners mailing list at [email protected]. Or, if you want to hack the SQLJ reference implementation - yep, all source, all public domain - go to www.sqlj.org. Or download one of the SQLJ implementations from the IBM, Informix, or Oracle Web site and get started.

Summary of SQLJ Syntax

  • Connection context declaration and use:
    #sql context CtxType;
    CtxTypectx = new CtxType(url, user, pwd, auto-commit);
    #sql [ctx] { ...sql statement... };
  • Setting the translate-time connection for CtxType:
    sqlj [email protected]=user/pwd ...
  • ExecutionContext declaration and use:
    import sqlj.runtime.ExecutionContext;
    ExecutionContext ec = new ExecutionContext();
    ... set properties on ec ...
    #sql [ec] { ...sql statement... };
    ... retrieve warnings, update count, side-channel results, etc. from ec ...
  • Using an explicit ExecutionContext and an explicit connection context:
    #sql [ctx, ec] { ...sql statement... };
  • Casting SQLJ iterators to JDBC result sets:
    SqljIterator iter;
    #sql iter = { CAST :jdbc_result_set };

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

@ThingsExpo Stories
DevOpsSummit New York 2018, colocated with CloudEXPO | DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City. Digital Transformation (DX) is a major focus with the introduction of DXWorldEXPO within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of bus...
With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...
DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
DXWorldEXPO | CloudEXPO are the world's most influential, independent events where Cloud Computing was coined and where technology buyers and vendors meet to experience and discuss the big picture of Digital Transformation and all of the strategies, tactics, and tools they need to realize their goals. Sponsors of DXWorldEXPO | CloudEXPO benefit from unmatched branding, profile building and lead generation opportunities.
DXWorldEXPO LLC announced today that ICOHOLDER named "Media Sponsor" of Miami Blockchain Event by FinTechEXPO. ICOHOLDER give you detailed information and help the community to invest in the trusty projects. Miami Blockchain Event by FinTechEXPO has opened its Call for Papers. The two-day event will present 20 top Blockchain experts. All speaking inquiries which covers the following information can be submitted by email to [email protected] Miami Blockchain Event by FinTechEXPO also offers s...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
Digital Transformation and Disruption, Amazon Style - What You Can Learn. Chris Kocher is a co-founder of Grey Heron, a management and strategic marketing consulting firm. He has 25+ years in both strategic and hands-on operating experience helping executives and investors build revenues and shareholder value. He has consulted with over 130 companies on innovating with new business models, product strategies and monetization. Chris has held management positions at HP and Symantec in addition to ...
Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities - ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups.
Poor data quality and analytics drive down business value. In fact, Gartner estimated that the average financial impact of poor data quality on organizations is $9.7 million per year. But bad data is much more than a cost center. By eroding trust in information, analytics and the business decisions based on these, it is a serious impediment to digital transformation.
The standardization of container runtimes and images has sparked the creation of an almost overwhelming number of new open source projects that build on and otherwise work with these specifications. Of course, there's Kubernetes, which orchestrates and manages collections of containers. It was one of the first and best-known examples of projects that make containers truly useful for production use. However, more recently, the container ecosystem has truly exploded. A service mesh like Istio addr...
Predicting the future has never been more challenging - not because of the lack of data but because of the flood of ungoverned and risk laden information. Microsoft states that 2.5 exabytes of data are created every day. Expectations and reliance on data are being pushed to the limits, as demands around hybrid options continue to grow.
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
As IoT continues to increase momentum, so does the associated risk. Secure Device Lifecycle Management (DLM) is ranked as one of the most important technology areas of IoT. Driving this trend is the realization that secure support for IoT devices provides companies the ability to deliver high-quality, reliable, secure offerings faster, create new revenue streams, and reduce support costs, all while building a competitive advantage in their markets. In this session, we will use customer use cases...
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As automation and artificial intelligence (AI) power solution development and delivery, many businesses need to build backend cloud capabilities. Well-poised organizations, marketing smart devices with AI and BlockChain capabilities prepare to refine compliance and regulatory capabilities in 2018. Volumes of health, financial, technical and privacy data, along with tightening compliance requirements by...
The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.
The IoT Will Grow: In what might be the most obvious prediction of the decade, the IoT will continue to expand next year, with more and more devices coming online every single day. What isn’t so obvious about this prediction: where that growth will occur. The retail, healthcare, and industrial/supply chain industries will likely see the greatest growth. Forrester Research has predicted the IoT will become “the backbone” of customer value as it continues to grow. It is no surprise that retail is ...
Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settlement products to hedge funds and investment banks. After, he co-founded a revenue cycle management company where he learned about Bitcoin and eventually Ethereal. Andrew's role at ConsenSys Enterprise is a mul...
DXWorldEXPO LLC announced today that "Miami Blockchain Event by FinTechEXPO" has announced that its Call for Papers is now open. The two-day event will present 20 top Blockchain experts. All speaking inquiries which covers the following information can be submitted by email to [email protected] Financial enterprises in New York City, London, Singapore, and other world financial capitals are embracing a new generation of smart, automated FinTech that eliminates many cumbersome, slow, and expe...
Cloud Expo | DXWorld Expo have announced the conference tracks for Cloud Expo 2018. Cloud Expo will be held June 5-7, 2018, at the Javits Center in New York City, and November 6-8, 2018, at the Santa Clara Convention Center, Santa Clara, CA. Digital Transformation (DX) is a major focus with the introduction of DX Expo within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive ov...