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_esca pe_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_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

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

[tanyali]

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

[tanyali]

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

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

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

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

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

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

[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:
[code=php]
ini_set('memory _limit', '1024M');
[/code]

[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 :)