Sunday, November 05, 2006

WTF with SQLJ

What is SQLJ

SQLJ allows embedding SQL statements in Java, similar to Pro*C (and all precompilable languages). A SQLJ program is pre-compiled into JDBC (by a translator) and therefore represents a simpler and more productive alternatve to straight JDBC programming in client-tier, middle-tier and database-tier. A runtime mechanism is usually needed at execution time.

#sql iter = {SELECT ENAME FROM EMP where EMPNO ='7934'};
if (iter.next()) { System.out.println("Employee# 7934 is " + iter.ENAME()); }

Oracle SQLJ Status

SQLJ has been briefly de-supported in early releases of the Oracle Database 10g (namely 10.1.0.1, 10.1.0.2, and 10.1.0.3) but is fully re-supported in the Oracle Database 10.1.0.4 and up.

SQLJ Benefits
Many benefits including simplicity features, translation-time type checking, interoperability with JDBC, Oracle extensions to SQLJ, SQLJ Stored Procedures, and Runtime-free code generation.
Simplicity Features (Compared with JDBC)
  • Embedded host variables (JDBC uses parameter markers).
  • No need to cast data types.
  • No need to programmatically register output parameters.
  • No need to explicitly handle the NULL value (See JDBC support for Null in Part II of my book).
  • Support for SELECT INTO statements.
  • Support for PL/SQL anonymous blocks makes it easy to embed PL/SQL in Java.
  • No need for String concatenation for long SQL statements.

Translation-Time Type Checking

  • Syntax checking of SQLJ constructs: a SQLJ parser checks the grammar, according to the SQLJ language specification.
  • Syntax checking of Java instructions: a Java parser (invoked under the covers) checks the syntax of Java host variables and expressions within SQLJ executable statements.
  • Semantics checking: Depending on the option settings, this includes: Syntax of SQL statements by a SQL semantics checker; SQL offline parser; Validation of schema objects (i.e., tables name, columns name); Validation of the mapping between Java types and SQL types.

Interoperability with JDBC

  • JDBC connections can be used by SQLJ as a connection context and vice versa.
  • JDBC ResultSet can be passed to a SQLJ application as a SQLJ Iterator, and vice versa.

Oracle Extensions

Support for Dynamic SQL: Oracle extends it's implementation beyond the specification which only allows static SQL statements, to allow constructing SQL statements at runtime, similarly to JDBC.

String table = "new_Dept"; #sql { INSERT INTO :{table :: dept} VALUES (:x, :y, :z) };

Compile-Time Performane Optimizations

  • Row prefetching
  • SQLJ statement caching
  • Update batching
  • Column type(s) definition
  • Parameter size definition

SQLJ Stored Procedures

The database-resident Java VM (covered in depth in Part I of my book) embeds a SQLJ translator and runtime which allow you to load, pre-compile, compile and execute SQLJ source programs direcly in the database.
...

dbprint("Hello from SQLJ in the Database!");

Date today;

#sql {select sysdate into :today from dual};

dbprint("Today is " + today);

...

static void dbprint(String s) {

try {

#sql { call dbms_output.put_line(:s)

};

} catch (SQLException e) {} }

Runtime-free code generation

SQLJ traditionally requires a runtime (implemenrtor specific). Oracle'simplementation allows you to generate runtime-free code (i.e., oure JDBC).

Advanced/Powerful SQLJ Concepts

SQLJ is more than a mere high-level JDBC code; the language/specification defines concepts such as connection contexts, execution context, iteratorsand expressions that are more advanced than their JDBC counterpart (when they exis).
A SQLJ program is a Java/JDBC code with SQLJ declaration statements,and SQLJ executable statements.

SQLJ declaration statements
This category contains import statements, connection contexts declaration,execution contexts declarations, and iterators declarations.

Excutable Statements

Executable statements can be grouped into Statementclauses and Assignment clauses.
Statement Clause:

#sql [optional SQLJ space] { Statement clause };

Assignment clauses

Query clause

#sql [conctx, exectx] iter = { SQLJ clause };

Function clause

#sql result = { VALUES ( ())};

Iterator conversion clause

#sql iter = { CAST :rset };

Expressions

