Skip to main content
Solved

APIServer to query another table before calling procedure

  • June 10, 2026
  • 1 reply
  • 49 views
E

Using CData APIServer™ 2026 - 26.1.9585.0

I created an API which connects to an Oracle stored procedure and another API which connects to Azure AD. I am trying to modify incoming request before calling the stored procedure.

For example, user sends { “WHOAMI”: “ [removed by moderator] ” } request, API Server calls Azure AD and gets sAMAccountName, then the stored procedure is called with { “WHOAMI”: “username” }

I tried something like following but I still calls procedure without changing value of WHOAMI:

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">
 <api:info title="[CDATA].[WHOAMI]" connection="ora2" restrictUser="" description="whoami">
 <input name="WHOAMI" type="string" required="true" desc="e-mail" />
 </api:info>

 <api:script method="POST">
 <api:set attr="sAMAccountName" value="" />
 <api:call op="apiSelect"
 in="table=[CData].[AzureAD].[Users]|filter=mail='[WHOAMI]'|columns=onPremisesSamAccountName">
 <api:set attr="sAMAccountName" value="[onPremisesSamAccountName]" />
 </api:call>

 <api:set attr="WHOAMI" value="[sAMAccountName]" />

 <api:push op="apiExecuteSP" />

 </api:script>
</api:script>

Best answer by Ethem Q

​@erdem Thank you for sharing your use case with the CData Community! Based on what you have shared I wanted to let you know that what you are trying in the modified script will not work unfortunately. The first thing to keep in mind is that you cannot really access other tables across connections like you are trying to do accessing the Users table from the AzureAD connection in the same script with the Oracle connection. This is not something supported within API Server. 

However one thing that can be done here through some careful client-side scripting is approaching that in multiple steps:

  1. Get value from AzureAD first
  2. POST Call with value obtained from #1

If you want to learn more on how to achieve this you will probably need some assistance from our support team so I would recommend you submit a ticket in our support portal here.

 

    1 reply

    Ethem Q
    Forum|alt.badge.img
    • Ethem Q
    • Employee
    • Answer
    • June 19, 2026

    ​@erdem Thank you for sharing your use case with the CData Community! Based on what you have shared I wanted to let you know that what you are trying in the modified script will not work unfortunately. The first thing to keep in mind is that you cannot really access other tables across connections like you are trying to do accessing the Users table from the AzureAD connection in the same script with the Oracle connection. This is not something supported within API Server. 

    However one thing that can be done here through some careful client-side scripting is approaching that in multiple steps:

    1. Get value from AzureAD first
    2. POST Call with value obtained from #1

    If you want to learn more on how to achieve this you will probably need some assistance from our support team so I would recommend you submit a ticket in our support portal here.

     

      CData for Developers: Connect AI Developer Edition, Python SDK, and CLI Are Now Available
      Terms and ConditionsAccessibility statement