search

grafana / xk6-sql

k6 extension to load test RDBMSs (PostgreSQL, MySQL, MS SQL and SQLite3)
Apache License 2.0
107 stars 57 forks source link

Fixing mssql_test.js example with correct sp_executesql syntax #40

Closed leandrodotec closed 1 year ago

leandrodotec commented 1 year ago

sp_executesql syntax needs a statement with @P parameters, not $1.

Just fixing the example for MS-SQL Server 2022.

This PR fixes issue: #39 .

CLAassistant commented 1 year ago

CLA assistant check
All committers have signed the CLA.

imiric commented 1 year ago

Hi, thanks for reporting and fixing this! :bow: We missed this difference with MS SQL in #6, since we haven't automated testing against real servers yet.

I just tested manually against a SQL Server 2022 container (https://hub.docker.com/_/microsoft-mssql-server, mcr.microsoft.com/mssql/server:2022-latest), and you're right that the previous $1 syntax fails with:

GoError: mssql: Incorrect syntax near the keyword 'key'.

But I still get the same error after changing it to @p1. :confused: @param1 didn't work either. Can you clarify if the new example worked for you? Or maybe which edition of SQL Server 2022 you're running? The container runs the Developer edition, though basic syntax like this shouldn't change. I'm not familiar with MS SQL, so not sure if we have to use SET somehow to define the variable, but according to the documentation, @param1 should work...

leandrodotec commented 1 year ago

Hi, sorry. I have just realised the mistake I made. Here is the right query line:

SELECT * FROM keyvalues WHERE [key] = @p1;

I ran the example again with the query above and now it is working fine. I thought having brackets was optional, but apparently "key" is a reserved word in MS-SQL.

It is all good now. I ran over SQL 2022 developer edition.