Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle JDBC error

Re: Oracle JDBC error

From: <joeNOSPAM_at_BEA.com>
Date: Fri, 27 Jul 2007 00:30:56 -0000
Message-ID: <1185496256.926338.254830@22g2000hsm.googlegroups.com>


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 Received on Thu Jul 26 2007 - 19:30:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US