Re: Oracle JDBC error

On Jul 27, 1:05 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
> 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

I'm using 1.6, but I've tried with 1.5 as well. I'm using the Oracle 10g 10.1.0.5 version of ojbc14.jar. I gave the latest 10.2.0.3 drivers a go as well, but to no avail (at least they worked!)

I'll keep plugging at it. Thanks for the input. Received on Thu Jul 26 2007 - 21:40:32 CDT