Hi James, I think you're right. The SQL helper on windows doesn't list INSTR as a function you can use to build queries, but it does have LOCATE. But it doesn't tell you what LOCATE does or what each of the params mean. Any idea where I can find some SQL doco? I've tried the net for DB2 SQL doc and not found much of use so far.
Thanks, Warwick -----Original Message----- From: James Mason [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 24, 2004 1:37 PM To: Slide Users Mailing List Subject: Re: No authorized routine named "INSTR" of type "FUNCTION" having com patible arguments was found Can you try executing the sql directly against the database? Maybe you can get a better error message that way. Also, I did some poking around and it looks like maybe DB2 needs to use LOCATE instead of INSTR. I know very very little about DB2, though ;). -James Warwick Burrows wrote: > Hi, > > Has anyone seen this exception with Slide 2.1b1? > > com.ibm.db2.jcc.b.SQLException: No authorized routine named "INSTR" > of type > "FUNCTION" having compatible arguments was found. > > I'm using the DB2RDBMSAdapter with DB2 Universal DB v8. I believe that > the DB2 client is at the same level. The exception comes from > executing an SQL statement created by the following code in > StandardRDBMSAdapter.java in the > retrieveRevisionDescriptors() method. eg. > > statement = connection.prepareStatement( > "select vh.REVISION_NO, b.BRANCH_STRING > from VERSION_HISTORY vh, BRANCH b, URI u > where vh.BRANCH_ID=b.BRANCH_ID and vh.URI_ID=u.URI_ID and > u.URI_STRING=? > order by " + convertRevisionNumberToComparable("vh.REVISION_NO")); > > statement.setString(1, uri.toString()); > res = statement.executeQuery(); > > Here is the SQL statement that it is actually trying to execute: > > select vh.REVISION_NO, b.BRANCH_STRING > from VERSION_HISTORY vh, BRANCH b, URI u > where vh.BRANCH_ID=b.BRANCH_ID and vh.URI_ID=u.URI_ID and > u.URI_STRING=? > order by > to_number(substr(vh.REVISION_NO,1,instr(vh.REVISION_NO,'.')-1)), > > to_number(substr(vh.REVISION_NO,instr(vh.REVISION_NO,'.')+1)) > > The "?" will be replaced by '/' which is the URI passed in. Is the > "instr" function not supposed to have two parameters? Any idea why it doesn't like > the vh.REVISION_NO and/or '.' parameters? Or could it be that one of the > "substr" functions are wrong. One takes 3 params and the other takes > 2?? > > Thanks, > Warwick > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
