Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

 
Eli Ran
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I have this code

String sql = " SELECT " +
" * " +
" FROM " +
" population_users pu " +
" LEFT JOIN population_entries pe on pu.curr_population_entry_id = pe.id " +
" LEFT JOIN populations p on pe.population_id = p.id, " +
" population_entries_hist peh "+
" LEFT JOIN population_entries pe_old_pop on peh.population_entry_id = pe_old_pop.id " +
" LEFT JOIN populations p_old_pop on pe_old_pop.population_id = p_old_pop.id " +
" WHERE " +
" pu.lock_history_id = peh.id " +
" AND peh.writer_id = ?";


ps = con.prepareStatement(sql);
ps.setLong(1, writerId);


Now... As you can see, I have no time/date reference in this query, and I the value that I'm setting is:
long writerId = 292 (It's not null)

Nevertheless I'm getting this weird SQL exception.
We are using Oracle 11G version 1.
Does anyone have any idea what could be the reason for that exception?

Thanks.
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Eli.

Are you shure that that exception comes from this query?
Can you post the definition of the tables, and the complete piece of code: the try block with prepare, bind, execute, and looping the resultset, and the catch and finally part?
 
Eli Ran
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey,
First... Thanks.

Second...
Yes I'm sure of that, as the full exception direct me to line number 953 which is the "rs = ps.executeQuery();" that comes right after the "ps.setLong(1, writerId); "

This is the full function (we don't have a catch block since we it throws the SQL exception)



about the tables definitions... they are quite big...
do you think it would be enough if I'll give you the keys which we use to do the joins?
I really don't think that it has something to do with the result set looping, since the exception occurs before it gets there....

 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have added code tags around your code. Makes the post more readable.
Here is an explanation on how to use this feature.

we don't have a catch block since we it throws the SQL exception
Can you temporary add a catch block for testing sake?

about the tables definitions... they are quite big...
do you think it would be enough if I'll give you the keys which we use to do the joins?
Yes, the fields used for joining and the fields used for filtering.

Last question: why do you mix two ways of joining?
One time you use the 'a left join b on ...' mechanism
Other time you use 'from a, b where a.id = b.id ...' mechanism
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, and I forgot:

are you using the correct version of the jdbc driver, and does the query work in sql*plus?
 
Eli Ran
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, Thanks again for the tip.

1) About the catch block, I can add one, but in test it's working just fine.
and also there's a catch block around this function.




2) About the different join mechanism, Does it matter if I use the FULL Join in the WHERE statement and the LEFT ones in the FROM?...
It just look more readable to me.

3) About the last 2 questions:
Yes, the query does work in sql*plus

BUT!! we did find out that we are not using in the correct version of jdbc driver... Do you think that might be the reason?...



Anyway here are the tables desc:


CREATE TABLE "USER"."POPULATION_USERS"
(
"ID" NUMBER NOT NULL ENABLE,
"USER_ID" NUMBER,
"CONTACT_ID" NUMBER,
"CURR_ASSIGNED_WRITER_ID" NUMBER,
"CURR_POPULATION_ENTRY_ID" NUMBER,
)

CREATE TABLE "USER"."POPULATION_ENTRIES"
(
"ID" NUMBER NOT NULL ENABLE,
"POPULATION_ID" NUMBER NOT NULL ENABLE,
CONSTRAINT "POPULATION_ENTRIES_POP_FK" FOREIGN KEY ("POPULATION_ID") REFERENCES "USER"."POPULATIONS" ("ID") ENABLE
)

CREATE TABLE "USER"."POPULATIONS"
(
"ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(30 CHAR) NOT NULL ENABLE,
)

CREATE TABLE "USER"."POPULATION_ENTRIES_HIST"
(
"ID" NUMBER NOT NULL ENABLE,
"POPULATION_ENTRY_ID" NUMBER NOT NULL ENABLE,
CONSTRAINT "POPULATION_ENTRIES_HIST_PE_FK" FOREIGN KEY ("POPULATION_ENTRY_ID") REFERENCES "USER"."POPULATION_ENTRIES" ("ID") ENABLE,

)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic