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]
