preparedStatement with like where clause

[peter tong]

my preparedStatement is

PreparedStatement pstmt = null;
ResultSet rs = null;
StringBuilder sb = new StringBuilder();
sb.append("select * from tableA where upper(field1) like ? ");
pstmt = conn.prepareStatement(sb.toString());
String str1 = "001_%";
pstmt.setString(1, str1);
rs = pstmt.executeQuery();

this query can return record if I run in Oracle sql developer, but no if in java (no record return and no error), what is the reason?

[Martin Vajsar]

I don't see any problem with it. Could you post (copy&paste) the command you run in SQL Developer and the records it produced?

[peter tong]

Martin Vajsar wrote:I don't see any problem with it. Could you post (copy&paste) the command you run in SQL Developer and the records it produced?

thanks for your reply, I am not in office now, but the command run successfully in SQL Developer, but not in JDBC preparestatement, in JDBC preparestatement, it returns no record.

[peter tong]

Martin Vajsar wrote:I don't see any problem with it. Could you post (copy&paste) the command you run in SQL Developer and the records it produced?

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;


public class TestLike {
	
	public static void main(String args[]){
		TestLike testLike = new TestLike();
		Connection conn = getConnection();
		StringBuilder sb = new StringBuilder();		
		sb.append(" select service_ver, log_dtm, log_no from  ALS_STG.ALS_AUDIT_LOG ");
		sb.append(" where 1 = 1 and upper(log_type_cd) = 'A' ");
		sb.append(" and service_ver "); 
		sb.append(" like '001%' order by log_no");
		try {
			String sql = sb.toString();
			System.out.println(sql);
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
public static Connection getConnection(){
		
		
		Connection conn = null;
		String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
			String serverName = "cdcehr29";
			String port = "14985";
			String servicename = "afw_dev.serverdev.ehr.gov.hk";
			String url = "jdbc:oracle:thin:@"+serverName + ":" + port + "/" 
				+ servicename;
			//String url = "jdbc:oracle:thin:@cdcehr29:14985/afw_dev.serverdev.ehr.gov.hk";
			String username = "als_stg_app_rw_user";
			String password = "xxzzxx";
			conn = DriverManager.getConnection(url, username, password);
			System.out.println("DB Connection success");
			return conn;
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			// could not find the database driver
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			// could not connect to the database
			e.printStackTrace();
		}		
		return null;
		
	}


}

from the attachment, if the sql is run in Oracle sql developer, there is 6 records retrieved, but if use preparedstatement, no record can be retrieved, what is the problem from?

[Paul Clapham]

The image you posted appears to be from about three weeks ago (what look like log entries at the bottom are dated February 27). Perhaps the database has changed since then?

[peter tong]

I know the problem finally, the oracle sql developer need run commit after update sql is run.

[Wendy Gibbons]

bother those pesky transactions. always one to look out for.