using bcp.exe
Bum...@gmail.com
in this database I have table: au_log
and in the table field: logfile type image
I want to load file c:\temp\test.rar with bcp.exe
username: sa
Can anyone write me how it to do?
SybaseNeal
With image or text data that contains embedded control characters such
as \t or \n you must be careful.
1) Either bcp out in the native format (-n):
bcp.exe au12..au_log out c:\temp\test.rar -Usa -P -n -T 123456789
(123456789 is the maximum size of the text/image column in your table
+ a little extra
The default maximum text/image size will be 32768. Anything larger
will be truncated
unless you use the "-T" parameter).
NOTE: Files copied out in the native format (-n) can only be used on
the platform they were created.
Don't try to take a native bcp file created on Windows and use it on
Solaris.
2) If you want the data in a character format (-c) you can use the
following command but
you must specify row & column terminators that you are sure are not in
your data:
bcp.exe au12..au_log out c:\temp\test.rar -Usa -P -c -T 123456789 -
t "^%^%" -r "$~$~"
If you do not specify "-t" and "-r" the default row terminator is "\r
\n" (on Windows) and the
default column terminator is \t. So if your text/image data also has
these characters bcp
will become very confused.
Thanks,
Neal
p.s. You can type the following command to find the maximum text
length of your column
to help you decide what a good value for "-T" might be:
select max(datalength(logfile)) from au12..au_log
Bum...@gmail.com
Bum...@gmail.com
SybaseNeal
Please review Chapter 4 of the Utility Guide "Using bcp to Transfer
Data to and from Adaptive Server"
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility54.htm
Thanks,
Neal
br...@sybase.com
Does the table have any other fields?
Does the file contain any data other than the image (i.e. values for
the other columns)?
Here is a reposting of something I wrote long ago...
While it is possible to store and retrieve images using bcp [which I
have
found only conveniant when the images are already in SQL Server, but
is then very good way to transfer them from one server to another] and
sql (see $SYBASE/scripts/installpix2 for an example of storing image
data using insert), you are probably best off with a dedicated client
program. Using plain TSQL, you have to use an ascii-encoded
binary represtentation of the file, as shown in installpix2.
Have a look at the getsend.c sample program. It comes
with the Open Client package and can be found in $SYBASE/samples.
The comments in the READ.ME file indicate that it demonstrates
text but can easily be modified to work with image.
With an Open Client program, I believe you can open an image file (or
use
copy and paste mechanisms), read the contents into a buffer, and pass
the
buffer directly to WRITETEXT.
However, if you don't want to go to the effort of writing an Open
Client
program, here is how to do it using a few utility programs and TSQL.
While the exact method used to create the installpix2 script seems to
have
been lost to history, it must have been something like the following
example.
(But not exactly the same, as the existing intallpix2 images cannot
be
succesfully extracted, they were converted to hex using some other
algorithm.)
Start with an image. For this example, I used the Sybase logo in the
upper right-hand corner of http://www.sybase.com (the image itself is
http://www.sybase.com/images/logo1.gif). Make a local copy of this
picture and name it "logo1.gif".
To include the image in a TSQL script, we need to code it in printable
ascii characters, such as a bin2hex routine. This could be written in
any
language (PERL, c, pascal, etc). A simple example is [don't bother to
compile
it, just understand what it is doing]:
bin2hex.c
--------------------------------------------------------------------------
#include "stdio.h"
main()
{
int c;
int linefeed;
linefeed = 0;
while (( c = getchar()) != EOF)
{
printf("%x",c/16);
printf("%x",c%16);
linefeed++;
if (linefeed == 20)
{
/* print a continuation marker and start a new line */
printf("\\\n");
linefeed = 0;
}
}
}
--------------------------------------------------------------------------
We need a place to put the image, so log into sql server and create a
table.
For the purposes of this example, run the following script:
use tempdb
go
create table my_pictures (x numeric(8,0) identity, pic image null)
go
Note that you will probably want much more information in a real
table,
such as
fields describing the image type (GIF, TIFF, Pict, etc.), size,
vertical
and
horizontal dimensions, name, etc.
I've modified the bin2hex to provide a TSQL wrapper, so for this
example, compile
the following "bin2sql.c" program and name the executable "bin2sql".
There is
lots of room for improvement here,in terms of passing parameters such
as
the file
name (for storage in a table field) and inserting metadata into a
table,
but I leave
that for you to develop.
bin2sql.c
--------------------------------------------------------------------------
--------
#include "stdio.h"
main()
{
int c;
int linefeed;
linefeed = 0;
printf("insert tempdb..mypictures values (0x00) \n");
printf("declare @val varbinary(16) \n");
printf("select @val = textptr(pic) from tempdb..mypictures \n");
printf("where x = @@identity \n");
printf("writetext tempdb..mypictures.pic @val \n") ;
printf("0x");
while (( c = getchar()) != EOF)
{
printf("%x",c/16);
printf("%x",c%16);
linefeed++;
if (linefeed == 20)
{
/* print a continuation marker and start a new line */
printf("\\\n");
linefeed = 0;
}
}
printf("\ngo \n");
}
--------------------------------------------------------------------------
---------
Now execute:
bin2sql < logo1.gif > logo1.sql
You should wind up with a file that looks like this (body has been
snipped):
logo1.sql
--------------------------------------------------------------------------
---------
insert tempdb..pict2 values (0x00)
declare @val varbinary(16)
select @val = textptr(pic) from tempdb..pict2
where x = @@identity
writetext tempdb..pict2.pic @val
0x47494638396196002b00f70000fffffff7f7f7ef\
efefe7e7e7f7f7ffefeff7e7e7efdedee7d6d6de\
ceced6c6c6cededeefd6d6e7cecedec6c6d6bdbd\
[snip]
0216bc80ca4f2e41833c3667379319b8340ab4a0\
074de8421bfad0886e4d4000003b
go
--------------------------------------------------------------------------
----------
Run this script through isql:
$SYBASE/bin/isql -Usa -Pxxxxxx -i logo1.sql
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x000100000000138a
If you log into SQL Server, you should see that tempdb..pict2 now
contains a row of data,
with the identity column (x) containing the value "1".
Now lets pull the image back out.
$SYBASE/bin/isql -Usa -Pxxxxxx -o logo1.hex << EOF
set nocount on
go
select pic from tempdb..mypictures where x = 1
go
EOF
The file logo1.hex will look something like this:
logo1.hex:
--------------------------------------------------------------------------
-------------
pic
------------------------------------------------------------------------
--------------------------------------------------------------------------
------
--------------------------------------------------------------------------
------
--------------------------------------------------------------------------
------
--------------------------------------------------------------------------
------
--------------------------------------------------------------------------
------
----------------------------------------
0x4d4d002a00000728800020503824160d0784426150b864361d0f8844625138a4562d1
78c466351
b8e4763d1f904864523924964d190080a552b01004070401ca6552e93cd66d08008100b
3b9d8180e
[snip]
00000000000000000000000000000000000000000000000000000000000000000000000000
000000
00000000000000000000000000000000000000000000000000000000000000000000000000
000000
000000000000000000000000000000
--------------------------------------------------------------------------
---------------
Now we need to strip off the header information (note that you could
have used the -b flag
for isql, if the isql version was 11.1 or higher) and convert the hex
encoded data back to
a binary file. For that, we need another program (this one strips the
header by disposing
characters until it finds an "x", which in this simple example occurs
only in that leading
"0x" hexidecimal data indicator.):
hex2bin.c:
--------------------------------------------------------------------------
---------------
#include "stdio.h"
main()
{
int c;
int highbits;
int c_high;
int c_low;
highbits = 1; /* 1 if we are currently reading the high bits of the
character */
/* skip all headers, etc, until character after leading "0x" */
while ((( c = getchar()) != EOF) && (c != 'x')) ;
/* convert hex representation of data to binary */
while (( c = getchar()) != EOF)
{
if ((c >= '0') && (c <= '9'))
{
if (highbits)
{
c_high = (c - '0');
}
else
{
c_low = (c - '0');
}
}
if ((c >= 'A') && (c <= 'F'))
{
if (highbits)
{
c_high = (c - 'A');
}
else
{
c_low = (c - 'A');
}
}
if ((c >= 'a') && (c <= 'f'))
{
if (highbits)
{
c_high = ((c - 'a') +10);
}
else
{
c_low = ((c - 'a') +10 );
}
}
if ((highbits==0) && isxdigit(c))
{
printf("%c",((c_high*16)+c_low));
highbits = 1;
}
else
{
if (isxdigit(c)) highbits = 0;
}
}
}
--------------------------------------------------------------------------
----------------------
Run hex2bin on logo1.hex, we should get our original image back.
hex2bin < logo1.hex > retreived_logo.gif
'diff' should reveal no difference between the two, and you should be
able to view
retrieved_logo.gif with your favorite gif-format image viewer.
Admitedly, this is a very crude example, but I trust it gives you the
starting point you need.
--
Bret Halford Imagine my disappointment
Sybase Inc. in learning the true
nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303