5.6 Retrieving Data

     

A typical database program retrieves data from a database server and processes it. These programs will execute SELECT statements following a process similar to that described in the previous section. The difference between executing a SELECT statement as opposed to a statement that does not return results is that with SELECT you must execute additional code to process the results returned by SELECT .

5.6.1 Retrieving Data Using ADO.NET

The following C# code fragment executes a SQL SELECT statement that returns author names from the authors table and then iterates through the results one row at a time, printing out each author's name . The code is explained in the detailed steps that follow:

 {OdbcOleDbSql}Command   statement   =   connection   .CreateCommand( );   statement   .CommandText = "SELECT au_fname, au_lname FROM authors"; {OdbcOleDbSql}DataReader   resultSet   =   statement   .ExecuteReader( ); while(   resultSet   .Read( ) ) {    String fname = "NULL";    String lname = "NULL";    if( !   resultSet   .IsDBNull( 0 ) ) fname =   resultSet   .GetString( 0 );    if( !   resultSet   .IsDBNull( 1 ) ) lname =   resultSet   .GetString( 1 );    System.Console.WriteLine( lname + ", " + fname ); }   resultSet   .Close( );   statement   .Close( ); 

Column ordinals are all zero-based (the first column is 0, the second is 1, etc.) in ADO.NET, which is different from the one-based ordinals used by JDBC.


