Re: Oracle JDBC error

On Jul 26, 8:52 pm, Peter Ashford <peter.m.ashf..._at_gmail.com> wrote:
> 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.

I'd still steer clear of it, just because it is reserved.

However, I used your SQL (without *all* the columns, just sued the first two), and I couldn't duplicate it with JDK 1.5 and the 10.2.0.1 ojdbc14.jar. Have you asked Oracle?

HTH, STeve Received on Thu Jul 26 2007 - 20:05:16 CDT