Mailing-List: contact tomcat-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Tomcat, PreparedStatement JDBC problem
Date: Mon, 10 Feb 2003 10:21:33 -0800
Message-ID: <C1B59B760D2C534B9A94AD93A6F0E25A7331F8@sfoexc01.nonstop.com>
Thread-Topic: Tomcat, PreparedStatement JDBC problem
Thread-Index: AcLRMKD60fCKNYHkR0mT7J/r15g6uAAADN4w
From: "Filip Hanik" <Filip.Hanik@evant.com>
To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>

It is a sql problem

if(roleId !=3D -1)
{
pstmt =3D con.prepareStatement("INSERT INTO ROLE (ID,NAME,OWNERID) =
VALUES  (?,?,?)");
                                               ^^^^^^^^^^^^^^^^^^
pstmt.setInt(1, roleId);
pstmt.setString(2, roleName);
pstmt.setInt(3, ownerId);
}
else
{
pstmt =3D con.prepareStatement("INSERT INTO ROLE (NAME,OWNERID) VALUES  =
(?,?)");
                                               ^^^^^^^^^^^^^^^^^^
pstmt.setString(1, roleName);
pstmt.setInt(2, ownerId);
}

-----Original Message-----
From: Mufaddal [mailto:mufaddal@wmotion.com]
Sent: Sunday, February 09, 2003 9:46 PM
To: Tomcat List
Subject: Tomcat, PreparedStatement JDBC problem


Hi Everybody

I am using tomcat on Mac OS X. One of my servlets does the following:

1. I am creating a table ROLE as follows:
________________________________________________________________________ =

_______________

             stmt.executeUpdate( "CREATE TABLE ROLE ("			+
             "ID			INTEGER NOT NULL AUTO_INCREMENT, "    	+
             "NAME	    	VARCHAR(20) NOT NULL, "			+
             "OWNERID		INTEGER NOT NULL, "			+
             "PRIMARY KEY( ID )"						+
             ")");
________________________________________________________________________ =

_______________


2. I want to insert (ID, name, ownerid) in the above table .. if ID !=3D =
=20
-1 and only (name, ownerid) if ID =3D=3D -1;
    I do this as follows:
________________________________________________________________________ =

_______________

             PreparedStatement pstmt =3D null;

             if(roleId !=3D -1)
             {
                 pstmt =3D con.prepareStatement("INSERT INTO ROLE VALUES =
=20
(?,?,?)");
                 pstmt.setInt(1, roleId);
                 pstmt.setString(2, roleName);
                 pstmt.setInt(3, ownerId);
              }
             else
             {
                 pstmt =3D con.prepareStatement("INSERT INTO ROLE VALUES =
=20
(?,?)");
                 pstmt.setString(1, roleName);
                 pstmt.setInt(2, ownerId);
             }

             pstmt.executeUpdate();
________________________________________________________________________ =

_______________

It throws an exception at pstmt.executeUpdate() .. saying:
________________________________________________________________________ =

_______________
java.sql.SQLException: General error,  message from server: "Column =20
count doesn't match value count at row 1"
________________________________________________________________________ =

_______________

Therefore I understood that maybe i need to make a change to the above =20
code as follows:
________________________________________________________________________ =

_______________
			...
		   // SAME AS BEFORE 	=09
			...
		   else
             {
                 pstmt =3D con.prepareStatement("INSERT INTO ROLE VALUES =
=20
(?,?)");
                 pstmt.setString(2, roleName);
                 pstmt.setInt(3, ownerId);
             }

             pstmt.executeUpdate();

________________________________________________________________________ =

_______________
Now it gives me an error saying:
________________________________________________________________________ =

_______________
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 2
________________________________________________________________________ =

_______________

Does anybody know what i am doing wrong here ?

Thanks in advance
Mufaddal.

---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org