Making variable field names

[Peter Jamieson]

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();

 

[Peter Jamieson]

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