pumlhorse-mssql

0.1.3 • Public • Published

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 

Package Sidebar

Install

npm i pumlhorse-mssql

Weekly Downloads

0

Version

0.1.3

License

MIT

Last publish

Collaborators

  • mdickin