Re: Oracle JDBC error

On Jul 26, 7:59 pm, Peter Ashford <peter.m.ashf..._at_gmail.com> wrote:
> On Jul 27, 3:15 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
>
>
>
> > On Jul 26, 12:39 am, 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?
>
> > Peter,
>
> > You (or a library you are using) are very likely passing invalid,
> > un-parsable SQL to Oracle. Are you using JDBC's
> > Connection.prepareStatement()? If so, post the SQL INSERT
> > statement you are supplying.
>
> Ok. Thanks for the very fullsome reply, by the way.
>
> Here's the java code for the prepared statement:
>
> 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 (?,?,?,?,?,?,?,?,?,?"+
> " ?,?,?,?,?,?,?)");
>
> Note that I can do an insert like this by hand using SQLPLUS. I can
> also read / write other tables using the JDBC driver in the same
> application.
>
>
>
> > Oracle, when accessed via JDBC and similar libraries, processes the
> > SQL statement in two phases: (1) preparing, where the SQL is
> > parsed/compiled and (2) execution, where the SQL is actually run,
> > including the substuting of actual values for "bind variable"
> > placeholders.
>
> > You are not getting past step one (1) as evidenced by ORA-01745
> > which is a parse time error. The use of a reserved word or
> > invalid characters in a "colon variable" will trigger this.
> > E.g.:
>
> > SELECT :view FROM dual
>
> > or
>
> > SELECT :&^ FROM dual
>
> > Your test in SQL*Plus (probably?) did not use bind variables and
> > so is not meaningful for diagnosing the problem.
>
> No, I'd read the description of the ORA error, and it seemed like it
> didn't apply to me - this is a straight forward insert. There *is* a
> trigger to insert a unique id which uses a ":" variable in the
> database definition:
>
> 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;
> /
>
> ...but I thought that was pretty harmless. I use the same kind of
> code for all my tables' uids.
>
> > Pls. post that SQL!
>
> Done. Thanks again for all your feedback.

ID Is a keyword, perhaps new in 10G, I don't have 9i handy to test, and I don't know what you are running...

SQL> select keyword from v$reserved_words where keyword = 'ID';

KEYWORD

SQL> Received on Thu Jul 26 2007 - 19:18:01 CDT