How can I use bcp in with blobs and clobs?

[Enam]

I would like to know how bcp is used for blobs and clobs.

[Manish Negandhi]

On Jan 10, 5:52 pm, Enam wrote:
> I would like to know how bcp is used for blobs and clobs.

you can use -T parameter of BCP for text and image columns.If you use
bcp with default values for -T parameter, BCP will truncate text/image
fields over 32K, so before you to run bcp out, you need to know the
maximum length from of the
largest text or image column.You can calculate max length of the text/
image columns by using
select max(datalength(text_col_name))

here is the details of BCP with each option..
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.utility/html/utility/utility73.htm?

-HTH
Manish Negandhi
[TeamSybase Intern]

[SybaseNeal]

Hello,

I have not heard of "clob" data type used with ASE.
Where is this data stored?

For blob data (in ASE referred to as text or image data types),
bcp defaults to copying the first 32K of the data from the
text or image column. This is done to control bcp's memory
consumption because it preallocates memory for rows of data.

You can override this with bcp's "-T" parameter:
http://manuals.sybase.com/onlinebooks/group-as/asg1251e/utility/@Generic__BookTextView/6055;pt=6055#X

It is not advised to set "-T" equal to the maximum 2GB unless
you really have text/image columns of that size. Select a value
that accommodates the largest value in your table (plus more if
you think that the columns could have more data)....

1> select datalength(copy) from blurbs
2> go

-----------
721
385
995
857
1217
678

Thanks,
Neal

[Enam]

Hi Neal,

Sorry for the lack of clarity, I did actual mean Text and
Image data types.

However I am not copying from a table, instead I would like
to copy into a table. I have stored each image in a file
which I would now like to load into the image column. Is
their anyway of doing this with bcp?

Many Thanks,
Enam

[Rob Verschoor]

If you want to do something like copy a JPG image from a file into an image
column, you can do that by using a BCP format file.

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

<Enam> wrote in message news:4786370a.29a...@sybase.com...

[Enam]

I've create a format file with the following,
(abc.fmt)
10.1
1.
1 SYBBINARY 0 77270 "" 1 Photo

With command,
bcp test_db..test_table in "c:\Sybase\test_pic.jpg"
-f"c\Sybase\abc.fmt"

However this does not seem to work, also would I have to run
bcp on each file or could the image file path be
incorporated into the format file?

Many Thanks,
Enam

> If you want to do something like copy a JPG image from a
> file into an image column, you can do that by using a BCP
> format file.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------

> --- Rob Verschoor

[SybaseNeal]

Hello,

I don't think you should use SYBBINARY as that type corresponds
to the ASE "binary" type which is limited to a length of 255 in
some versions of ASE. I think the more appropriate data type
would be SYBIMAGE. Also, it appears you left out the "-T" parameter
which I mentioned earlier. For example:

C:\Temp>dir *.jpg
Directory of C:\Temp

07/27/2007 12:13 PM 15,468 cyr_error.JPG
07/31/2007 01:39 PM 168,172 EER.JPG
07/30/2007 02:44 PM 73,544 sybmigrate.JPG
08/07/2007 02:50 PM 43,194 syb_cent.JPG

C:\Temp>cat bcp.fmt
10.0
1
1 SYBIMAGE 0 168172 "" 1 c1

C:\Temp>bcp tempdb..t1 in EER.JPG -Usa -P -f bcp.fmt -T200000

Starting copy...

1 rows copied.
Clock Time (ms.): total = 109 Avg = 109 (9.17 rows per sec.)

C:\Temp>bcp tempdb..t1 in EER.JPG -Usa -P -f bcp.fmt

Starting copy...

1 rows copied.
Clock Time (ms.): total = 31 Avg = 31 (32.26 rows per sec.)

C:\Temp>isql -Usa -P -D tempdb
1> select datalength(c1) from t1
2> go

-----------
168172
32768

(2 rows affected)

So I think you would have to have a format file that matched
each JPG file. You would also have to run the bcp command for
each file, you can't just tell bcp to load everything from a
given directory.

It sounds like you may need a more custom solution. We provide
some sample C programs that you could customize. Look in the
directory c:\sybase\ocs-15_0\sample\dblib (or $SYBASE/$SYBASE_OCS/sample/dblibrary):
example9.c: Uses the au_pix table in the "pubs2" database
to show the basic outline of how to use the text
and image routines in DB-Library.

example10.c: Illustrates how to insert arbitrarily large image
data into the "pubs2" database.

example11.c: Illustrates how to retrieve arbitrarily large image
data from the "pubs2" database.

These samples use the DB-Library API which has been frozen in time.
Essentially, engineering will fix any bugs for this API but they
aren't adding new features to it. So for example, if you want to
access character columns greater than 255 bytes in length, you need
to use the CT-Library API (c:\sybase\ocs-15_0\sample\ctlib).

If you are more familiar with Java, you could look at our jConnect samples:
file:///C:/sybase/jConnect-6_0/sample2/index.html

In particular:
file:///C:/sybase/jConnect-6_0/sample2/TextPointers.java
or
file:///C:/sybase/jConnect-6_0/sample2/BinaryStream.java

We also have ODBC/ADO/OLEDB interfaces.

Thanks,
Neal