How to Call Native Procedure with Variables

Follow

0

Eralper Yilmaz

• October 21, 2019 08:42

I am trying to call a native procedure with variables, how can I do that?

Following syntax fails with error message:

Processing exception for request XMBXnh6XoPRl.6 'TEIID30141 Invalid param name(s):
[@Country]. Name(s) of params without explicit values: [variable]'.

 

select
*
from
(
Call "HANA_B1P.native"
(
"request" =>'
SELECT * FROM "system-local.bw.bw2hana::ASOPCSD"
where asserorgc = '' || @Country || ''
',
"@Country" => '50000121'
)
) as a;;

 

 

Thanks in advance,

Eralper

• Comment actions Permalink

  Official comment

  

  Nick Golovin

  • October 31, 2019 19:18

  Hi Eralper, 

  the solution you posted certainly works, thank you. One thing one would need to care about in this case would be to make sure that all special characters like quote etc. in the parameters would be properly escaped. 

  Alternatively, you could use one of the following (Example for Postgres):

   

  call "PostgreSQL.native"(
  'select ? where 1=?','2',(select 1)
  );;

  or

  call "PostgreSQL.native"(
  "request" => 'select ? where 1=?',
  "variable" => array('2',(select 1))
  );;

  In this case, any special characters in the parameters would be escaped automatically. 

  Hope this helps,

  best regards,

  Nick

   

• 0

  Comment actions Permalink

  

  Eralper Yilmaz

  • October 30, 2019 08:26

  Hello,

  Is there a sample procedure call script where parameters are passed as part of the native call?

• 0

  Comment actions Permalink

  

  Eralper Yilmaz

  • October 31, 2019 12:28

  OK, a colleague suggested to use as follows; defining the parameter before calling the native procedure

   

  begin

  declare string country;
  country = 50000121;

  select
  *
  from
  (
  Call "HANA_B1P.native"
  (
  "request" =>'
  SELECT * FROM "system-local.bw.bw2hana::ASOPCSD"
  where asserorgc = '|| country ||'
  '
  )
  ) as a;

  end;;

Please sign in to leave a comment.