Data Truncation

When JDBC unexpectedly truncates data on a read, a data truncation warning is reported. When it occurs on a write, a data truncation exception is thrown. In both cases, the SQLstate is set to “01004.”

A set of methods is available to discover what happened.

DataTruncation

int getDataSize();
int getTransferSize();
int getIndex();
boolean getParameter();
boolean getRead();

int getDataSize();

This method returns the number of bytes that should have been transferred. It returns -1 if the size is unknown. The size may be an approximation if data conversions occurred.

int getTransferSize();

This method returns the number of bytes actually transferred. A -1 means that the size is unknown.

int getIndex();

This method gets the index of the column or parameter that was truncated. A -1 means that the index is unknown, in which case the next two methods should be ignored.

boolean getParameter();

This returns true if the value truncated was passed through a statement’s parameter. It returns false if it was returned by a column.

boolean getRead();

This returns true if the data truncation occurred on a database read. It returns false if the data was truncated on a write. Listing 5-30 shows how to catch a data truncation exception.

Listing 5-30: How to catch a data truncation exception.

...
...
  {
       try
       {
               // initiate a connection,
               // then execute a statement
               // ...
       }
       catch(DataTruncation ex)
       {
               System.out.println(“\n*** DataTruncationexception caught ***\n”);
                       int idx = ex.getIndex();
               System.out.print(“Index: “ + idx);
               if (idx != -1)
               {
                       if (ex.getParameter())
                       {
                                  // the truncation happened
                                  // in a parameter
                                  System.out.print(“ of the set
                                          of parameters”);
                       }
                       else
                       {
                                  // the truncation happened
                                  // on a resultset column
                                  System.out.print(“ in the resultset”);
                       }
               }
               if (ex.getRead())
               {
                       // the truncation happened
                       // on a read
                       System.out.println(“ was truncated on a read”);
               }
               else
               {
                       // the truncation happened
                       // on a write
                       System.out.println(“ was truncated on a write”);
               }
               System.out.println(“It was: “ +
                       ex.getDataSize() + “ bytes long”);
               System.out.println(“Actual size: “ +
                       ex.getTransferSize() +
                       “ bytes transferred”);
               System.out.println(“”);
       }
  // ...
  }
...
...

Summary

In this chapter, you learned how to:

•  Import java.sql.* to avoid long member names

•  Build a JDBC URL

•  Load one or more specific JDBC driver with class.forName()

•  If necessary, set the JDBC log stream with setLogStream()

•  If necessary, adjust the connection properties

•  Open a connection with getConnection()

•  Create a statement object

•  Build a SQL statement

•  Execute the SQL statement

•  Manage multiple result sets

•  Fetch rows of data in cases of result set of rows

•  Fetch columns of rows by column name or column index

•  Fetch integer results

•  Check for warnings

•  Manage data truncation errors

•  Close the result set

•  Close the statement

•  Terminate the connection with close()

•  Catch exceptions

The next chapter discusses data type conversions, how to use the SQL escape syntax, what a database transaction is, some theory, and provides exercises on SQL cursors.