How do I run a UDF in a LiveReport?

I have created several Livereports and run them just fine. I needed to get the full path for containers for a client report. I found a UDF in the knowledge base to do this. I keyed in the code into the LiveReport SQL area using "Create Function..." I now want to call that function, but I cannot seem to get it to wor. I am guessing it is a LiveReport newbie issue, but i cannot find the problem.

Here is my SQL from the LiveReport. I am only putting on one node to verifry the function works:

CREATE FUNCTION getFullPath (@DataID INT)
RETURNS varChar(8000) AS
BEGIN

    DECLARE @TmpParentID int
    DECLARE @FullPath varChar(8000)
    DECLARE @Name varChar(255)
    DECLARE @TmpName varChar(255)
    IF @dataID <> 0
    BEGIN
        WHILE @DataID <> -1
        BEGIN
            SELECT @TmpParentID = ParentID FROM dtree WHERE dataID = @DataID
@rowcount = 0
                BREAK
            IF @dataID < -1 AND @TmpParentID = -1
                BEGIN
                     SET @DataID = ABS(@DataID)
                END
            ELSE
                BEGIN
                    SET @DataID = @TmpParentID
                END
            SET @TmpName = @Name
            SELECT @Name=Name FROM dtree WHERE dataID = @DataID
            if @FullPath IS NULL
                BEGIN
                    Set @FullPath = @Name
                END
            ELSE
                BEGIN
                    IF @TMPName <> @Name
                        BEGIN
                            SET @FullPath = @Name + ':' + @FullPath
                        END
                END
END
    END

RETURN @FullPath
END

SELECT a.* FROM dtree AS a WHERE a.dataid = 1529215

This is the error I receive once I added the last SELECT after the function:

[[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'select'

Any guidance would be appreciated.

Find more posts tagged with

Comments

Appu_Nair1

Hereare some observations.When you do these kind of DML things within a LiveReport you are severely restricted in seeing the real errors,so I would use a proper GUI like SQL Server Managment studio(Free absolutely) to test the function.Secondly in your query you have not called your function what I mean is if the function was created sucessfully you need to call it

for e.g select dataid,[databaseuser].GetFullPath(dataid) ,subtype,name from dtree where dataid=1234

If the query works in SQLserver proper there is no reason barring(whitespace,CR,LF problems)

I took these screencaps from an old SQLserver VM with livelink I had I really cut and pasted

Stephen Fisher's code to test it.

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objaction=overview&objid=32800311

Appu_Nair1

BTW once you create a function you are re-using it with parameters it should not be like you are creating the function within the LR textbox and then trying to read from the new function.That kind is called a CTE in SQLserver.CTE's are used to emulate the "connect by" traversal function of Oracle.OT expects you to use dtreeancestors table to rid yourself of any CTE's functions etc as it also keeps ancestry info very effectively.

Donna_Pellett

Thank you for the quick response.

I was not calling the function yet. I was just trying to get some code to run with the Create Function code in there. I did try to run the code calling the function as you mentioned, but I get the same error.

Maybe there is some white space or line feeds that I am not seeing. I can try rekeying the whole thing again just to make sure.

Your response at least tells me that I understand how to create and call the UDF. I thought I was maybe missing something there.

Colin_Schmidt

I test mine first in SQL Developer. You could use TOAD or SQL Razor or some other tool that’ll give back more clarifying errors. There are times, though, when it works in one, but not the other. Data will come back into the LR that it seems to find problematic and it fails to complete.

Colin J. Schmidt

MSLive/Livelink Admin

Tulsa, OK 74172

Colin.Schmidt@williams.com

If you have received this message in error, please reply to advise the sender of the error and then immediately delete this message.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, February 08, 2013 10:27 AM
To: eLink Recipient
Subject: How do I run a UDF in a LiveReport?

How do I run a UDF in a LiveReport?

Posted by appu.n.nair@conocophillips.com (Nair, Appu) On 02-08-2013 11:23

for e.g select dataid,[databaseuser].GetFullPath(dataid) ,subtype,name from dtree where dataid=1234

If the query works in SQLserver proper there is no reason barring(whitespace,CR,LF problems)

I took these screencaps from an old SQLserver VM with livelink I had I really cut and pasted

Stephen Fisher's code to test it.

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objaction=overview&objid=32800311

[To post a comment, use the normal reply function]
Topic:	How do I run a UDF in a LiveReport?
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Donna_Pellett

Thanks I will try the code in one of the options you suggest. I appreciate the guidance.

Donna_Pellett

It was truly a newbie mistake. I was trying to create and call the function in one LiveReport. I had to put the Create Function code in one LiveReport and run it with updat on to add it to the database, then I could call the function in my LiveReport.

Thanks for helping. It is nice to have a place to reach out to those who will help us newbies.