Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

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

by srikantvm (Initiate)
on Apr 08, 2008 at 10:59 UTC ( #678961=perlquestion: print w/ replies, xml ) Need Help??
srikantvm has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
Select or Download Code
Re: error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
by apl (Monsignor) on Apr 08, 2008 at 11:36 UTC
    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...
Re: error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
by thundergnat (Deacon) on Apr 08, 2008 at 15:38 UTC

    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.

Re: error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
by strat (Canon) on Apr 09, 2008 at 07:53 UTC

    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;

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://678961]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (7)
As of 2014-10-25 13:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (143 votes), past polls