2.4 The Database Source

In this video we show you how to create a database connector and how to connect to a stored procedure using Warewolf.

In this video we are going to create a database connector. Database connectors in Warewolf consume stored procedures and functions in your database server, so you can use them to either read or update your database tables.

You will need to have access to Microsoft SQL Server.  We use the standard AdventureWorks database in this example. You will need to re-create this stored procedure in your AdventureWorks database to complete this exercise

We will demonstrate how to select data using a stored procedure, and use it in a Warewolf microservice.

In doing so, we are going to use this stored procedure to return a list of players for a game.

1. To start, create a new service by clicking the new service button in the toolbar.
   Scroll down in the Tool Box until you find the Database category.
2. Drag the SQL Server tool onto the design surface and connect it to the Start node.
3. Click the New button to create a new SQL Server Database source.
4. On the New SQL Server Source tab add your Server (this is the same as the Server Name in SQL Server Express – you can copy and paste it here).
5. Select the Authentication Type as Windows.
6. Click Test Connection. Assuming this is successful, you will be able to select your AdventureWorks2008R2 database from the Database drop down.
7. Click the Save icon in the left hand menu and save your Source as AdventureWorks. We’ve just created our first Database Source. You will find it in the Explorer.
8. Navigate back to your service and open the SQL Server Database tool. From the Source drop down list, select the AdventureWorks source. After you have selected the source, the Action list becomes populated with all of the stored procedures in your database. If you are working on the database at the same time, you can click the refresh button to refresh the list. It is important to note that the Warewolf server is actually making the connection to the database server, so your Warewolf server must be able to access the SQL database server.
9. We are going to use the dbo.FetchPlayers stored procedure, so select it from the Action list.
10. We now want to add a Variable as [[NumberOfPlayers]]. This allows us to pass in the variable at run time.
11. It is time to FetchPlayers using the Generate Outputs button.
12. In the GameNumber field, add the number of players you want to fetch. We will use 4. Then click Test. You should see a list of 4 players in the Outputs section.
13. Now click Done.  You will notice the Recordset Name in the Variables list.
14. In the variable list, mark NumberOfPlayers as Input and dbo_FetchPlayers() as Output.
15. Now let’s Debug this service. Add any number of Inputs in the Debug Window in the NumberOfPlayers input box. We’ll use 4 again. Then click Debug. If you look at the Output box, you will see the Inputs of NumberOfPlayers is 4, and the Outputs are the same as the Outputs that you selected earlier in the Variables List. You can also view this in your browser by using the shortcut key F7. Your browser will now display the XML dataset that you have flagged as output variables.
16. It’s time to save your microservice. Click the Save button on the toolbar, and name your new microservice FetchPlayersService.

We’ve just built our first microservice that returns data from a database.