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 workaround (sample code in comments) #121

Open
steve-krisjanovs opened this issue Nov 14, 2019 · 0 comments
Open

MSSQL workaround (sample code in comments) #121

steve-krisjanovs opened this issue Nov 14, 2019 · 0 comments

Comments

@steve-krisjanovs
Copy link

steve-krisjanovs commented Nov 14, 2019

Great module by the way!

I used it quite extensively against PGSQL and now I want to expand my app to support MSSQL as well.

MSSQL has it's own es6 templating engine in their node driver but it's very different from this module's implementation which isn't compatible with mssql due to how parameters are handled (Microsoft's way templates the query and executes it against the database in one pass - ugh). I didn't want that in order for my app to work across both mssql and pgsql with minimal refactoring, so I implemented the following code that accepts a query string templated by this module, transforms it into a parameterized mssql query, then executes it :)

Code example below ("cmd" is an query templated by node-sql-template-strings, and "client" is a request object from a mssql pool or transaction):

const padding_length = 4;

//transform the query e.g. "select * from table where id = $1" >> "select * from table where id = @param0001"
//the loop below will handle queries with as many as 1000 parameters (i.e. param0001..param1000)
var newquery = cmd.text;
for (var i = 1; i <= 1000; i++) {
    var findthis = `${i}`;
    var replacewith = `@param${(i).toString().padStart(padding_length, "0")}`;
    newquery = newquery.replace(findthis, replacewith); //replace first occurance
}

//added this for debugging
client.debug_newquery = newquery; 
client.debug_cmd = cmd;

//build the parameters for the call....
for (var i = 0; i < cmd.values.length; i++) {
    var val = cmd.values[i];
    client.input(`param${(i + 1).toString().padStart(padding_length, "0")}`, val);
}

//execute query
res = await client.query(newquery);
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