Due to a bug in SQL Server 2019 Linux, a node-mssql GET query that should be one single statement:
router.get('/getresult/:srid/:x/:y', function (req, res) {
sql.connect(config, error => {
new sql.Request()
.input('srid', sql.Int, req.params.srid)
.input('x', sql.Float, req.params.x)
.input('y', sql.Float, req.params.y)
.query('SELECT * from dbo.bTest(geometry::Point(@x,@y,@srid))', (error, result) => {
res.send(result.recordset);
})
if (error) {
console.error(`error: ${error.message}`);
}
})
});
Now needs to be two statements. The query that crashes SQL Server 2019 Linux is
SELECT * from dbo.bTest(geometry::Point(@x,@y,@srid))
The test table-valued function is this:
create function dbo.bTest ( @p_geom geometry )
Returns @geometries TABLE
(
id integer,
geom geometry
)
as
begin
declare @g geometry;
set @g = @p_geom.STBuffer(0.5);
insert into @geometries values (1, @g);
return;
end;
Until there's a fix, the workaround I've found is to use two statements in the query:
DECLARE @wkt geometry = geometry::Point(@x,@y,@srid);
SELECT * FROM dbo.bTest(@wkt);
As you can see in the following dbfiddle, the two statement query does not crash SQL Server 2019 Linux when it is executed the second time, whereas the single statement query crashes SQL Server 2019 Linux on the second execution:
https://dbfiddle.uk?rdbms=sqlserver_2019l&fiddle=4a7759a742f361fc075f35ae6fbdc186
So, is it possible in the node-mssql GET request to use two statements (DECLARE and SELECT) or will I need to put the query in a stored procedure? And if I put it in a stored procedure, can I use a node-mssql GET request to execute a stored procedure and get values back? I'm getting back a result.recordset that can consist of one or more results.
EDIT:
Big thanks to @David for what appears to be the answer. This seems to work in the node-mssql GET request (query):
SET NOCOUNT ON;
DECLARE @geom geometry = geometry::Point(@x,@y,@srid);
SELECT * FROM dbo.bTest(@geom);
It works multiple times in succession and doesn't crash SQL Server 2019 Linux. So then the resulting node-mssql router.get is:
router.get('/getresult/:srid/:x/:y', function (req, res) {
sql.connect(config, error => {
new sql.Request()
.input('srid', sql.Int, req.params.srid)
.input('x', sql.Float, req.params.x)
.input('y', sql.Float, req.params.y)
.query('SET NOCOUNT ON; \
DECLARE @geom geometry = geometry::Point(@x,@y,@srid)); \
SELECT * from dbo.bTest(@geom);', (error, result) => {
res.send(result.recordset);
})
if (error) {
console.error(`error: ${error.message}`);
}
})
});
Wasn't aware you could use multiple statements in a node-mssql query. Later on though, when I want to push this to production, I'm most likely going to want to use prepared statements. The way I understand it, semi-colons are verboten in prepared statements. Is it possible to (safely) query using more than one statement using prepared statements without having to use a stored procedure? (Sorry for the additional question, just want to make sure the workaround works for prepared statements as well, hopefully this SQL Server 2019 Linux bug gets fixed soon).
