Building SQL statement

[Mark Wildey]

True beginners question…. sorry

I have a MS Access database that has lots of fields in it. When I build the
SQL statement to insert data retrieved from a text file, rather than build a
very long SQL statement I have used a  “@column = $db->FieldNames()”
subroutine to build a scalar variable ( called $Values ) that would contain
the field names. The subroutine is called only once prior to the foreach
loop

'$Var1','$Var2','$Var3','$Var4' etc etc

A brief synopsis of my code is

foreach $word ( @DataIn ) {

if ( substr($word,32,1) eq ":" and substr($word,11,4) ne "0.00" ) {

                  $Var1       = substr($word,1,6)     ;
$Var2       = substr($word,8,7)     ;
etc etc

$query = "INSERT INTO $dbFile ($VarList) Values($Values)" ;
!$db->Sql($query) || die print "Unable to Update Database" .
Win32::ODBC::Error() ."\n" ;

}
}

The database is updated and the database fields contain entries “$var1”  etc
etc rather than the value of $var1 which was built using a substr statement
prior to the $query statement above.

If I build the SQL statement

!$db->Sql("INSERT INTO TABLE1 (var1, var2,var3,…) VALUES(‘$var1’,’$var2’,’
var3’…)”)

then all is ok


I kinda understand what is happening but do not know Perl well enough ( in
fact this is my first Perl program ) to understand where I’m going wrong and
how to fix it !

Thanks in advance

Mark