Re: inserting BLOB data

Kiran Annaiah wrote:

>The contents of the file.
>The reason i wanted to insert the contents of the file into the table was so I could query the table and be able to extract out substrings of that particular content.
>But i am wondering if that is even possible using a BLOB type or should i save it as a TEXT column.
> 
>The file basically has 550,000 columns of single digit numbers (except one/2 columns) represented in a single row.
>0 12345 1 2 1 0 1 2 1 1 1 1 1 1 1 2 0 1 0 1 1 1 1 1 1 1 ...etc
> 
>  
>
Kiran,

You do not want to do queries on a blob  or text ;) If you had to, you 
could insert the file as text (if you don't have to ensure the file 
stays exactly as it needs to be on disk) and then create a fulltext 
index on it.

>I will have when all is done around 100,000 of such files. 
> 
>SO, i am wondering if it is good idea of inserting them into a table so as to be able to query and do manipulations of the contents on the fly.
>
Very expensive. It all depends what you have to do. Perhaps just using 
perl/awk/sed on the file on disk?

> If i cannot do any of that then i really dont need to store it in a table
>


> 
>If it is indeed possible to do a select query in the contents with some substring or other manipulations - i am wondering if the QUERY will be efficient/fast enough
>
No.

> compared to just having a program open and read these files from a server and extract what i need. 
> 
>Thanks for all the suggestions and ideas.:)
>  
>

regards,

Patrick

> 
>-kiran
>
>
>
>  
>
>>Date: Tue, 11 Dec 2007 14:44:01 -0800> From: [email protected]> Subject: Re: inserting BLOB data> To: [email protected]> > Are you trying to insert the file - or the "contents" of the file?> > > --- Kenny Scott <[email protected]> wrote:> > > Actually, there are more differences these days between blob and text> > columns. But nothing major:> > > > http://dev.mysql.com/doc/refman/5.0/en/blob.html> > > > -- > > Kenny> > > > > > On Dec 11, 2007 9:39 PM, Kenny Scott <[email protected]> wrote:> > > Hi,> > >> > > Well, the only difference between the text and blob column types,> > > afaik, is that a blob is a case sensitive text column. Is the issue> > > that you have simply how to get the data in the table, rather than how> > > you would do it using Perl? Or am I completely missing the point?> > >> > > --> > > Kenny> > >> > >> > >> > > On Dec 11, 2007 9:30 PM, Kiran Annaiah <[email protected]> wrote:> > > >> > > >> > > > No i havent yet tried using the perl script to insert the data. I was> > > > looking around and seems like it is done mostly using a script.> > > >> > > > No the file is not binary. It is just a one row of numbers(about half> > > > million columns)> > > > 0 12333 1 1 0 1 2 2 1 1 ....so on> > > >> > > > should i use a different data type instead of BLOB?> > > >> > > >> > > >> > > > ________________________________> > > > > Date: Tue, 11 Dec 2007 21:18:37 +0000> > > > > From: [email protected]> > > > > To: [email protected]> > > > > Subject: Re: inserting BLOB data> > > >> > > >> > > > >> > > > > Hi,> > > > >> > > > > What are the file contents? Is it binary data, which is why you're> > > > > wanting to use the BLOB column type?> > > > >> > > > > What have you tried so far, and has it not worked?> > > > >> > > > > I'm guessing that you've tried the following type of thing:> > > > >> > > > > my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die $DBI::errstr;> > > > > my $sth = $dbh->prepare( "INSERT INTO t SET file_name=?, file_size=?,> > > > file=?" );> > > > > $sth->execute( $file_name, $file_size, $file );> > > > > $sth->finish();> > > > >> > > > > Did you try that and it didn't work for some reason?> > > > >> > > > > --> > > > > Kenny> > > > >> > > > >> > > > > On Dec 11, 2007 8:44 PM, Kiran Annaiah <[email protected]> wrote:> > > > > > I am trying to store my file contents in a table. I have never worked> > > > with BLOB data types before.> > > > > > I realised that inserting the file contents into the BLOB column is not> > > > the same as inserting into a regular column.> > > > > >> > > > > > The file size is about 2.2M. I have set the system variable> > > > max_allowed_packet to 3M.> > > > > >> > > > > > Can anyone please give me some ideas about how the file contents can be> > > > inserted?> > > > > >> > > > > > Table structure is as follows.> > > > > >> > > > > >> > > > +-----------+-----------------------+------+-----+---------+----------------+> > > > > > | Field | Type | Null | Key | Default | Extra |> > > > > >> > > > +-----------+-----------------------+------+-----+---------+----------------+> > > > > > | file_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |> > > > > > | file_name | varchar(25) | NO | | | |> > > > > > | file_size | mediumint(8) unsigned | NO | | | |> > > > > > | file | mediumblob | NO | | | |> > > > > >> > > > +-----------+-----------------------+------+-----+---------+----------------+> > > > > >> > > > > >> > > > > > _________________________________________________________________> > > > > > i'm is proud to present Cause Effect, a series about real people making> > > > a difference.> > > > > > http://im.live.com/Messenger/IM/MTV/?source=text_Cause_Effect> > > >> > > > ________________________________> > > > i'm is proud to present Cause Effect, a series about real people making a> > > > difference. Learn more> > >> > > > -- > > MySQL Perl Mailing List> > For list archives: http://lists.mysql.com/perl> > To unsubscribe: http://lists.mysql.com/[email protected]> > > > > > > -- > MySQL Perl Mailing List> For list archives: http://lists.mysql.com/perl> To unsubscribe: http://lists.mysql.com/[email protected]> 
>>    
>>
>_________________________________________________________________
>Get the power of Windows + Web with the new Windows Live.
>http://www.windowslive.com?ocid=TXT_TAGHM_Wave2_powerofwindows_122007
>  
>


-- 
Patrick Galbraith, Senior Programmer 
Grazr - Easy feed grazing and sharing
http://www.grazr.com 

Satyam Eva Jayate - Truth Alone Triumphs
Mundaka Upanishad





-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]