error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.

[srikantvm]

use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

# Change these three variables
my $database = "TestAccess.mdb";
my $table    = "Clients";
my $field1   = "CompanyName";
my $field2     = "ClientID";
my $field3   = "Phone";

my $Conn = Win32::OLE->new("ADODB.Connection");
my $RS   = Win32::OLE->new("ADODB.Recordset");

my $DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};
           DBQ=$database;UID=;PWD=;";
$Conn->Open($DSN);

my $SQL = "SELECT $field1, $field2, $field3 FROM $table";

$RS->Open($SQL, $Conn, 1, 1);

until ($RS->EOF) {
    
    my $value1 = $RS->Fields($field1)->value;
    my $value2 = $RS->Fields($field2)->value;
    my $value3 = $RS->Fields($field3)->value;
    
    print $value1,"\t",$value2,"\t",$value3,"\n";
    
   $RS->MoveNext;
}

$RS->Close;
$Conn->Close;

Hi All,

Please go through the above code, If i execute the same code with tables and columns having whitespace characters in their names. I am getting the following error (Can't call method "value" on an undefined value). Please help.... Kindly suggest me the solution for this particular scenario.

Thanks & Regards,
Srikant

[strat]

Try [-braces around the column names, e.g

my $string = "SELECT [col 1], [col 2], col3 ...";

and then use "col 1" as hash keys, e.g

my $value1 = $RS->Fields("col 1")->value;

[apl]

Please bracket your code with <code> and </code>. If your post didn't look readable to you when you Previewed it, you can imagine what it looks like to the rest of us...

[thundergnat]

Please enclose code in <c> ... </c> brackets. It makes it much easier for others to help you.

As I am not working on a Windows system, I can't test this, but I believe you need to enclose the variable names in double quotes. You'll need to escape them so they get passed through in the SQL statement though.

my $SQL = "SELECT \"$field1\", \"$field2\", \"$field3\" FROM \"$table\
+"";

Or such.

You might be better off using DBIx to handle your SQL so you can use placeholders and avoid the whole headache of trying to figure out how and when to quote the fields.