Replacing the "instr" function with "locate" and also replacing the
"to_number" function with "integer" seems to solve my problem. My questions,
to the original writer of the DB2RDBMSAdapter.java, are:
1. how does this work with DB2 currently?? Is this a DB2 version specific
difference? Should this change be applied back to the slide source tree?
2. is what I have done correct. I can't find doc for "locate" and "integer"
but from how they are used this seems to be correct.
The fix I made to my DB2RDBMSAdapter is to override the method
convertRevisionNumberToComparable() from CommonRDBMSAdapter. Eg. to override
the original:
protected String convertRevisionNumberToComparable(String
revisioNumber) {
return
"to_number(substr("+revisioNumber+",1,instr("+revisioNumber+",'.')-1)),
to_number(substr("+revisioNumber+",instr("+revisioNumber+",'.')+1))";
}
With:
protected String convertRevisionNumberToComparable(String
revisioNumber) {
return "integer(substr("+revisioNumber+",1,locate('.',
"+revisioNumber+")-1)),
integer(substr("+revisioNumber+",locate('.',
"+revisioNumber+")+1))";
}
Warwick
-----------------------------------------------------------
Warwick Burrows E2open
Senior Engineer 9600 Great Hills Trail, #325
http://www.e2open.com Austin TX 78759
-----------------------------------------------------------
-----Original Message-----
From: James Mason [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 24, 2004 1:58 PM
To: Slide Users Mailing List
Subject: 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:[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]
>
>
>
---------------------------------------------------------------------
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]
