Catalogname in Retrieval SP is not overwritten.

Posts   
 
    
jwijnker
User
Posts: 32
Joined: 09-Mar-2011
# Posted on: 02-Nov-2012 17:51:06   

Using LLBLGen Pro Version 3.1 Final (March 12th, 2012) Using Template: Adapter Target Platform: .NET 2.0 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll File version 3.1.12.0806

I try to execute a SP :


public static InvoicesTypedView SP_GetInvoices(int amount)
{
    InvoicesTypedView result = new InvoicesTypedView ();

    var query = RetrievalProcedures.GetQueryFor InvoicesTypedView (amount   );

    using (var daa = DataSourceHelper.GetDataAccessAdapter())
    {
        daa.FetchTypedView(result, query);
    }
    return result;  
}

When I monitor (IRetrievalQuery) ‘query’ the referenced catalog is ‘tenant’.

In the dataAccessAdapter I use catelog overwrites.



    public partial class DataAccessAdapter : Data.DatabaseSpecific.DataAccessAdapter
    {
        private const string TENANT_CATALOG_NAME_TO_USE_SETTING_KEY = "DefaultCatalogNameToUse";
        private const string MASTER_CATALOG_NAME_TO_USE_SETTING_KEY = "MasterCatalogNameToUse";

        private const string TENANT_DATABASE_CATALOG_NAME = "Tenant";
        private const string MASTER_DATABASE_CATALOG_NAME = "Master";

        public DataAccessAdapter() : base() {

            this.CatalogNameUsageSetting = CatalogNameUsage.Default;

            this.CatalogNameOverwrites = new CatalogNameOverwriteHashtable(CatalogNameUsage.Default);
            this.CatalogNameOverwrites.Add(TENANT_DATABASE_CATALOG_NAME, System.Configuration.ConfigurationManager.AppSettings[TENANT_CATALOG_NAME_TO_USE_SETTING_KEY]);
            this.CatalogNameOverwrites.Add(MASTER_DATABASE_CATALOG_NAME, System.Configuration.ConfigurationManager.AppSettings[MASTER_CATALOG_NAME_TO_USE_SETTING_KEY]);
        }

}

I've read the thread http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20590 and expected that the catalogoverwrites should also work on sp's.

Why is the catalogname not rewritten for StoredProcedures? I use the same DataAccessAdapter for fetching other entities, where the catalogname gets rewritten.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 03-Nov-2012 05:50:00   

The thing is: when you call

var query = RetrievalProcedures.GetQueryFor InvoicesTypedView (amount );

... the query must be constructed with some schema/catalog, so the frameworks instantiate a normal DataAccessAdapter to do that. Then wjen you use your custom daa, it's too late to insert that overrides. This thread reveals a similar situation: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21272&HighLight=1

My recommendation is to do this:

public static InvoicesTypedView SP_GetInvoices(int amount)
{
    InvoicesTypedView result = new InvoicesTypedView ();
    using (var daa = DataSourceHelper.GetDataAccessAdapter())
    {
           RetrievalProcedures.FetchInvoicesTypedView(daa, result, amount);
    }
    return result ; 
}

... so it always use your custom adapter.

David Elizondo | LLBLGen Support Team
jwijnker
User
Posts: 32
Joined: 09-Mar-2011
# Posted on: 04-Nov-2012 00:15:44   

Thanks for your reply! Unfortunatly I can't find any method in RetrievalProcedures with such a signature...

Even when I use this code where the query is created when the daa is already loaded, then correct catalog isn't set.


public static InvoicesTypedView SP_GetInvoices(int amount)
{
    InvoicesTypedView result = new InvoicesTypedView();

    using (var daa = DataSourceHelper.GetDataAccessAdapter())
    {
        
        var query = RetrievalProcedures.GetQueryFor InvoicesTypedView (amount   );

        daa.FetchTypedView(result, query);
    }
    return result;  
}

I have 3 options in the RetrievalProcedures for this SP. 1 returns a DataTable, the other 2 return a IRetrievalQuery. All 3 options do fail in the catalog-overwrite.

Do I miss a 'using'-statement? or does it matter I do not give the catalogRewriteTable as a constructor-argument?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 04-Nov-2012 21:12:10   

Even if you place the call inside the using statement it wont work because you are not passing the the adapter object. There should be a RetrievalProcedures call that receives an adaper parameter for that TypedView.

David Elizondo | LLBLGen Support Team
jwijnker
User
Posts: 32
Joined: 09-Mar-2011
# Posted on: 05-Nov-2012 09:55:10   

The only option left where i can pass a DataAccessAdapter is:


using (var daa = DataSourceHelper.GetDataAccessAdapter())
{
    InvoicesTypedView result = new InvoicesTypedView();

    var dataTable = RetrievalProcedures.GetInvoices(
        Amount,
        daa
    );

    
    result.Load(dataTable.CreateDataReader());
}

Now I can execute the SP (using a dataTable) and get the TypedView as a result. Thanks.