www.fingertip.page.tl or www.fingertip.us.tt - DB-Page2
   
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.6.4 Executing Statements using Statement Objects

 

 

Method
Statements Executed
ExecuteQuery
SELECT
ExecuteUpdate
INSERT, UPDATE, DELETE
 
 
CREATE TABLE, DROP TABLE.

 

Example:

 

Statement artifactStatement = myConnection.createStatement();   

ResultSet artifactResultSet = artifactStatement.executeQuery("select artifactname from artifacts ");

 

 

Note:

 

·         It is recommended as good programming practice that all statement objects be closed explicitly when they are no longer needed. This frees DBMS resources immediately and helps avoid potential memory problems.   It is best to create a separate Statement object for each SQL statement to be processed.

 

1.6.5 Executing Prepared Statements

 

 

The PreparedStatement Object can be created using the following piece of code:

 

PreparedStatement artifactPreparedStatement = myConnection.prepareStatement(<SelectStatement>) ;

 

where myConnection is the Connection object.

 

Example:

 

 String p_SubmittedBy ;

 

 PreparedStatement artifactPreparedStatement = myConnection .prepareStatement("    

 Update Artifacts Set SubmittedBy = ? where artifactid = '6871' " ) ;  

 

 artifactPreparedStatement.setDate(1,p_SubmittedBy);                             

 

 artifactPreparedStatement.executeUpdate() ;

 

In the above example, the value is passed to the PreparedStatement using the setXXX() method.   The appropriate method should be used depending upon the data type of the column in the database. Using getInt() while fetching a String from the database will result in an error. It is always a good practice to check with the data types of the column in the database and use the appropriate method.

 

 

1.6.6 ResultSet

 

ResultSet contains all the rows which satisfied the conditions in an SQL statement, and it provides access to the data in these rows through a set of “get” methods that allow access to the various columns of the current row. The “ResultSet.next” method is used to move to the next row of the ResultSet, making the next row become the current row.

 

Example:

 

String l_artifactname ;

 

Statement myStatement = myConnection.createStatement() ;

 

ResultSet artifactResultSet   = myStatement.executeQuery(" select artifactid, artifactname from artifacts ");

 

 while(artifactResultSet.next()) {

                   l_aritifactid = artifactResultSet.getInt(1) ;

      l_artifactname = artifactResultSet.getString(2) ;

            }

 

In the above example, the value is fetched from the resultset using the getXXX() method.   The appropriate method should be used depending upon the data type of the column in the database. 

 

The columns should be fetched in the correct order from the resultset using the getXXX() method. In the above example first artifactid column has to be fetched and only then artifactname column should be fetched.   The order of fetching is immaterial when working with Oracle thin driver but is very important when working with Microsoft SQLServer driver.   In order to have the same code running for both the drivers it is best to fetch the columns in the same order as given in the select statement.

 

Setting and getting values for LONG datatype of Oracle database can be done by setString() and getString() functions. But the same does not work fine when working with TEXT datatype of SQLServer database.    In order to have the same code working for both the Oracle driver and the SQLServer driver the following logic can be used when fetching values for LONG or TEXT datatype:

 

    Replace Statement.setString (2, str) call with following lines of code:

 

StringBufferInputStream sbi = new StringBufferInputStream(str); Statement.setAsciiStream(2,sbi, str.length());

 

    Replace String str = Statement.getString("name") with following lines of code:

 

InputStream in = Statement.getAsciiStream("name");

BufferedReader dis = new BufferedReader (in);

While((temp = dis.readLine()) != null) str = str + temp;

 

 

Note:

 

·         It is recommended that all ResultSet objects be closed explicitly when they are no longer needed. This is all the more important when they are accessed in loops. If this is not done then the error “Too many Cursors Open” is encountered. It is best to create a separate ResultSet object for each SQL statement to be processed.

·         It is recommended to have only one resultset open at any given time. It is very common to have the need for multiple resultsets open while developing applications. In such cases we would be forced to use one resultset inside the while of the first resultset when both the resultsets are open. This works fine with the Oracle thin driver but does not work when working with the Microsoft SQL Server driver. The solution to this is to use a join and use only one resultset. The other alternative is to first store the results of the first resultset in array or vector and close the resultset. The values stored in the array or vector can then be used in the second resultset.

 

1.6.7 Commit

 

To make permanent all the changes made to the database the Commit Statement has to be executed.     This can be done using the following line of code

 

myConnection.commit() ;

 

where myConnection is the connection object.

 

1.6.8 Close

 

It is always a good practice to close a Connection object as and when it is used and is no longer needed. The recommended programming style is to explicitly close any Connection objects when they are no longer need, this releases DBMS resources as soon as possible.   This can be done using the following line of code

 

myConnection.close() ;

 

where myConnection is the connection object.

 

 

1.6.9 Querying Database Specific Information

 

JDBC provides the DatabaseMetaData interface that provides specific information about a database. Information specific to a database like the data types supported, database version and so on can be found using the MetaData interface. 

 

This has been found very useful while coding scripts for creating tables. As discussed in section 1.4 there are quite a few data types that are specific to the database like Long Varchar in Oracle and Text in Microsoft SQL Server.   We can use these data types in the application and use the JDBC equivalent for these data types while fetching and setting values in the database. However we will end up having separate scripts for creating tables for each of the database. It is in such cases that the DatabaseMetaData interface is very handy and can be used to query the database and the tables can be created based on the return values.  

 

 

 

1.6.10 Guidelines for naming Tables & Columns

 

·         Give meaningful names to Tables and it should closely relate to the data being stored in the table.

·         It is a good practice to give full name to table like Employee, Invoice, Department instead of using short names like Emp, Inv, Dept and so on.

·         Do not give spaces in between words if the table name is made up of two words like InvoiceDetails, EmployeeDetails and so on.

·         It is better to avoid symbols like “_” in the table names like Invoice_Details, Employee_Details and so on. This is also the case with column names, it is better to avoid names like Inv_No, Date_Of_Birth and so on.  It is just that remembering them becomes difficult.

·         The names of VIEWS should start with V_<Name>.   Views are based on joins on two or more tables.   Names should actually reflect on the tables used in the join condition.   For example, if there is VIEW based on the EMPLOYEE table then the name should be named as V_Employee.

·         Incase you are using some temporary tables then their names should start with T_<name>.

·         Only use the underscore and alphanumerics in table and column names. Start all table and column names with an alphabetic letter.

 

 

1.6.11 Debugging and Tracing

 

·         It is best to code each SELECT statement in a separate “Try” block. Code appropriate error message in the corresponding “Catch” block. You could always put all the SELECT statements in one single “Try” block but the disadvantage with this is that in case of errors it will be difficult to point out which SELECT statement is causing the error condition.

·         It is also a good practice to trap the database specific errors along with your own error message. This helps in pointing to the exact reason for the error. This can be done by printing <Errorname>.toString() in the exception.

 

Example:

 

try{

            String l_artifactname ;

 

Statement myStatement = myConnection.createStatement() ;

 

ResultSet artifactResultSet   = myStatement.executeQuery("select artifactname from artifacts ");

 

 while(artifactResultSet.next()) {

             l_artifactname = artifactResultSet.getString(1) ;

            }

}

catch (Exception myError)

         {

          System.out.println(myError.toString());

          System.out.println("Error connecting to database. Please inform the administrator.") ;

          return ;

         }

 

·         Errors while processing SQL statements generally will be due to very simple mistakes like a missing comma between column names, no space between column names and so on.   In such cases it is best to copy the statement and try the same statement in a tool like SQL*PLUS (Oracle) or ISQL (Microsoft SQL Server). It is easy to trap such mistakes while working with these tools.

 

 

1.6.12 Do’s and Don’ts while using JDBC

 

·         Execute each SQL statement in a separate try block and give appropriate error messages in the exceptions. These helps you point to the exact block incase of errors, especially if you are using nested loops.

·         For SQL statements that are executed again and again, use PreparedStatements as they are precompiled and executed faster.

·         For processing each SQL statement use a separate Statement object and ResultSet.

·         Close all ResultSets and Statements after use. If you don’t do this you will end up getting errors like “Too many cursors open”.

·         Make sure that the type you try to retrieve matches the type in the database, or else you may get a poorly documented exception like ‘ISO 1920’ which gives no clue to the error. This commonly happens when a getInt() is used instead of a getString()

·         Similar care should be taken whilst setting parameters in a PreparedStatement.

·         If executing a Statement in a loop, make it a prepared statement and initialize it before the loop. Close it once the loop is over.

·         All string values should be enclosed in single quotes. If you use double quotes or no quote at all, the database will assume it’s a column name and not a value and will throw an exception.

 

APPENDIX A: JDBC Types Mapped to Database-specific SQL Types

 

 

JDBC Type Name
Oracle 7.2
Sybase 11.0
Informix 7.12
IBM DB2 2.1
Microsoft SQL Server 6.5
Microsoft Access 7.0
Sybase SQL Anywhere 5.5
BIT
 
BIT
 
 
BIT
BIT
BIT
TINYINT
 
TINYINT
 
 
TINYINT
BYTE
TINYINT
SMALLINT
SMALLINT
SMALLINT
SMALLINT
SMALLINT
SMALLINT
SMALLINT
SMALLINT
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
INTEGER
BIGINT
 
 
 
 
 
 
 
REAL
REAL
REAL
REAL
 
REAL
REAL
REAL
FLOAT
FLOAT
FLOAT
FLOAT
FLOAT
FLOAT
FLOAT
FLOAT
DOUBLE
DOUBLE PRECISION
DOUBLE PRECISION
DOUBLE PRECISION
DOUBLE PRECISION
DOUBLE PRECISION
DOUBLE
DOUBLE PRECISION
NUMBERIC(p,s)
NUMBERIC(p,s)
NUMBERIC(p,s)
NUMBERIC(p,s)
NUMBERIC(p,s)
NUMBERIC(p,s)
 
NUMBERIC(p,s)
DECIMAL(p,s)
DECIMAL(p,s)
DECIMAL(p,s)
DECIMAL(p,s)
DECIMAL(p,s)
DECIMAL(p,s)
 
DECIMAL(p,s)
CHAR(n)
CHAR(n)
n<=255
CHAR(n)
n<=255
CHAR(n)
n<=32,767
CHAR(n)
n<=254
CHAR(n)
n<=255
CHAR(n)
n<=255
CHAR(n)
n<=32,767
VARCHAR(n)
VARCHAR(n)
n<=2000
VARCHAR(n) n<=255
VARCHAR(n) n<=255
VARCHAR(n)
n<4000
VARCHAR(n)
n<=255
VARCHAR(n)
n<=255
VARCHAR(n)
n<=32,767
LONGVARCHAR
LONG VARCHAR
Limit is 2 Gigabytes           
TEXT
Limit is 2 Gigabytes
TEXT
Limit is 2 Gigabytes
LONG VARCHAR
Limit is 32,700 bytes
TEXT
Limit is 2 Gigabytes
LONGTEXT
Limit is 1.2 Gigabytes
LONG VARCHAR
Limit is 2 Gigabytes
BINARY(n)
 
BINARY(n)
n<=255
 
CHAR(n) FOR BIT DATA
n<=254
BINARY(n)
n<=255
BINARY(n)
n<=255
BINARY
n<=32,767
VARBINARY
RAW(n)
n<=255
VARBINARY(n)
n<=255
 
VARCHAR(n) FOR BIT DATA
n<=4000
VARBINARY(n)
n<=255
VARBINARY(n)
n<=255
 
LONGVARBINARY
LONG RAW
Limit is 2 Gigabytes
IMAGE
Limit is 2 Gigabytes
BYTE
Limit is 2 Gigabytes
LONG VARCHAR FOR BIT DATA
Limit is 32,700 bytes
 IMAGE
Limit is 2 Gigabytes
LONGBINARY
Limit is 1.2 Gigabytes
IMAGE
Limit is 2 Gigabytes
DATE
DATE
DATETIME
DATE
DATE
 
DATE
DATE
TIME
DATE
DATETIME
 
TIME
 
TIME
TIME
TIMESTAMP
 
 
 
TIMESTAMP
DATETIME
 
TIMESTAMP

 

 

Appendix B: DBMS Limitations

You also need to be aware of the limitations of each DBMS. Take particular note of the maximum length of column and table names, because if you use a 34 character table name on one DBMS, the name may be too long for the other DBMS. Along with that, try very hard to avoid any table or column names that could be a keyword on any DBMS.

Limitations
Recommendation
Access 97
Microsoft SQL Server 6.5
Oracle 8.04
DB2 7.2
Informix 7.2
Column name lengths
Maximum of 14
64
30
?
18
18
Number of fields in a table
 
255
250 (1024 in 7.0)
1000
500
 
Number of characters in a record (excluding Memo and OLE Object fields)
 
2000
1962 (8060 in SQL 7.0)
 
4005
 
Table size
 
1 gigabyte
1 terabyte (1,000,000 TB 7)
 
64 * 999 gigabytes
 
Number of characters in a Text field
 
255
255 (8000 in SQL 7.0)
char = 2k, varchar = 4k
char=254 varchar=4k longvarchar=32k
255
Number of characters in a Memo field
 
65,535 or 1 gigabyte
2 gigabytes
4 gigabytes
2 gigabytes
2 gigabytes
Number of indexes in a table
 
32
250
 
32767
 
Number of fields in an index
 
10
16
 
16
 
Longest SQL Stmt
2k - use insert followed by update for more
64k
128k
64k
32767
 
Most columns in select list
 
255
4096
250?
500
 

 

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! <=