www.fingertip.page.tl or www.fingertip.us.tt - DB-Page1
   
www.fingertip.us.tt
  Index
  Free WebHost&DNS
  Hardware&Networking
  Red Hat Enterprise Linux 5
  Active Server Page2.0
  Database Coding Standards
  => DB-Page1
  => DB-Page2
  .Net Platform
  Link Website
  Online Game
  Satellite Image Home
  Download Toolbar
  Other
  WebMaster
  Counter
  SiteMap
  Guestbook
  To Contact Us
  IT People
  Website Grade
  Learn English
  Donate Money
  Comrades
  Free Dictionary
  VaaHOO

www.e-comwebsite.blogspot.com
Search for
 
1.2 SQL Standards:
 
SQL, Structured Query Language, is a database query language that was adopted as an industry standard in 1986.   A major revision to the SQL Standard was completed in 1992 and standardized by ANSI and is called SQL-92 (also referred to as SQL2).    Since SQL-92 is the latest standard available we will be following the same during our product development.  
 
 
1.3 ODBC / JDBC Drivers:
 
Accessing the database from the Java application can be done through Java Database Connectivity (JDBC). It consists of a set of classes and interfaces written in the Java programming language.   JDBC provides a standard API for tool/database developers and makes it possible to write database applications using a pure Java API.
 
Some of the database vendors provide a pure Java JDBC driver for connecting to the database. Oracle provides a pure JDBC driver called the “Thin JDBC” driver. This driver is completely written in Java and is fully compliant with the JDBC standard. On the other hand, if no native driver is available then the other option would be to connect to the database using the JDBC-ODBC bridge. JavaSoft provides this bridge which in turn makes calls to the ODBC driver.
 
ODBC defines a method of connecting to data sources that is open to as many applications and data sources as possible. ODBC defines its own set of SQL-grammar conformance levels. The three levels of ODBC SQL are minimum, core and extended.   If the database driver supports a particular conformance level then it means that the driver supports the data types and SQL grammar as defined for that level.
 
The ODBC drivers provided by the database vendors belong to different conformance levels.   What conformance level the application should support becomes a major point for consideration. The database vendor claims that the driver supports a particular level of conformance. It has been observed that the driver would also be supporting some of the features of the next level.   The list of features of the next level that are supported by the driver is not documented.
 
Due to these considerations it is best to stick to the Core SQL Grammar as this is what almost all database drivers support.   Let us now look into the data types and the SQL Grammar supported by the core SQL grammar.
 
 
1.4 Data Types :
 
For developing a database independent product it is best to use the following data types only as they are available across all standard databases.
 
·         Float
·         Integer
·         Varchar
 
In addition to the above, data types like LONG in Oracle and TEXT in SQL Server could be used for storing large data. 
 
The data types mentioned above are supported by all databases and hence can be used.    However there are quite a few data types which are specific to a given database and hence should not be used at all.   One example is the Date data type. Oracle calls it as Date and stores both Date and Time. Microsoft SQL Server on the other hand has a data type called DateTime and it also stores both Date and Time.   Since Date is not supported by the Core SQL grammar most of the drivers don’t support date. It is, therefore, best not to use such data types.
 
However there are quite a few data types which are specific to a particular database but have the JDBC equivalent data type.   Such data types can be used because the database specific data types can be used for creating tables and the equivalent JDBC data type can be used for getting and setting values from the database.   The following list displays the JDBC type name corresponding to the database specific data type for two databases – Oracle & Microsoft SQL Server.
 
 
JDBC Type Name
Oracle 8.0.4
Microsoft SQL Server 6.5
Bit
Number
Bit
Char
Char
Char
Float
Float
Float
Integer
Integer
Integer
Numeric
Number
Numeric
Real
Real
Real
Smallint
Number
Smallint
Varchar
Varchar2
Varchar
Longvarchar
Long Varchar
Text
Tinyint
Number
Tinyint
Varbinary
Raw
Varbinary
Date
Date
-
Time
Date
-
Timestamp
Date
DateTime
 
 
From the above table, it is clear that the data types in the green area can be used straight away without any problems since all databases support these data types.
The data types in the grey area could be used in the application even though they are not standard across databases (these data types are a part of the extended grammar support). This is because the database specific types like Long Varchar (Oracle) and Text (Microsoft SQL Server)could be used while creating tables and its equivalent JDBC data type Longvarchar could be used in the application for getting and setting values in the database. The data types in the red area like Date, Time and Timestamp should not be used at all. One major disadvantage with this is that we cannot be using the Date data type that we commonly come across like any other data type. 
 
We need to have a work around for storing dates without using the Date data type. One work around which can be used is using string for storing dates.  It can be stored in the format ‘YYYYMMDDHHMMSS’.    We can then have common utility classes that have functions to convert date to string and vice versa.   We could also have functions for displaying date in various formats.     The advantage in using this format for storing is that it becomes very handy when selecting rows from the database for a particular condition.
 
