pumlhorse-mssql

Provides Microsoft SQL functions for Pumlhorse scripts

Wraps node-mssql package

Installing npm module

npm install pumlhorse-mssql

Referencing module

See Pumlhorse documentation

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