insert BLOB with Derby tools

[Oleg Zenzin <oz...@lohika.com>]

hello derby

the pb is I cannot find any tool in Derby which allow me to insert
BLOB data without developing/runnig Java code. I need to execute some
SQL script that will insert data, let's say from file, into a field of
BLOB type. There are tools in DB2, MS_SQL or Oracle which usually use
some built-in functions or tools and help to execute SQL insert with
BLOB, but no such thing in Derby.

I need to execute sql like

INSERT INTO items (uri, content_type, data) values
('unique_url_used_as_key', 'application/xml', BLOB);

Is there anything providing this funcionality (except OJB)?

Thank you,
OZ
-- 

Re[2]: insert BLOB with Derby tools

[Oleg Zenzin <oz...@lohika.com>]

Andrew,
really thanks for your reply - I started to think my question a matter
of history.
In my case the BLOB is presumed not only for XML docs (we'd use CLOB,
or better varchar for that), but any resource, for instance - pics, or
some other format docs. We keep MIME-type field in that DB to mark
what type of content stored in that BLOB.
From your answer I could conclude that there are no concerns till now
abt BLOB direct insert/import/export (I saw that restriction for
import in the doc). From the first look it doesn't seems complex to
built-in a system function which works with input-output stream based
on URI/URL to resource of BLOB content. What do you think?

oz
-- 
                           mailto:ozenzin@lohika.com


Thursday, April 20, 2006, 8:44:41 AM, your letter:

> On 4/14/06, Oleg Zenzin <oz...@lohika.com> wrote:
>> I need to execute some
>> SQL script that will insert data, let's say from file, into a field of
>> BLOB type. There are tools in DB2, MS_SQL or Oracle which usually use
>> some built-in functions or tools and help to execute SQL insert with
>> BLOB, but no such thing in Derby.
>>
>> I need to execute sql like
>>
>> INSERT INTO items (uri, content_type, data) values
>> ('unique_url_used_as_key', 'application/xml', BLOB);
>>
>> Is there anything providing this funcionality (except OJB)?

> It's not clear to me, are you trying to insert the entire XML document
> into the BLOB? Or just the content (and not the markup)?

> If you want to insert the entire XML document, there is limited
> support for XML types in Derby, try searching the
> derby-dev@db.apache.org mailing list for how to use it.

> If you want to insert data/content from inside an XML documentinto a
> blob, that is currently outside the scope of Derby's import procedure
> as it doesn't handle inserting into a BLOB (see
> SYSCS_UTIL.SYSCS_IMPORT_DATA in the docs).

> andrew

Re: insert BLOB with Derby tools

[Andrew McIntyre <mc...@gmail.com>]

On 4/14/06, Oleg Zenzin <oz...@lohika.com> wrote:
> I need to execute some
> SQL script that will insert data, let's say from file, into a field of
> BLOB type. There are tools in DB2, MS_SQL or Oracle which usually use
> some built-in functions or tools and help to execute SQL insert with
> BLOB, but no such thing in Derby.
>
> I need to execute sql like
>
> INSERT INTO items (uri, content_type, data) values
> ('unique_url_used_as_key', 'application/xml', BLOB);
>
> Is there anything providing this funcionality (except OJB)?

It's not clear to me, are you trying to insert the entire XML document
into the BLOB? Or just the content (and not the markup)?

If you want to insert the entire XML document, there is limited
support for XML types in Derby, try searching the
derby-dev@db.apache.org mailing list for how to use it.

If you want to insert data/content from inside an XML documentinto a
blob, that is currently outside the scope of Derby's import procedure
as it doesn't handle inserting into a BLOB (see
SYSCS_UTIL.SYSCS_IMPORT_DATA in the docs).

andrew