Making variable field names

I have many tables of results, a sample record looks like as below
in my code together with fields in the db.

The problem is this. Not every table I receive has the same fields.
Sometimes there are fields, "test4", "test5" for example.There is always
a fixed number of 7 fields.

Is it possible to have my code change the INSERT INTO statement so that the
fields change
depending on the fields in each table?....of course the db will have all
possible fields
included.
In other words, can the field names be variables?...if so what is the syntax
for this?

Any help appreciated!


#!/usr/bin/perl -w
use strict;
use warnings;

use Win32::ODBC;
use dbi;

my $mdb = "Treatment.mdb";
my $dns = "driver=Microsoft Access Driver (*.mdb);dbq=$mdb";

# Connect to the mdb and prepare request
my $dbh = DBI->connect("dbi:ODBC:$dns", ',') or die "Connection to $mdb
failed!\n";

# sample results

$mdate = '17/1/2008'; $venue = 'ICPM'; $patient = 'G Gomez'; $dob =
'21/08/1977'; $test1 = 88.5;

$test2 = 75.1; $test3 = 81.9;

# Load the results into the database

my $input = $dbh->prepare("INSERT INTO
Treatment_table(mdate,venue,patient,dob,test1,test2,test3)

VALUES (?,?,?,?,?,?,?)");

$input->execute( $mdate, $venue, $patient, $dob, $test1, $test2, $test3);

$dbh->disconnect();

Re: Making variable field names

Peter Jamieson wrote:
> Is it possible to have my code change the INSERT INTO statement so that the
> fields change depending on the fields in each table?

The SQL command is just a string scalar. You can interpolate variables
in it just like any other string, such as:

my $sth = $dbh->prepare("INSERT INTO
Treatment_table(mdate,venue,patient,dob,$c1,$c2,$c3)...

If you want to determine what the column names are for a particular
table, use the column_info method of DBI.

Of course, if you change either the table or the column names, you must
build a separate prepare statement. You cannot prepare a generic
database plan and then execute it against different tables and columns.

Re: Making variable field names

"David Filmer" wrote in message
news:DP-dnZAC9_cBzw3a4p2dnAA@giganews.com...
> Peter Jamieson wrote:
>> Is it possible to have my code change the INSERT INTO statement so that
>> the fields change depending on the fields in each table?
>
> The SQL command is just a string scalar. You can interpolate variables in
> it just like any other string, such as:
>
> my $sth = $dbh->prepare("INSERT INTO
> Treatment_table(mdate,venue,patient,dob,$c1,$c2,$c3)...
>
> If you want to determine what the column names are for a particular table,
> use the column_info method of DBI.
>
> Of course, if you change either the table or the column names, you must
> build a separate prepare statement. You cannot prepare a generic database
> plan and then execute it against different tables and columns.
>

Thanks David!
Your solution worked well!...your help is appreciated!....cheers, Peter