java - Oracle MERGE and prepared statement -
i have backup utility, workig on restore section. table:
create table "sbooks"."dev_corpus" ( "corpusid" number(9,0) not null enable, "corpus_name" varchar2(768 byte) not null enable, "corpuslastsync" date, primary key ("corpusid") in restore class primary key in table, if exists update row, if not insert row.
problem need pass parameters class (they not exist in table), how can this? suggestion? using preparedstatment or what? , how? please give examples or links sources.
more info: using oracle sql developer , java netbeans.
edit: trying in command window:
merge dev_corpus using (select corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain dual corpusid = 1000156 , corpus_name = 'sahar' , corpusdesc = 'sahaaaaa' , corpusimageids null , rocf1 null , rocf2 null , rocf3 null , rocc1 null , rocc2 null , rocc3 null , corpusactive null , corpusrunfrequency null , corpuslastrun null , corpuslastsync null , rocsettingid null , corpusaffinity null , corpusterms null , corpusdomain null) incoming on (a.corpusid = incoming.corpusid ) when matched update set corpusid = incoming.corpusid , corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain when not matched insert (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) values (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain) this real table:
create table "sbooks"."dev_corpus" ( "corpusid" number(9,0) not null enable, "corpus_name" varchar2(768 byte) not null enable, "corpusdesc" varchar2(4000 byte), "corpusimageids" varchar2(768 byte), "rocf1" float(63), "rocf2" float(63), "rocf3" float(63), "rocc1" float(63), "rocc2" float(63), "rocc3" float(63), "corpusactive" number(3,0), "corpusrunfrequency" number(3,0), "corpuslastrun" date, "corpuslastsync" date, "rocsettingid" number(3,0), "corpusaffinity" number(3,0), "corpusterms" varchar2(4000 byte), "corpusdomain" number(3,0), primary key ("corpusid") throws me error:
error @ command line:2 column:644 error report: sql error: ora-00904: "corpusdomain": invalid identifier 00904. 00000 - "%s: invalid identifier" *cause: *action: .
i dont know missing. u have idea?
thanks!
edit2: works in comand win:
merge dev_corpus using (select 1000156 corpusid, 'sss2' corpus_name, 'sahaaaaaar' corpusdesc, null corpusimageids, null rocf1, null rocf2, null rocf3, null rocc1, null rocc2, null rocc3, null corpusactive, null corpusrunfrequency, null corpuslastrun, null corpuslastsync, null rocsettingid, null corpusaffinity, null corpusterms, null corpusdomain dual) incoming on (a.corpusid = incoming.corpusid ) when matched update set corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain when not matched insert (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) values (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain) but in java class nooot works in update cases, if insert case work. in case of update not throw error freezes untill delete record db, inserts it, no update!. find wrong in code?
preparedstatement = dbconnection.preparestatement("merge dev_corpus " + "using (select ? corpusid, ? corpus_name, ? corpusdesc, ? corpusimageids, ? rocf1, ? rocf2, ? rocf3, ? rocc1, ? rocc2, ? rocc3, ? corpusactive, ? corpusrunfrequency, ? corpuslastrun, ? corpuslastsync, ? rocsettingid, ? corpusaffinity, ? corpusterms, ? corpusdomain dual) incoming " + "on (a.corpusid = incoming.corpusid ) " + "when matched " + "update set corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain " + "when not matched " + "insert (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) " + "values (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)"); thanks!
if you're using oracle you'll need focus on merge statement - insert ... on duplicate key doesn't work in oracle.
i don't know if problem, there syntax errors in merge posted:
merge dev_corpus using (select * dual (corpusid=?, corpus_name=?, corpuslastsync=?) ^^^^^^^^^^^^^^^^^^^^^^ (1) on (a.corpusid = incoming.corpusid ) when matched update set (a.corpus_name = incoming.corpus_name , a.corpuslastsync = incoming.corpuslastsync ) ^^^^^^^^^^^^^^^^^^ (2) when not matched insert (a.corpusid, a.corpus_name, a.corpuslastsync) values (incoming.corpusid, incoming.corpus_name, incoming.corpuslastsync) problem (1): have unclosed parenthesis, plus multiple where conditions should separated and, not comma. here's corrected version, indented doesn't require horizontal scrolling:
using ( select * dual (corpusid=? , corpus_name=? , corpuslastsync=?)) the parentheses surrounding where conditions optional in case, work too:
using ( select * dual corpusid=? , corpus_name=? , corpuslastsync=?) problem (2): parentheses after set, , here need comma instead of and. should (as above, i've indented can read without horizontal scrolling)
update set a.corpus_name = incoming.corpus_name, a.corpuslastsync = incoming.corpuslastsync that should take care of syntax errors, of course that's no guarantee results expected :)
finally, mentioned in comments, try out in sqlplus or sql developer using test values (not parameters). it'll much, easier debug way. when you've got merge behaving correctly can move java code , parameterize it. best of luck!
addendum updated question
first of all, news you're using sql developer work out! disregard last paragraph above :)
second, includes and corpuslastsync = null. when checking null, need is null, not = null:
... , corpuslastsync null finally, i'm pretty sure specific error you're getting latest attempt due aliases on line:
insert (a.corpusid, a.corpus_name, a.corpuslastsync) try instead:
insert (corpusid, corpus_name, corpuslastsync) oracle knows columns named here belong dev_corpus table, aliased a.
Comments
Post a Comment