BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Custom Function Not working


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
Denso
Senior Member
Senior Member



Joined: 22 Jul 2010

Posts: 75



PostPosted: Fri Mar 04, 2011 6:55 am 
Post subject: Custom Function Not working

Hi guys,
i created a function like this ;
Code:

BEGIN
   TRY
      $vCount = SQL( 'DS_TEST' , 'SELECT FN_VALIDFILENAME({$pFileName}) FROM DUAL' );
   CATCH(ALL)
      $vCount = 99;
END
RETURN $vCount;


So i have also a oracle function called FN_VALIDFILENAME(Filename in)
function does this ;
it searchs a table existance from a table ,
if given table exits it returns 1 else 0 thats all.

it perfectly works when i try it manuelly like ;
Code:

SQL( 'DS_TEST' , 'SELECT FN_VALIDFILENAME('test.csv') FROM DUAL' )


but When i try it with function ;
Code:


$FileName = 'test.csv';

print ( SQL( 'DS_TEST' , 'SELECT FN_VALIDFILENAME({$FileName}) FROM DUAL' ) ) ;

print( Fn_IsValidName($FileName));

print( Fn_IsValidName('$FileName'));

print( Fn_IsValidName('{$FileName}'));

print( Fn_IsValidName('[$FileName]'));

print( Fn_IsValidName('[$FileName]'));

Only first print function returns 1 and other return 0,
i dont know what i m doing wrong,i try many opitons to work it out but i coulnt.

Any idea?
Back to top
ScoobyDoo
Principal Member
Principal Member



Joined: 10 May 2007

Posts: 161


flag
PostPosted: Tue Mar 08, 2011 9:17 am 
Post subject: Re: Custom Function Not working

You can assume then that your SQL in the function is executing as you're getting 0 back rather than 99 because of an error.

Because you're used {} in the SQL function you only need to pass the file name as is: i.e call it with the command:

print( Fn_IsValidName($FileName));


Are you sure that the $pFileName is set as a varchar and is long enough?

If it is then add a print statement inside the custom function just to print out the contents of $pFileName to the log.

Cant see anything wrong with what you're trying to do so I'm guessing its a coding error somewhere icon_eek.gif
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4280
Location: Denver Area


flag
PostPosted: Tue Mar 08, 2011 3:47 pm 
Post subject: Re: Custom Function Not working

See my comments in-line...
Code:
$FileName = 'test.csv';

---- Normally this next line should work
print( Fn_IsValidName($FileName));

---- This will never work.  It means you are passing the string $FileName, not the contents of the variable.
print( Fn_IsValidName('$FileName'));

---- This will never work.  It seems like an error would be in order as the net result is ''test.csv''.  I'm scratching my head trying to figure out how DS really interprets the value passed to the function.
print( Fn_IsValidName('{$FileName}'));

---- Normally this next line should work
print( Fn_IsValidName('[$FileName]'));


If all else fails you could import the database function FN_VALIDFILENAME into the Datastore. That would make it easier to call and there is no need to use the SQL() function.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
Denso
Senior Member
Senior Member



Joined: 22 Jul 2010

Posts: 75



PostPosted: Fri Mar 11, 2011 7:53 am 
Post subject: Re: Custom Function Not working

Well i guess i found the problem,

its because i set same name on both global and local variable name.

i mean i had 2 same name on global and local variable,
when i delete the global one it worked fine .
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Sat Mar 12, 2011 2:10 pm 
Post subject: Re: Custom Function Not working

Excellent. I would have never found out...
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0312 seconds using 17 queries. (SQL 0.0026 Parse 0.0009 Other 0.0277)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo