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]

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!

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]

On the database standards side of things, you probably shouldn't create a table with a slash in the title. Also the period is used to designate different table owners such as sys.all_tables.

Hi,
Thanks for all the good replies! Especially the example, which I have been using to alter my code.

As for how this script exits, below is the output from the script:

---

CREATE NEW SHIAI

Start main block

parameters: shiai_name closedate eventdate entry_fee first_prize secon
+d_prize third_prize fourth_prize fifth_prize desc user_id submit butt
+on

Start of collect_shiai_data

Shiai_data array = Lance BLANK_for_now test14 m ladder m BLANK blank b
+lank BLANK m blank m m m m m m

Start of validate_shiai_data

validated data = Lance BLANK_for_now test14 m ladder m BLANK blank bla
+nk BLANK m blank m m m m m m

Start of generate_initial_values

Internal dta now equals = Lance Lancetest14 test14 m ladder m for now 
+just an abitrary data filed till I pass the users name OK! NO 0 0 m W
+ed Jan 28 21:01:29 2004 m m m m m m

Validated data + initial values = Lance Lancetest14 test14 m ladder m 
+for now just an abitrary data filed till I pass the users name OK! NO
+ 0 0 m Wed Jan 28 21:01:29 2004 m m m m m m

END of generate_initial_values

Start of add_shiai_to_db subroutine
SELECT * FROM shiai_db WHERE shiai_id = ? [Lancetest14] SQL ERROR: Unk
+nown column constraint: 'position'! DBD::AnyData::db do failed: SQL E
+RROR: Unknown column constraint: 'position'! 

about to insert the new record
INSERT INTO shiai_db VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) 
+[Lance Lancetest14 test14 m ladder m for now just an abitrary data fi
+led till I pass the users name OK! NO 0 0 m Wed Jan 28 21:01:29 2004 
+m m m m m m] 

Shiai Created

Data added to the DB = Lance Lancetest14 test14 m ladder m for now jus
+t an abitrary data filed till I pass the users name OK! NO 0 0 m Wed 
+Jan 28 21:01:29 2004 m m m m m m

END of add_shiai_to_db subroutine

Start of create shiai db files

Start of createladder_data

table fields = player_name ID position fights wins joined_date date_la
+st_fight
[download]

---

Kia Kaha, Kia Toa, Kia Manawanui!
Be Strong, Be Brave, Be perservering!

Okay,
I can appreciate the not wanting to have slashes in the table name idea.
How then do I specify the directory to create the data table in?

In my situation I want to create a table called userTable.ldr (or perhaps better would be userTable_ldr ?)
But I want the table created in the data/shiai_data/ directory.

Thoughts?

Lance.

Kia Kaha, Kia Toa, Kia Manawanui!
Be Strong, Be Brave, Be perservering!

Sorry for the formatting, I'll try posting as code (should have read further!) I think your approach will work if you change $ladder_table_fields so that it has the datatypes (not just the field names), i.e.  my $ladder_table_fields = "player_name varchar2(30), id varchar2(9), position varchar2(30)"; # etc., this would work in Oracle Also, change $sth->execute( $@sql_parameters ); to  $sth->execute( $ladder_table, $ladder_table_fields[0], $ladder_table_fields[1] # etc. I tested something like you had where you wanted to pass in the field names as a list (array), but I couldn't get $sth->execute to see it in a list context. Passing it as individual scalars worked.

$sth->execute( $ladder_table, $ladder_table_fields[0], $ladder_table_fields[1],...
You probably meant this instead... :-)


$sth->execute( $ladder_table, @ladder_table_fields );
[download]