Where do you define database connection for sql action

Getting this error when testing sql action. SQL works on database. How do I know what database SQL action is executing from. Module uses multiple database connections

Hello Tony, how are you calling the table in SQL1?

Are you tring to call Outsystems entittes or external (with extesntion)?

Can you show the SQL?


Please see the documentation here: https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries


Tony Mazur wrote:

Getting this error when testing sql action. SQL works on database. How do I know what database SQL action is executing from. Module uses multiple database connections


Hi Luis, This is what I have



Thanks for the reply.

As you have the table referenced in extension, you just need to use the Outsystems nomenclature:

Select {CFRoleVerification}.[Name_ID] (and other fields...)

From {CFRoleVerification}


Can you test it?

BR,

Luis

I only added database name to see if it would take care of error. It did not. I do not want to use hard coded database name.

Why you need/want to do that? You need to create a integration for each database


Tony Mazur wrote:

I only added database name to see if it would take care of error. It did not. I do not want to use hard coded database name.



Tony Mazur wrote:

I only added database name to see if it would take care of error. It did not. I do not want to use hard coded database name.

When I used your suggestion it has error Unknown Entity



When we promote to different environments (TEST, QA, PROD)

Tony Mazur wrote:

When we promote to different environments (TEST, QA, PROD)

Did you configure the database connection on those environments?

Can you call the entity as you made here?


Environments are configured. Not sure I understand 'Can you call the entity '

How does it know what database to look at?'

I am refereing to the print screen, can you select the entity?

You need to define the database connection for each extension:

And you need to configure the database connection in ServiceCenter

Please see documentation here: https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/Integrate_with_an_External_Database

I can select entity CFRoleVerification. It is empty as I expected. It does not find function 'Invalid Object'

Can you sent the print of your query?

The function uses 3 tables from one database and 1 table from another database. The one table is not defined in OutSystems. But that should not make a difference because we are using function that returns table to OutSystems, correct


/* ===========================================================================================

Author:Mills


Date:04/07/2020


Description:




Paramters:



Return Value:


None


Used by:


   --------------------------------------------------------------------------------------

   Change ID        DeveloperDescription

   --------------------------------------------------------------------------------------



   =========================================================================================== */


ALTER FUNCTION [dbo].[CFNameCourtDurationFN]

(

@NAME_IDint,

@COURT_NUMBERchar(12)


)

RETURNS TABLE

AS

RETURN


/**********************************/

/* Get current member information */

/**********************************/


with step_0 as

(

select a.NAME_ID, IsNull(H.ADULT_CODE, 'N') as ADULT_CODE, IsNull(b.VOTE_CODE,'N') as VOTE_CODE

from PNAME a


inner join CF_MBR b

on a.NAME_ID = b.NAME_ID


        Outer Apply (Select Top (1) OneYrEarlierDate

 From CFConventionCntrl as Dte

 Where GetDate() Between StartDate and EndDate) as SDte


    Outer Apply (Select Top (1) ADULT_CODE-- Adult code must originate from CF_MBR_HIST. Court Number does not apply in this case.

             From [CFDataPrd].[dbo].[CF_MBR_HIST]

             Where NAME_ID = a.NAME_ID

   --and COURT_NUMBER = @COURT_NUMBER

   and MONTH_END_DATE <= SDte.OneYrEarlierDate

 Order By MONTH_END_DATE desc) as H


where a.NAME_ID = @NAME_ID

),


/*********************************************/

/* Get amount of months in the current court */

/*********************************************/


step_01 as

(

SELECT

NAME_ID,

COURT_NUMBER,

min(MONTH_END_DATE) as BEGIN_MONTH,

max(MONTH_END_DATE) as END_MONTH,

datediff(m, min(MONTH_END_DATE), max(MONTH_END_DATE)) as MonthsInCourt,

'N' as Merged_Court


FROM [CFDataPrd].[dbo].[CF_MBR_HIST]


Outer Apply (Select Top (1) OneYrEarlierDate

 From CFConventionCntrl as Dte

 Where GetDate() Between StartDate and EndDate) as SDte


where@COURT_NUMBER = [COURT_NUMBER] and

@NAME_ID = NAME_ID       and

MONTH_END_DATE <= SDte.OneYrEarlierDate


group by [NAME_ID], [COURT_NUMBER]

),


/************************************************************************/

/* Get the amount of months the member spent in the previous court.*/

/* This would occur if there was a previous court transfer.*/

/************************************************************************/


step_02 as

(

select

NAME_ID,

a.FROM_AFFILIATE_NO,

min(b.MONTH_END_DATE) as BEGIN_MONTH,

max(b.MONTH_END_DATE) as END_MONTH,

datediff(m, min(b.MONTH_END_DATE), max(b.MONTH_END_DATE)) as MonthsInCourt,

'Y' as MergedCourt


from PASS_GAS a


inner join [CFDataPrd].[dbo].[CF_MBR_HIST] b

on a.FROM_AFFILIATE_NO = b.COURT_NUMBER


Outer Apply (Select Top (1) OneYrEarlierDate

 From CFConventionCntrl as Dte

 Where GetDate() Between StartDate and EndDate) as SDte


where@COURT_NUMBER = a.[TO_AFFILIATE_NO] and

b.NAME_ID = @NAME_ID                  and

a.TRANSFER_TYPE = 'C'        and

b.MONTH_END_DATE <= SDte.OneYrEarlierDate


group by [NAME_ID], a.FROM_AFFILIATE_NO


)


selecta.NAME_IDas NAME_ID,

IsNull(a.ADULT_CODE, 'N')as ADULT_CODE,

a.VOTE_CODEas VOTE_CODE,

isnull(b.MonthsInCourt,0)as MonthsInCurrentCourt,

isnull(c.MonthsInCourt,0)as MonthsInMergedCourt,

isnull(IsNull(b.MonthsInCourt, 0) + IsNull(c.MonthsInCourt,0), 0)as TotalMonthsInCourt,

isnull(c.MergedCourt,' ') as MergedCourt


from step_0 a


left outer join step_01 b

on a.NAME_ID = b.NAME_ID


left outer join step_02 c

on a.NAME_ID = c.NAME_ID


We have many projects in OutSystems. This is 1st time we are using function

Table [CFDataPrd].[dbo].[CF_MBR_HIST] is not in service studio 

Hello Tony,

Can you please describe with more detail what you wanto to do in the Outsystems application?

I don't get your point yet...


Thanks


Tony Mazur wrote:

We have many projects in OutSystems. This is 1st time we are using function



We were trying to execute table function. Have error could not find function. This morning we changed approach to use stored procedure to execute function. We get error Could not find stored procedure. I have structure for items returned.