Win32::ODBC problem with colon in column name

[BeemerBiker]

I can create an access database with a colon in the field name, but I cant access it. I tried escaping the colon with single, double quotes and the backslash but didnt find a combo that worked.

The following code creates a 3 column access database with colons in the column name and attempts to insert values using every combination of quote and double quote
[code=20x50]
use DBI; #ppm install DBI
#ppm install DBD::ODBC
use Win32::OLE::Con st 'Microsoft DAO 3.6 Object Library';

my (
$acc, $db, $tab, $field, $dba, $sqlcmd, $sth, $file );

$file = 'hascolon.mdb';
unlink $file;

$acc = Win32::OLE->new("DAO.DBEng ine.36", 'quit');
$db = $acc->CreateDatabase ($file, dbLangGeneral);
$tab = $db->CreateTableDef ('TestTab');
$field = $tab->CreateField('T ryFor:Bit1',dbL ong);
$tab->Fields->Append($field) ;
$field = $tab->CreateField('T ryFor:Bit2',dbL ong);
$tab->Fields->Append($field) ;
$field = $tab->CreateField('T ryFor:Bit3',dbL ong);
$tab->Fields->Append($field) ;
$db->TableDefs->Append($tab) ;
$db->close();
$acc->close();

$dba = DBI->connect('dbi:O DBC:driver=micr osoft access driver (*.mdb);dbq='.
$file.'','','', {RaiseError => 0,PrintError=>1 });

$sqlcmd = 'INSERT INTO TestTab ([TryFor:Bit1], [TryFor:Bit2], [TryFor:Bit3]) VALUES (1,2,3);';
print "sqlcmd:$sqlcmd \n";
$sth = $dba->prepare($sqlcm d);
$sth->execute();

$sqlcmd = 'INSERT INTO TestTab ([TryFor\:Bit1], [TryFor\:Bit2], [TryFor\:Bit3]) VALUES (1,2,3);';
print "sqlcmd:$sqlcmd \n";
$sth = $dba->prepare($sqlcm d);
$sth->execute();

$sqlcmd = "INSERT INTO TestTab ([TryFor:Bit1], [TryFor:Bit2], [TryFor:Bit3]) VALUES (1,2,3);";
print "sqlcmd:$sqlcmd \n";
$sth = $dba->prepare($sqlcm d);
$sth->execute();

$sqlcmd = "INSERT INTO TestTab ([TryFor\:Bit1], [TryFor\:Bit2], [TryFor\:Bit3]) VALUES (1,2,3);";
print "sqlcmd:$sqlcmd \n";
$sth = $dba->prepare($sqlcm d);
$sth->execute();
[/code]


There are 4 error messages when executing the above script and this is the results:


If you remove the colon's from the source and rerun the modified code you will get this

[KevinADC]

You need to find out how to escape it in the database, not in the perl code, maybe you need two slashes?

TryFor\\:Bit1

But I am just guessing. You may also want to look into placeholders, see the DBI documentation.

[eWish]

Look into quote or use placeholders and bind values with the DBI. This will escape the characters for you.

--Kevin

[BeemerBiker]

Hmm - use strict found a problem with $dba = DBI->
and I had to put a DBI:$dba = DBI->
before the $dba->quote() was accepted

I am looking at the problem. I think I should not be using Win32. It is strange that I could create the fields with a : using DAO but was unable to access them using DBI. Am reading up on the differences between them.

thanks.

[BeemerBiker]

Solved - Unaccountably, the problem was the [] and not the :

Worked:

$qry = 'SELECT "TryFor:Bit1" FROM TestTAB';
$sth = $dbh->prepare($qry);
$sth->execute;
$sth->dump_results;
$dbh->disconnect;

However, I have not had any problem with any of my other code that uses the access []. For example, the following code had been working just fine tho I commented out that stupid : stuff as I didnt know what was happening at the time.

my @RunThese = (
"DROP TABLE [##info];",
"SELECT [Employee Info].[Employee Name], [supervisorIDlookup].[Email], [Employee Info].[Status], [Employee Info].[Employee ID], ".
 " [Employee Info].[Department],  [supervisorIDlookup].[EmployeeID], [Employee Info].[Status Date], [Employee Info].[Position Title], ".
# " [Employee Info].[SkillSoft:Bus-Select], [Employee Info].[SkillSoft:Bus-Full], [Employee Info].[SkillSoft:Desktop], [Employee Info].[SkillSoft:IT], ".
 " [Employee Info].[Working on Degree]  INTO [##info] FROM [Employee Info] INNER JOIN [supervisorIDlookup] on [Employee Info].[Supervisor] = [supervisorIDlookup].[LastName];",

I will have to go back and use single quote on the query and substitute double where that [] is about the SkillSoft:blahb lah stuff

Another observation: The editor that I am using displays tags, but seems to get confused (or maybe I am) about which methods belong to which class.

For example
my DBI:$dbh;
$dbh-> does not get its methods listed, but
$dbh->quote( does as shown here

note that that $dbh->quote_identifi er( has a different class and the arguments to that class do not match the ones referenced by Kevin (eWish)'s post here



I do not know why DBI was first used, then DBD and that suggests that one or the other of the code would not work. The quote_identifie r listed in the reference took up to 4 args with the first being the catalog. That is not shown in the above method tooltip.