LblGenPro 5.7 (5.7.0) RTM

We're using the following stored procedure in a context where each tenant has it's own MSSQL database

    using var da = _dbContext.GetNewAdapter(); // see below how it is created

    return await da.FetchProjectionAsync<VenueDistanceResultRow>(
        RetrievalProcedures.GetVenueDistanceCallAsQuery(maxDistance, latitude, longitude, da), cancellationToken);
}

Actual command created:

Executed Sql Query: 
    Query: Stored procedure call: [dbo].[VenueDistance](@MaxDistance, @Lat, @Lng)
    Parameter: @MaxDistance : Double. Length: 0. Precision: 38. Scale: 0. Direction: Input. Value: 0,5.
    Parameter: @Lat : Double. Length: 0. Precision: 38. Scale: 0. Direction: Input. Value: 48,1802849.
    Parameter: @Lng : Double. Length: 0. Precision: 38. Scale: 0. Direction: Input. Value: 10,762421700000003.

This query fails unless the current user has a default database set, but which may be the wrong database for the tenant.

Expected command - should include the database [a-indoor]:

    Query: Stored procedure call: [a-indoor].[dbo].[VenueDistance](@MaxDistance, @Lat, @Lng)
    Parameter: @MaxDistance : Double. Length: 0. Precision: 38. Scale: 0. Direction: Input. Value: 0,5.
    Parameter: @Lat : Double. Length: 0. Precision: 38. Scale: 0. Direction: Input. Value: 48,1802849.
    Parameter: @Lng : Double. Length: 0. Precision: 38. Scale: 0. Direction: Input. Value: 10,762421700000003.

_dbContext.GetNewAdapter() returns the following:

return new DataAccessAdapter(ConnectionString)
{
    KeepConnectionOpen = true,
    CompatibilityLevel = SqlServerCompatibilityLevel.SqlServer2012,
    CommandTimeOut = CommandTimeOut,
    CatalogNameOverwrites =
        new CatalogNameOverwriteHashtable(new Dictionary<string, string> { { "*", Catalog } })
        {
            CatalogNameUsageSetting = CatalogNameUsage.ForceName
        },
    SchemaNameOverwrites =
        new SchemaNameOverwriteHashtable(new Dictionary<string, string> { { "*", Schema } })
        {
            SchemaNameUsageSetting = SchemaNameUsage.ForceName
        }
};

Is this a bug or which different method should we use to call the stored procedure which must include the selected database?

What's Catalog in new CatalogNameOverwriteHashtable(new Dictionary<string, string> { { "*", Catalog } }) ?

If it's an empty string, you wont' get a catalog name. If you don't submit any catalog name overwriting, do you still get no catalog name in the proc call?

Otis wrote:

What's Catalog in new CatalogNameOverwriteHashtable(new Dictionary<string, string> { { "*", Catalog } }) ?

If it's an empty string, you wont' get a catalog name. If you don't submit any catalog name overwriting, do you still get no catalog name in the proc call?

Catalog is the database name ("a-indoor" in this case), and it's never empty. This is what is generated for a SELECT which is called immediately before the call to the stored procedure:

    Query: SELECT [a-indoor].[dbo].[VenueTeam].[City], [a-indoor].[dbo].[VenueTeam].[Direction], [a-indoor].[dbo].[VenueTeam].[Latitude], [a-indoor].[dbo].[VenueTeam].[Longitude], [a-indoor].[dbo].[VenueTeam].[PostalCode], [a-indoor].[dbo].[VenueTeam].[PrecisePosition], [a-indoor].[dbo].[VenueTeam].[RoundId], [a-indoor].[dbo].[VenueTeam].[Street], [a-indoor].[dbo].[VenueTeam].[TeamClubName], [a-indoor].[dbo].[VenueTeam].[TeamId], [a-indoor].[dbo].[VenueTeam].[TeamName], [a-indoor].[dbo].[VenueTeam].[TeamNameForRound], [a-indoor].[dbo].[VenueTeam].[TournamentId], [a-indoor].[dbo].[VenueTeam].[VenueExtension], [a-indoor].[dbo].[VenueTeam].[VenueId], [a-indoor].[dbo].[VenueTeam].[VenueName] FROM [a-indoor].[dbo].[VenueTeam] WHERE ( ( ( [a-indoor].[dbo].[VenueTeam].[VenueId] = @p1)))
    Parameter: @p1 : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 112.

I can't reproduce it with the lastest 5.7.7 version:

[Test]
public async Task AutoGeneratedProjectorOnProcTest3()
{
    using(var adapter = new DataAccessAdapter())
    {
        adapter.CatalogNameOverwrites =
            new CatalogNameOverwriteHashtable(new Dictionary<string, string> { { "*", "Northwind2" } })
            {
                CatalogNameUsageSetting = CatalogNameUsage.ForceName
            };
        
        var results = await adapter.FetchProjectionAsync<CustomerDTO>(RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery("USA", adapter));
        Assert.AreEqual(13, results.Count);
        foreach(var c in results)
        {
            Assert.AreEqual("USA", c.Country);
        }
    }
}

Results in: EXECUTE [Northwind2].[dbo].[pr_CustomersAndOrdersOnCountry] @country while not using any overwrites, results in: EXECUTE [Northwind].[dbo].[pr_CustomersAndOrdersOnCountry] @country

Also if I specify the same schema overwrites it also applies those.

Could you try a later runtime version than 5.7.0 ? Thanks (I checked, but we didn't file a bugfix for this in the 5.7x timeframe)

Otis wrote:

I can't reproduce it with the lastest 5.7.7 version:

Results in: EXECUTE [Northwind2].[dbo].[pr_CustomersAndOrdersOnCountry] @country while not using any overwrites, results in: EXECUTE [Northwind].[dbo].[pr_CustomersAndOrdersOnCountry] @country

Also if I specify the same schema overwrites it also applies those.

Could you try a later runtime version than 5.7.0 ? Thanks (I checked, but we didn't file a bugfix for this in the 5.7x timeframe)

Thanks! I upgraded to the latest 5.7.7 version:

With overwrites: The database name is not included in the stored procedure call, although CatalogNameToUseis properly set.

Without overwrites: The (default) database name is included.

nb wrote:

Thanks! I upgraded to the latest 5.7.7 version:

With overwrites: The database name is not included in the stored procedure call, although CatalogNameToUseis properly set.

Without overwrites: The (default) database name is included.

I was just thinking about differences between your and my test case, guessing the only difference is that my database name has a dash in it. So I used another database name (i.e. "aindoor" instead of "a-indoor") and voilà, the stored procedure call contained the name as expected. Whew!

Could you try to reproduce this behavior?

As MSSQL accepts the dash, so should LLBLGen?

It should, we use a regexp for finding the catalog and schema name in 1 go (as the name for a proc is passed in with catalog/schema and proc name in 1 string) and it might contain a limitation that doesn't find the -. We'll look into it.

Otis wrote:

It should, we use a regexp for finding the catalog and schema name in 1 go (as the name for a proc is passed in with catalog/schema and proc name in 1 string) and it might contain a limitation that doesn't find the -. We'll look into it.

Yeah, I saw it in the source code, without digging deeper. Still, it works for queries, but not for stored procedures.

nb wrote:

Otis wrote:

It should, we use a regexp for finding the catalog and schema name in 1 go (as the name for a proc is passed in with catalog/schema and proc name in 1 string) and it might contain a limitation that doesn't find the -. We'll look into it.

Yeah, I saw it in the source code, without digging deeper. Still, it works for queries, but not for stored procedures.

Correct, for queries we directly convert the catalog name so we don't have to match it from the string the engine gets, but for stored procedures the name is in its entirety in 1 string, so to replace a catalog in there we have to parse that string first. It usually goes ok, but apparently here it doesn't.

(edit)it does 2 passes, the first pass is the per-call replacement, the second the global replacement. The first pass introduces correctly the name with a -, the second one uses the regexp but fails (as it can't match the catalog name) and thus thinks there's no catalog specified (as some people do that in custom code). So it is a bug in the regexp.

Fixed. See hotfix builds 5.7.7 and 5.8.4

Otis wrote:

Fixed. See hotfix builds 5.7.7 and 5.8.4

Thanks for the explanations and for the hint that overwrites may leave Catalog and/or Schema blank, resulting in a

Stored procedure call: ..[VenueDistance](@MaxDistance, @Lat, @Lng)

I was not aware of that.

Confirm the fix is working. Great!