Connect to an SQL Server

Follow the guide Add Module to Apis Hive, but this time select a module of type ApisSQL from the "Module type" dropdown list.

After adding the module, select the new module named "ApisSQL1" from the Solution Explorer.

The module supports communication with SQL Server, either on a fixed interval (based on the Timer interval property) or triggered by using a trigger item.

This Quick Start Guide will show you how to connect an SQL Bee to your SQL Server instance, and send and receive values from SQL Server, using simple query and more advanced stored procedures.

To start with, you'll need to configure your new SQL Bee to connect to SQL Server. You'll need access to an SQL Server from the computer on which you're configuring the SQL Bee. This includes a database username and password, along with network access between the two.

In your SQL Module, you'll need to set the following properties:

The "Database login" property should contain the name of the user you want to connect as. The "Database login password" property should contain the password for that user. The "Database name" should contain the name of the SQL database you want to use. The "Database server" property should contain the name or IP address of the SQL server, along with the name of the instance, if the database isn't on the default instance.

Assume database name is "test", we use "sa" login and database server is local.

It can also be useful to change the SQL statement property to "Select 1" and the Timer interval property to 1000. Press Apply when you're happy with the field values. This will allow you to see if the connection was successful by using the "Connection state" property. If the connection is successful, it should display "Connection state: open".

The next step is to change the "SQL Statement" property to reflect the SQL command you want run. Writing "Select 1" allowed us to check the connection worked, but we're going to want to do something more useful.

First of all you will need a table named "Items" you can use this query to create it.


USE [test]

GO

/****** Object: Table [dbo].[Items] Script Date: 30.06.2017 10.24.40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Items](

[ItemID] [nvarchar](50) NOT NULL,

[ItemValue] [float] NULL,

[ItemTimestamp] [datetime] NULL,

[ItemQuality] [int] NULL,

CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED

(

[ItemID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

The table should at least have one row with ItemId and ItemValue set.

Execute as text

The simplest way to acquire data is by sending a simple SQL statement to the server.

Set the "SQL statement type" property to "Execute as text"

Set "SQL statement" to "Select * FROM [test].[dbo].[Items]"

To add a Read Item, right-click on the SQL Bee module again and press Read Item. In the dialog box, instead of pressing Add Item, press Browse.

This will give you a list of available items, one of which should be ReadItem1 :

Press the checkbox beside ReadItem1 and then OK. Press OK again to add the item.

The Read Item will now reflect the values of "ItemValue", "ItemTimestamp" and "ItemQuality" of row "ReadItem" in table "Items"

When the foreign system updates these field in the table the values will be reflected in the namespace of ApisHive.

Execute as stored procedure

For more advanced queries execution of stored procedures in the SQL server might me required.

In this example, we'll be using a stored procedure called TestBeeParams:


USE [test]

GO

/****** Object: StoredProcedure [dbo].[TestBeeParams] Script Date: 28.06.2017 12.50.45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[TestBeeParams] @Number int, @SPName nvarchar(max), @Source nvarchar(max), @XML nvarchar(max)

as

begin

set nocount on

DECLARE @idoc int;

EXEC sp_xml_preparedocument @idoc OUTPUT, @XML;

DECLARE @ItemID nvarchar(max);

DECLARE @ItemValue float;

DECLARE @ItemTimestamp DateTime;

DECLARE @ItemQuality int;

SELECT @ItemID = ItemID, @ItemValue = ItemValue, @ItemTimeStamp = ItemTimestamp, @ItemQuality = ItemQuality

FROM OPENXML(@idoc, 'ROOT/ItemSample')

WITH

(

ItemID nvarchar(50) '@ItemID',

ItemValue float '@ItemValue',

ItemTimestamp DateTime '@ItemTimestamp',

ItemQuality int '@ItemQuality'

)

if @ItemID is not null

begin

-- Update write items

if exists(select itemid from Items where ItemID = @ItemID)

begin

-- Item exsists update data

update Items set ItemValue = @ItemValue, ItemTimestamp= @ItemTimestamp, ItemQuality= @ItemQuality where ItemID =@ItemID

end

else

begin

-- Item does not exsist, insert it into table

insert into dbo.Items(ItemID,ItemValue,ItemTimestamp,ItemQuality)

SELECT @ItemID, @ItemValue , @ItemTimeStamp , @ItemQuality

end

end

-- Return all items regardless they have changed or not

select * from dbo.Items

end

This stored procedure takes in write item(s) and returns all items as read item(s). Add it to your SQL database.

You will need a table named "Items" you can use this query to create it.


USE [test]

GO

/****** Object: Table [dbo].[Items] Script Date: 30.06.2017 10.24.40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Items](

[ItemID] [nvarchar](50) NOT NULL,

[ItemValue] [float] NULL,

[ItemTimestamp] [datetime] NULL,

[ItemQuality] [int] NULL,

CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED

(

[ItemID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

As you can see from the stored procedure, the write items are passed in using XML. For multiple items, you can add a WHERE clause to the initial select, allowing you to distinguish between different items. However, for this example, we'll just be sending in a single value.

Once the stored procedure is on your database, change the following SQL Bee parameters:

The "SQL statement" should contain the name of the statement without any parameters or the EXEC keyword. This is because the "SQL statement type" is set to "Execute as stored procedure" so the parameters and EXEC will be added automatically. The "Table schema" property is set to "By rows", since we're sending back a row per value. The Timer interval is set to 1000 milliseconds, so we can see a response fairly rapidly.

After we apply the values, we need to add a single write item to the module. Do this by right-clicking on the SQL Bee module, going to "Add Item" and pressing on Write Item:

The name of the item doesn't matter in this case, the item name will be inserted into the table, so you can just press the "Add Item" button at the bottom of the dialog box:

Then press "Ok" to finish adding the Write Item. You can then alter the item to set it to any type you like.

Once the write item is setup, you can add a Read Item. Assume the Item table contains Item named ReadItem1 or you can add it with following query :


USE [test]

GO

INSERT INTO [dbo].[Items]

([ItemID]

,[ItemValue]

,[ItemTimestamp]

,[ItemQuality])

SELECT 'ReadItem1', 123.5 , '2017-06-30 10:06:08.000' , 192

GO

To add a Read Item, right-click on the SQL Bee module again and press Read Item. In the dialog box, instead of pressing Add Item, press Browse.

This will give you a list of available items, one of which should be ReadItem1 (and Write Item1 added previously):

Press the checkbox beside ReadItem1 and then OK. Press OK again to add the item.

The Read Item will now reflect any value you write in the Write Item. The Write Item value is sent into the stored procedure, read from the XML, and sent back through the SELECT statement at the bottom of the stored procedure.

Troubleshooting

An important point to note is that stored procedures called from the SQL Bee module may not contain temporary tables. Any stored procedure containing a temporary table will fail to run.

Apis Foundation 9

Connect to an SQL Server

Execute as text

Execute as stored procedure

Troubleshooting