To execute a SQL query and process the results using ADO.NET, take the following steps:

  1. Create the Command object that will be used to execute the SELECT statement and attach a SELECT statement to it:

     {OdbcOleDbSql}Command statement = connection.CreateCommand( ); statement.CommandText = "SELECT au_fname, au_lname FROM AUTHORS"; 

  2. Invoke the ExecuteReader method on the Command object, creating a new DataReader object.

     {OdbcOleDbSql}DataReader resultSet = statement.ExecuteReader( ); 

  3. Iterate through each row in the result set. After execution of the SELECT statement, retrieve rows using the DataReader 's Read method. If you expect a multiple row result, you should invoke the Read method from within a while loop:

     while( resultSet.Read( ) ) { 

  4. Having fetched a row of data into the DataReader object, the column data can be extracted using the DataReader 's Get methods . Before calling the Get methods, we check to see if the data is NULL using the DataReader 's IsDBNull method. If the value returned by the IsDBNull method is true, then the value of the string will be NULL. There are many Get methods for each type of column. Using the appropriate datatype for the column is important, because a conversion is not always possible. For a list of available Get methods and the ANSI SQL types they should be used with, please see Table 5-4.

     String fname = "NULL";    String lname = "NULL";    if( !resultSet.IsDBNull( 0 ) ) fname = resultSet.GetString( 0 );    if( !resultSet.IsDBNull( 1 ) ) lname = resultSet.GetString( 1 ); 

    When creating an ADO.NET program that fetches data that can contain NULL values, it is always safest to check the return value of the DataReader object's IsDBNull method prior to extracting a value.

  5. The Read method of DataReader returns false when all the data has been read from the database server. At that point, the Close methods on the Command object and DataReader should be invoked to free up the resources used internally to process the statement.

 resultSet.Close( ); statement.Close( ); 

Table 5-4. ADO.NET DataReader Get methods

Method name

Description

GetBoolean(int i )

Returns the value from the i th column as a Boolean value, where i is the zero-based column number.

GetByte(int i )

Returns the value from the i th column as a single-byte value, where i is the zero-based column number. No conversion will be done if the data in the column exceeds one byte and an InvalidCastException object will be thrown.

GetBytes(int i , long dataIndex , byte[] buffer , int bufferIndex , int length )

Returns the value from the i th column as a binary value, where i is the zero-based column number, dataIndex is the offset in the column value to start reading, buffer is the byte array to copy the data into, bufferIndex is the offset into buffer at which to start copying data, and length is the maximum length to copy into buffer .

GetChar(int i )

Returns a single char value from the i th column. For use with character type columns , where i is the zero-based column number.

GetChars(int i , long dataIndex , char[] buffer , int bufferIndex , int length )

Returns a string of characters from the i th column. For use with character type columns, where i is the zero-based column number, dataIndex is the offset in the column value to start reading, buffer is the char array to copy the data into, bufferIndex is the offset into buffer at which to start copying data, and length is the maximum length to copy into buffer .

GetDataTypeName(int i )

Gets a String value that contains the name of the column's datatype, where i is the zero-based column number.

GetDateTime(int i )

Returns a DateTime value from the i th column. For use with temporal type columns, where i is the zero-based column number.

GetDecimal(int i )

Returns a single Decimal value from the i th column. For use with numeric type columns, where i is the zero-based column number.

GetDouble(int i )

Returns a single double value from the i th column. For use with double precision type columns, where i is the zero-based column number.

GetFloat(int i )

Returns a single float value from the i th column. For use with floating-point columns, where i is the zero-based column number.

GetInt{16,32,64}(int i )

Retrieves data from integer columns. The precision of the return value is encoded within the function names. Use GetInt16 for a 16-bit signed short integer, GetInt32 for a 32-bit signed int , and GetInt64 for a 64-bit signed long .

GetName(int i )

Returns a String value containing the name of the i th column, where i is the zero-based column number.

GetOrdinal(string name )

Returns an int value containing the ordinal value of the column with name matching the name argument.

GetString(int i )

Returns a single String value from the i th column. For use with character type columns, where i is the zero-based column number.


Beyond the Get methods, there are three other methods on the DataReader type that are frequently used when processing data from a query. Those three methods are listed in Table 5-5.

Table 5-5. Frequently used ADO.NET DataReader methods

Method name

Description

Close( )

Closes the DataReader object, freeing up resources held by the instance.

IsDBNull(int i )

Returns true if the specified column is NULL, otherwise it returns false, where i is the zero-based column number.

Read( )

Fetch the next row if one is available, and return true, otherwise return false.


5.6.2 Retrieving Data Using JDBC

The following Java code fragment executes a SQL SELECT statement that returns author names from the authors table and then iterates through the results one row at a time, printing out each author's name:

 java.sql.Statement   statement   =   connection   .createStatement( ); java.sql.ResultSet   result   =   statement   .executeQuery("SELECT au_fname, au_lname FROM authors" ); while(   result   .next( ) ) {    String fname =   result   .getString( 1 );    if(   result   .wasNull( ) ) fname = "NULL";    String lname =   result   .getString( 2 );    if(   result   .wasNull( ) ) lname = "NULL";    System.out.println( lname + ", " + fname ); }   result   .close( );   statement   .close( ); 

Column ordinals are all one-based (first column is 1, second is 2, etc.) in JDBC, which is different from the zero-based ordinals used by ADO.NET.


5.6.2.1 Use the following steps to execute query statements in JDBC:
  1. Create a JDBC Statement object by invoking the createStatement method on a valid Connection object:

     java.sql.Statement   statement   =   connection   .createStatement( ); 

  2. The query is executed by invoking one of the execute methods on the Statement object. Result sets from query statements are processed by JDBC ResultSet objects, which are returned from the executeQuery method of JDBC Statement objects.

     java.sql.ResultSet result =     statement.executeQuery("SELECT au_fname, au_lname FROM authors" ); 

  3. After creating a ResultSet object, you can iterate through one row at a time by invoking the next method. The next method returns a Boolean value, true for each row in a result set and false after all rows have been iterated through. It is common to invoke the next method within a while loop to process the rows one-by-one. The ResultSet does not begin on the first row of the result, so you must invoke the next method to advance to the first row before calling any of the get methods.

     while( result.next( ) ) { 

  4. To retrieve column data from the rows within a result set, invoke the appropriate get method on the ResultSet object. For a list of the most common get methods, check Table 5-6. Note that the column data is checked for a NULL value after the get method has been invoked, since the nullness of a value can't be determined from the value returned by the get methods.

     String fname = result.getString("au_fname");    if( result.wasNull( ) ) fname = "NULL";    String lname = result.getString("au_lname");    if( result.wasNull( ) ) lname = "NULL"; 

    When creating a JDBC program that fetches data from nullable columns, it is always safest to check the value returned from a ResultSet object for a NULL value using the wasNull method.

    The JDBC get methods that return object types will return Java NULL values when returning database NULL values. However, this does not apply to non-object types such as getInt() , which returns zero in the case of a database NULL. For this reason, this chapter uses the verbose wasNull() method to test the value for a NULL.


  5. Free the resources held by the result set and statement objects. When finished with the ResultSet and Statement objects, invoke their close methods so that database resources can be freed.

 result.close( ); statement.close( ); 

Table 5-6. JDBC ResultSet get methods

Method name

Description

getBlob({int i String name })

Retrieves a Blob value from BLOB type columns, where i is the one-based column ordinal and name is the name of the column.

getBoolean({int i String name })

Retrieves a boolean value from BOOLEAN type columns, where i is the one-based column ordinal and name is the name of the column.

getByte({int i String name })

Retrieves a byte value from CHARACTER or BINARY type columns, where i is the one-based column ordinal and name is the name of the column.

getBytes({int i String name })

Retrieves a byte[] value from BINARY type columns, where i is the one-based column ordinal and name is the name of the column.

getClob({int i String name })

Retrieves a Clob value from CLOB type columns, where i is the one-based column ordinal and name is the name of the column.

getDate({int i String name })

Retrieves a Date value from TEMPORAL type columns, where i is the one-based column ordinal and name is the name of the column.

getDouble({int i String name })

Retrieves a double value from DOUBLE PRECISION type columns, where i is the one-based column ordinal and name is the name of the column.

getFloat({int i String name })

Retrieves a float value from REAL type columns, where i is the one-based column ordinal and name is the name of the column.

getInt({int i String name })

Retrieves an int value from INTEGER type columns, where i is the one-based column ordinal and name is the name of the column.

getLong({int i String name })

Retrieves a long value from INTEGER type columns, where i is the one-based column ordinal and name is the name of the column.

getRow( )

Returns the current row number.

getShort({int i String name })

Retrieves a short value from INTEGER type columns, where i is the one-based column ordinal and name is the name of the column.

getString({int iString name})

Retrieves a String value from CHARACTER type columns, where i is the one-based column ordinal and name is the name of the column.

getTime({int i String name })

Retrieves a Time value from TEMPORAL type columns, where i is the one-based column ordinal and name is the name of the column.

getTimestamp({int i String name })

Retrieves a Timestamp value from TEMPORAL type columns, where i is the one-based column ordinal and name is the name of the column.


Beyond the get methods, there are three other methods on the ResultSet type that are frequently used when processing data from a query. Those three methods are listed in Table 5-7.

Table 5-7. Frequently used JDBC ResultSet methods

Method name

Description

close( )

Closes the ResultSet object, freeing up resources held by the instance.

next( )

Advances the ResultSet object to the next available row and returns true. If no rows remain false will be returned.

wasNull( )

Returns true if the last column returned with a get method contained a database NULL value, returns false otherwise.




SQL in a Nutshell
SQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596518846
EAN: 2147483647
Year: 2003
Pages: 78

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net