DB Best blog

As Oracle DBMS_PIPE mechanism has no direct equivalent in SQL Server, we should provide a method of emulating it. This article describes a possible approach to this task.

The DBMS_PIPE package allows two or more sessions in the same instance to communicate between each other. To do that, one user creates a Pipe. This pipe can be public or private. If the pipe is private, then only its creator, his/her procedures, and SYSDBA can work with it. Private pipes must be removed explicitly.

Any user that has privileges to DBMS_PIPE package can work with a public pipe. Note that a public pipe is removed implicitly when it does not contain any messages. A message should be packed into local buffer before it can be sent to the pipe.

Only one user can receive a message from a pipe and, when received, the message is deleted from the pipe. To read a received message a user should unpack it from the buffer. Prior to unpacking the user can check the type of the item.
A user can also delete all non-received messages from a pipe, reset local message buffer and remove the pipe.

To emulate DBMS_PIPE package in SQL Server, we need to create a table where pipe messages will be stored.

create table pipe (
id int identity,
spid int NOT NULL,
login_time datetime NOT NULL,
privat varchar(max),
pipe_name varchar(max),
varbinary_value varbinary(max),
varchar_value varchar(8000),
nvarchar_value nvarchar(max),
datetime_value datetime,
pipe_status int,
order_msg_in_pipe int,
order_item_in_msg int,
CONSTRAINT [pk_dbms_pipe_table] PRIMARY KEY CLUSTERED (id ASC))
go

The table must contain fields for different message data types and fields for information about a message. Information about a message is: message id, session process id, sender login time, private pipe owner, pipe name, pipe status, message order and item order.
One row in this table represents one message item.

Session process id and sender login time are used to identify the user session.
Private pipe owner field contains the user name if a pipe is created as private, or NULL otherwise.

Pipe status can be:
0 — empty pipe or not sent messages packed in the buffer
1 — sent messages
2 — received messages

Most of common DBMS_PIPE subroutines can be emulated by SQL Server stored procedures which work with this table.

Create_pipe (pipename, private) — explicitly creates a private pipe if private is “true” (default) or a public pipe if private is “false”.
The emulation should insert a new row with empty data fields to the pipe table, and then set the information about the user session. If the pipe is private, write a user name in the field privat.

Pack_message (item) — places item in the local buffer.
Here we pack data to the pipe table with NULL as pipe_name, set the information about user session, and auto numerate items in the field item_order.

Send_message (pipename, timeout) — Sends a message to a pipe.
If the pipe is private then first check if this user is allowed to access this pipe. Then auto numerate messages in the field message_order. Update all items packed by this session and set pipe_name field, set pipe_status to 1.

Next_item_type — Determines the data type of the next item in a received message.
Reads next item row and returns the item type which it contains.

Receive_message(pipename, timeout) — Gets a message from a specified pipe.
Algorithm: if the pipe is private then first check if this user is allowed to access this pipe. Update all items of the first message by setting pipe_status to 2. Update information about user session to the session which receives the message.

Unpack_message (item OUT) — Retrieves the next data item from a message into a type-compatible variable. The algorithm: unpack the data from the pipe table with pipe_status = 2 to a variable. Next, delete the row from the table. If the message was from a private pipe create an empty private pipe with this name.

Remove_pipe (pipename) — Deletes an explicitly created pipe.
Algorithm: if the pipe is private then first check if this user is allowed to access this pipe. Delete all rows with this pipe_name and pipe_status 1 and delete empty pipes with this pipe_name.

Purge (pipename) — Removes unreceived messages from the specified pipe.
Algorithm: if the pipe is private then first check if this user is allowed to access this pipe. Delete all rows with this pipe_name and pipe_status 1. For private pipe, reserve empty private pipe with this name.

Reset_buffer — Resets the local message buffer.
Algorithm: Delete the items packed by this session.

Written by: Arthur Alchangian

Tagged as Conversion, DBMS_PIPE, emulation, Migration, Oracle, SQL Server + Categorized as Newsletters, Newsletter #5, January, 22, 2009