This week's book giveaway is in the JavaScript forum.
We're giving away four copies of Cross-Platform Desktop Applications: Using Node, Electron, and NW.js and have Paul Jensen on-line!
See this thread for details.
Win a copy of Cross-Platform Desktop Applications: Using Node, Electron, and NW.js this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

HELP - newbie question on JDBC  RSS feed

 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 144
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!