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

The following is a snippet from my code to allow for testing to get the actual logic to work. I have used a similar functionality in a CSV format and it worked but when I try to use it in a XML format it won't save to a XML file, even when I "export" it.

Installed Perl modules to support the functionality:
* DBI
* SQL::Statement
* DBD::File
* AnyData
* DBD::AnyData
* XML::Twig
* XML::Parse

I've left in the comments where the error occurs and what the error messages shows. Also shows all the "attempts" (should say "failures") in the comments below.

Thanks for any help or suggestions,
warsting

use DBI;

$name       = 'Testing';
$agency     = 'TT';
$email      = 'test@abc.com';
$org        = 'ABC';
$mailstop   = 'X159';
$phone      = '(555) 123-4567';
$title      = 'Other';

$DATA_COLUMNS  = 'name,agency,email,org,mailstop,phone,title';

# tried it with/without the 'eadata' tag too (ie '/eadata/user/name' f
+or the first one)
$XML_COL_MAP = [ {'/eadata/user/name','name'},
                 {'/eadata/user/agency','agency'},
                 {'/eadata/user/email','email'},
                 {'/eadata/user/org','org'},
                 {'/eadata/user/mailstop','mailstop'},
                 {'/eadata/user/phone','phone'},
                 {'/eadata/user/title','title'} ];

# open a new connection to the XML file (user.xml)
$dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
#$dbh->func('users', 'XML', './users.xml', {col_names => $DATA_COLUMNS
+, col_map => $XML_COL_MAP}, 'ad_import');

# XML file added below for ease of testing
$dbh->func('users', 'XML', [<DATA>], {col_names => $DATA_COLUMNS, col_
+map => $XML_COL_MAP}, 'ad_import');
#$dbh->func('users', 'XML', [<DATA>], {col_names => $DATA_COLUMNS}, 'a
+d_import');


# insert a new record in to the memory table 'users'
$SQL = qq{insert into users($DATA_COLUMNS)
          values('$name','$agency','$email','$org','$mailstop','$phone
+','$title')};
$dbh->do($SQL);


# export out to a new file (users_new.xml)
$dbh->func('users', 'XML', './users_new.xml', {col_names => $DATA_COLU
+MNS, col_map => $XML_COL_MAP}, 'ad_export');
#$dbh->func('users', 'XML', './users_new.xml', {col_names => $DATA_COL
+UMNS}, 'ad_export');
# getting this error (no matter which line above I try)):
#   SQL ERROR: Bad table or column name '1' starts with non-alphabetic
+ character!
#   -- actually errors twice


# close the database and closes the file
$dbh->disconnect;

# this is from the xml file (users.xml), places here for ease of testi
+ng
__END__
<eadata>
   <user>
      <name>Testing Tester</name>
      <agency>LL</agency>
      <email>test4@abc.net</email>
      <org>Test</org>
      <mailstop>T125</mailstop>
      <phone>98765</phone>
      <title>Other</title>
   </user>
   <user>
      <name>Walt Arsting...</name>
      <agency>XX</agency>
      <email>arsting...@xyz.net</email>
      <org>ASQ</org>
      <mailstop>L339</mailstop>
      <phone>555-123-4567</phone>
      <title></title>
   </user>
</eadata>

[download]

Replies are listed 'Best First'.
Re: Saving data to XML file using DBD::AnyData
by snoopy (Curate) on Dec 04, 2009 at 07:02 UTC
    I added the following to the top of your program to get a trackback dump: 
    use Carp;
$SIG{__DIE__} = \&Carp::confess;

    [download]
    This indicated that DBD::AnyData is dying on the following line of code in the ad_data method: 
          my $sth = $dbh->prepare("SELECT 1 FROM $table_name") or die DBI-
+>errstr;

    [download]
    I then wrote the following test program to exercise SQL::Statement directly: 
    use common::sense;
use SQL::Statement;
my $sql1    = "SELECT 1+0 FROM some_table";
my $sql2    = "SELECT 1 FROM some_table";
my $parser = SQL::Parser->new();

for ($sql1, $sql2) {
    warn "trying: $_";
    my $stmt = SQL::Statement->new($_,$parser);
}

    [download]
    This produces: 
    trying: SELECT 1+0 FROM some_table at sql.pl line 10.
trying: SELECT 1 FROM some_table at sql.pl line 10.
Bad table or column name: '1' starts with non-alphabetic character! at
+ /usr/local/share/perl/5.10.0/SQL/Parser.pm line 2894.

    [download]
    So "SELECT 1+0 from some_table" parses ok, but "SELECT 1 from some_table" doesn't!

    I started with SQL::Statement 1.20. The problem persisted after upgrading to 1.23.

    Looks to me like a bug in SQL::Statement. Its not recognising the literal value '1' as a simple expression. My guess is that this has stopped working in the last few releases of SQL::Statement, but DBD::AnyData relies on it.

    Update: I've created an rt ticket.

[reply]
[d/l]
[select]

      Thanks for the in depth debugging. Being the AnyData module was new to me, I wasn't sure on how to get at that error message.

[reply]
Re: Saving data to XML file using DBD::AnyData
by stefbv (Priest) on Dec 04, 2009 at 08:10 UTC
    A working example, for select and insert from/into in memory table 'users'. Put the XML from the DATA section in eadata.xml. 
    # always:
use strict;
use warnings;

use DBI;

my $table  = 'users';
my $format = 'XML';
my $file   = 'eadata.xml';

my $dbh = DBI->connect('dbi:AnyData(RaiseError => 1):');

# insert a new record in to the memory table 'users'
$dbh->func( $table, $format, $file, 'ad_import');

# Select data from users

my $sql1 =  qq{ SELECT * FROM $table };

my $sth = $dbh->prepare($sql1);
$sth->execute();

while (my $row = $sth->fetchrow_hashref){
    print "Row:\n";
    while (my ($key, $value) = each (%{$row})) {
        print " $key: $value\n";
    }
}

# Insert a new record in to the memory table 'users'
my $sql2 = qq{
   INSERT INTO users (name, agency, email, org, mailstop, phone, title
+)
              VALUES (?, ?, ?, ?, ?, ?, ?)
};
$sth = $dbh->prepare($sql2);

# It's easy if we have the data in a hash
my $users = {
    name     => 'Testing',
    agency   => 'TT',
    email    => 'test@abc.com',
    org      => 'ABC',
    mailstop => 'X159',
    phone    => '(555) 123-4567',
    title    => 'Other',
};

# Transform hash into two arrays
my @fields = keys %{$users};
my @values = map { $users->{$_} } @fields;

# And use a generic insert statement
# code stolen from the IBPerl module examples ;)
$sql2 = "INSERT INTO $table (" . join( ',', @fields ) . ')
               VALUES (' . ( '?,' x $#fields ) . "?)";
# print "sql=", $sql2, "\n";

$sth->execute(@values);

print "\nLet's see the results:\n";

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

$sth->execute();

while (my $row = $sth->fetchrow_hashref){
    print "Row:\n";
    while (my ($key, $value) = each (%{$row})) {
        print " $key: $value\n";
    }
}

    [download]
[reply]
[d/l]

      In the "actual" web program that this snippet came from uses the strict and warning, but I left it out do to "laziness" and I knew it wasn't the problem here.

      The data for the new record would be gotten from a web form, I provided for ease of testing only. In my version I can insert the record, but the creation of the XML file is what I need help with. This is my problem, not the SQL part.

      Looking at the code you provided (and testing it), it doesn't save the "newly" inserted record to the XML file. How is this done using AnyData modules? What am I doing wrong?

[reply]

        In my code there is no ad_export statement and if I add one I get the same error messages. I see now that I didn't understand what was the real problem, sorry, in this case, probably snoopy was right about what is happening.

        Anyhow the DBD-AnyData module has 8 active bugs and the last is 2 years old, I'm not sure it's ready for production.

        Best regards, Stefan

[reply]

Back to Seekers of Perl Wisdom