Re: Oracle JDBC error

On Jul 27, 12:30 pm, "joeNOS..._at_BEA.com" <joe.weinst..._at_gmail.com> wrote:
> On Jul 26, 5:05 pm, Peter Ashford <peter.m.ashf..._at_gmail.com> wrote:
>
>
>
> > On Jul 26, 8:09 pm, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
>
> > > Peter Ashford <peter.m.ashf..._at_gmail.com> wrote:
> > > > I'm getting this error doing an SQL insert:
>
> > > > java.sql.SQLException: ORA-01745: invalid host/bind variable name
>
> > > > I know there's no invalid names in the insert because I can insert
> > > > data into the table by hand over sqlplus.
>
> > > > I'm using oracle's ojbdc14 jdbc driver and Java 1.6.
>
> > > > Any ideas?
>
> > > You probably have invalid host variable names.
>
> > No, I don't. I'm already accessing the database successfully with
> > this driver. I have been for over a year, with ,amy different
> > databases. This is the first time I've had this issue.
>
> > > Check your code! This has nothing to do with INSERTS via sqlplus.
>
> > > If you are clueless, post a section of your code so people can help you
> > > spot what's wrong.
> > > Tell the table definition too!
>
> > I wouldn't describe myself as "clueless". I've been using JDBC and
> > Oracle successfuly for some time. Nevertheless, here's the table
> > definition & prepared statement code:
>
> > CREATE TABLE departments (
> > id NUMBER(10) NOT NULL,
> > deptName VARCHAR(128) NOT NULL,
> > schoolName VARCHAR(128),
> > division VARCHAR(128) NOT NULL,
> > dean VARCHAR(128),
> > AVC VARCHAR(128),
> > HOD VARCHAR(128),
> > admin VARCHAR(128),
> > adminPhone VARCHAR(32),
> > adminEmail VARCHAR(256),
> > streetAddress VARCHAR(256),
> > city VARCHAR(64),
> > cbCostCentre VARCHAR(64),
> > cbOHAccountCode VARCHAR(12),
> > cbOHDisection NUMBER(3),
> > comments VARCHAR(256),
> > schoolLiasonAdmin VARCHAR(256),
> > dateEntered DATE NOT NULL,
> > lastModifed DATE DEFAULT current_date NOT NULL,
> > CONSTRAINT departments_idx PRIMARY KEY (id)
> > );
>
> > CREATE SEQUENCE seq_departments_ID
> > MINVALUE 1
> > MAXVALUE 999999999999999999
> > START WITH 1
> > INCREMENT BY 1
> > NOCACHE;
>
> > CREATE OR REPLACE TRIGGER trg_departments_insert
> > BEFORE INSERT ON departments
> > FOR EACH ROW
> > BEGIN
> > IF :new.id IS NULL THEN
> > SELECT seq_departments_ID.nextval INTO :new.id
> > FROM DUAL;
> > END IF;
> > END;
> > /
>
> > -------
>
> > PreparedStatement pStmt = db.prepareStatement(
> > "INSERT INTO departments ("+
> > " deptName, schoolName, division, dean, AVC, HOD,
> > admin, adminPhone, adminEmail," +
> > " streetAddress, city, cbCostCentre,
> > cbOHAccountCode, cbOHDisection, comments, " +
> > " schoolLiasonAdmin, dateEntered)" +
> > " VALUES (?,?,?,?,?,?,?,?,?,?"+
> > " ?,?,?,?,?,?,?)");
>
> Just for complete JDBC coverage, are you absolutely
> sure you're setting all the variables (17) in your prepared statement?
> Perhaps, 14 setStrings(), one setInt() and one setTimestamp()?
>
> Joe Weinstein at BEA Systems

Yes, I've checked that previously. To be specific, here is the code which fills the statement:

  int i=0;

            pStmt.setString(++i, row[D_DEPT_NAME]);
            pStmt.setString(++i, row[D_SCHOOL_NAME]);
            pStmt.setString(++i, row[D_DIVISION]);
            pStmt.setString(++i, row[D_DEAN]);
            pStmt.setString(++i, row[D_AVC]);
            pStmt.setString(++i, row[D_HOD]);
            pStmt.setString(++i, row[D_DEPT_ADMIN]);
            pStmt.setString(++i, row[D_ADMIN_PHONE]);
            pStmt.setString(++i, row[D_ADMIN_EMAIL]);
            pStmt.setString(++i, row[D_ADDRESS]);
            pStmt.setString(++i, row[D_CITY]);
            pStmt.setString(++i, row[D_CB_COST_CENTRE]);
            pStmt.setString(++i, row[D_CB_OH_ACCOUNT]);
            pStmt.setString(++i, row[D_CB_OH_DISECTION]);
            pStmt.setString(++i, comments);
            pStmt.setString(++i, row[D_SCHOOL_LIASON_ADMIN]);
            pStmt.setDate(  ++i, dateEntered);