Re: Oracle JDBC error

From: Peter Ashford <peter.m.ashford_at_gmail.com>
Date: Thu, 26 Jul 2007 17:05:36 -0700
Message-ID: <1185494736.872016.87300@q75g2000hsh.googlegroups.com>

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 (?,?,?,?,?,?,?,?,?,?"+
                "           ?,?,?,?,?,?,?)");

Received on Thu Jul 26 2007 - 19:05:36 CDT