RE: inserting BLOB data

I would think that a query would be faster than opening a file and parsing it.

You can simply read from the file and insert the data as long as it fits. 

I believe the longtext field type holds 4294967295 characters.

Maybe this:
open (FILE, $file) or die "Couldn't open $file: $!\n";
my @data = <FILE>;
close(FILE);

# if data is first line, and the only line
my $line = shift @data; 

my $dbh = DBI->connect('DBI:mysql:database','user') or die "Couldn't connect to database: " .
DBI->errstr;       
my $sth = $dbh->prepare("insert into table (SOME_KEY,FIELD) values('$key','$line')") or die
"Couldn't execute statement: " . $sth->errstr;
$sth->execute;


--- Kiran Annaiah <[email protected]> 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
>  
> 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. 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 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.:)
>  
> -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


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