Results 1 to 10 of 10
  1. #1
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    165
    Rep Power
    7

    Default How to connect to different schemas using prepared statement

    I have the following code running fine with one sql statement referring to schema1. In this scenario, I have hard coded the value of empID as 2 as shown in the sql below :

    Java Code:
    private String selectEmpShowDocs_SQL = 
        			"SELECT " + 
        				"emp_doc " +
        			"FROM " + 
        				"schema1.emp_info " +				
        			"WHERE " +				
        				"doc_id = ? "+
        			"AND"+
                         "empID = 2";
    Now, I have another sql statement which is retrieving the emp_id value and instead of hardcoding the value just like I did above for empID, I want to pass the value of emp_id obtained from the following sql statement to the above sql statement. This is the statement which is referring to schema2.

    Java Code:
    private String selectEmpIDSQL = 
        			"SELECT " + 
        				"emp_id " +
        			"FROM " + 
        				"schema2.emp_id " +				
        			"WHERE " +				
        				"company_id = 435 "
    I am wondering is it possible to connect with two different schemas with one prepared statement?

    Here is the full code that works fine for me using only the SQL query referring to schema1.

    Java Code:
      public List<EmployeeDocument>  getEmployeeDocument(String docId, Integer employeeID) throws DaoException
        	{
        		StopWatch stopWatch = new StopWatch();
        		stopWatch.start();						
        		DataSource ds = null;
        		Connection conn = null;
        		PreparedStatement pstmt = null;
        		ResultSet rs = null;
        		
        		List<EmployeeDocument> empShowDocs = new ArrayList<EmployeeDocument>();			
        		try {
        			ds = jdbcTemplate.getDataSource();
        			conn = ds.getConnection();
        			pstmt = conn.prepareStatement(selectEmpShowDocs_SQL);
        			logger.debug("sql query :" + selectEmpShowDocs_SQL);
        			System.out.println(selectEmpShowDocs_SQL);
        			pstmt.setString(1, docId);					
        			logger.debug("sql parameters, docId:" + docId);
        			rs = pstmt.executeQuery();			
        			while(rs.next()) {				
        				EmployeeDocument empShowDocRecord = new EmployeeDocument();		
        				empShowDocRecord.setEmp_Content(rs.getString("emp_doc")));	
        				empShowDocs.add(empShowDocRecord);
        			}
        		} catch(Throwable th) {
        			throw new DaoException(th.getMessage(), th);
        		} finally {
        			if (rs != null) {
        				try {
        					rs.close();
        				} catch (SQLException e) {					
        					e.printStackTrace();
        				}
        				rs = null;
        			}
        			if (pstmt != null) {
        				try {
        					pstmt.close();
        				} catch(SQLException sqe) {
        					sqe.printStackTrace();
        				}
        				pstmt = null;
        			} 
        			if (conn != null) {
        				try {
        					conn.close();
        				} catch (SQLException sqle) {
        					sqle.printStackTrace();
        				}
        				conn = null;
        			}
        			if (ds != null) {				
        				ds = null;
        			}
        		}		
        		return empShowDocs;
        	}
        	
        	private String selectEmpShowDocs_SQL = 
        			"SELECT " + 
        				"emp_doc " +
        			"FROM " + 
        				"schema1.emp_info " +				
        			"WHERE " +				
        				"doc_id = ? "+
        			"AND"+
                         "empID = 2";			
        				
        				
        				
            private String selectEmpIDSQL = 
        			"SELECT " + 
        				"emp_id " +
        			"FROM " + 
        				"schema2.emp_id " +				
        			"WHERE " +				
        				"company_id = 435 "
    In order to connect to schema 1 and 2, I have to use different connection paremeters.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How to connect to different schemas using prepared statement

    How would you write the query in something like SQL Developer?
    By the way, emp_id is a strange name for a table.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    165
    Rep Power
    7

    Default Re: How to connect to different schemas using prepared statement

    Quote Originally Posted by Tolls View Post
    How would you write the query in something like SQL Developer?
    By the way, emp_id is a strange name for a table.
    In SQL developer, the two queries for two schemas looks like the following :

    For Schema #2:

    Java Code:
    SELECT
              emp_id
    FROM  
              schema2.emp_id
    WHERE
             company_id = 435
    For Schema #1:

    Java Code:
    SELECT
             emp_doc
    FROM
            schema1.emp_info
    WHERE
             doc_id = 12345 
    AND 
            emp_id = 2;

  4. #4
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,971
    Rep Power
    7

    Default Re: How to connect to different schemas using prepared statement

    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  5. #5
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    165
    Rep Power
    7

    Default Re: How to connect to different schemas using prepared statement

    Quote Originally Posted by SurfMan View Post
    Thanks but I don't think that answers my question. My concern is that whether I need to use two PreparedStatement pstmt1,pstmt2, two ResultSet rs1,rs2 and two Connection conn1, conn2 ?

  6. #6
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,971
    Rep Power
    7

    Default Re: How to connect to different schemas using prepared statement

    Quote Originally Posted by Jack_Tauson_Sr View Post
    Thanks but I don't think that answers my question. My concern is that whether I need to use two PreparedStatement pstmt1,pstmt2, two ResultSet rs1,rs2 and two Connection conn1, conn2 ?
    If it's the same server than one connection will do. Two different servers obviously need two of everything (Connection, PreparedStatement and ResultSet).

    Assuming the same server, whether you need two PreparedSatements depends if you want to run over them simultaneously. If you want to run two loops, one for each ResultSet, then you need two PreparedStatements.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How to connect to different schemas using prepared statement

    Quote Originally Posted by Jack_Tauson_Sr View Post
    In SQL developer, the two queries for two schemas looks like the following :
    But that doesn't show how you would write a single query covering two schemas.

    If you can't do it in SQL Developer, say because the user does not have access to both schemas, then you can't do it in JDBC.
    If your user does have access to both schemas then you can do it in JDBC.

    Java Code:
    SELECT inf. emp_id, inf.emp_doc
    FROM schema2.emp_id ei JOIN
         schema1.emp_info inf ON ei.emp_id = inf.emp_id
    WHERE ei.company_id = 435
      AND inf.doc_id = 12345;
    That above is merging the two queries you posted, so it brings back all emp_id/emp_doc combinations for a single company_id (of course, not tested, so possibly has some typos etc in it).
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #8
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    165
    Rep Power
    7

    Default Re: How to connect to different schemas using prepared statement

    Quote Originally Posted by Tolls View Post
    But that doesn't show how you would write a single query covering two schemas.

    If you can't do it in SQL Developer, say because the user does not have access to both schemas, then you can't do it in JDBC.
    If your user does have access to both schemas then you can do it in JDBC.

    Java Code:
    SELECT inf. emp_id, inf.emp_doc
    FROM schema2.emp_id ei JOIN
         schema1.emp_info inf ON ei.emp_id = inf.emp_id
    WHERE ei.company_id = 435
      AND inf.doc_id = 12345;
    That above is merging the two queries you posted, so it brings back all emp_id/emp_doc combinations for a single company_id (of course, not tested, so possibly has some typos etc in it).
    Thanks. But the reason I kept the two queries separate is because for both the schemas I have to connect with different database server.

    For Example, in order to run the following SQL query in the SQL Worksheet, I have a dedicated connection defined :

    Java Code:
    SELECT
              emp_id
    FROM  
              schema2.emp_id
    WHERE
             company_id = 435
    If I have to run the following query:

    Java Code:
    SELECT
             emp_doc
    FROM
            schema1.emp_info
    WHERE
             doc_id = 12345
    AND 
            emp_id = 2;
    In the SQL Worksheet, I have to change the connection from the dropdown option on the top right corner and then run this query.

    In this scenario, the way you suggested to JOIN two schemas won't work right? Would it be possible for me to get the value of emp_id by querying the schema2 and then use it in the query which uses schema1?

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: How to connect to different schemas using prepared statement

    So it's not "two schemas", it's "two databases".

    Are the servers with these two DBs "near" each other, as in on the same network?

    If so, you can use a DATABASE LINK, which will allow you to query the other database schema, including JOINs.

    Indeed, the two DBs do not have to be "near", but obviously the further apart they are the slower any query will be.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  10. #10
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    165
    Rep Power
    7

    Default Re: How to connect to different schemas using prepared statement

    Quote Originally Posted by Tolls View Post
    So it's not "two schemas", it's "two databases".

    Are the servers with these two DBs "near" each other, as in on the same network?

    If so, you can use a DATABASE LINK, which will allow you to query the other database schema, including JOINs.

    Indeed, the two DBs do not have to be "near", but obviously the further apart they are the slower any query will be.
    Thanks. I was finally able to connect to two databases with two different connections. I posted another question in a separate thread on the same forum.

Similar Threads

  1. SQL Prepared Statement with date
    By Dcalladi in forum New To Java
    Replies: 3
    Last Post: 02-18-2012, 03:37 PM
  2. problem when inserting a row with prepared Statement
    By sabith79 in forum New To Java
    Replies: 1
    Last Post: 11-18-2011, 03:00 PM
  3. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-20-2009, 11:53 PM
  4. Prblem in Prepared Statement
    By haneeshrawther in forum JDBC
    Replies: 2
    Last Post: 04-25-2008, 09:49 AM
  5. Using Prepared Statement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •