Re: DBD::ANYDATA create table help please.

I have written a simple demo to demonstrate how to create a (CSV) database table using DBD::AnyData. Hope you will find it useful. ;-)

use strict;
use warnings;
use DBI;
use DBD::AnyData;

my $table = "data";

my @fields = qw/ id name /;
my %field_def = (
    id => 'char(20)',
    name => 'char(20)',
);

my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):')
    or die "Can not create database connection";

# build the create table SQL on the fly
$dbh->do (
    "CREATE TABLE $table (" .
    join(',', map { $_ . ' ' . $field_def{$_} } @fields) .
    ")" )
    or die "Can not create table";

$dbh->do("INSERT INTO $table VALUES ('01', 'FOO' )");
$dbh->do("INSERT INTO $table VALUES ('02', 'BAR' )");

unlink './data.csv';     # delete existing csv file if any
$dbh->func( 'data', 'CSV', './data.csv', 'ad_export');

$dbh->disconnect();
[download]

Comment on Re: DBD::ANYDATA create table help please. Download Code

Replies are listed 'Best First'.
Re: Re: DBD::ANYDATA create table help please. by lwicks (Friar) on Jan 28, 2004 at 22:00 UTC
Roger, many thanks, I have used your example as a template and managed to get my code to work properly!!! With DBD::Anydata in CSV mode do we need to define the field definition still? Thanks for your help Roger and all! Lance Kia Kaha, Kia Toa, Kia Manawanui! Be Strong, Be Brave, Be perservering!	[reply]
Re: Re: Re: DBD::ANYDATA create table help please. by Roger (Parson) on Jan 28, 2004 at 22:52 UTC
With DBD::Anydata in CSV mode do we need to define the field definition still? Yes you do. Because you are creating the table using SQL, you need to form a valid SQL statement to work properly. There is another way to create the table - by directly creating the underlying CSV file. This way you do not have to specify field definitions at all: use strict; use warnings; use DBI; use DBD::AnyData; use IO::File; my $table = "data"; my @fields = qw/ id name /; # === create an empty CSV file === my $csvfile = new IO::File "./data.csv", "w" or die "Can not create CSV file"; print $csvfile join(',', @fields), "\n"; undef $csvfile; my $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):') or die "Can not create database connection"; $dbh->func( 'data', 'CSV', './data.csv', 'ad_catalog'); $dbh->do("INSERT INTO $table VALUES ('01', 'DUMMY' )") or die "Can not insert into data table"; $dbh->disconnect(); [download]	[reply] [d/l]