Expresions are another powerful SQLJ mechanism that allows you to exploit Java expressions in SQLJ statements including: host expressions,context expressions and result expressions.

#sql [connctx_exp, execctx_exp] result_exp = { SQL with host expression };

Accessing Oracle SQL and PL/SQL Data Types
SQL and PL/SQL data are manipulated in SQLJ through SQLJ host variables/expressions, result expressions, and iterator accessors.

Assume a table with an XMLType column, created as follows:

create xmltype_tbl (xmltype_col SYS.XMLType);

insert into xmltype_tbl values(SYS.XMLType('tom'));

insert into xmltype_tbl values(SYS.XMLType('jon'));

It can be manipulated using the following code fragments:

import oracle.xdb.XMLType;

...

//

#sql iter={select xmltype_col from xmltype_tbl;}

while(iter.next()) {

System.out.println(iter.xmltype_col().getStringVal());

}

//

while (iter.next())

{

System.out.println(iter.xmltype_col.getClobVal());

}

Learn more about SQLJ and Oracle implementation and code samples

6 comments:

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰套,自慰,性感吊帶襪,吊帶襪,情趣用品加盟AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,成人網站,A片,A片下載,免費A片,免費A片下載愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片,豆豆聊天室,聊天室,UT聊天室,尋夢園聊天室,男同志聊天室,UT男同志聊天室,聊天室尋夢園,080聊天室,080苗栗人聊天室,6K聊天室,女同志聊天室,小高聊天室,上班族聊天室,080中部人聊天室,同志聊天室,聊天室交友,中部人聊天室,成人聊天室,一夜情聊天室,情色聊天室,寄情築園小遊戲情境坊歡愉用品,情趣用品,成人網站,情人節禮物,情人節,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,成人影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,視訊聊天,A片,A片下載,免費A片,嘟嘟成人網,寄情築園小遊戲,女同志聊天室,免費視訊聊天室,一夜情聊天室,聊天室

Anonymous said...

We specialize in laptop battery,laptop AC adapters. All our products are brand new, with the excellent service from our laptop battery of customer service team.
the most convenient and cheap replacement battery online shop in uk. We specialize in laptop batteries, laptop AC adapters.
All our laptop AC adapters are brand new, with the excellent service from our customer service team.
the most convenient and cheap battery online shop in uk.
You can find some battery and adapter from here is very cool.

said...

A片,A片,成人網站,成人漫畫,色情,情色網,情色,AV,AV女優,成人影城,成人,色情A片,日本AV,免費成人影片,成人影片,SEX,免費A片,A片下載,免費A片下載,做愛,情色A片,色情影片,H漫,A漫,18成人

a片,色情影片,情色電影,a片,色情,情色網,情色,av,av女優,成人影城,成人,色情a片,日本av,免費成人影片,成人影片,情色a片,sex,免費a片,a片下載,免費a片下載

情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣

A片,A片,A片下載,做愛,成人電影,.18成人,日本A片,情色小說,情色電影,成人影城,自拍,情色論壇,成人論壇,情色貼圖,情色,免費A片,成人,成人網站,成人圖片,AV女優,成人光碟,色情,色情影片,免費A片下載,SEX,AV,色情網站,本土自拍,性愛,成人影片,情色文學,成人文章,成人圖片區,成人貼圖

情色,AV女優,UT聊天室,聊天室,A片,視訊聊天室

Anonymous said...

童裝批發
童裝批發
童裝
童裝
酒店喝酒
酒店消費
暑假打工
寒假打工
酒店經紀人
酒店現領
禮服店
酒店小姐
酒店兼職
酒店上班
酒店兼職
禮服店
酒店上班
酒店打工
酒店小姐
酒店經紀
酒店兼差
假日打工
台北酒店經紀
寒暑假打工
專業酒店經紀
酒店藝名
台北酒店
酒店日保
酒店入行
台北禮服店
酒店類型
酒店午場
酒店規定
酒店行政
酒店資訊

data entry outsourcing said...

SQLJ traditionally requires a runtime (implemenrtor specific). Oracle'simplementation allows you to generate runtime-free code (i.e., oure JDBC).

Data Entry Outsourcing said...

SQLJ declaration statements
This category contains import statements, connection contexts declaration,execution contexts declarations, and iterators declarations.