Oracle sql exception:

[NitishK Kumar]

Hi I have written the following servlet program.

package com.nitish.servlet;
import javax.servlet.*;
import java.io.*;
import java.sql.*;
import oracle.sql.*;

public class SubmitServlet extends GenericServlet{
	public void init()throws ServletException{
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}//try
		catch(Exception e){
			throw new ServletException("Unable to Load the Driver");
		}//catch
	}//init

	public void service(ServletRequest req,ServletResponse res)throws ServletException,IOException{
        String name=req.getParameter("name");
		String phno=req.getParameter("phno");
		String college=req.getParameter("college");
		String university=req.getParameter("university");
		String email=req.getParameter("email");
		String course[]=req.getParameterValues("course");
		Connection con=null;
		PreparedStatement ps=null;
		PrintWriter out=res.getWriter();
		try{
			con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","nitish");
			ps=con.prepareStatement("insert into stenquiry values(?,?,?,?,?,?)");
			ps.setString(1,name);
			ps.setString(2,phno);
			ps.setString(3,college);
			ps.setString(4,university);
			ps.setString(5,email);
            ArrayDescriptor ad = ArrayDescriptor.createDescriptor("COURSEARRAY",con); 
			ARRAY a = new ARRAY(ad,con,course); 
			ps.setArray(6,a);
			int i=ps.executeUpdate();
			if(i==1){
				out.println("Records Inserted Sucessfully");
			}//if
			else{
				out.println("Records Not Inserted");
			}//else
		}//try
		catch(Exception e){e.printStackTrace();}
		finally{
			try{
				ps.close();
				con.close();
			}//try
			catch(Exception e){e.printStackTrace();}
		}//finally
	}//service
}//class

When I run this application, I got the following message in my server console:
java.sql.SQLException: invalid name pattern: SYSTEM.course
I am not getting what is this. I am using oracle 10g express edition and tomcat 5.5.9. I tried it with oracle 11g release2 also, but still facing the same problem. However, I used array in an standalone application in the same manner as I have used here and its working perfect, but here its not working. Please help.

[Bear Bibeault]

Moved to the JDBC forum.

[Martin Vashko]

Are you trying to insert several records at once? If so, it is not possible this way. You need to execute the statement repeatedly, once for every course.

If you're really trying to insert an array into a column, what is the database type of the column? The most common types certainly do not support inserting arrays into them.

[NitishK Kumar]

Martin Vajsar wrote:Are you trying to insert several records at once? If so, it is not possible this way. You need to execute the statement repeatedly, once for every course.

If you're really trying to insert an array into a column, what is the database type of the column? The most common types certainly do not support inserting arrays into them.

Why not, after all I am using prepared statement. And, as I have already mention, I tried the same with an standalone application and its working fine. My database array type is varchar2().

[NitishK Kumar]

Bear Bibeault wrote:Moved to the JDBC forum.

Sorry, by mistake I posted my query in wrong forum.

[Martin Vashko]

NitishK Kumar wrote:
Why not, after all I am using prepared statement. And, as I have already mention, I tried the same with an standalone application and its working fine. My database array type is varchar2().

Why what? What are you trying to achieve? Insert one record with array stored in a column, or insert several records? The answer depends on that.

Inserting several records: by definition INSERT ... VALUES statement creates just one record.

Inserting array: VARCHAR2 datatype cannot hold an array. In Oracle, you can create a VARRAY type and use it in a table (though I have no experience with this), but then it would not be a simple VARCHAR2 column.

Can you post the code that worked (the standalone application you mentioned), including table creation statement? What did the value looked like when you selected it back from the table using some tool (TOAD, SQL Developer)?

[NitishK Kumar]

Martin Vajsar wrote:
Why what? What are you trying to achieve? Insert one record with array stored in a column, or insert several records? The answer depends on that.

Inserting several records: by definition INSERT ... VALUES statement creates just one record.

Inserting array: VARCHAR2 datatype cannot hold an array. In Oracle, you can create a VARRAY type and use it in a table (though I have no experience with this), but then it would not be a simple VARCHAR2 column.

Can you post the code that worked (the standalone application you mentioned), including table creation statement? What did the value looked like when you selected it back from the table using some tool (TOAD, SQL Developer)?

I think there is some misunderstanding. I am inserting only one record at a time. And the array type is VARRAY of type varchar2.
Anyways I am posting my standalone application's code.

[NitishK Kumar]

package com.stech.jdbc2.array;
import java.sql.*;
import oracle.sql.*;
public class ArrayStore1{
	public static void main(String []s)throws Exception{
		Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","nitish");
		PreparedStatement ps = con.prepareStatement("insert into stdetails1 values(?,?,?)");
		ps.setInt(1,2);
		ps.setString(2,"Most2");
		ArrayDescriptor ad = ArrayDescriptor.createDescriptor("MARKSARRAY",con);
		int marks[] = {90,90,90,90,90};
		ARRAY a = new ARRAY(ad,con,marks); 
		ps.setArray(3,a);
		int count = ps.executeUpdate();
		System.out.println("RowsUpdated::"+count);
	}//main
}//class

Well here's the code that you asked for. I hadn't used any tool.
I simply fired the query:
select * from stdetails1;
I had attached the output screenshot.

array type creation statement:
create type marksarray as varray(5) of number;

table creation statement:
create table stdetails1 (sr number,name varchar2(20),marks MARKSARRAY);

[Martin Vashko]

In that case it should work. I was under impression you're trying to insert multiple records as you print out message "Records inserted successfully" and disregarded other possibilities a bit.

I use similar technique to pass varrays into stored procedures in Oracle 10g, so yes, this should work. If you google for the error message ("java.sql.sqlexception invalid name pattern oracle"), there are some links that suggest the problem is that the name of the type in the createDescriptor call could not be resolved.

Do you have everything (table, type) in the same schema (SYSTEM)?

[NitishK Kumar]

Ya, I have everything in the same schema. Earlier to this also, I applied the same methodology for two more applications. All of them worked well. Only in this one, I am facing problem. And I am not getting what is the error.

[Martin Vashko]

Try to use full name (SYSTEM.COURSEARRAY) instead of COURSEARRAY in the createDescriptor call. I recall that the method has problems with unqualified names. If you need to refer to current schema without specifying its name, .COURSEARRAY (with period at the beginning) should also work.

[NitishK Kumar]

I tried it again the same problem.
But why is it giving exception saying invalid name pattern: SYSTEM.course when I am using SYSTEM.COURSEARRAY as you have suggested to me.

[Martin Vashko]

Is COURSEARRAY the type, or a synonym to the type? I've found indications in some forums that the method has problems with synonyms and it might be necessary to use fully qualified real name.

Other than this, I can't think of anything. Sorry for the time you've lost with me before we got to the core of the problem.

[NitishK Kumar]

Its not so. At least you tried to help. Thanks for your cooperation. I am trying to get the solution.

[NitishK Kumar]

I solved my problem and now its working.

[Martin Vashko]

NitishK Kumar wrote:I solved my problem and now its working.

Glad you got it working. Wouldn't you mind posting your solution? It might be interesting for others should they encounter similar problems.