inserting BLOB data

--_0866f382-de1c-4a6d-b66a-e2593b0db117_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

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_pac=
ket to 3M.

Can anyone please give me some ideas about how the file contents can be ins=
erted?

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=92m is proud to present Cause Effect, a series about real people making a=
difference.
http://im.live.com/Messenger/IM/MTV/?source=3Dtext_Cause_Eff ect=

--_0866f382-de1c-4a6d-b66a-e2593b0db117_--

RE: inserting BLOB data

--_abc70698-e3ef-4d1f-8c58-69416d42c698_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


No i havent yet tried using the perl script to insert the data. I was looki=
ng 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 milli=
on 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: kenny@kennyscott.co.uk
> To: anna3144@hotmail.com
> Subject: Re: inserting BLOB data
>=20
> Hi,
>=20
> What are the file contents? Is it binary data, which is why you're
> wanting to use the BLOB column type?
>=20
> What have you tried so far, and has it not worked?
>=20
> I'm guessing that you've tried the following type of thing:
>=20
> my $dbh =3D DBI->connect( "dbi:mysql:test", "root" ) or die $DBI::errstr;
> my $sth =3D $dbh->prepare( "INSERT INTO t SET file_name=3D?, file_size=3D=
?, file=3D?" );
> $sth->execute( $file_name, $file_size, $file );
> $sth->finish();
>=20
> Did you try that and it didn't work for some reason?
>=20
> --=20
> Kenny
>=20
>=20
> On Dec 11, 2007 8:44 PM, Kiran Annaiah wrote:
> > I am trying to store my file contents in a table. I have never worked w=
ith 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_incre=
ment |
> > | 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=3Dtext_Cause_Eff ect

____________________________________________________________ _____
i=92m is proud to present Cause Effect, a series about real people making a=
difference.
http://im.live.com/Messenger/IM/MTV/?source=3Dtext_Cause_Eff ect=

--_abc70698-e3ef-4d1f-8c58-69416d42c698_--

Re: inserting BLOB data

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 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: kenny@kennyscott.co.uk
> > To: anna3144@hotmail.com
> > 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 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_Effec t
>
> ________________________________
> 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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

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 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 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: kenny@kennyscott.co.uk
> > > To: anna3144@hotmail.com
> > > 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 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_Effec t
> >
> > ________________________________
> > 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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

Are you trying to insert the file - or the "contents" of the file?


--- Kenny Scott 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 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 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: kenny@kennyscott.co.uk
> > > > To: anna3144@hotmail.com
> > > > 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 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_Effec t
> > >
> > > ________________________________
> > > 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/perl?unsub=gregmeckes@yahoo.com
>
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

Here's a cool article:

http://www.james.rcpt.to/programs/mysql/blob/


--- Kenny Scott 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 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 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: kenny@kennyscott.co.uk
> > > > To: anna3144@hotmail.com
> > > > 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 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_Effec t
> > >
> > > ________________________________
> > > 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/perl?unsub=gregmeckes@yahoo.com
>
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

Kiran,

May I recommend that you not store blobs such as files in the database?
The database will handle it, but it you would get much better
performance by having the files in a directory structure and a fast
lookup table to store the file's location on disk. Also, it'll make
backups much faster as well as use less memory - when you retrieve/store
a blob, it requires that much memory in the MySQL server.

Alas, If you absolutely must put the file in the database, you'll want
to at least have two tables, both 1:1. The first table will be a fast
lookup table with all columns except the blob, including "file_id". The
second table will contain the blob and "file_id" as the first table. The
idea being that the first table is for lookups, second for retrieving
the data if you need it.

You want to avoid performing queries on a table will blobs as much as
possible.

As far as storing the blob (if you must ;) just store it as you would
any other data. Gone are the days when you had to pre-calculate the size
of the blob prior to insert.

--Patrick

Kiran Annaiah 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_Effe ct
>
>


--
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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: inserting BLOB data

--_df15a20b-7d3b-46ad-b5ef-365359a173a1_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

The contents of the file.
The reason i wanted to insert the contents of the file into the table was s=
o I could query the table and be able to extract out substrings of that par=
ticular content.
But i am wondering if that is even possible using a BLOB type or should i s=
ave it as a TEXT column.
=20
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
=20
I will have when all is done around 100,000 of such files.=20
=20
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 c=
annot do any of that then i really dont need to store it in a table.=20
=20
If it is indeed possible to do a select query in the contents with some sub=
string or other manipulations - i am wondering if the QUERY will be efficie=
nt/fast enough compared to just having a program open and read these files =
from a server and extract what i need.=20
=20
Thanks for all the suggestions and ideas.:)
=20
-kiran



> Date: Tue, 11 Dec 2007 14:44:01 -0800> From: gregmeckes@yahoo.com> Subjec=
t: Re: inserting BLOB data> To: perl@lists.mysql.com> > Are you trying to i=
nsert the file - or the "contents" of the file?> > > --- Kenny Scott @kennyscott.co.uk> wrote:> > > Actually, there are more differences these d=
ays 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 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=
wrote:> > > >> > > >> > > > No i havent yet tried u=
sing the perl script to insert the data. I was> > > > looking around and se=
ems 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 diff=
erent data type instead of BLOB?> > > >> > > >> > > >> > > > ______________=
__________________> > > > > Date: Tue, 11 Dec 2007 21:18:37 +0000> > > > > =
From: kenny@kennyscott.co.uk> > > > > To: anna3144@hotmail.com> > > > > Sub=
ject: 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 tha=
t you've tried the following type of thing:> > > > >> > > > > my $dbh =3D D=
BI->connect( "dbi:mysql:test", "root" ) or die $DBI::errstr;> > > > > my $s=
th =3D $dbh->prepare( "INSERT INTO t SET file_name=3D?, file_size=3D?,> > >=
> file=3D?" );> > > > > $sth->execute( $file_name, $file_size, $file );> >=
> > > $sth->finish();> > > > >> > > > > Did you try that and it didn't wor=
k for some reason?> > > > >> > > > > --> > > > > Kenny> > > > >> > > > >> >=
> > > On Dec 11, 2007 8:44 PM, Kiran Annaiah wrote:=
> > > > > > I am trying to store my file contents in a table. I have never =
worked> > > > with BLOB data types before.> > > > > > I realised that inser=
ting the file contents into the BLOB column is not> > > > the same as inser=
ting 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 cont=
ents can be> > > > inserted?> > > > > >> > > > > > Table structure is as fo=
llows.> > > > > >> > > > > >> > > > +-----------+-----------------------+--=
----+-----+---------+----------------+> > > > > > | Field | Type | Null | K=
ey | Default | Extra |> > > > > >> > > > +-----------+---------------------=
--+------+-----+---------+----------------+> > > > > > | file_id | smallint=
(5) unsigned | NO | PRI | NULL | auto_increment |> > > > > > | file_name | =
varchar(25) | NO | | | |> > > > > > | file_size | mediumint(8) unsigned | N=
O | | | |> > > > > > | file | mediumblob | NO | | | |> > > > > >> > > > +--=
---------+-----------------------+------+-----+---------+--- -------------+>=
> > > > >> > > > > >> > > > > > __________________________________________=
_______________________> > > > > > i'm is proud to present Cause Effect, a =
series about real people making> > > > a difference.> > > > > > http://im.l=
ive.com/Messenger/IM/MTV/?source=3Dtext_Cause_Effect> > > >> > > > ________=
________________________> > > > i'm is proud to present Cause Effect, a ser=
ies about real people making a> > > > difference. Learn more> > >> > > > --=
> > MySQL Perl Mailing List> > For list archives: http://lists.mysql.com/p=
erl> > To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgregmeckes@yahoo=
..com> > > > > > > -- > MySQL Perl Mailing List> For list archives: http://l=
ists.mysql.com/perl> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dan=
na3144@hotmail.com>=20
____________________________________________________________ _____
Get the power of Windows + Web with the new Windows Live.
http://www.windowslive.com?ocid=3DTXT_TAGHM_Wave2_powerofwin dows_122007=

--_df15a20b-7d3b-46ad-b5ef-365359a173a1_--

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 = ;
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 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: gregmeckes@yahoo.com> Subject: Re: inserting BLOB
> data> To: perl@lists.mysql.com> > Are you trying to insert the file - or the "contents" of the
> file?> > > --- Kenny Scott 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 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
> 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: kenny@kennyscott.co.uk> > > > > To: anna3144@hotmail.com> > >
> > > 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 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_Effec t> > > >> > > >
> ________________________________> > > > 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/perl?unsub=gregmeckes@yahoo.com> > > > > > > -- > MySQL Perl Mailing
> List> For list archives: http://lists.mysql.com/perl> To unsubscribe:
> http://lists.mysql.com/perl?unsub=anna3144@hotmail.com>
> ____________________________________________________________ _____
> Get the power of Windows + Web with the new Windows Live.
> http://www.windowslive.com?ocid=TXT_TAGHM_Wave2_powerofwindo ws_122007


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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 wa=
s so I could query the table and be able to extract out substrings of tha=
t particular content.
>But i am wondering if that is even possible using a BLOB type or should =
i save it as a TEXT column.
>=20
>The file basically has 550,000 columns of single digit numbers (except o=
ne/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
>=20
> =20
>
Kiran,

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

>I will have when all is done around 100,000 of such files.=20
>=20
>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=20
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 tab=
le
>


>=20
>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 ef=
ficient/fast enough
>
No.

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

regards,

Patrick

>=20
>-kiran
>
>
>
> =20
>
>>Date: Tue, 11 Dec 2007 14:44:01 -0800> From: gregmeckes@yahoo.com> Subj=
ect: Re: inserting BLOB data> To: perl@lists.mysql.com> > Are you trying =
to insert the file - or the "contents" of the file?> > > --- Kenny Scott =
wrote:> > > Actually, there are more differences=
these days between blob and text> > columns. But nothing major:> > > > h=
ttp://dev.mysql.com/doc/refman/5.0/en/blob.html> > > > -- > > Kenny> > > =
> > > On Dec 11, 2007 9:39 PM, Kenny Scott 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 miss=
ing the point?> > >> > > --> > > Kenny> > >> > >> > >> > > On Dec 11, 200=
7 9:30 PM, Kiran Annaiah 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(ab=
out 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: kenny@kennyscott.co.uk> > > >=
> To: anna3144@hotmail.com> > > > > Subject: Re: inserting BLOB data> > =
> >> > > >> > > > >> > > > > Hi,> > > > >> > > > > What are the file cont=
ents? Is it binary data, which is why you're> > > > > wanting to use the =
BLOB column type?> > > > >> > > > > What have you tried so far, and has i=
t not worked?> > > > >> > > > > I'm guessing that you've tried the follow=
ing type of thing:> > > > >> > > > > my $dbh =3D DBI->connect( "dbi:mysql=
:test", "root" ) or die $DBI::errstr;> > > > > my $sth =3D $dbh->prepare(=
"INSERT INTO t SET file_name=3D?, file_size=3D?,> > > > file=3D?" );> > =
> > > $sth->execute( $file_name, $file_size, $file );> > > > > $sth->fini=
sh();> > > > >> > > > > Did you try that and it didn't work for some reas=
on?> > > > >> > > > > --> > > > > Kenny> > > > >> > > > >> > > > > On Dec=
11, 2007 8:44 PM, Kiran Annaiah 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 th=
e 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 con=
tents can be> > > > inserted?> > > > > >> > > > > > Table structure is as=
follows.> > > > > >> > > > > >> > > > +-----------+---------------------=
--+------+-----+---------+----------------+> > > > > > | Field | Type | N=
ull | 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 p=
resent Cause Effect, a series about real people making> > > > a differenc=
e.> > > > > > http://im.live.com/Messenger/IM/MTV/?source=3Dtext_Cause_Ef=
fect> > > >> > > > ________________________________> > > > i'm is proud t=
o present Cause Effect, a series about real people making a> > > > differ=
ence. Learn more> > >> > > > -- > > MySQL Perl Mailing List> > For list a=
rchives: http://lists.mysql.com/perl> > To unsubscribe: http://lists.mysq=
l.com/perl?unsub=3Dgregmeckes@yahoo.com> > > > > > > -- > MySQL Perl Mail=
ing List> For list archives: http://lists.mysql.com/perl> To unsubscribe:=
http://lists.mysql.com/perl?unsub=3Danna3144@hotmail.com>=20
>> =20
>>
>___________________________________________________________ ______
>Get the power of Windows + Web with the new Windows Live.
>http://www.windowslive.com?ocid=3DTXT_TAGHM_Wave2_powerofwi ndows_122007
> =20
>


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

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/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: inserting BLOB data

On Wed, 2007-12-12 at 08:45 -0500, Patrick Galbraith wrote:

> Kiran,
>
> May I recommend that you not store blobs such as files in the database?
> The database will handle it, but it you would get much better
> performance by having the files in a directory structure and a fast
> lookup table to store the file's location on disk. Also, it'll make
> backups much faster as well as use less memory - when you retrieve/store
> a blob, it requires that much memory in the MySQL server.

I realise that this is the conventional wisdom. However let me share an
experience that goes against the grain. We use the 'dbamil' mail / imap
server, which stores all mail in a database ( MySQL for us ).

Performance is much, much, much faster than it ever used to be with a
file-based imap server. In fact, I was starting to worry when we had
about 2 GB of email, because the server had *constant* disk activity
from people checking their email, and retrieving a message would
sometimes take up to a minute, and other times simply fail. Since
migrating to dbmail, folder listing is instantaneous, and email
retrieval is *always* under 5 seconds, and usually within 2 seconds.
Also, disk activity is way down. We now have 20 GB of email. People
insist on emailing us huge PDFs, and then we insist of keeping these
emails around ... forever.

On the issue of backups, it might be slower to back up a DB with huge
blobs in it, but it sure is easier. And there's no 'directory sprawl',
permissions worries ( ie people being able to access the blobs because
they're just sitting there on the network ), etc, etc.

Having said all that, for our *internal* PDF archiving, I have done as
recommended, and stored the files on a server, linked with a lookup
table in MySQL. To be perfectly honest, it freaks me out. Users must
have permission to create, overwrite and delete PDFs ( for example, in
case we render a PDF, see a mistake, then make a change and re-render
it ). It happens all the time. Since users need all these permissions,
there's nothing to stop them from 'accidentally' deleting PDFs en-masse.
Or renaming them. Or dragging them somewhere else. If they were in a
database, we'd have strict control over when and how they're deleted /
updated.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: inserting BLOB data

I agree that if all you want to do is find some strings in file there are
simpler ways; Perl would be my choice.

You'll find my attempt at this below; it 'greps' binary files (it's been a
boring afternoon ;-)

...snip...
>
>>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.
... snip ...
>Very expensive. It all depends what you have to do.
>Perhaps just using
>perl/awk/sed on the file on disk?


# Finds a pattern (regular expression) in a binary file(s) (or standard
input)
# Prints out the position in the file of each occurence of the pattern
# and the occurence itself (separated by a tab)

# Usage: perl bingrep.pl < file ... >
#
# example : perl bingrep.pl 321 myData.dat / finds
'321'
# example : perl bingrep.pl [AD]C myData.dat / finds
'AC' or 'DC'
# example : perl bingrep.pl [0-9]{3}-[0-9]{3}-[0-9]{4} / finds
phone numbers

use English;

# Ignore newlines so file looks like one big string of characters.
undef $INPUT_RECORD_SEPARATOR;

# Slurp the entire file into a single variable
$file = <>;

# The 1st argument on the command line is the pattern we're looking for.
$regularExpression = shift @ARGV;

# Split the file using the pattern as the delimiter, retaining the
# delimiter. Resulting array has the delimeter in every other element.
@parts = split( /($regularExpression)/, $file );

$filePostion = 0;

for ( $i = 0; $i < $#parts; $i += 2 )
{
$filePostion += length( $parts[ $i ] );

print "$filePostion\t$parts[ $i+1]\n";

$filePostion += length( $parts[ $i+1 ] );
}




--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

Daniel,

Very interesting. Do you also use replication? What storage engine are
your tables using?

regards,

Patrick

Daniel Kasak wrote:

>On Wed, 2007-12-12 at 08:45 -0500, Patrick Galbraith wrote:
>
>
>
>>Kiran,
>>
>>May I recommend that you not store blobs such as files in the database?
>>The database will handle it, but it you would get much better
>>performance by having the files in a directory structure and a fast
>>lookup table to store the file's location on disk. Also, it'll make
>>backups much faster as well as use less memory - when you retrieve/store
>>a blob, it requires that much memory in the MySQL server.
>>
>>
>
>I realise that this is the conventional wisdom. However let me share an
>experience that goes against the grain. We use the 'dbamil' mail / imap
>server, which stores all mail in a database ( MySQL for us ).
>
>Performance is much, much, much faster than it ever used to be with a
>file-based imap server. In fact, I was starting to worry when we had
>about 2 GB of email, because the server had *constant* disk activity
>from people checking their email, and retrieving a message would
>sometimes take up to a minute, and other times simply fail. Since
>migrating to dbmail, folder listing is instantaneous, and email
>retrieval is *always* under 5 seconds, and usually within 2 seconds.
>Also, disk activity is way down. We now have 20 GB of email. People
>insist on emailing us huge PDFs, and then we insist of keeping these
>emails around ... forever.
>
>On the issue of backups, it might be slower to back up a DB with huge
>blobs in it, but it sure is easier. And there's no 'directory sprawl',
>permissions worries ( ie people being able to access the blobs because
>they're just sitting there on the network ), etc, etc.
>
>Having said all that, for our *internal* PDF archiving, I have done as
>recommended, and stored the files on a server, linked with a lookup
>table in MySQL. To be perfectly honest, it freaks me out. Users must
>have permission to create, overwrite and delete PDFs ( for example, in
>case we render a PDF, see a mistake, then make a change and re-render
>it ). It happens all the time. Since users need all these permissions,
>there's nothing to stop them from 'accidentally' deleting PDFs en-masse.
>Or renaming them. Or dragging them somewhere else. If they were in a
>database, we'd have strict control over when and how they're deleted /
>updated.
>
>--
>Daniel Kasak
>IT Developer
>NUS Consulting Group
>Level 5, 77 Pacific Highway
>North Sydney, NSW, Australia 2060
>T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
>email: dkasak@nusconsulting.com.au
>website: http://www.nusconsulting.com.au
>
>
>
>


--
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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

On Wed, 2007-12-12 at 20:57 -0500, Patrick Galbraith wrote:

> Daniel,
>
> Very interesting. Do you also use replication?

No. We're happy enough with nightly ( full ) backups, and the
transaction logs. But there is often talk of replication in the DBMail
mailing list, so certainly others are doing it.

> What storage engine are your tables using?

Mostly InnoDB. DBMail uses InnoDB for everything, and this is by *far*
the biggest user of MySQL now ( 20 GB of email, compared to 2 GB of
everything else ). I also use InnoDB for larger tables ( we ran into
record-locking problems with MS Access and MyISAM, which disappeared
completely when we converted tables to InnoDB. We've since largely moved
away from Access, but we're happy with InnoDB's performance ). We use
MyISAM for limited use tables, or read-only tables.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

Daniel Kasak wrote:

>On Wed, 2007-12-12 at 20:57 -0500, Patrick Galbraith wrote:
>
>
>
>>Daniel,
>>
>>Very interesting. Do you also use replication?
>>
>>
>
>No. We're happy enough with nightly ( full ) backups, and the
>transaction logs. But there is often talk of replication in the DBMail
>mailing list, so certainly others are doing it.
>
>
>
Daniel,

One other question - do you use fulltext indexes on your blobs/text
columns for searches?

regards,

Patrick

>>What storage engine are your tables using?
>>
>>
>
>Mostly InnoDB. DBMail uses InnoDB for everything, and this is by *far*
>the biggest user of MySQL now ( 20 GB of email, compared to 2 GB of
>everything else ). I also use InnoDB for larger tables ( we ran into
>record-locking problems with MS Access and MyISAM, which disappeared
>completely when we converted tables to InnoDB. We've since largely moved
>away from Access, but we're happy with InnoDB's performance ). We use
>MyISAM for limited use tables, or read-only tables.
>
>--
>Daniel Kasak
>IT Developer
>NUS Consulting Group
>Level 5, 77 Pacific Highway
>North Sydney, NSW, Australia 2060
>T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
>email: dkasak@nusconsulting.com.au
>website: http://www.nusconsulting.com.au
>
>
>


--
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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

On Thu, 2007-12-13 at 16:36 -0500, Patrick Galbraith wrote:

> Daniel,
>
> One other question - do you use fulltext indexes on your blobs/text
> columns for searches?
>
> regards,
>
> Patrick

We don't for any of our applications. A quick look at the DB schema for
DBMail shows that they don't either. There is a solitary message in the
DBMail mailing list about enabling fulltext indexes, but no-one
responded to it.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: inserting BLOB data

Hi. If all u want to do is locate things in the file, like say finding
out where "1 2 3 4" occurs, straight Perl is what u want. However if u
want to be able to find those substrings *a lot*, then it may be worth
it to use MySQL. That's because u'll have an index. I was able to scan
a 19 MB file for a couple hundred matches in about 2 seconds with this
script. :) Still wanna use MySQL?

This script should do what u want.

$string = $ARGV[0] or die;
$file = $ARGV[1] or die;
$offset = 0;
$chunksize = 65536;
$chaff = length($string) - 1;
open FILE, $file;
while (my $read = read FILE, $buffer, $chunksize) {
last unless $read;
#print "checking $read byte block at offset $offset\n";
$buffer .= $save;
$save = "";
$position = 0;
while (my $loc = index $buffer, $string, $position) {
last unless $loc >= 0;
print "found $string at offset ", $offset + $loc, "\n" if $loc >=0;
$position = $loc + $chaff + 1;
}
$save = substr $buffer, -$chaff, $chaff;
#print "Saving chaff value of $save\n";
$offset = $offset + $read - $chaff;
undef $buffer;
}
close FILE;

The other scripts posted will take down ur machine with large files. :)



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



--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Executing System Program

Greetings, I have the following task:

I have to run a Perl script to run spawn multiple processes on a Linux server. The issue is that
each process is huge and may take a while to run.

I simply want my program to run, then start process 1 and let it run in the background, then
immediately start process 2 etc.

Example: let's say I want to tar several directories, and each is 5 GB in size. I would like
program to run and spawn separate tar processes for each and NOT sit there and wait for each one
to finish before it goes to the next.

I have tried system, but it runs and waits and returns the output to the screen. Exec sits there
and waits too.

Right now I run through a loop and on each iteration I do:
tar -xvf foo.tar
chdir($dir);
my @args = ("tar -cvf $File");
system(@args) == 0 or die "system @args failed: $?";



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Executing System Program

Greetings, I have the following task:

I have to run a Perl script to run spawn multiple processes on a Linux server. The issue is that
each process is huge and may take a while to run.

I simply want my program to run, then start process 1 and let it run in the background, then
immediately start process 2 etc.

Example: let's say I want to tar several directories, and each is 5 GB in size. I would like
program to run and spawn separate tar processes for each and NOT sit there and wait for each one
to finish before it goes to the next.

I have tried system, but it runs and waits and returns the output to the screen. Exec sits there
and waits too.

Right now I run through a loop and on each iteration I do:

#Either this:
my @args = ("tar -cvf $File"); #example
system(@args) == 0 or die "system @args failed: $?";

#Or This:
my @args = ("tar -cvf $File > log.txt");
exec @args or print STDERR "Couldn't exec: $!";

Either one sits there and waits until it's done.

Any ideas?




--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Executing System Program

I would go with the good old fork() and exec() method.
e.g.
if (fork()) {
1;
}
else {
exec("do something");
}

Greg Meckes wrote:
>
> Greetings, I have the following task:
>
> I have to run a Perl script to run spawn multiple processes on a Linux server. The issue is that
> each process is huge and may take a while to run.
>
> I simply want my program to run, then start process 1 and let it run in the background, then
> immediately start process 2 etc.


--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Executing System Program

thanks Chris. Fork is confusing to me. How can I execute the following using fork?


-----SNIP

opendir (DIR,"/somedir") or die "Couldn't open somedir: $!\n";
my @list = grep (!/^\.\.?$/, readdir(DIR));
closedir(DIR);


foreach my $dir (@list) {

#Spawn this tar command, then continue.
#Don't wait for it to finish.
#Go immediately to the next one and
# spawn another process of tar - as many as needed.

my @args = ("tar -cvf $dir");
exec @args or print STDERR "Couldn't exec tar -cvf $dir: $!";

}

--- END SNIP

If I were to run this manually I would say:

$>tar -cvf /dir1 &
$>tar -cvf /dir2 &
$>tar -cvf /dir3 &

Then I would see (for example):

$>ps -eaf | grep tar
root 3647 1 0 Dec12 ? 00:01:13 /bin/tar -cvf /dir1
root 3678 1 0 Dec12 ? 00:00:14 /bin/tar -cvf /dir2
root 3681 1 0 Dec12 ? 00:00:26 /bin/tar -cvf /dir3

How can I do this?






--- "Wagner, Chris (GEAE, CBTS)" wrote:

> I would go with the good old fork() and exec() method.
> e.g.
> if (fork()) {
> 1;
> }
> else {
> exec("do something");
> }
>
> Greg Meckes wrote:
> >
> > Greetings, I have the following task:
> >
> > I have to run a Perl script to run spawn multiple processes on a Linux server. The issue is
> that
> > each process is huge and may take a while to run.
> >
> > I simply want my program to run, then start process 1 and let it run in the background, then
> > immediately start process 2 etc.
>
>
> --
> Chris Wagner
> CBTS
> GE Aircraft Engines
> Chris.Wagner@ae.ge.com
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Executing System Program

------=_Part_8496_6686005.1198033588705
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 12/18/07, Greg Meckes wrote:
>
> Greetings, I have the following task:
>
> I have to run a Perl script to run spawn multiple processes on a Linux
> server. The issue is that
> each process is huge and may take a while to run.
>
> I simply want my program to run, then start process 1 and let it run in
> the background, then
> immediately start process 2 etc.
>
perldoc -q "How do I start a process in the background?"

--
-jp


When Chuck Norris was in middle school, his English teacher assigned an
essay: "What is Courage?" Chuck Norris received an "A+" for writing only the
words "Chuck Norris" and promptly turning in the paper.

------=_Part_8496_6686005.1198033588705--

Re: Executing System Program

On 18/12/2007, Greg Meckes wrote:
> Greetings, I have the following task:
>
> I have to run a Perl script to run spawn multiple processes on a Linux server. The issue is that
> each process is huge and may take a while to run.
>
> I simply want my program to run, then start process 1 and let it run in the background, then
> immediately start process 2 etc.
>
> Example: let's say I want to tar several directories, and each is 5 GB in size. I would like
> program to run and spawn separate tar processes for each and NOT sit there and wait for each one
> to finish before it goes to the next.
>
> I have tried system, but it runs and waits and returns the output to the screen. Exec sits there
> and waits too.
>
> Right now I run through a loop and on each iteration I do:
> tar -xvf foo.tar
> chdir($dir);
> my @args = ("tar -cvf $File");
> system(@args) == 0 or die "system @args failed: $?";

Something like this...

my (%kids, @kids);
for my $file (replace_with_your_loop()) {
my $k;

if ($k = fork) {
$kids{$k} = 'running';
push @kids, $k;
}
else {
my @args = ("tar", "-cvf", $file);
system @args and die "system @args failed: $?";
}
}

# wait for all kids to come home
my $done;
while (($done = wait) > -1) {
next unless $done;
$kids{$done} = 'done';
print "Child process $done exited with status $?\n";
print join("\n" map "$_ : $kids{$_}", @kids), "\n";
}


Note that I have had problems on Solaris 9 where Solaris's fork does
not actually return the proper process ID of the child process. Never
figured out why though. It was a pain.

--
Sean "Dodger" Cannon

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Executing System Program [SOLVED]

Thanks to all for your responses. The method I found thanks to the FTFM suggestion namely:
"perldoc -q "How do I start a process in the background?"

....lead me to the Parallel::Jobs module. It works great.

So I just drop it in my loop and it works.

The script runs and I see multiple processes and my script takes 1 second to run.

THANKS TO ALL!

Greg

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Executing System Program

For the sake of completeness I'll offer this. Portability with
fork-like functions is a stickty wicket in Perl. Something that works
under unix might not work (read: does not) under Windows. I put
together this little fork-open script and tested it on unix and
Windows. This is a tad obnoxious and really the "wrong way" to do this
but unfortunately the "right way" doesn't work everywhere.


$SIG{CHILD} = "IGNORE";
$a = 65;
foreach $dir (@dirs) {
if ($pid = fork) {
# parent here
print "post fork here! spawned $pid for $dir\n";
}
elsif (defined $pid) { # $pid is zero here if defined
# child here
my $b = chr($a);
close STDOUT; close STDIN; close STDERR;
open "$b", "tar -cvf $dir |" or print "could not exec!\n";
exit;
}
else {
# weird fork error
die "Can't fork: $!\n";
}
$a++;
}
exit;
__END__



Greg Meckes wrote:
>
> thanks Chris. Fork is confusing to me. How can I execute the following using fork?
>
> -----SNIP
>
> opendir (DIR,"/somedir") or die "Couldn't open somedir: $!\n";
> my @list = grep (!/^\.\.?$/, readdir(DIR));
> closedir(DIR);
>
> foreach my $dir (@list) {
>
> #Spawn this tar command, then continue.
> #Don't wait for it to finish.
> #Go immediately to the next one and
> # spawn another process of tar - as many as needed.
>
> my @args = ("tar -cvf $dir");
> exec @args or print STDERR "Couldn't exec tar -cvf $dir: $!";
>
> }



--
Chris Wagner
CBTS
GE Aircraft Engines
Chris.Wagner@ae.ge.com

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org