开发者

How to put a message on a Websphere MQ Queue from SQL Server sp?

Is there an API to connect to a Websphere MQ queue from an SQL Server stored procedu开发者_开发技巧re and put a message onto a queue?

If not, what would be the best way to achieve this?


The solution I am going to use for this is to write a CLR stored procedure and deploy this onto SQL Server.

Inside the CLR stored proc I will use the MQ .NET api.

Update: I created a stored proc using the following code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using IBM.WMQ;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static int MQStoredProc(String queueManager, String queueName, String messageText)
    {
        //MQEnvironment.Hostname = "localhost";
        //MQEnvironment.Port = 1414;
        //MQEnvironment.Channel = "SYSTEM.DEF.SVRCONN";

        MQQueueManager mqQMgr = null;          // MQQueueManager instance
        MQQueue mqQueue = null;         // MQQueue instance

        try
        {
            mqQMgr = new MQQueueManager(queueManager);
            mqQueue = mqQMgr.AccessQueue(queueName, MQC.MQOO_OUTPUT + MQC.MQOO_FAIL_IF_QUIESCING);   // open queue for output but not if MQM stopping

            if (messageText.Length > 0)
            {
                // put the next message to the queue
                MQMessage mqMsg = new MQMessage();
                mqMsg.WriteString(messageText);
                mqMsg.Format = MQC.MQFMT_STRING;
                MQPutMessageOptions mqPutMsgOpts = new MQPutMessageOptions();
                mqQueue.Put(mqMsg, mqPutMsgOpts);
            }

            return 0;
        }
        catch (MQException mqe)
        {
            return ((int)mqe.Reason);        
        }
        finally
        {
            if (mqQueue != null)
                mqQueue.Close();
            if (mqQMgr != null)
                mqQMgr.Disconnect();
        }
    }
};

This is not production ready but is inserting messages successfully on a queue manager running on the same server as the SQL server in bindings mode.


There is a much simpler solution than that..Check this out.

http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fbc34040_.htm


The simplest way I can think of is to, write the information on to a file and then use rfhutil to export the message onto queue. This would require manual intervention. The other option would be to write a simple java application using JMS and JDBC.


The .NET CLR approach would have been my suggestion, too. I'd be curious to see an example of the code, I've never tried that before! Should work I guess.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