On Jul 27, 12:18 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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
> --------------------------------------------------------------------------------
> ID
>
> SQL>
I'm using 10g. I use 'id' in all my tables, and they all work fine.
Received on Thu Jul 26 2007 - 19:52:07 CDT
