aspose file tools*
The moose likes JDBC and the fly likes HELP - newbie question on JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "HELP - newbie question on JDBC" Watch "HELP - newbie question on JDBC" New topic
Author

HELP - newbie question on JDBC

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

I have two classes, one that gets the JDBC connection and one that builds the sql statement and runs it. My problem is trying to utilize the "con" to create my statement and how I cannot reach it. Any direction or help would be greatly appreciated. Here is my code for the connection:

public Connection SQLConn(){
Connection con = null;
String hostname = "localhost"; // local machine
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://"+ hostname +":1433;DatabaseName=esdev;User=test;Password=test");

}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally {
if(con != null){
try { con.close(); }
catch (Exception e) {System.out.println("Error closing connection: " + e.getMessage());
}
}
}
return con;
}

My attempted java code:

public class ReadSOAPImpl implements org.tempuri.Read_PortType{
public org.texashealth.www.SCM.DataTier.ReadResponse.ReadResponse read(
org.texashealth.www.SCM.DataTier.BTResponse.ReadRequest readRequest
) throws java.rmi.RemoteException {

// Create the "ReadResponse" and "Header" objects.
ReadResponse response = new ReadResponse();
Header responseheader = new Header();

// Stuff the responseheader with the hardcoded VERSION and STATUS.
responseheader.setVersion(BigInteger.valueOf(1));
responseheader.setStatus(Status.fromString("OK"));

//Stuff the "response" object variable by passing in the header object.
response.setHeader(responseheader);

// Is the version in the header valid?
ReadRequest request = new ReadRequest();
if (request.getHeader().getVersion().intValue() != 1){
responseheader.setStatus(Status.fromString("VersionNotSupported"));
return response;
}

// Is there a tablename, query and fieldnames?
if (request.getTableName().toString() == null || request.getQuery().toString() == null || request.getFieldNames().toString() == null){
responseheader.setStatus(Status.fromString("NoTableNameQueryInfoFieldNamesFound"));
return response;
}

// Is there a transaction ID?
Transaction transaction = new Transaction();
if (Transaction.get(readRequest.getTransactionID().intValue()) == null){
responseheader.setStatus(Status.fromString("InvalidTransactionID"));
return response;
}
else{
// Make a SQL connection.
transaction.SQLConn();
String tablename = (request.getTableName());
String fields[] = (request.getFieldNames());
org.texashealth.www.SCM.DataTier.Common.QueryParameter[] query[];

Statement stmt = transaction.SQLConn().createStatement();
if (query == null){
ResultSet rs = stmt.executeQuery("SELECT " + fields + " FROM " + tablename);

while (rs.next()) {
String field = rs.getString(1);
String data = rs.getString(2);
// Do something like System.out.println(mykeyfield);
System.out.println("Field: " + field + " Value: " + data);
}
}
else{
ResultSet rs = stmt.executeQuery("SELECT " + fields + " FROM " + tablename + "WHERE " + query);

while (rs.next()) {
String field = rs.getString(1);
String data = rs.getString(2);
// Do something like System.out.println(mykeyfield);
System.out.println("Field: " + field + " Value: " + data);
}
}
return response;
}
}
}
Arvind Sampath
Ranch Hand

Joined: May 11, 2005
Posts: 144
The problem with ur code is that the function which returns the connection object (SQLConn()) is closing the connection before returning it.
-------

try { con.close(); }

-------

Typically, u'll use this class to create a connection and return it. The class which uses this connection should take care of closing it once it is done with it.

You cannot create a statement using a connection which is already closed.

Cheers,
Arvind
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HELP - newbie question on JDBC