Request.getParameterValues related question

[Farakh khan]

String[] expertise=req.getParameterValues("experty");	
String type=req.getParameter("type");

	if(type.equals("1")){

	String query=	" Select unique(emp_id) from expertise "+
			" where regexp_like(expertise, Replace(?, ' ', '|'))";

			PreparedStatement ps=cnn.prepareStatement(query);
			ps.setString(1, expertise);
			
			rs=ps.executeQuery();
			while(rs.next()){
			String cv_id=rs.getString(1);

			out.println(cv_id);					
			}		
			ps.close();			
		
	}

ERROR:
setString(int,java.lang.String) in java.sql.PreparedSTatement cannot be applied to (int,java.lang.String[])
ps.setSTring(1, expertise);

1 error

Thanks in anticipation

[Ankit Garg]

Do you have any question?? The setString method doesn't take a String[]. That's why you are getting an error...

[Farakh khan]

Thanks for your prompt reply

Ankit Garg wrote:Do you have any question?? The setString method doesn't take a String[]. That's why you are getting an error...

This is my Questions as to how can I find the way to pass array in setString method. I tried with loop but not works. Can you guide me please

Thanks again

[Farakh khan]

any help please

[Jack Anderson]

Hi Farakh,

The way i understand you problem is that you want to execute this query for each of the array objects.

If that is the case, you may write a very simple code like this:

  
String[] expertise=req.getParameterValues("experty");     
String type=req.getParameter("type");  

if(type.equals("1")){     
 	for(int i = 0; i < expertise.length; i++) {
     	 	String query=   " Select unique(emp_id) from expertise where regexp_like(expertise, Replace(?, ' ', '|'))";     
		PreparedStatement ps=cnn.prepareStatement(query);  
                ps.setString(1, expertise[i]);                  
                rs=ps.executeQuery();  
                while(rs.next()){  
     	           String cv_id=rs.getString(1);  // By the way, what are you doing here? Are you expecting one value or many? - Jack
     	           out.println(cv_id);                   
                }         
          	ps.close();                      
       }  
}

[Farakh khan]

My table structure & Query:

create table expertise(
ex_id number(14) primary key,
emp_id number(14) constraint expertise_fk1 references employee(emp_id),
job_id number(14) constraint expertise_fk2 references jobs(job_id),
expertise number(5)
);

create sequence expertise_seq
start with 1 increment by 1;

create or replace trigger expertise_trig
before insert on expertise
referencing new as new
for each row
begin
select expertise_seq.nextval into :new.ex_id from dual;
end;
/


SQL> select unique(emp_id) from expertise where expertise=5 and emp_id is not NULL;

    EMP_ID
----------
         1
        21

When am running Oracle SQL statement then its giving accurate results as I copy pasted above but when am running in servlet then it displays:

cv_id=1
cv_id=1
cv_id=21
cv_id=1
cv_id=21
cv_id=1
cv_id=21

Please note: cv_id is emp_id and this should be unique according to my SQL query but not getting the required results. Here is my code

if(type.equals("1")){       
     for(i = 0; i < expertise.length; i++) {  	
	
         	String query="Select distinct emp_id from expertise where expertise=? "+
		" and emp_id is not NULL"; 
      
         	PreparedStatement ps=cnn.prepareStatement(query);   
		if(!expertise[i].equals("")){ 
                 	ps.setString(1, expertise[i]);  
		}//Null Check                 
                 	rs=ps.executeQuery();    
                 	while(rs.next()){    
                    	String cv_id=rs.getString(1);  		
                    	out.println("cv_id="+cv_id+"<br>");                     
                        }    
             	ps.close();  

                      
     }//For loop    
 }// type 

I need output:

cv_id=1
cv_id=21

Where I am making mistake? Please help me to fix this problem

Thanks & best regards