morc said:
Hi,
i haven't been doing database related programming for a large period of
time.
I have forgotten how to connect to a databse.
if someone could please do me the favour of giving me an example of
how to connect to access db and run a query.
Here are some fragments that should answer your questions; your code simply
needs to invoke the three methods at the appropriate time. Naturally, the
sequence should be that you load the JDBC driver first, then get a
connection to the database, then query or update the database. You'll also
want to define some variables as follows:
final String CLASS_NAME = getClass().getName();
Connection conn01 = null;
static final String DEMO_TABLE = "RHINO.PERSONNEL";
public void loadDriver() {
String METHOD_NAME = "loadDriver()";
/* Initialize the variable that contains the name of the JDBC
driver. */
String jdbcDriverName = "com.ibm.db2.jcc.DB2Driver";
/* Load the JDBC driver. */
try {
Class.forName(jdbcDriverName);
} catch (ClassNotFoundException excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME + " -
Encountered ClassNotFoundException while attempting to load JDBC driver " +
jdbcDriverName + ". Error: " + excp);
excp.printStackTrace();
System.exit(16);
}
}
public void connectToDatabase() {
String METHOD_NAME = "connectToDatabase()";
/* Initialize the variables used to get the connection. */
String databaseName = "sample";
String url = "jdbc:db2:" + databaseName;
String loginName = "admin";
String password = "adminpw";
/* Connect to the database. */
try {
conn01 = DriverManager.getConnection(url, loginName, password);
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException on connect to URL " + url
+ ". Error: "
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
/* Set autocommit off. */
try {
conn01.setAutoCommit(false);
System.out.println("Turn off autocommit...");
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException on attempt to turn
autocommit off. Error: "
+ sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}
public void queryTable() {
String METHOD_NAME = "queryTable()";
System.out.println("Current D21 rows in the table: ");
String queryTableSQL = "select lastname, workdept, salary, hiredate
from "
+ DEMO_TABLE + " where workdept = 'D21' ";
/*
* Query the demonstration table to get information about certain
* employees.
*/
Statement queryTableStmt = null;
ResultSet rs01 = null;
try {
queryTableStmt = conn01.createStatement();
rs01 = queryTableStmt.executeQuery(queryTableSQL);
} catch (SQLException excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while trying to get
information from "
+ DEMO_TABLE + " table. Error: " + excp);
excp.printStackTrace();
System.exit(16);
}
/* Initialize the host variables used for handling the result set.
*/
String lastname = null;
String workdept = null;
BigDecimal salary = null;
java.sql.Date hiredate;
/*
* Print each line of the result set. The salary, which is a double,
is
* converted to a BigDecimal and then copied to another BigDecimal
that
* has exactly 2 decimal places.
*/
try {
while (rs01.next()) {
lastname = rs01.getString(1);
workdept = rs01.getString(2);
salary = rs01.getBigDecimal(3);
hiredate = rs01.getDate(4);
System.out.println(lastname + " " + workdept + " " +
salary.toString() + " "
+ hiredate.toString());
}
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while reading " +
demoTable
+ " table. Error: " + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
/* Close the result set, dispose of the statement, and commit. */
try {
rs01.close();
queryTableStmt.close();
conn01.commit();
} catch (SQLException sql_excp) {
System.err.println(CLASS_NAME + "." + METHOD_NAME
+ " - Encountered SQLException while closing " +
demoTable
+ " result set, closing statement, or committing. Error:
" + sql_excp);
sql_excp.printStackTrace();
System.exit(16);
}
}
dbc:" + databaseName;