Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MSSQL stored procedures does not correctly works with schemas #791

Open
kkkmail opened this issue Apr 26, 2023 · 0 comments
Open

MSSQL stored procedures does not correctly works with schemas #791

kkkmail opened this issue Apr 26, 2023 · 0 comments

Comments

@kkkmail
Copy link

kkkmail commented Apr 26, 2023

SqlDataProvider does not have the functionality to specify schema of stored procedure (in contrast to accessing the tables). Rather, all stored procedures appear under ctx.Procedures where ctx is a database context obtained via call to GetDataContext, e.g.:

    type private WorkerNodeDb = SqlDataProvider<
                    Common.DatabaseProviderTypes.MSSQLSERVER,
                    ConnectionString = WorkerNodeConnectionStringValue,
                    UseOptionTypes = Common.NullableColumnType.OPTION>


    type private WorkerNodeDbContext = WorkerNodeDb.dataContext
    let private getDbContext (c : unit -> ConnectionString) = c().value |> WorkerNodeDb.GetDataContext

This how clm.tryUpdateProgressRunQueue (from schema clm) appears in F#:

image

Hovering over (as shown on the picture) correctly shows that the stored procedure belongs to clm schema. This is inconvenient but that would've been OK and that could be dealt with.

However, the error appears if there is a stored procedure with the same name but in a different schema (eeInf in the example). The second procedure "acquires" an extra ' in the name:

image

Unfortunately, finally nothing works. Hovering over Invoke shows SP parameters mixed up from both procedures:

image

Here are the blank SPs along with schema creation scripts for convenience:

if not exists(select schema_name from information_schema.schemata where schema_name = 'clm') begin
	print 'Creating schema clm...'
	exec sp_executesql N'create schema clm'
end else begin
	print 'Schema clm already exists...'
end
go


if not exists(select schema_name from information_schema.schemata where schema_name = 'eeInf') begin
	print 'Creating schema eeInf...'
	exec sp_executesql N'create schema eeInf'
end else begin
	print 'Schema eeInf already exists...'
end
go


drop procedure if exists clm.tryUpdateProgressRunQueue
go


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create procedure clm.tryUpdateProgressRunQueue (
						@runQueueId uniqueidentifier,
						@progress decimal(18, 14),
						@callCount bigint,
						@relativeInvariant float,
						@maxEe float,
						@maxAverageEe float,
						@maxWeightedAverageAbsEe float,
						@maxLastEe float)
as
begin
	declare @rowCount int
	set nocount on;

        -- Do something useful here.

	set @rowCount = @@rowcount
	select @rowCount as [RowCount]
end
go


drop procedure if exists eeInf.tryUpdateProgressRunQueue
go


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create procedure eeInf.tryUpdateProgressRunQueue (
						@runQueueId uniqueidentifier,
						@progress decimal(18, 14),
						@callCount bigint,
						@relativeInvariant float,
						@dummy float)
as
begin
	declare @rowCount int
	set nocount on;

        -- Do something useful here.

	set @rowCount = @@rowcount
	select @rowCount as [RowCount]
end
go

I am using SQLProvider version 1.3.7 and MSSQL.

@Thorium Thorium changed the title SqlDataProvider does not correctly works with schemas MSSQL stored procedures does not correctly works with schemas May 25, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant