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 Warwick Burrows <wa...@e2open.com> on 2004/08/24 20:10:49 UTC

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

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

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

Posted by James Mason <ma...@apache.org>.
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