Hi,

Just checking to see if I'm missing something basic before I delve into this
mini project.

I have a situation where I need to pull values from multiple rows in a table
and pass them to a procedure in an external module.  The limitations of this
are that I must pass all of the values from all of the rows in the same call
and one of the fields is binary data.  Because of the binary data, I simply
can't deal with the data directly within the SQL script.

My thought process at this point was to build a CLOB with XML data
representing the values in the rows and pass the whole thing as one value to
the external module.  The sticking point is how to convert the binary data
into something the SQL script can handle.  Is there any similar function
available in a SQL script that will take either a entire row or single field
and return it as a string?

If not, I'll work up a custom function that I can pass a blob field and
return it back as a string.

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 The Atrex 13 beta is now available.
 Visit http://v13beta.atrex.com for more information.

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Terry,

<< I have a situation where I need to pull values from multiple rows in a
table and pass them to a procedure in an external module.  The limitations
of this are that I must pass all of the values from all of the rows in the
same call and one of the fields is binary data.  Because of the binary data,
I simply can't deal with the data directly within the SQL script. >>

Sure you can.  You can declare BLOB variables, parameters, and constants in
scripts, as well as pass them to external modules.  Binary constants
(literals) are simply defined as hex with a X'0101010101' format:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Binary_Types

<< My thought process at this point was to build a CLOB with XML data
representing the values in the rows and pass the whole thing as one value to
the external module.  The sticking point is how to convert the binary data
into something the SQL script can handle.  Is there any similar function
available in a SQL script that will take either a entire row or single field
and return it as a string? >>

Just CAST() all column values into strings, concatenate them together with a
delimiter, and pass them through as a one big string.

Do you know the columns in advance that you want to dump out to the external
module ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> Sure you can.  You can declare BLOB variables, parameters, and constants
> in scripts, as well as pass them to external modules.  Binary constants
> (literals) are simply defined as hex with a X'0101010101' format:

Definitely familiar with passing BLOBs as I do that in a couple of other
places.


> Just CAST() all column values into strings, concatenate them together with
> a delimiter, and pass them through as a one big string.

I'll take a shot at that.  I wasn't aware that you could cast a blob to a
string so that might be the ticket.

> Do you know the columns in advance that you want to dump out to the
> external module ?

I do.

Unfortunately with the complexity of this data (DevExpress Scheduler
component), I might just end up doing all of the updates at the application
end because of the way that it handles recurring events with where
individual entries within the series are missing or modified.  Since the
component has code already in place to determine the chain of events it
might be more expedient to handle it there.

--

---------------------------------------
 Terry Swiers
 Millennium Software, Inc.
 http://www.1000years.com
 http://www.atrex.com

 The Atrex 13 beta is now available.
 Visit http://v13beta.atrex.com for more information.

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------