perl script and xml

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello dear friends my name is ivette and i need help with the following code, what am trying to do is opening a data base and search for a specific record which will be shown in a xml file but the problem i have is that my query works but when i try to generate the xml file i can't please help me

#!C:\perl\bin\perl.exe -w
#I USE THE LIBRARY
use DBI;

#WHERE I CONNECT WITH THE DATA BASE
my $biblioperl =  'driver=Microsoft Access Driver (*.mdb);dbq=C:\\bibl
+io.mdb';      
my $dbh = DBI->connect("dbi:ODBC:$biblioperl",",") or die "$DBI::errst
+r\n";

#WHERE I COMPARED THE SELECTION AND IF IT IS IN THE TABLE THEN IT MUST
+ SHOW IT
$qrystrg = $ENV{'QUERY_STRING'};
$qrystrg =~ tr/+/ /;
$qrystrg =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; 

($varcampo1, $varcampo2) = split(/&/,$qrystrg);
($parcampo1,$varvalor1) = split(/=/,$varcampo1);
($parcampo2,$varvalor2) = split(/=/,$varcampo2);

#QUERY 
$consulta = "SELECT Authors.Author, Titles.Title, Titles.[Year Publish
+ed], Publishers.[Company Name], Titles.ISBN, Titles.Description, Titl
+es.Notes FROM (Publishers INNER JOIN Titles ON Publishers.PubID = Tit
+les.PubID) INNER JOIN (Authors INNER JOIN [Title Author] ON Authors.A
+u_ID = [Title Author].Au_ID) ON Titles.ISBN = [Title Author].ISBN WHE
+RE (((Titles.[Year Published])=$varvalor1))";                        
+                                                                     
+                    

$sth = $dbh->prepare($consulta);


$rv = $sth->execute() or die $dbh->errstr;

#print qq~
#~;

#I USE A FILE HANDLER WHICH WILL ALLOW ME TO WRITE IN EVERYTIME I 
#DO THE QUERY

open (FILEXML, " > biblio.XML");

print " FILEXML <Structures>";
#print "<?xml  version="1.0" encoding="ISO-8859-1"?>";
while( @data = $sth->fetchrow_array() ) 
{
    # I GENERATE THE STRUCTURE OF THE XML FILE
    print FILEXML "<Book>";
    print FILEXML "<Author>$data[0]</Author>";
    print FILEXML "<Title>$data[1]</Title>";
    print FILEXML "<Y_Publi>$data[2]</Y_Publi>";
    print FILEXML "<C_Name>$data[3]</C_Name>";
    print FILEXML "<ISBN>$data[4]</ISBN>";
    print FILEXML "<Descrip>$data[5]</Descrip>";
    print FILEXML "<Notes>$data[6]</Notes>";
    print FILEXML "</Book>";
}
    print FILEXML "</Structures>";

$sth->finish();
close(FILEXML);


#print "Content-type: text/html\n\n";
#print "Location: http:\\localhost\resultado.html\";

$dbh->disconnect || warn "\nFalló al desconectar.\nError: $DBI::errstr
+\n";

exit;
[download]

Comment on perl script and xml Download Code

Replies are listed 'Best First'.
(jeffa) Re: perl script and xml by jeffa (Bishop) on Oct 12, 2002 at 23:47 UTC
And while we are on the subject, please don't roll your own XML as well ... consider XML::Generator::DBI instead. It is as simple as: `use strict; use DBI; use XML::Generator::DBI; use XML::Handler::YAWriter; my $biblioperl = yadda yadda ... my $dbh = DBI->connect( "dbi:ODBC:$biblioperl", {RaiseError => 1}, ); my $handler = XML::Handler::YAWriter->new(AsFile => 'biblio.xml'); my $generator = XML::Generator::DBI->new( Handler => $handler, dbh => $dbh, Indent => 1, ); my $consulta = yadda yadda ... $generator->execute($consulta); $dbh->disconnect;` [download] Check out this tutorial for more. jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
•Re: perl script and xml by merlyn (Sage) on Oct 12, 2002 at 23:15 UTC
Please don't roll your own CGI handling. Please use CGI.pm. -- Randal L. Schwartz, Perl hacker	[reply]
Re: perl script and xml by bart (Canon) on Oct 13, 2002 at 16:00 UTC
but when i try to generate the xml file i can't please help me `print " FILEXML <Structures>"; #print "<?xml version="1.0" encoding="ISO-8859-1"?>";` [download] Uh oh... for a start, the order of these two items should be reversed. The "`<?xml ..?>`" tag should be the first thing you output. `while( @data = $sth->fetchrow_array() ) { # I GENERATE THE STRUCTURE OF THE XML FILE print FILEXML "<Book>"; print FILEXML "<Author>$data[0]</Author>"; print FILEXML "<Title>$data[1]</Title>"; ... }` [download] And here, you appear to be assuming that your data text may never contain any reserved characters, like "<" and "&". These two must be encoded. If you want a simple module that will take care of that, use XML::Writer. It'll do the special character encoding for you, it'll even check that your tags are properly nested. What it doesn't do is check if your data is in the proper character set, let alone convert it for your. That is your responsibility, although you can tell it to generate the proper "`<?xml ... ?>`" header at the start. n.b. Without that header, the data encoding should be in UTF-8, which isn't really the case, it it?	[reply] [d/l] [select]