Why do I have to reaccess the database for Array, Blob, and Clob data?

JDBC Interview Questions and Answers

(Continued from previous question...)

Why do I have to reaccess the database for Array, Blob, and Clob data?

Most DBMS vendors have implemented these types via the SQL3 Locator type
Some rationales for using Locators rather than directly returning the data can be seen most clearly with the Blob type. By definition, a Blob is an arbitrary set of binary data. It could be anything; the DBMS has no knowledge of what the data represents. Notice that this effectively demolishes data independence, because applications must now be aware of what the Blob data actually represents. Let's assume an employee table that includes employee images as Blobs.
Say we have an inquiry program that presents multiple employees with department and identification information. To see all of the data for a specific employee, including the image, the summary row is selected and another screen appears. It is only at this pont that the application needs the specific image. It would be very wasteful and time consuming to bring down an entire employee page of images when only a few would ever be selected in a given run.
Now assume a general interactive SQL application. A query is issued against the employee table. Because the image is a Blob, the application has no idea what to do with the data, so why bring it down, killing performance along the way, in a long running operation?
Clearly this is not helpful in those applications that need the data everytime, but these and other considerations have made the most general sense to DBMS vendors.

(Continued on next question...)

Other Interview Questions

• What is JDBC?
• What's the JDBC 3.0 API?
• Does the JDBC-ODBC Bridge support the new features in the JDBC 3.0 API?
• Can the JDBC-ODBC Bridge be used with applets?
• How do I start debugging problems related to the JDBC API?
• What is new in JDBC 2.0?
• How to move the cursor in scrollable resultsets?(new feature in JDBC 2.0)
• How to update a resultset programmatically? (new feature in JDBC 2.0)
• How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
• Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
• What causes the "No suitable driver" error?
• Why isn't the java.sql.DriverManager class being found?
• How to insert and delete a row programmatically? (new feature in JDBC 2.0)
• What are the two major components of JDBC?
• What is JDBC Driver interface?
• How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column?
• What are the common tasks of JDBC?
• Why does the ODBC driver manager return 'Data source name not found and no default driver specified Vendor: 0'
• How to use JDBC to connect Microsoft Access?
• What are four types of JDBC driver?
• Which type of JDBC driver is the fastest one?
• Are all the required JDBC drivers to establish connectivity to my database part of the JDK?
• Is the JDBC-ODBC Bridge multi-threaded?
• Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
• What is the query used to display all tables names in SQL Server (Query analyzer)?
• Why can't I invoke the ResultSet methods afterLast and beforeFirst when the method next works?
• How can I retrieve a String or other object type without creating a new object each time?
• How many types of JDBC Drivers are present and what are they?
• What is the fastest type of JDBC driver?
• There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
• I would like to download the JDBC-ODBC Bridge for the Java 2 SDK, Standard Edition (formerly JDK 1.2). I'm a beginner with the JDBC API, and I would like to start with the Bridge. How do I do it?
• If I use the JDBC API, do I have to use ODBC underneath?
• Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?
• How do I insert a .jpg into a mySQL data base?
• What is the best way to generate a universally unique object ID? Do I need to use an external resource like a file or database, or can I do it all in memory?
• Whan happens when I close a Connection application obtained from a connection Pool? How does a connection pool maintain the Connections that I had closed through the application?
• How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method?
• Where can I find info, frameworks and example source for writing a JDBC driver?
• How can I create a custom RowSetMetaData object from scratch?
• How does a custom RowSetReader get called from a CachedRowSet?
• How do I implement a RowSetReader? I want to populate a CachedRowSet myself and the documents specify that a RowSetReader should be used. The single method accepts a RowSetInternal caller and returns void. What can I do in the readData method?
• How can I instantiate and load a new CachedRowSet object from a non-JDBC source?
• Can I set up a conection pool with multiple user IDs? The single ID we are forced to use causes probelems when debugging the DBMS.
• How can I protect my database password ? I'm writing a client-side java application that will access a database over the internet. I have concerns about the security of the database passwords. The client will have access in one way or another to the class files, where the connection string to the database, including user and password, is stored in as plain text. What can I do to protect my passwords?
• Detecting Duplicate Keys I have a program that inserts rows in a table ...
• What driver should I use for scalable Oracle JDBC applications?
• Can you scroll a result set returned from a stored procedure?...
• How do I write Greek ( or other non-ASCII/8859-1 ) characters to a database?
• How can I insert images into a Mysql database?
• Is is possible to open a connection to a database with exclusive mode with JDBC?
• What are the standard isolation levels defined by JDBC?
• Update fails without blank padding. Alhough a particular row is present in the database for a given key, executeUpdate() shows 0 rows updated and, in fact, the table is not updated. If I pad the Key with spaces for the column length (eg if the key column is 20 characters long, and key is msgID, length 6, I pad it with 14 spaces), the update then works!!! Is there any solution to this problem without padding?
• What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?
• How can I determine the isolation levels supported by my DBMS?
• Connecting to a database through the Proxy I want to connect to remote database using a program that is running in the local network behind the proxy. Is that possible?
• How do I receive a ResultSet from a stored procedure?
• How can I write to the log used by DriverManager and JDBC drivers?
• How can I get or redirect the log used by DriverManager and JDBC drivers?
• What does it mean to "materialize" data?
• Why do I have to reaccess the database for Array, Blob, and Clob data?
• What is an SQL Locator?
• How do I set properties for a JDBC driver and where are the properties stored?
• What is the JDBC syntax for using a literal or variable in a standard Statement?
• How do I check in my code whether a maximum limit of database connections have been reached?
• DB2 Universal claims to support JDBC 2.0, But I can only get JDBC 1.0 functionality. What can I do?
• Many connections from an Oracle8i pooled connection returns statement closed. I am using import oracle.jdbc.pool.* with thin driver. If I test with many simultaneous connections, I get an SQLException that the statement is closed.
• How can I overwrite blob data in an Oracle database? ...
• How do I disallow NULL values in a table?
• ) How to get a field's value with ResultSet.getxxx when it is a NULL? I have tried to execute a typical SQL statement: select * from T-name where (clause); But an error gets thrown because there are some NULL fields in the table.
• How do I insert/update records with some of the columns having NULL value?
• Is there a way to find the primary key(s) for an Access Database table? Sun's JDBC-ODBC driver does not implement the getPrimaryKeys() method for the DatabaseMetaData Objects.
• Why can't Tomcat find my Oracle JDBC drivers in classes111.zip?
• I have an application that queries a database and retreives the results into a JTable ...
• How does one get column names for rows returned in a ResultSet?
• What are the considerations for deciding on transaction boundaries?
• What is the JDBC syntax for using a date literal or variable in a standard Statement?
• How can I determine where a given table is referenced via foreign keys?
• How can I get information about foreign keys used in a table?
• Can I use JDBC to execute non-standard features that my DBMS provides?
• What is DML?
• What is the significance of DataBaseMetaData.tableIndexStatistics? How to obtain and use it?
• What types of DataSource objects are specified in the Optional Package?
• What is a JDBC 2.0 DataSource?
• Does the database server have to be running Java or have Java support in order for my remote JDBC client app to access the database?
• Which Java and java.sql data types map to my specific database types?
• Are the code examples from the JDBC API Tutorial and Reference, Second Edition available online?
• When an SQL select statement doesn't return any rows, is an SQLException thrown?
• Why should I consider optimistic versus pessimistic approaches to database updates?
• What is optimistic concurrency?
• What is pessimistic concurrency?
• Can I get information about a ResultSet's associated Statement and Connection in a method without having or adding specific arguments for the Statement and Connection?
• What is the most efficient method of replicating data betwen databases using JDBC?
• How can I tell if my JDBC driver normalizes java.sql.Date and java.sql.Time objects?
• What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?
• What is JDO?
• When I intersperse table creation or other DDL statements with DML statements ....
• What's the best way, in terms of performance, to do multiple insert/update statements, a PreparedStatement or Batch Updates?
• I need to have result set on a page where the user can sort on the column headers. Any ideas?
• What are the components of the JDBC URL for Oracle's "thin" driver and how do I use them?
• Why doesn't JDBC accept URLs instead of a URL string?
• What JDBC objects generate SQLWarnings?
• What's the fastest way to normalize a Time object?
• What does normalization mean for java.sql.Date and java.sql.Time?
• How do I create a java.sql.Date object?
• How do I create a java.sql.Time object?
• What scalar functions can I expect to be supported by JDBC?
• What does setFetchSize() really do?
• Is there a practical limit for the number of SQL statements that can be added to an instance of a Statement object
• How can I determine whether a Statement and its ResultSet will be closed on a commit or rollback?
• How do I get runtime information about the JDBC Driver?
• How do I create an updatable ResultSet?
• How can I connect to an Oracle database not on the web server from an untrusted applet?
• How can I insert multiple rows into a database in a single transaction?
• How do I display and parse a date?
• How can I retrieve string data from a database in Unicode format?
• Can ResultSets be passed between methods of a class? Are there any special usage
• How can I convert a java array to a java.sql.Array?
• Could we get sample code for retrieving more than one parameter from a stored procedure?
• What is the difference between client and server database cursors?
• How can I pool my database connections so I don't have to keep reconnecting to the database?
• How can I connect to an Excel spreadsheet file using jdbc?
• How do I execute stored procedures?
• How can I get data from multiple ResultSets?
• How can resultset records be restricted to certain rows?
• How do I insert an image file (or other raw data) into a database?
• How can I connect from an applet to a database on the server?
• Can I use the JDBC-ODBC bridge driver in an applet?
• Which is the preferred collection class to use for storing database result sets?
• The java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database?
• How can I manage special characters (for example: " _ ' % ) when I execute an INSERT query? If I don't filter the quoting marks or the apostrophe, for example, the SQL string will cause an error.
• How can I make batch updates using JDBC?
• How do I extract SQL table column type information?
• How can I investigate the parameters to send into and receive from a database stored procedure?
• How do I check what table-like database objects (table, view, temporary table, alias) are present in a particular database?
• What does ResultSet actually contain? Is it the actual data of the result or some links to databases? If it is the actual data then why can't we access it after connection is closed?
• How do I extract a BLOB from a database?
• How do I extract the SQL statements required to move all tables and views from an existing database to another database?
• How do I check what table types exist in a database?
• What is the advantage of using a PreparedStatement?
• How do I find all database stored procedures in a database?
• How can I investigate the physical structure of a database?
• How does the Java Database Connectivity (JDBC) work?
• What is Metadata and why should I use it?
• How do I create a database connection?