Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Unanswered: Image store in DB

    I have a requirement to store digital image like notary stamp or signature into sybase table and manipulate.
    can anybody help me in this regards..
    Rajib Banerjee

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    I started my tests on IMAGE using Bret Harford's example. Here's is Bret's response on the newsgroup from few years back.

    ================================================
    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.

    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..pict2 values (0x00) \n");
    printf("declare @val varbinary(16) \n");
    printf("select @val = textptr(pic) from tempdb..pict2 \n");
    printf("where x = @@identity \n");
    printf("writetext tempdb..pict2.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..pict2 where x = 1
    go
    EOF

    The file logo1.hex will look something like this:

    logo1.hex:
    ---------------------------------------------------------------------------------------
    pic







    ------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    ----------------------------------------

    0x4d4d002a00000728800020503824160d0784426150b86436 1d0f8844625138a4562d1
    78c466351
    b8e4763d1f904864523924964d190080a552b01004070401ca 6552e93cd66d08008100b
    3b9d8180e
    [snip]
    00000000000000000000000000000000000000000000000000 000000000000000000000000000000
    00000000000000000000000000000000000000000000000000 000000000000000000000000000000
    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.
    ================================================== ===

  3. #3
    Join Date
    Nov 2003
    Posts
    11
    Thanks a lot.
    I will try and let you know.
    Rajib Banerjee

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •