headers
Sham Prasad | 1 Feb 2006 08:27
Picon

Extract files from a mysql database

Hi all,

I am new to this list and this is my first posting. Please correct if my
mail is not specific to the list.
I am working on bugzilla which has a mysql database called "bugs". It has a
table named "attachments" in it. All the bug attachments are stored in the
database in a zip format. what i am trying to do is, i want the attachment
to be extracted to my file system from the database and i am trying it
through a perl script using DBI and DBD::mysql modules.
I will give an example: there is a bug having bug_id 5626, it has a
attachment named 5626.zip. now my script should extract/copy this file(
5626.zip) to a drectory on the file system. The script which i have written
gets me the contents of the file only and not the file. But the content i
guess is stored in a binary format(BLOB) so i am not able to read anything
in the content. Below is my code

#!usr/lib/perl
use DBI;
my $dbh =
DBI->connect("DBI:mysql:databse=Pbugs;host=appletest","root","deltazia",{'RaiseError"
=> 1});
my $sth = $dbh->prepare(select thedata from attachments where
attach_id=143") or die "can't prepare statement";
print"Query Results\n";
while(my  <at> row = $sth->fetchrow_array()){
print" <at> row\n";
}
$sth->execute or die "can't execute statement";
$dbh->disconnect;
(Continue reading)

Permalink | Reply | 1 comment |
headers
Jan Kratochvil | 1 Feb 2006 08:53

Re: Extract files from a mysql database

Hi,

just that it got resolved.

On Wed, 01 Feb 2006 08:50:35 +0100, Sham Prasad wrote:
> Thanks jan,
> 
> I tested the code you had sent. Now its working for me.
...
> On 2/1/06, Jan Kratochvil <lace <at> jankratochvil.net> wrote:
> >
> > On Wed, 01 Feb 2006 08:27:24 +0100, Sham Prasad wrote:
> > ...
> > > Below is my code
> >
> > This script has some typos and cannot work. But still:
> >
> > > #!usr/lib/perl
> > > use DBI;
> > > my $dbh =
> > >
> > DBI->connect("DBI:mysql:databse=Pbugs;host=appletest","root","deltazia",{'RaiseError"
> > > => 1});
> > > my $sth = $dbh->prepare(select thedata from attachments where
> > > attach_id=143") or die "can't prepare statement";
> > > print"Query Results\n";
> > > while(my  <at> row = $sth->fetchrow_array()){
> >
> > > print" <at> row\n";
> > local *F;
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jochen Wiedmann | 1 Feb 2006 11:30
Picon
Gravatar

Re: Extract files from a mysql database

On 2/1/06, Sham Prasad <sham.ts <at> gmail.com> wrote:

> I am working on bugzilla which has a mysql database called "bugs". It has a
> table named "attachments" in it.

In your special case, I'd recommend *not* to retrieve the file using
DBD::mysql, but via the Bugzilla CGI binaries. (In other way, connect
to the URL /attachments.cgi?id=<id>.) That way, you leave Bugzilla the
possibility to change its database structure (which it frequently
does), and you are within Bugzillas security system.

Jochen (Bugzilla Consultant)

--
If you obey all the rules you miss all the fun. (Katharine Hepburn)

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 0 comments |
headers
Sham Prasad | 2 Feb 2006 12:26
Picon

query inside a loop

Hi all,

I have a script which reads in a file containing ID's into an array.
Then i am using mysql query to query for each ID one by one.but i am
getting a syntax error. i want to know is it possible to pass variable
to a query? i am pasting the script along with this mail please have
 a look.

use DBI;
open F1, "filepath";
$"=",";
 <at> bug = <F1>;
close F1;
for(my $i=0;$i<= <at> bug;$i++)
{
$id = $bug[$i];
my $dbh =
DBI->connect("DBI:mysql:database=Pbugs;host=appletest","username","password",{'RaiseError'
=> 1});
my $sth = $dbh->prepare("select bug_id,short_desc,bug_status from bugs where
bug_id=$id") or die "cant prepare statement";
$sth->execute();
print "$sth";
while(my  <at> row = $sth->fetchrow_array()){
open FILE, "filepath";
 <at> arr=<FILE>;
splice( <at> arr,2,1);
open FILE, ">filepath";
$" = ",";
print (FILE  <at> arr);
(Continue reading)

Permalink | Reply | 0 comments |
headers
Selke, Gisbert W. | 2 Feb 2006 13:11
Picon

AW: <<Spam-Verdacht>>query inside a loop

Hi, Sham --

What type of variable is bugs.bug_id? If  it is numeric, it should work (at
least after newline removal). If it is numeric, however, direct
interpolation gives you an unquoted string in your select statement, which
is illegal (or at least will give you unwanted results). Instead of trying
manual quoting, it would be a simpler and cleaner approach to do it like
this:

use DBI;
open F1, "filepath";
$"=",";
my  <at> bugs = <F1>;
close(F1);
my $dbh = 

DBI->connect("DBI:mysql:database=Pbugs;host=appletest","username","password"
,
               {'RaiseError' => 1});
  my $sth = $dbh->prepare("select bug_id,short_desc,bug_status 
                          from bugs where
                          bug_id=?") or die "can't prepare statement";
foreach my $id( <at> bugs)
{
  chomp($id);
  $sth->execute($id);
  while(my  <at> row = $sth->fetchrow_array()){
    open FILE, "filepath";
     <at> arr=<FILE>;
    splice( <at> arr,2,1);
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jake Peavy | 4 Feb 2006 01:16
Picon

Fwd: query inside a loop

oops, forgot the list on this one.  I sure wish it was in the replyto....

-jp

---------- Forwarded message ----------
From: Jake Peavy <djstunks <at> gmail.com>
Date: Feb 3, 2006 5:16 PM
Subject: Re: query inside a loop
To: Sham Prasad <sham.ts <at> gmail.com>

On 2/2/06, Sham Prasad <sham.ts <at> gmail.com> wrote:
>
> Hi all,

<snip>

i want to know is it possible to pass variable
> to a query?

Search for "Placeholders and Bind Values"

Also, you should put your db connect outside your for loop but I'll let you
muddle through the rest of your nutty perl which is, I'm sure, where your
syntax error is coming from.

-jp
Permalink | Reply | 0 comments |
headers
Beau E. Cox | 5 Feb 2006 04:48
Picon

chopblanks error in 3.0002_5

Hi -

My testing shows that the blob problem in 3.0002_4 that I
reported eariler has been corrected in 3.0002_5, but I get
this chopblanks error:

$ make test
...
t/41blobs_prepare....ok
t/50chopblanks.......dubious
        Test returned status 0 (wstat 11, 0xb)
t/50commit...........ok
        14/30 skipped: Unable to detect a transactional...
t/60leaks............skipped
        all skipped: $ENV{SLOW_TESTS} is not set or Proc::...
t/ak-dbd.............ok
t/akmisc.............ok
t/dbdadmin...........ok
t/insertid...........ok
t/mysql..............ok
t/mysql2.............ok
Failed Test      Stat Wstat Total Fail  Failed  List of Failed
-------------------------------------------------------------------------------
t/50chopblanks.t    0    11    ??   ??       %  ??
1 test and 14 subtests skipped.
Failed 1/21 test scripts, 95.24% okay. 0/899 subtests failed, 100.00% okay.
make: *** [test_dynamic] Error 255
$

$ make test TEST_VERBOSE=1 TEST_FILES=t/50chopblanks.t
(Continue reading)

Permalink | Reply | 0 comments |
headers
Jake Peavy | 6 Feb 2006 21:13
Picon

Re: query inside a loop

On 2/5/06, Sham Prasad <sham.ts <at> gmail.com> wrote:
>
> Hi jake,
>
> Thanks for the reply. As you have suggested i have put the dbconnect
> outside the loop and also changed the script little bit. After running the
> script i am getting the following error.
>
> DBD::mysql::st execute failed:called with 1 bind variables when 0 are
> needed at a.pl line 14
>
> Below is the script
>
> use DBI;
> open F1, "filepath";
> $"=",";
> my  <at> bugs = <F1>;
> close(F1);
> my $dbh =
>
> DBI->connect("DBI:mysql:database=Pbugs;host=appletest","username","password"
>
> ,
>               {'RaiseError' => 1});
>  my $sth = $dbh->prepare("select bug_id,short_desc,bug_status
>                          from bugs where
>                          bug_id=?") or die "can't prepare statement";
> foreach my $id( <at> bugs)
> {
>  chomp($id);
(Continue reading)

Permalink | Reply | 0 comments |
headers
Martin J. Evans | 14 Feb 2006 12:22
Favicon

FWD: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

Hi,

I posted the following email to dbi-users list but have so far received no
responses. I hoped I might have more luck here. I'd appreciate any ideas on how
to persue this.

Thanks

-----FW: <XFMail.20060210120433.martin.evans <at> easysoft.com>-----

Date: Fri, 10 Feb 2006 12:04:33 -0000 (GMT)
From: "Martin J. Evans" <martin.evans <at> easysoft.com>
To: dbi-users <at> perl.org
Subject: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

Hi,

selectall_hashref and fetchall_hashref work fine for me most of the time but
I've hit a scenario where I get a result I just cannot fathom.

I'm using DBI 1.50 and DBD::mysql 3.0002_4 (with a few minor patches I posted
on this list to make it compile and get rid of the FREE UNBIND problem I had).

I have tables:

race
race_id               primary key auto increment
meeting_id            foreign key to meeting_id in meeting

meeting
(Continue reading)

Permalink | Reply | 2 comments |
headers
Boysenberry Payne | 14 Feb 2006 15:43

Re: Strange selectall_hashref/fetchall_hashref problem with DBD::mysql

It sounds to me like DATEDIFF and DATE might be messing things up a bit,
hence the problems go away after they're gone.

I myself use perl to do the Date calculations then translate it for the 
database.

It avoids issues where database syntax changes as well as your problem.

Try it with perl doing the calculations, or just supply arbitrary date 
info to
get it working as a request.  It will isolate the issue a bit more, as 
well as
allow you to get what you want done until you figure out what's going 
on with
'DATEDIFF', and 'DATE'.

You might also want to try the mod_perl group they're really good about
knowing the internals of Packages like DBI and DBD::mysql:

modperl <at> perl.apache.org

Boysenberry

boysenberrys.com | habitatlife.com | selfgnosis.com

On Feb 14, 2006, at 5:22 AM, Martin J. Evans wrote:

> Hi,
>
> I posted the following email to dbi-users list but have so far
(Continue reading)

Permalink | Reply | 1 comment |

Gmane