For example, If we want to select rows from the table where date is the current year then a statement like “SELECT DATE FROM TABLE WHERE DATE > ‘1999’ “ will retrieve all the rows for that year even though the date is stored in the format ‘19990530101010’.    Therefore it is not necessary to give the entire string in the select clause.
 
The other work around which can be used is using float for storing dates.   We can have a common date utility class that fetches the float from the database and converts it to Java Date. Similarly we can have another date class that converts the Java date to float and stores it in the database.
 
 
1.5   SQL Grammar :
 
The Core SQL Grammar supports the following Statements :
 
 
Data Manipulation Language Statements
 
·         SELECT
·         INSERT
·         DELETE
·         COMMIT TRANSACTION
·         ROLLBACK TRANSACTION
 
 
Data Definition Language / Data Control Language Statements
 
·         CREATE TABLE
·         DROP TABLE
·         ALTER TABLE
·         CREATE INDEX
·         DROP INDEX
·         CREATE VIEW
·         DROP VIEW
·         GRANT
·         REVOKE
 
 
 
The syntax for these statements is given below:
 
SELECT Statement :  
 
Every application requires data to be fetched from the database and the SELECT statement is the most commonly used SQL statement. There are six possible clauses that can be used in a SELECT statement along with the following predicates :
 
FROM, WHERE, GROUP BY, HAVING, ORDER BY
 
 
The syntax to be followed for each of these predicates is as follows :
 
SELECT <Columns> FROM <Table> ;
 
SELECT <Columns> FROM <Table> WHERE <Condition> ;
 
SELECT <Columns> FROM <Table> WHERE <Condition> GROUP BY <Columns> ;
 
SELECT <Columns> FROM <Table> WHERE <Condition> GROUP BY <Column> HAVING <Condition> ;
 
SELECT <Columns> FROM <Table> WHERE <Condition> ORDER BY <Condition> ;
 
 
Here are some of the DO’s and DON’T’s while writing SQL Statements:
 
(1) String Delimiter:
 
The string delimiter is used in SQL to let the DBMS know the beginning and end of a value that contains string, text or character data.   Use SINGLE QUOTES as your string delimiter.
 
Example:  SELECT * from customers where lname = ‘Joe’ ;
 
 
(2) How to handle single quotes in your data
 
If you accept that a single quote is your string delimiter, you should determine how you will handle single quotes contained within your user data. Use TWO SINGLE QUOTES to tell the DBMS to look for one single quote.
 
Example:  SELECT * from customers where city =’Joe’’s Home” ;
 
 
(3) Placing quotes around numeric data
 
Do not put quotes around numeric data. Quotes are not allowed around numeric data. Only some of the databases allow you to put quotes around string and numeric data.
 
Example:  SELECT * from customers where ID = ‘15’ ;
 
 
(4) AS clause
 
The AS clause is often used in two places, for substituting column names and for substituting table names.   While the AS clause is supported by all DBMS for column names, it is not for table names. DO NOT USE THE AS CLAUSE FOR TABLE ALIASES, DO USE THE AS CLAUSE FOR COLUMN ALIASES.
 
 
Example:  SELECT lname AS LastName from customers where ID = 51 ;
 
 
 
(5) Concatenation Operand
 
Avoid concatenating in SQL. You may want to combine the data in multiple table columns to a single column in your resultset.   This is done by concatenating the columns in the SQL. This is however not supported by most databases.   The solution for this is to concatenate the columns in the calling program after receiving the resultset whenever possible.
 
Example:  SELECT lname || ‘,’ || Fname from customers where ID = 15;
 
 
 
(6) Substring functions
 
Avoid obtaining substrings in SQL. Most Databases offer a function to allow you to extract a range of characters from a string. Unfortunately, the command for doing so varies from database to database.   All the functions here take 3 parameters: starting point, number of characters, column name.
 
Example:  SELECT substring(4,2,lname) from customers where ID = 41;
 
 
 
(7) Trim functions
 
Avoid trimming strings in SQL.   Most databases offer a function to allow you to trim spaces from a string. Unfortunately, the command for doing this varies across databases.
 
Example:  SELECT trim(lname) from customers where ID = 29;
 
 
(8) Convert to string function
 
Avoid convert to string functions in SQL. Most databases offer a function to allow you to convert a number to string. But this again is different across databases.
 
Example:  SELECT str(emp_no) from employee where ID = 12;
 
 
(9) Symbol for Not Greater Than and Not Equal To
 
Use <= for “Not Greater Than” and <> for “Not Equal To”. 
 
Example:  SELECT * from customers where ID <=41 and ID <> 15;
 
 
(10)    Symbols for NULL Testing
 
Use IS NULL and IS NOT NULL for comparing with NULL Values.
 
Example:  SELECT * from customers where Fname is NULL;
 
 
Note:
 
