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