/SQL and SQLJ

SQL and SQLJ

SQL :-

SQL “Structured Query Language” :- It enables the developer communicate with a database.
SQL statements are used to perform tasks such as :-

  • – create new databases,
  • – create new tables in a database,
  • – access and query database,
  • – retrieve data from database,
  • – insert records in database,
  • – update records in a database,
  • – delete records from a database,
  • – create stored procedures in a database,
  • – create views in a database,
  • – set permissions on tables, procedures, and views.

SQL works with relational databases. A relational database stores data in tables (relations). A database is a collection of tables.
A table consists a list of records. A table is a collection of related data entries and it consists of columns and rows. Each record in a table has the same structure. Each record has a fixed number of “fields” of a given type.
RDBMS “Relational DataBase Management Systems” that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres.
The data in RDBMS is stored in database objects called tables.

There are 3 basic categories of SQL Statements :-

1- SQL-Data Statements :- query and modify tables and columns.

  • –  SELECT Statement : query tables and views in the database.
  • –  INSERT Statement : add rows to tables.
  • – UPDATE Statement : modify columns in table rows.
  • – DELETE Statement : remove rows from tables.


2- SQL-Transaction Statements
:- control transactions.

  • – COMMIT Statement : commit the current transaction.
  • – ROLLBACK Statement : roll back the current transaction.


3- SQL-Schema Statements
:- maintain schema (catalog).

  • – CREATE TABLE Statement : create tables.
  • – CREATE VIEW Statement : create views.
  • – DROP TABLE Statement : drop tables.
  • – DROP VIEW Statement : drop views.
  • – GRANT Statement : grant privileges on tables and views to other users.
  • – REVOKE Statement : revoke privileges on tables and views from other users.


SQLJ :-

SQLJ :- It’s an easy way for Java programmers to access databases via SQL. Without SQLJ, Java programmers are limited to JDBC result set processing. SQLJ facilitates the development of database oriented projects, during developing Java applications using JDBC, you have to test your SQL against the database. SQLJ enables developers to insert SQL statements directly into the Java code, and the sqlj.exe file convert SQLJ syntax into Java source file. and enables developers to fully control the SQLJ development environment. It enables developers write less code within your Java applications that deal with SQL calls to database.
SQLJ enables SQL statements to be checked during compilation and not runtime. It gives exactly what is needed for quick development, with less code, easy debugging, and automatic performance improvement.
SQLJ programs require fewer lines of code than JDBC programs, hence easier to debug. SQLJ can perform syntactic and semantic checking on the code. using database connections at compile time. SQLJ checks query results and return parameters, while JDBC values are passed to and from SQL without checking at compile time.
SQLJ provides such an efficient way of processing SQL statements, instead o fwriting separate method calls to bind each input parameter and retrieve each select list item, now you can write one SQL statement that uses Java variables, SQLJ takes care of binding.
The SQLJ Standard provide:-
**
The SQLJ Language Specification :
– provides standard language syntax and semantics for ensurance static SQL in Java programs, The SQLJ translator checks the syntax of SQL statements during translation, his check prevents any run-time SQL syntax errors. JDBC doesn’t perform any checks, which allow run-time SQL syntax errors to occur.

** 
The Stored Procedure Specification :
– defines standards for implementing database stored procedures and functions in Java.

** 
The Stored Java Class Specification addresses :-
defines standard ways to store Java datatypes and classes as objects in database.
 

   Differences between SQL and SQLJ :-

  • ** With JDBC, SQL and DML, statements are only validated at runtime and not compile time, AS the SQL is only known as String object to JDBC. SQLJ wont allow invalid SQL to compile and will inform the developer at development time instead of runtime {automatic validation of SQL code}
  • ** SQLJ can execute the same tasks as JDBC with much fewer lines and it’s much easier to read SQL statements line by line in Java.

But Note That :-
With SQLJ .. There’s need for extra step to create compiled code to access the database and it may cause problems !

** You Can learn More from these references .. I think that it’s such an awesome reference and it will be useful :
http://tinman.cs.gsu.edu/~raj/books/Oracle9-chapter-6.pdf
and ..
http://docs.oracle.com/cd/B10501_01/java.920/a96655.pdf
and finally it’s IBM reference ..
http://publib.boulder.ibm.com/infocenter/rtnlhelp/v6r0m0/index.jsp?topic=%2Fcom.ibm.etools.sqlj.doc%2Ftopics%2Fcsqlj.html