·         Do not use statements like “SELECT * FROM <Table> INTO <Variable>"            This is because JDBC does not support INTO clause. It is also a good practice to give all the column names to be selected in the SELECT statement. SELECT * works with the Oracle thin driver but does not work with Microsoft driver for SQL Server. It is best not to use SELECT * but to specifically give all the column names to be selected.
 
·         Outer Joins should not be used in the SELECT statement mentioned above. This is because outer joins are a part of the extended grammar and is therefore not supported by most drivers.
 
 
INSERT Statement :
 
INSERT INTO <Table> (<Col1>, <Col2>,<Coln>) VALUES(<Val1>,<Val2>,<Valn>) ;
 
Note:
 
·         It is always a good practice to mention the column names along with the values when using an insert statement.   This is very useful when the table structure is modified.
 
 
 
UPDATE Statement :
 
UPDATE <Table> SET <Col1> = <Val1> , <Coln> = <Valn> WHERE <Condition> ;
 
 
DELETE Statement :
 
DELETE FROM <Table> WHERE <Condition> ;
 
 
CREATE TABLE Statement :
 
CREATE TABLE (<Table>(<Col1> <Type1>, <Col2> <Type2>, <Coln> <Typen>, PRIMARY KEY (<Columns>),   FOREIGN KEY (<Col>) REFERNCES <REFERENCED TABLE> (<REFERENCED COLUMN), CONSTRAINT <CONSTRAINT_NAME> CHECK (CONDITION) ) ;   
 
 
DROP TABLE Statement :       
 
DROP TABLE <TABLENAME> ;
 
 
CREATE VIEW Statement :
 
CREATE VIEW <VIEW NAME> AS <SELECT STATEMENT> ;
 
 
DROP VIEW Statement :
           
DROP VIEW <VIEW NAME> ;
 
 
Note:
 
·         Do not use Stored Procedures, Batch Statements and Triggers as these are all supported by the extended grammar only. The work around for this is to code the Stored Procedures and Triggers in the application logic itself.
·         Use business objects to enforce cascade deletes. Many databases allow to create a CASCADE DELETE via a Constraint, or a Trigger. It is recommended to use Constraint and not a Trigger.
 
 
 
 
1.6 JDBC Specific Information:
 
Simply put, JDBC makes it possible to do three things :
 
·         Establish a connection with a database
·         Send SQL Statements
·         Process the results.
 
1.6.1 Establishing Connection with the database
 
A connection object represents a connection with a database. Establishing connection with the database involves two steps – loading the driver and making the connection.
 
(1) Loading the driver is very simple and the following line of code needs to be used
 
Class.forName(“jdbc.DriverName”) ;
 
Where “DriverName” is the name of the driver to be loaded. The driver documentation will give the class name to use. 
 
Example
For connecting to the Oracle database using the Oracle thin driver the following line of code has to be used
 
Class.forName("oracle.jdbc.driver.OracleDriver");
 
For connecting to the Microsoft SQL Server using the JDBC-ODBC Bridge driver the following code line of code has to be used
 
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 
 
 
(2)    The next step is to have the driver connect to the Database. The following line of code needs to be used
 
Connection myConnection = DriverManager.getConnection(url, “myLogin”, “myPassword”) ;
 
 
 
Example:
 
For connecting to the Oracle database using the Oracle thin driver, the following line of code needs to be used
 
Connection myConnection =
DriverManager.getConnection("jdbc:oracle:thin:@hsssun01.hss.hns.com:1521:HSSB","secondlook","secondlook");
 
where “hsssun01.hss.hns.com” is the machine on which the Oracle database is running and HSSB the name of the Oracle instance.   The login and password for the database user is “secondlook”.
 
For connecting to the Microsoft SQL Server using the JDBC-ODBC Bridge driver, the following line of code needs to be used
 
Connection myConnection = DriverManager.getConnection("jdbc:odbc:slookdb","secondlook","secondlook");
 
where “slookdb” is the data source name. The data source needs to be created using the ODBC administration in the desktop.   The login and password for the database user is “secondlook”.
 
 
1.6.2 Sending SQL Statements
 
Once a connection is established, it is used to pass SQL statements to its underlying database.   A statement object is used to send SQL statements to a database. There are different kinds of statement objects, all of which act as containers for executing SQL statements.
 
SQL statements to be sent to the database could belong to the following categories :
 
·         Simple SQL statements (no parameters)
·         SQL statements with one or more parameters
·         Simple SQL statements that are executed frequently
 
The “Statement” interface provides the basic methods for executing and retrieving results. The “PreparedStatement” interface is used for executing SQL statements with parameters and SQL statements that are executed frequently.
 
 
1.6.3 Creating Statement Objects
 
A Statement object is created with the Connection method createStatement, as in the following line of code:
 
Statement myStatement = myConnection.createStatement() ;
 
where myConnection is the Connection object.

Locations of visitors to this page





Add comment to this page:
Your Name:
Your Email address:
Your website URL:
Your message:

   

=> Do you also want a homepage for free? Then click here! <=