Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2002
    Location
    Italy
    Posts
    36

    Exclamation Unanswered: INSERT INTO query for pictures data types

    INSERT INTO "categories"("categoryid", "categoryname", "description", "picture") VALUES (1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales', ?/some strange char...);

    This query don't run correctly. How can I do to run This?

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    pic data

    Do you want to store the picture as a binary in the database?

    Then you should use BLOB
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    May 2002
    Location
    Italy
    Posts
    36

    BLOB???

    What is BLOB? A postgre Data type?
    Another question? In the SQL Query to add the picture how can I do?
    For Example...

    INSERT INTO Authors(Name, Surname, Picture) VALUES ('John', 'Lucas', ...);

    (...) What char:

    [ ' ] For string
    and for pictures???

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    BLOB

    BLOB = Binary Large Object

    Is a datatype in postgresql.

    String you can take varchar
    But I'm taking always text.

    text costs more bytes than varchar but it has then no restrictions about the length.

    One question:

    What dou you store as a picture?

    give me a real example?

    I think you need blob
    Look at this url

    BLOB DOC
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    May 2002
    Location
    Italy
    Posts
    36

    Cool Real Example

    This is my real Example:

    I've got a Ms-Access Table to migrate in PostGreSQL.

    In ms-access table there is a pictures field cotaining strange chars (the picture data).

    Now... the query for PostGreSQL is:

    CREATE TABLE categories(categoryid char(254), categoryname char(50), description char(50), picture bytea);

    Is right???

    The query for the migration is...

    INSERT INTO "categories"("categoryid", "categoryname", "description", "picture") VALUES (1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales', '?/
    !???????????????A? ? ???h ? ?? V ( x  ? ?   ?? ? ?  ? A? a? aA ?A ???  ? A ? ?  ??  A ? ? ?aa A?? ?? a??????? A? ?   ? ?????????y?Aa?  ?? ?? I?????????y? ? ?  ?A???   ???????????J? A A   ? ???? ???????Aaa??  aA  A?? ?????? A   ?aa! ?? ?????p???q ?aAa  ????u???? A???????????A?   @ ???A?????  A ??g????????????o ????????????????  ? ????q?????????????%?????????w??????0?  A ??????????????????????????????????? ? A? a????????????????????????????????????? a ?????????????????????????????c????? ?A A ?????????????????????????????????????? ? ????????????????????????????????? ? a????v?????? ????????????????????????? AA ????g?????????????????????????????? ? a????F????????????????????????????!A??AA !????????????w???????????a ?????????I?? A??????????????????
    ??????????????????A A AA????????????????????????????????????I?? ???????????????????????????????????AA ??A ?A?@A??????????????????????y?-?s???  ??????a ?t?????????????????????a ??  ? ?? ?????????????????????0AaA A? ?? ??` ???????????????k???????? ?a  A?0? ???? ????????????????e????we~? A ?@f @ ?????????????????????????a??P?p ?? ???????????????a?d???????e?g ??? ?@ ???????????????????????????Fd?g?E??a??w??@?? ??????????????????????????? ?????E???t?? ?? ?????????????????????????????_????????p ?v????????????????????????????e?????????????????d ????????????????????????e??????????????D????????? ??????????????????????????????p?p? dd????????????????????????????????????4??` @?????????????????????????????????????P??? ????w???????????????`???????????????@?w???@ ?g?w???????????????????????????????p?? ????D?d?p???????????????????????????????7?w??????v ? ???????????????????????????????? ?????????? ???????????????????????????????? A???????p? ? d???????????
    ????????????????W@ ????? ?? ????? ? ????????????????????3?D D ?? ?? ?????? ?????????????????????? g? ?? ? ?????? ??????????????????????e ?? ?? ????d???????????????????????????????????7 ??w ??????????????????????????????? ???t ?@ ??????????????????????????????? ???p ?`??????????-F???????????????????????? @ ??????????????????????????????????? `???????t?????????????????????????? F `???????0 A???????????????????????? ?? ?????????? ??????????????????????? ?? ??????? ????????????????????????? ?@ ???????? ?JA???????????????????????? ? d?@ `???????? ???????????????????????q ?@?? ???????? ?? ??????????????????????p F??F  `?????? ?????????????????????????w ?? ?@`?????? ?????????????????????????? ??? ??????? ?????????????????????????p ??? ???????? `????????????????????????P ??D@ ???????? ????????????????????????? ???` ??????????????????????????????????? ???@ `????????f????????????????????????? ?? ?????????p?????????????
    ??????????? ?d? ?????????A????????????????????????? ?d `???????d?????????????????????????? ?@ ??????????????????????????????????? ? ??????????????????????????????????P ??` ?????????@????????????????????????p ???d ??????????????????????????????????? ???@ ??????????????????????????????????p? ??? ?????????????????????????????????p@ ??? ?w???????????????????????????????w ???D ?k????????????????????????????????$ ???f? ?5???w???????????????????????????W? ???d ??????D??????????????????????????? ?  ?@ ??????????????????????????????????@ ? ?? ??????????????????????????????????? ??@d ?????`???????????????????????????? ?? ?@ ?????????????????????????????????@ @??d ???w?????????????????????????+???? @ ??@ ???w?????????????????????????????u? F? ????v????????????????????????????s? ?` ??????????????????????????????????? ? ??????????????????????????????????? ? ? ???????????????????????????????????? ? ????????????????????????????????
    ???$ ??????????????????????????????????q?? ???????????????????????????????????@ ????v?????????????????????????????????@ ????p?????????????????????????????? ?@ ???????????????????????????????????r??@? ??? ????????????????????????????????A@?????????????? ??????????????????????????????  ????????????????????????????????????P? @? ????????????????????????????????????7???????????? ???????????????????????????????`?????????????????? ????????????????????????????????????????????????? ???????????????????? ?? @??????????????????????????????????????? ???????????????????????????????????????????? @?????????????????????????????????????????????u?? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ???????????a?????????????????????????????????????? ???????????????????????????????????????????????
    ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????? ? ??');

    This query don't run correctly Message Error: Unterminated quoted String...

  6. #6
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Question

    ok
    this picture data
    is this a string or is this a real file

    I mean did you made this string from a picture or do you have the string.

    when you only have this string you must save this in text. and you MUST escape some characters.

    Igf you have a file you only save the id from the BLOB in the table

    I must look it uo. don't know it by heart.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  7. #7
    Join Date
    May 2002
    Location
    Italy
    Posts
    36

    Wink And now?

    What are the characters I must remove and what would be the Query to be executed correctly?

  8. #8
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    characters

    as far as I know

    are these

    ---'---
    ---\---

    you must escape them
    \'
    \\

    that must be all of them
    the others have no effect

    try to insert this picture data as a string!
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  9. #9
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400
    And you should write the insert statement this way:

    INSERT INTO categories(categoryid, categoryname, description, picture) VALUES (1, 'Beverages', 'Soft drinks, coffees, teas, beers,......

    Maybe this was the error
    I don't have seen it til now
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  10. #10
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    Post what are the advantages in using BLOB.

    what are advantages and disadvantages in using BLOB?
    disk space is getting smaller after creating the database... thats what i see on it compared to linking files.

    pls... can you guys make some suggestions, comments or wild reactions.
    Last edited by MaleMan; 06-19-02 at 05:38.
    --- Hey! its me!!! the MaleMan ---

  11. #11
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    my personal opinion

    My personal opinino is not to use BLOB before you don't really need it.

    If you have huge amaount of data storing in a blob then think about it.

    You cannot easily with BLOB's.
    you must understand a bit of the handling.

    Read the docs at postgresql and you will see
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  12. #12
    Join Date
    Mar 2002
    Posts
    2

    a little how to about blob and postgresql

    hi all there:

    I was working a little around this subject and want to share my experiences with blobs and postgresql. This explanation is for PostgreSQL + Perl + DBI + DBD::Pg under Linux.
    The version i'm using are:
    postgres: 7.1.3
    perl 5.6.0
    DBI and DBD::Pg 1.30
    Linux Red Hat 7.1 kernel 2.4.9

    First, postgresql use a special data type to store blob named BYTEA
    then, for the experiment, create the following table:

    create table images (
    id integer not null, -- or what ever you want
    picture bytea
    );

    First step: inserting an image into the table: Create a test script,
    named as you like:

    #!/usr/bin/perl
    use DBI qw/:sql_types/;

    $dbh = DBI->connect("DBI:Pg:dbname=mybase");

    DBI-> trace(3,'/tmp/dbierr'); ## use to trace all db activity

    ### copy an image in the same directory you use to create the script
    ## of course you can put it in every place you like, just point to it
    ###
    my $imagen = "hotNtot-436d.gif";

    ### reading the file
    ##
    open A, "<$imagen";
    $img = "";
    while(read(A, $data, 1024)) {
    $img .= $data;
    }
    close A;

    ### now, inserting the image in the table:
    #

    ## determining the max size we allow to store
    ##
    $dbh->{LongReadLen} = 512 * 1024; ## 512 KB
    $dbh->{LongTruncOk} = 1;

    my $ID = 1; ### just for test

    my $salvaimg = "INSERT INTO images VALUES ($ID, ?)";

    my $sth = $dbh->prepare($salvaimg);

    ### this is the key to insert the blob
    ## here we are binding the blob, note that SQL_BINARY is
    ## defined in DBI qw/:sql_types/;
    ## and "$img" is the size in bytes of the image
    ## we got in the read loop:

    $sth->bind_param(1, "$img", SQL_BINARY) || die $dbh->errstr;

    $sth->execute($img); ## that it's !!!!
    $dbh -> disconnect();
    1;

    Now, take a look in the /tmp/dbierr file and see very interesting stuff

    Second Step: fetching the image.

    In this step, i create a directory visible in the web, and grant write
    permission to the web server user, in my case "nobody":

    $ mkdir /var/www/html/images
    $ chmod 775 /var/www/html/images
    $ chgrp nobody /var/www/html/images

    Now, the second script:

    #!/usr/bin/perl
    use DBI qw/:sql_types/;

    $dbh = DBI->connect("DBI:Pg:dbname=mybase");
    $dbh->{LongReadLen} = 512 * 1024;
    $dbh->{LongTruncOk} = 1;

    ### use again to trace all db activity and for this you may erase
    ## the previous content of the /tmp/dbierr file
    DBI-> trace(3,'/tmp/dbierr');

    $sth = $dbh -> prepare("SELECT picture from images");
    $sth -> execute();

    $SALIDA = $sth -> fetchrow_array;

    ### recontructing the image
    ##
    $IMA = "/var/www/html/images/recovered_image.gif";
    open A, ">$IMA";
    print A $SALIDA;
    close A;

    $dbh -> disconnect();
    1;

    Finally, open your browse and point to:

    http://yourserver/images/recoverd_image.gif

    With a litte patient, you can with easy transport this scripts into
    the language you use and of course, apply them in more complex
    situations.

    I hoppe you find this helpfull.

    hachiman
    Last edited by hachiman; 11-19-02 at 23:59.

  13. #13
    Join Date
    Nov 2003
    Posts
    1

    Re: a little how to about blob and postgresql

    hi,
    This script works fine with dataype of bytea type but
    fails for lo ,can u help in this

    Regards
    omkar

    Originally posted by hachiman
    hi all there:

    I was working a little around this subject and want to share my experiences with blobs and postgresql. This explanation is for PostgreSQL + Perl + DBI + DBD::Pg under Linux.
    The version i'm using are:
    postgres: 7.1.3
    perl 5.6.0
    DBI and DBD::Pg 1.30
    Linux Red Hat 7.1 kernel 2.4.9

    First, postgresql use a special data type to store blob named BYTEA
    then, for the experiment, create the following table:

    create table images (
    id integer not null, -- or what ever you want
    picture bytea
    );

    First step: inserting an image into the table: Create a test script,
    named as you like:

    #!/usr/bin/perl
    use DBI qw/:sql_types/;

    $dbh = DBI->connect("DBI:Pg:dbname=mybase");

    DBI-> trace(3,'/tmp/dbierr'); ## use to trace all db activity

    ### copy an image in the same directory you use to create the script
    ## of course you can put it in every place you like, just point to it
    ###
    my $imagen = "hotNtot-436d.gif";

    ### reading the file
    ##
    open A, "<$imagen";
    $img = "";
    while(read(A, $data, 1024)) {
    $img .= $data;
    }
    close A;

    ### now, inserting the image in the table:
    #

    ## determining the max size we allow to store
    ##
    $dbh->{LongReadLen} = 512 * 1024; ## 512 KB
    $dbh->{LongTruncOk} = 1;

    my $ID = 1; ### just for test

    my $salvaimg = "INSERT INTO images VALUES ($ID, ?)";

    my $sth = $dbh->prepare($salvaimg);

    ### this is the key to insert the blob
    ## here we are binding the blob, note that SQL_BINARY is
    ## defined in DBI qw/:sql_types/;
    ## and "$img" is the size in bytes of the image
    ## we got in the read loop:

    $sth->bind_param(1, "$img", SQL_BINARY) || die $dbh->errstr;

    $sth->execute($img); ## that it's !!!!
    $dbh -> disconnect();
    1;

    Now, take a look in the /tmp/dbierr file and see very interesting stuff

    Second Step: fetching the image.

    In this step, i create a directory visible in the web, and grant write
    permission to the web server user, in my case "nobody":

    $ mkdir /var/www/html/images
    $ chmod 775 /var/www/html/images
    $ chgrp nobody /var/www/html/images

    Now, the second script:

    #!/usr/bin/perl
    use DBI qw/:sql_types/;

    $dbh = DBI->connect("DBI:Pg:dbname=mybase");
    $dbh->{LongReadLen} = 512 * 1024;
    $dbh->{LongTruncOk} = 1;

    ### use again to trace all db activity and for this you may erase
    ## the previous content of the /tmp/dbierr file
    DBI-> trace(3,'/tmp/dbierr');

    $sth = $dbh -> prepare("SELECT picture from images");
    $sth -> execute();

    $SALIDA = $sth -> fetchrow_array;

    ### recontructing the image
    ##
    $IMA = "/var/www/html/images/recovered_image.gif";
    open A, ">$IMA";
    print A $SALIDA;
    close A;

    $dbh -> disconnect();
    1;

    Finally, open your browse and point to:

    http://yourserver/images/recoverd_image.gif

    With a litte patient, you can with easy transport this scripts into
    the language you use and of course, apply them in more complex
    situations.

    I hoppe you find this helpfull.

    hachiman

  14. #14
    Join Date
    Dec 2003
    Posts
    5

    Question Re: Question

    Originally posted by eperich
    ok
    this picture data
    is this a string or is this a real file

    I mean did you made this string from a picture or do you have the string.

    when you only have this string you must save this in text. and you MUST escape some characters.

    Igf you have a file you only save the id from the BLOB in the table

    I must look it uo. don't know it by heart.


    Hi all,
    but if I use bytea type, when I execute the insert query, the response is the same, "Warning: pg_exec(): Query failed: ERROR: parser: parse error at end of input . "

    This is my code.
    **********

    //Open file in binary mode
    $FileBinario = fopen("$upload_dir/$file_name", "rb");
    //Read file
    $DimensioneFile = filesize("$upload_dir/$file_name");

    $contenuto = fread($FileBinario, $DimensioneFile );
    //Close file
    fclose($FileBinario);

    //Insert in the table
    $conn = pg_pconnect("dbname=test user=postgres");
    pg_Exec ($conn, "INSERT INTO input_utente_dettaglio VALUES ($id_valore, $contenuto)");
    pg_close($conn);

    ************

    I'm working whit server web apache on linux whit php and postgreSQL

    Regards!!!

  15. #15
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    ok

    solution for this problem

    LOOK AT THE EXAMPLE BELOW
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

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