Store BUFR files in MySql

[tanyali]

Hi guys,

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

thanks a million~

Tanya.

[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_escape_string() the contents before putting it in your query.

[tanyali]

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_escape_string() the contents before putting it in your query.

thanks for answering first !

this is what I did :

create table bufr( id int auto_increment primary key, bufr_data text not null ) ; 
insert into bufr values( 1, mysql_real_escakpe_string('/home/...../ob2007080900.gz'));

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

[pbmods]

Heya, Tanya.

mysql_real_escape_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?

[tanyali]

Heya, Tanya.

mysql_real_escape_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

[tanyali]

Heya, Tanya.

mysql_real_escape_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:

$query = sprintf("INSERT INTO  (`id`, `name`, `number`,`date`,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz, 0, 20070809,
mysql_real_escape_string(ob2007080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );
 
        mysql_query($query, $link);
 
        if (mysql_affected_rows($link) > 0)
            echo "Product inserted\n";
        else
            echo "failure";

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

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

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

$query = sprintf("INSERT INTO (`id`, `name`, `number`,`date`,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz, 0, 20070809,
mysql_real_escape_string(ob2007080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );
 

[tanyali]

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.

$query = sprintf("INSERT INTO (`id`, `name`, `number`,`date`,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz, 0, 20070809,
mysql_real_escape_string(ob2007080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );
 

stupid mistake...

$query = sprintf("INSERT INTO BUFR (`id`, `name`, `number`,`date`,`bufr_data`) VALUES (%d, '%s', %d, %d, '%s')", 1 , ob2007080900.gz, 0, 20070809,
mysql_real_escape_string(ob2007080900.gz(the name of a BUFR file put in the same dir with this PHP page), $link) );
 
mysql_query($query, $link);
 
if (mysql_affected_rows($link) > 0)
echo "Product inserted\n";
else
echo "failure";

forgot the name of the table !!!

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

...

Tanya

[pbmods]

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.

$data = mysql_real_escape_string(file_get_contents('/path/to/file.ext'));
 
$sql = "
INSERT
    INTO
        `Table`
    .
    .
    .
    VALUES
        (
            '{$data}'
    .
    .
    .
 

[tanyali]

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.

[pbmods]

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:

ini_set('memory_limit', '1024M');
 

[tanyali]

Hi pbmods,

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

[pbmods]

Heya, Tanya.

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.

[tanyali]

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

[pbmods]

Heya, Tanya.

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