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

use DBI;  #ppm install DBI
          #ppm install DBD::ODBC
use Win32::OLE::Const '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.DBEngine.36", 'quit');
$db = $acc->CreateDatabase($file, dbLangGeneral);
$tab = $db->CreateTableDef('TestTab');
$field = $tab->CreateField('TryFor:Bit1',dbLong);
$tab->Fields->Append($field);
$field = $tab->CreateField('TryFor:Bit2',dbLong);
$tab->Fields->Append($field);
$field = $tab->CreateField('TryFor:Bit3',dbLong);
$tab->Fields->Append($field);
$db->TableDefs->Append($tab);
$db->close();
$acc->close();
 
$dba = DBI->connect('dbi:ODBC:driver=microsoft 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($sqlcmd);
$sth->execute();
 
$sqlcmd = 'INSERT INTO TestTab ([TryFor\:Bit1], [TryFor\:Bit2], [TryFor\:Bit3]) VALUES (1,2,3);';
print "sqlcmd:$sqlcmd\n";
$sth = $dba->prepare($sqlcmd);
$sth->execute();
 
$sqlcmd = "INSERT INTO TestTab ([TryFor:Bit1], [TryFor:Bit2], [TryFor:Bit3]) VALUES (1,2,3);";
print "sqlcmd:$sqlcmd\n";
$sth = $dba->prepare($sqlcmd);
$sth->execute();
 
$sqlcmd = "INSERT INTO TestTab ([TryFor\:Bit1], [TryFor\:Bit2], [TryFor\:Bit3]) VALUES (1,2,3);";
print "sqlcmd:$sqlcmd\n";
$sth = $dba->prepare($sqlcmd);
$sth->execute();
 

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:blahblah 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_identifier( 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_identifier listed in the reference took up to 4 args with the first being the catalog. That is not shown in the above method tooltip.