Store BUFR files in MySql

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • tanyali
    New Member
    • Feb 2007
    • 43

    Store BUFR files in MySql

    Hi guys,

    BUFR : WMO format for weather observation data
    who knows how to store it info MySql ? please help ~~

    thanks a million~

    Tanya.
  • pbmods
    Recognized Expert Expert
    • Apr 2007
    • 5821

    #2
    Heya, Tanya.

    You can store BUFR data just like any other file. Simply create a TEXT column in your table, and be sure to mysql_real_esca pe_string() the contents before putting it in your query.

    Comment

    • tanyali
      New Member
      • Feb 2007
      • 43

      #3
      Originally posted by pbmods
      Heya, Tanya.

      You can store BUFR data just like any other file. Simply create a TEXT column in your table, and be sure to mysql_real_esca pe_string() the contents before putting it in your query.

      thanks for answering first !

      this is what I did : [code=mysql]
      create table bufr( id int auto_increment primary key, bufr_data text not null ) ;
      insert into bufr values( 1, mysql_real_esca kpe_string('/home/...../ob2007080900.gz '));[/code]

      and it returns :
      ERROR 1305 (42000): FUNCTION test.mysql_real _escape_string does not exist

      do I need to set something first before use it ?

      another thing is , the limitation of text type is 65,535 characters long
      and my BUFR files are up to 1120167 bytes,
      can it work ?

      Tanya

      Comment

      • pbmods
        Recognized Expert Expert
        • Apr 2007
        • 5821

        #4
        Heya, Tanya.

        mysql_real_esca pe_string() is a PHP function, not a MySQL function.

        Also, are you trying to store the actual file in the database, or just the path to the file?

        Comment

        • tanyali
          New Member
          • Feb 2007
          • 43

          #5
          Originally posted by pbmods
          Heya, Tanya.

          mysql_real_esca pe_string() is a PHP function, not a MySQL function.

          Also, are you trying to store the actual file in the database, or just the path to the file?
          Hi, pbmods
          so I need to insert BUFR files through PHP ,

          yes, I am trying to store the actual BUFR file in the database,
          so do the BUFR files need to be put in the same dir with the PHP page ?


          I still do not quite understand how to use this function to insert BUFR into db...

          Tanya

          Comment

          • tanyali
            New Member
            • Feb 2007
            • 43

            #6
            Originally posted by pbmods
            Heya, Tanya.

            mysql_real_esca pe_string() is a PHP function, not a MySQL function.

            Also, are you trying to store the actual file in the database, or just the path to the file?

            and this is what I used in PHP:
            [code=php]
            $query = sprintf("INSERT INTO (`id`, `name`, `number`,`date` ,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz , 0, 20070809,
            mysql_real_esca pe_string(ob200 7080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );

            mysql_query($qu ery, $link);

            if (mysql_affected _rows($link) > 0)
            echo "Product inserted\n";
            else
            echo "failure";[/code]

            PS: I am sure no problem with the db connection.

            but it returned "failure".. ....

            Comment

            • dafodil
              Contributor
              • Jul 2007
              • 389

              #7
              Please display the complete error message.

              Please also check your code below, I think it got complicated because you combined different methods as one statement.
              [code=php]
              $query = sprintf("INSERT INTO (`id`, `name`, `number`,`date` ,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz , 0, 20070809,
              mysql_real_esca pe_string(ob200 7080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );
              [/code]

              Comment

              • tanyali
                New Member
                • Feb 2007
                • 43

                #8
                Originally posted by dafodil
                Please display the complete error message.

                Please also check your code below, I think it got complicated because you combined different methods as one statement.
                [code=php]
                $query = sprintf("INSERT INTO (`id`, `name`, `number`,`date` ,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz , 0, 20070809,
                mysql_real_esca pe_string(ob200 7080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );
                [/code]
                stupid mistake...[code=php]
                $query = sprintf("INSERT INTO BUFR (`id`, `name`, `number`,`date` ,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz , 0, 20070809,
                mysql_real_esca pe_string(ob200 7080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );

                mysql_query($qu ery, $link);

                if (mysql_affected _rows($link) > 0)
                echo "Product inserted\n";
                else
                echo "failure";[/code]

                forgot the name of the table !!!

                but it seems that what has been inserted into the db is the name( only the string "ob2007080900.g z") instead of the actual BUFR file named ob2007080900.gz !

                ...

                Tanya

                Comment

                • pbmods
                  Recognized Expert Expert
                  • Apr 2007
                  • 5821

                  #9
                  Heya, Tanya.

                  Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

                  Instead of using the file name, you need to actually load the file's contents into a string and save that instead.

                  [code=php]
                  $data = mysql_real_esca pe_string(file_ get_contents('/path/to/file.ext'));

                  $sql = "
                  INSERT
                  INTO
                  `Table`
                  .
                  .
                  .
                  VALUES
                  (
                  '{$data}'
                  .
                  .
                  .
                  [/code]

                  Comment

                  • tanyali
                    New Member
                    • Feb 2007
                    • 43

                    #10
                    Hi pbmods,

                    thanks, it did work for files with limitation of 65535 byte !
                    but most of my files are much more than that up to 1120167 byte.

                    any other way to store them ?

                    thanks a million !!
                    Tanya.

                    Comment

                    • pbmods
                      Recognized Expert Expert
                      • Apr 2007
                      • 5821

                      #11
                      Heya, Tanya.

                      Because you are loading the file into memory to store it, you also need to increase PHP's memory limit.

                      Use the following statement:
                      [code=php]
                      ini_set('memory _limit', '1024M');
                      [/code]

                      Comment

                      • tanyali
                        New Member
                        • Feb 2007
                        • 43

                        #12
                        Hi pbmods,

                        I meant 65535 characters is the limitation of the type "text" in MySql.

                        Comment

                        • pbmods
                          Recognized Expert Expert
                          • Apr 2007
                          • 5821

                          #13
                          Heya, Tanya.

                          Originally posted by tanyali
                          Hi pbmods,

                          I meant 65535 characters is the limitation of the type "text" in MySql.
                          Good thing I'm on the ball with these things :P

                          Have a look at this document, towards the bottom. Instead of a TEXT, you'll probably want a LONGTEXT or LONGBLOB, depending on whether you are storing an ASCII or a Binary file, respectively.

                          Comment

                          • tanyali
                            New Member
                            • Feb 2007
                            • 43

                            #14
                            Originally posted by pbmods
                            Heya, Tanya.



                            Good thing I'm on the ball with these things :P

                            Have a look at this document, towards the bottom. Instead of a TEXT, you'll probably want a LONGTEXT or LONGBLOB, depending on whether you are storing an ASCII or a Binary file, respectively.
                            pbmods,
                            got it, use LONGTEXT!
                            How can I express my thanks to you.... I love you so much !!

                            Regards,
                            Tanya

                            Comment

                            • pbmods
                              Recognized Expert Expert
                              • Apr 2007
                              • 5821

                              #15
                              Heya, Tanya.

                              Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

                              Comment

                              Working...