Is it possible in R (as in other languages) to do this with R-database-functionality:
my database contains stored procedures which use user-defined types (UDT, especially table-types) for some parameters (TVP). I would like to execute these stored procedures from my R program, but can't find a solution how to use the UDTs for the affected parameters. Maybe it's not supported in R as I know it for example in .NET?
Simple example (database: SQL Server):
The user-defined table type:
CREATE TYPE T_KEYS AS TABLE
(
key nvarchar(3) NOT NULL
)
A stored procedure using this UDT:
CREATE PROCEDURE SP_DoIt
(@name NVARCHAR(4),
@keylist T_KEYS READONLY)
AS
BEGIN
-- do something useful with the values of keylist
SELECT something
FROM somewhere
WHERE mykey IN (SELECT * FROM keylist);
RETURN
END
Executing the stored procedure in the SQL Server database context:
BEGIN
DECLARE @somekeys AS T_KEYS
INSERT INTO @somekeys
VALUES ('mX2'), ('x8U'), ('K2i')
EXEC SP_DoIt 'callaspadeaspade', @somekeys
end
What to do inside the R-program in general to execute this stored procedure?
(just template-code, using glue_sql is maybe not smart)
sql <- "exec SP_DoIt {name}, {keys};"
mykeys <- ** how to instantiate a list of T_KEYS-datatype ??? **
query <- glue::glue_sql(sql,
name = 'callaspadeaspade',
keys = mykeys,
.con = dbconnobject)
resultset <- DBI::dbSendQuery(connectionobject, query)
gotIt <- DBI::dbFetch(resultset)
DBI::dbClearResult(resultset)