i need substitute following values select query. got error mentioned below
self.jobno = j-12060 qcactivity = c173 self.wrkarea = 1666339 cursor.execute("""select a.markerid, d.comments,a.status,a.x1,a.y1,a.x2,a.y2,c.errgroup,c.errdesc,c.category mdp_err_master a,(select markerid, max(recno) maxrecno mdp_err_master project_code = ':jobno' , errorcode ':jobno_:qcactivity%' , workarea ':workarea%' group markerid) b,mdp_err_config c,(select markerid, comments mdp_err_master project_code = ':jobno' , recno = 1 , errorcode ':jobno_:qcactivity%' , workarea ':workarea%') d where(a.markerid = b.markerid , a.recno = b.maxrecno , a.markerid = d.markerid)and a.project_code = ':jobno' , a.errorcode ':jobno_:qcactivity%' , a.workarea ':workarea%' , a.errorcode = c.errcode""", {"jobno" : str(self.jobno), "qcactivity" : str(qcactivity), "workarea" : str(self.wrkarea) }) traceback (most recent call last): file "d:\work\venkat\qgis\tools\globalerrormarker\globalerrormarker.py", line 272, in btndownloaderror_clicked "workarea" : str(self.wrkarea) databaseerror: ora-01036: illegal variable name/number
i think have misunderstood how bind variables work oracle , cx_oracle.
oracle recognises expression such :myvar in sql query bind variable placeholder. when encounters this, make note needs value variable before can run query, can still continue parsing query without value.
bind variable placeholders not work inside string literals. condition such project_code = ':jobno' match rows project_code actual six-character string :jobno, regardless of whether have bind parameter name jobno defined. instead, should write project_code = :jobno. don't worry telling oracle type of value jobno must contain; check you've got correct type when give value.
there few places attempting build like clauses concatenating bind variable placeholders. concatenation can still done, must done in sql using || operator instead. so, instead of writing ':workarea%', write :workarea || '%', , insted of writing ':jobno_:qcactivity%', write :jobno || '_' || :qcactivity || '%'.
i made these changes sql query, created couple of tables enough columns make query valid, , ran it. had no data run on, got no results back, database @ least parsed , ran query successfully. i've formatted query make bit easier read:
cursor.execute(""" select a.markerid, d.comments, a.status, a.x1, a.y1, a.x2, a.y2, c.errgroup, c.errdesc, c.category mdp_err_master a, (select markerid, max(recno) maxrecno mdp_err_master project_code = :jobno , errorcode :jobno || '_' || :qcactivity || '%' , workarea :workarea || '%' group markerid) b, mdp_err_config c, (select markerid, comments mdp_err_master project_code = :jobno , recno = 1 , errorcode :jobno || '_' || :qcactivity || '%' , workarea :workarea || '%') d a.markerid = b.markerid , a.recno = b.maxrecno , a.markerid = d.markerid , a.project_code = :jobno , a.errorcode :jobno || '_' || :qcactivity || '%' , a.workarea :workarea || '%' , a.errorcode = c.errcode""", {"jobno" : str(self.jobno), "qcactivity" : str(qcactivity), "workarea" : str(self.wrkarea) }) 
Comments
Post a Comment