How to return a value from an Oracle select and return into inmsg

[Sanders@discussions.microsoft.com Paul Sanders]

Hello, I need to execute a select against a Oracle table and return a
sequence number that the table is maintaining for me.

SELECT X12DATA_LOAD.SEQ_STG_837_BIZTALK.NEXTVAL FROM DUAL;

I plan to do this from a pipeline component (c#) and then update a file
(837) with that value.

So two parts to the question. How to read the value from Oracle, and
secondly how to get the body of the inmsg to where I can edit it with this
value.

For the Oracle peice I have this so far, not fully working yet:

string sql = "SELECT X12DATA_LOAD.SEQ_STG_837_BIZTALK.NEXTVAL FROM
DUAL;";
OracleConnection conn = null;
OracleDataReader rdr = null;

public string execQuery(string sql) {

string ConnectionString = "Data Source=db;User
Id=x12data_load;Password=somepwd";
conn = new OracleConnection(ConnectionString);
// instantiate a new command with a query and connection
OracleCommand cmd = new OracleCommand(sql,conn);
// call execute reader to get query results
rdr = cmd.ExecuteReader();

//read the query result.
rdr.Read();

return rdr[0];

And this for capturing the inmsg to where I can edit it:

// Process the incoming message and extract the attachment.
private Stream ProcessMessage(IBaseMessage inmsg)
{
byte[] buffer = new byte[4096];
int count = 0;

//new memory stream to manipulate the message.
MemoryStream messageStream = new MemoryStream();

//stream pointer to the original stream.
Stream originalStream = inmsg.BodyPart.Data;

//write the original message to the memory stream
while (0 != (count = originalStream.Read(buffer, 0, 4096)))
{
messageStream.Write(buffer, 0, count);
}

//rewind the stream to the beginning
messageStream.Position = 0;

IBaseMessagePart bodyPart = inmsg.BodyPart;
if (bodyPart!=null)
{
byte[] prependByteData = ConvertToBytes(prependData);
byte[] appendByteData = ConvertToBytes(appendData);
Stream strm = new
FixMsgStream(bodyPart.GetOriginalDataStream(),
prependByteData, appendByteData, resManager);
bodyPart.Data = strm;
pc.ResourceTracker.AddResource( strm );
}
return inmsg;
}

To give credit, this code I've taken from the various BizTalk blogs out
there I've seen and really appreciate helping me get started. I could use
some advice on how to merge the two ideas.

Thanks,
Paul

[Daniel S]

Hi Paul,

I would highly recommend not doing Oracle queries from inside a
pipeline component. There are adapter for Oracle database that are
intended to do this kind of thing, so it would be best to create a two-
way send port with the Oracle adapter to get the data you need.

- Dan

On Jan 6, 8:11 am, Paul Sanders <Paul

[Paul Sanders]

Hi Dan, I hadn't given that much thought, I have an Oracle adapter
installed, but when I try to configure the transport properties of the
solicit response port I only have connection information to edit for Oracle.
I don't recall where this adapter came from, are there multiple Oracle
adapters, or am I just not familiar enough with it to know where to edit it.

Thanks.

"Daniel S" wrote:

> .
>

[Paul Sanders]

I generated a schema from the table and used a map hoping to capture the
value in a field not used in the destination schema, deployed it to an oracle
receive port, but not having any luck getting it that way. I'll keep playing.

Thanks