Database Querying

Question 1

What are the typical steps in the database connection and query pipeline?

Answer 1

<ol>
<li>Connect to DBMS</li>
<li>Build up SQL statement in a buffer</li>
<li>Pass buffer to DBMS for execution</li>
<li>Check for succesful completion of statement</li>
<li>For queries, retrieve each row of result</li>
<li>Disconnect from DBMS</li>
</ol>

Question 2

What does a Statement represent in Java SQL execution and what does it generate upon execution?

Answer 2

Represents an SQL statement

Generates a ResultSet objects

Question 3

What are the three SQL statements you can create in Java etc?

Answer 3

<ul>
<li>Statement</li>
<li>Prepared Statement</li>
<li>Callable Statement</li>
</ul>

Question 4

What are Statements used for?

Answer 4

Simple SQL with no parameters

Question 5

What are PreparedStatements used for?

Answer 5

Precompiling SQL statements that may contain parameteres

Question 6

What are CallableStatements used for?

Answer 6

Executing stored procedures which may have both input and output

Question 7

What does executeQuery return?

Answer 7

Returns one ResultSet object

Question 8

What does executeUpdate return?

Answer 8

Returns the number of rows affected by the update

Question 9

In what singular circumstance would you use Statement?

Answer 9

When you know the EXACT STRING for the query, absolutely no parameters

Question 10

Why would you use PreparedStatement instead of just inserting input in Statement?

Answer 10

To prevent SQL injections

Question 11

What is PHP used for?

Answer 11

Sending data to web browser and retrieving data from database

Question 12

What are some potential problems in dealing with databases?

Answer 12

<ul>
<li>Failure to connect to database server</li>
<li>Failure to select a database</li>
<li>Failure to run a query</li>
<li>Failure to return results from a query</li>
</ul>