pumlhorse-mssql
Provides Microsoft SQL functions for Pumlhorse scripts
Wraps node-mssql package
Installing npm module
npm install pumlhorse-mssql
Referencing module
Connecting to a database
The connect
function takes a connection string. See node-mssql documentation
for connection string details.
steps: - connect: "Server=(local);Database=MyDatabase;User ID=my_username;Password=my_password"
It's likely that you would want to store this connection string in a context file and reference the variable instead.
Inserting data
The following code inserts three records into the myFavoriteMovies table.
steps: - connect: $sqlConnectionString - insert: table: myFavoriteMovies data: - name: Shawshank Redemption stars: 4.5 notes: Excellent cinematography reviewer: $username reviewDate: ${new Date()} - name: The Matrix stars: 4.5 notes: Groundbreaking reviewer: $username reviewDate: ${new Date()} - name: Hot Rod stars: 5 notes: Just plain perfect reviewer: $username reviewDate: ${new Date()}
Retrieving data
Assuming that the data above has been inserted, we can retrieve it like so
steps: - connect: $sqlConnectionString - movies = query: parameters: stars: 4 sql: > SELECT Name, Stars, Notes, ID FROM myFavoriteMovies WHERE Stars >= @stars ORDER BY Stars, Name DESC - for: each: row in: $movies steps: - log: - %s (%s stars) - %s - $row.Name - $row.Stars - $row.Notes
The code above outputs the following lines:
Hot Rod (5 stars) - Just plain perfect
Shawshank Redemption (4.5 stars) - Excellent cinematography
The Matrix (4.5 stars) - Groundbreaking
If you don't want to use the for
function, you can reference the result as an array:
- log: $movies[0].Name # logs "Hot Rod"
Other queries
The query
function accepts more SQL statements than just SELECT
. All commands
should be accepted, with the exception of CREATE PROCEDURE
and queries with temp tables.
See node-mssql query documentation for more
info
Multiple connections
If your script needs multiple connections, you can explicity pass the connection to the functions
steps: - conn1 = connect: $sqlConnection1String - conn2 = connect: $sqlConnection2String - insert: connection: $conn1 table: table1 data: - #table1 data - insert: connection: $conn2 table: table2 data: - #table2 data - movies = query: connection: $conn1 parameters: #parameters sql: #SQL query