You are viewing a plain text version of this content. The canonical link for it is here.
Posted to slide-user@jakarta.apache.org by James Mason <ma...@apache.org> on 2004/08/24 20:57:41 UTC

Re: No authorized routine named "INSTR" of type "FUNCTION" having com patible arguments was found

No idea idea about docs. I ran across this: 
http://sqlzoo.net/howto/source/s.pl.htm?file=tip238311&e=db2

The sql for db2 is invalid, but this works:

SELECT name,
        LOCATE(' ',name),
        SUBSTR(name,1,LOCATE(' ',name))
   FROM cia
   WHERE name LIKE '% %'

-James

Warwick Burrows wrote:

> 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:masonjm@apache.org] 
> 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: slide-user-unsubscribe@jakarta.apache.org
>>For additional commands, e-mail: slide-user-help@jakarta.apache.org
>>
>>
>>
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: slide-user-help@jakarta.apache.org
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: slide-user-help@jakarta.apache.org
> 
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org