Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Passing values into the SQL connection class?

 
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've never made a database connection in java and building a select statement to retrieve the info needed and am needing some help. I have a class that makes the connection:

// Method to get a connection to a SQL Server 2000 database.
public void 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");
String tablename = "POI";
String fields = "assetnumber, chargeto, hospcode, itemnumber, lineitemprice, nec, nextacct, purchaseum, quantityordered, recstatus, requisitionnumber, spcdescription, typeswitch";
String query = "";
Statement stmt = con.createStatement();
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);
}
}
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());
}
}
}
}

But I am needing to pass in the TABLENAME, FIELDS, and QUERY (WHERE statement) into this connection but I'm lost. I've tried googling for some sample code but to no avail. The class that I have that needs to make this call is as follows:

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();

return response;
}
}
}

Any direction or help would be appreciated.

Regards.
 
author
Posts: 201
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I understand your question correctly, it would be as simple as changing your SQLConn() method to take String arguments representing the table name, etc.:

public void SQLConn(String fields, String table, String whereClause) { ...

Is this what you really mean, or am I missing some aspect of your question?

Regards,

Jacquie
 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jacquie,

Thanks for the time and your response. I think you understand correctly but what I don't think I made clear is what I actually wanted to do which is the two classes that I'm dealing with in my webservice. The SOAPImplentation class and a TRANSACTION class. The parameters that build the select query statement are coming from the SOAPImplentation class and need to be passed to the TRANSACTION where the SQL connection method is located. That is what I am trying to do unless there is another way to accomplish this process.

Thanks again for your reply.

Regards.
 
On top of spaghetti all covered in cheese, there was this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic