Java and multiple connections to database

[Former Member]

Hello,

I have a project where I  connect to my main database with jdbc.

There I get the paths for other databases, and I open those I with the START DATABASE command.

Then I have to make a new jdbc connection to that newly started database.

So far so good.

But the problem is that I can only get info from the first database in my list, and for some reason I don't understand my resultSet seems empty ...While I know there are way more database in the list.

My personal logging:

17-07-14 15:30:12 [ERROR] TechLogger - Database LANDUYT - w:\TOP\DATA\LANDUYT\LANDUYT.DB doesn't exist

17-07-14 15:30:12 [ERROR] TechLogger - Database BVCACC - w:\TOP\DATA\BVCACC\BVCACC.DB doesn't exist

17-07-14 15:30:12 [ERROR] TechLogger - Database LMC - w:\TOP\DATA\LMC\LMC.DB doesn't exist

17-07-14 15:30:12 [ERROR] TechLogger - Database LIFT - w:\TOP\DATA\LIFT\LIFT.DB doesn't exist

17-07-14 15:30:12 [ERROR] TechLogger - Database JULIE - w:\TOP\DATA\JULIE\JULIE.DB doesn't exist

17-07-14 15:30:12 [ERROR] TechLogger - Database BACKLINE - w:\TOP\DATA\BACKLINE\BACKLINE.DB doesn't exist

17-07-14 15:30:16 [INFO ] TechLogger - Database BRAL started

17-07-14 15:30:22 [INFO ] TechLogger - Added 141 creditors for BRAL

17-07-14 15:30:22 [INFO ] TechLogger - Added debitor: Debitor [companyCode=BRAL, vatregno=BE463604669]

17-07-14 15:30:22 [INFO ] TechLogger - Added VC creditor: Creditor [creditorId=0, name1=BRAL CONSULTING, vatregno=BE463604669]

17-07-14 15:30:22 [ERROR] TechLogger - Could not stop database BRAL

17-07-14 15:30:22 [ERROR] TechLogger -  -> com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -83: Specified database not found

Maybe it has something to do with the fact that I also can't seem to stop the database I started, or am I wrong to think that I need to close the connection to the database and then stop it?

My code:

public static void processFiles(Configuration config) {

  SqlUtils sqlUtils = new SqlUtils();

  //map networkdrive, if needed
  mapNetworkDrive(config);

  //connect to database
  try {
  sqlUtils.connect(config);
  logger.info("Connected to db");
  } catch (ClassNotFoundException | SQLException e) {
  logger.error("Could not connect to database");
  logger.error(" -> " + e.getMessage());
  }


  //voor elke klante de database starten, gegevens ophalen en database stoppen
  try {
  sqlUtils.getCustomers();

  //sqlUtils.startDatabase("W:\\TOP\\DATA\\ARCUS\\ARCUS.DB", "ARCUS");

  //sqlUtils.stopDatabase("ARCUS");

  } catch (SQLException e) {
  logger.error("Could not get customers");
  logger.error(" -> " + e);
  }


  //close database
  sqlUtils.close();

  }

package eu.adm.solutions.topaccount2irisxtract.util;


import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import org.apache.log4j.Logger;


import eu.adm.solutions.topaccount2irisxtract.pojo.Configuration;
import eu.adm.solutions.topaccount2irisxtract.pojo.Creditor;
import eu.adm.solutions.topaccount2irisxtract.pojo.Debitor;


public class SqlUtils {


  static final Logger logger = Logger.getLogger("TechLogger");


  Connection connection;
  Statement statement;
  ResultSet resultSet;
  Configuration config;
  CsvUtils csvUtils = new CsvUtils();


  public void connect(Configuration config) throws SQLException, ClassNotFoundException {

  this.config = config;
  Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
  String connectionString = "jdbc:sybase:Tds:" + config.getHost() + ":" + config.getPort() + "?ServiceName=" + config.getDatabase();
  connection = DriverManager.getConnection(connectionString, config.getUser(), config.getPassword());
  }


  public void close() {
  if (resultSet != null) {
  try {
  resultSet.close();
  } catch (SQLException e) {
  logger.error("Could not close result set");
  logger.error(" -> " + e.getMessage());
  }
  }
  if (statement != null) {
  try {
  statement.close();
  } catch (SQLException e) {
  logger.error("Could not close SQL statement");
  logger.error(" -> " + e.getMessage());
  }
  }
  if (connection != null) {
  try {
  connection.close();
  } catch (SQLException e) {
  logger.error("Could not close Sybase connection");
  logger.error(" -> " + e.getMessage());
  }
  }
  }


  public void getCustomers() throws SQLException {
  statement = connection.createStatement();


  resultSet = statement.executeQuery("SELECT NM_DATABASE,C_DBF FROM DBA.TOP_DATABASE WHERE BOL_ACTIVE=1 AND COD_STATUS IS NULL OR COD_STATUS <> 'B'");


  String databasePath = "";
  String databaseName = "";
  int count = 0;

  //create new csv files
  csvUtils.createNewMasterCreditorFile(config.getOuputFolder() + File.separator + config.getMaster_Creditor());
  csvUtils.createNewMasterDebitorFile(config.getOuputFolder() + File.separator + config.getMaster_Debitor());
  csvUtils.createNewMasterOwnvatregFile(config.getOuputFolder() + File.separator + config.getMaster_Ownvatregno());


  while(resultSet.next()){

  databasePath = resultSet.getString("C_DBF");
  databaseName = resultSet.getString("NM_DATABASE");



  startDatabase(databasePath, databaseName);

// stopDatabase(databaseName);

  count ++;

  }

  logger.info("gedaan met werken na " + count  + " resulset: " + resultSet);
  }

  public void startDatabase(String databasePath, String databaseName) throws SQLException{

  statement = connection.createStatement();

  String query = "START DATABASE '" + databasePath + "' AS " + databaseName;

  //String query = "START DATABASE 'W:\\TOP\\DATA\\ARCUS\\ARCUS.DB' AS ARCUS";

  //check if path exists

  File fDatabase = new File(databasePath);

  if(fDatabase.exists()){

  //start database
  statement.execute(query);

  logger.info("Database " + databaseName + " started");

  //connect to database
  try {
  Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
  String connectionString = "jdbc:sybase:Tds:" + config.getHost() + ":" + config.getPort() + "?ServiceName=" + databaseName;
  Connection connection2  = DriverManager.getConnection(connectionString, config.getUser(), config.getPassword());


  //get VC client customer info
  statement = connection2.createStatement();

  resultSet = statement.executeQuery("SELECT TNR_ID,NM_ID,CNR_VAT_ID,NM_FILTER_ID,CNR_ZIP_ID,CNR_VATCNTRY_ID,NM_TOWN_ID,TOP_COUNTRY.CNR_INTRASTAT FROM TOP_IDENTITY JOIN TOP_COUNTRY ON (TOP_IDENTITY.COD_CNTRY_ID = TOP_COUNTRY.COD_CNTRY)");

  int count = 0;
  String companyCode=databaseName;

  while(resultSet.next()){

  Creditor creditor = new Creditor();

  creditor.setCreditorId(resultSet.getString("TNR_ID"));
  creditor.setName1(resultSet.getString("NM_ID"));
  creditor.setName2(resultSet.getString("NM_FILTER_ID"));
  creditor.setVatregno(resultSet.getString("CNR_VATCNTRY_ID") + resultSet.getString("CNR_VAT_ID"));
  creditor.setCity(resultSet.getString("NM_TOWN_ID"));
  creditor.setZip(resultSet.getString("CNR_ZIP_ID"));
  creditor.setCountryCode(resultSet.getString("CNR_INTRASTAT"));
  //companycode ???
  creditor.setCompanyCode(companyCode);

  count ++;

  //add creditor to csv file or temp file
  csvUtils.saveCreditor(creditor, config.getOuputFolder() + File.separator + config.getMaster_Creditor());

  //logger.info("Added creditor: " + creditor);
  }


  logger.info("Added " + count + " creditors for " + databaseName);


  //get VC client customer own company info

  //resultSet = statement.executeQuery("SELECT TOP_IDENTITY.CNR_VATCNTRY_ID,TOP_IDENTITY.CNR_VAT_ID,NM_STREET_COMP,CNR_HOUSE_COMP,CNR_ZIP_COMP,NM_TOWN_COMP FROM TOP_COMPANY JOIN TOP_IDENTITY ON (TOP_COMPANY.TNR_ID = TOP_IDENTITY.TNR_ID)");
  resultSet = statement.executeQuery("SELECT TOP_COUNTRY.CNR_INTRASTAT,TOP_IDENTITY.CNR_VATCNTRY_ID,TOP_IDENTITY.CNR_VAT_ID,TOP_IDENTITY.NM_ID,TOP_IDENTITY.NM_FILTER_ID,NM_STREET_COMP,CNR_HOUSE_COMP,CNR_ZIP_COMP,NM_TOWN_COMP FROM TOP_COMPANY JOIN TOP_IDENTITY ON (TOP_COMPANY.TNR_ID = TOP_IDENTITY.TNR_ID) JOIN TOP_COUNTRY ON (TOP_IDENTITY.COD_CNTRY_ID = TOP_COUNTRY.COD_CNTRY)");
  while(resultSet.next()){

  Debitor debitor = new Debitor();

  debitor.setCompanyCode(companyCode);
  debitor.setVatregno(resultSet.getString("CNR_VATCNTRY_ID") + resultSet.getString("CNR_VAT_ID"));
  debitor.setSimple("Y");
  debitor.setDre(resultSet.getString("NM_STREET_COMP").trim() + resultSet.getString("CNR_HOUSE_COMP").trim()+ resultSet.getString("CNR_ZIP_COMP").trim()+ resultSet.getString("NM_TOWN_COMP").trim());
  debitor.setDpro1(resultSet.getString("NM_STREET_COMP").trim() + resultSet.getString("CNR_HOUSE_COMP").trim());
  debitor.setDpro2(resultSet.getString("NM_STREET_COMP").trim());

  //add debitor to csv file or temp file
  csvUtils.saveDebitor(debitor, config.getOuputFolder() + File.separator + config.getMaster_Debitor());
  csvUtils.saveOwnvatreg(debitor, config.getOuputFolder() + File.separator + config.getMaster_Ownvatregno());

  logger.info("Added debitor: " + debitor);

  // get VC customer info
  Creditor creditor = new Creditor();

  //TODO: zelf nummer voorzien of uit hoofdDatabase??
  creditor.setCreditorId("0");
  creditor.setName1(resultSet.getString("NM_ID"));
  creditor.setName2(resultSet.getString("NM_FILTER_ID"));
  creditor.setVatregno(resultSet.getString("CNR_VATCNTRY_ID") + resultSet.getString("CNR_VAT_ID"));
  creditor.setCity(resultSet.getString("NM_TOWN_COMP"));
  creditor.setZip(resultSet.getString("CNR_ZIP_COMP"));
  creditor.setCountryCode(resultSet.getString("CNR_INTRASTAT"));
  //companycode ???
  creditor.setCompanyCode("VC");

  csvUtils.saveCreditor(creditor, config.getOuputFolder() + File.separator + config.getMaster_Creditor());

  logger.info("Added VC creditor: " + creditor);
  }

// //close connection 2 database
// if (connection2 != null) {
// try {
// connection2.close();
// logger.info("Closed connection2 to " + databaseName);
// } catch (SQLException e) {
// logger.error("Could not close Sybase connection2");
// logger.error(" -> " + e.getMessage());
// }
// }

  //stop database
  stopDatabase(databaseName);





  } catch (ClassNotFoundException e) {
  logger.error("Could not get customer info");
  logger.error(" -> " + e);
  }
  catch (SQLException e){
  logger.error("Could not get customer info");
  logger.error(" -> " + e);

  }

  }
  else{

  logger.error("Database " + databaseName + " - " + databasePath + " doesn't exist");

  }

  }

  public void stopDatabase(String databaseName) throws SQLException{

  try {
  statement = connection.createStatement();


  statement.execute("STOP DATABASE " + databaseName + "UNCONDITIONALLY");

  logger.info("Database " + databaseName + " stopped");

  } catch (SQLException e) {
  logger.error("Could not stop database " + databaseName);
  logger.error(" -> " + e);
  }


  }


  public void getCustomerInfo(String databaseName) throws SQLException{

  statement = connection.createStatement();


  resultSet = statement.executeQuery("SELECT * FROM " + databaseName  + ".TOP_IDENTITY");

  while(resultSet.next()){


  }
  }

}

[JasonHinsperger · Accepted solution]

To connect to a specific database running on a SQLA server using JConnect, you need to either use the ServiceName parameter or set the RemotePWD connection parameter in your getConnection() call. You can get details on setting up the connection here:

http://dcx.sybase.com/index.html#sa160/en/dbprogramming/dbname-jconnect-jdbc.html

You should consider using the SQL Anywhere JDBC driver instead of JConnect. It ships with SQL Anywhere and is generally faster.  You can find information on it here:

http://dcx.sybase.com/index.html#sa160/en/dbprogramming/jdbc-url-driver.html

--Jason

[Former Member · Accepted solution]

Hi Jason,

Thank you for your answer.

But I use ServiceName on line 22 successfully and also on line 83 it works the first database.

I'm now trying the RemotePWD option and the JDBC driver, will let you know it that helps.

[Former Member · Accepted solution]

Hi Jason,

Seems I made a stupid mistake. There was nothing wrong with the way I was connecting to the database, but I used the same resultset in my function StarDatabase, so when I changed this to a new resultset everything worked ...

But thank you anyway