By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,661 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,661 IT Pros & Developers. It's quick & easy.

Win32::ODBC problem with colon in column name

BeemerBiker
P: 87
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
Expand|Select|Wrap|Line Numbers
  1. use DBI;  #ppm install DBI
  2.           #ppm install DBD::ODBC
  3. use Win32::OLE::Const 'Microsoft DAO 3.6 Object Library';
  4.  
  5. my (
  6.  $acc, $db, $tab, $field, $dba, $sqlcmd, $sth, $file );
  7.  
  8. $file = 'hascolon.mdb';
  9. unlink $file;
  10.  
  11. $acc = Win32::OLE->new("DAO.DBEngine.36", 'quit');
  12. $db = $acc->CreateDatabase($file, dbLangGeneral);
  13. $tab = $db->CreateTableDef('TestTab');
  14. $field = $tab->CreateField('TryFor:Bit1',dbLong);
  15. $tab->Fields->Append($field);
  16. $field = $tab->CreateField('TryFor:Bit2',dbLong);
  17. $tab->Fields->Append($field);
  18. $field = $tab->CreateField('TryFor:Bit3',dbLong);
  19. $tab->Fields->Append($field);
  20. $db->TableDefs->Append($tab);
  21. $db->close();
  22. $acc->close();
  23.  
  24. $dba = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq='.
  25.     $file.'','','',{RaiseError => 0,PrintError=>1});
  26.  
  27. $sqlcmd = 'INSERT INTO TestTab ([TryFor:Bit1], [TryFor:Bit2], [TryFor:Bit3]) VALUES (1,2,3);';
  28. print "sqlcmd:$sqlcmd\n";
  29. $sth = $dba->prepare($sqlcmd);
  30. $sth->execute();
  31.  
  32. $sqlcmd = 'INSERT INTO TestTab ([TryFor\:Bit1], [TryFor\:Bit2], [TryFor\:Bit3]) VALUES (1,2,3);';
  33. print "sqlcmd:$sqlcmd\n";
  34. $sth = $dba->prepare($sqlcmd);
  35. $sth->execute();
  36.  
  37. $sqlcmd = "INSERT INTO TestTab ([TryFor:Bit1], [TryFor:Bit2], [TryFor:Bit3]) VALUES (1,2,3);";
  38. print "sqlcmd:$sqlcmd\n";
  39. $sth = $dba->prepare($sqlcmd);
  40. $sth->execute();
  41.  
  42. $sqlcmd = "INSERT INTO TestTab ([TryFor\:Bit1], [TryFor\:Bit2], [TryFor\:Bit3]) VALUES (1,2,3);";
  43. print "sqlcmd:$sqlcmd\n";
  44. $sth = $dba->prepare($sqlcmd);
  45. $sth->execute();
  46.  

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

Attached Files
File Type: zip src.zip (9.4 KB, 43 views)
Feb 24 '09 #1
Share this Question
Share on Google+
4 Replies


KevinADC
Expert 2.5K+
P: 4,059
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.
Feb 24 '09 #2

eWish
Expert 100+
P: 971
Look into quote or use placeholders and bind values with the DBI. This will escape the characters for you.

--Kevin
Feb 24 '09 #3

BeemerBiker
P: 87
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.
Feb 25 '09 #4

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

Worked:
Expand|Select|Wrap|Line Numbers
  1. $qry = 'SELECT "TryFor:Bit1" FROM TestTAB';
  2. $sth = $dbh->prepare($qry);
  3. $sth->execute;
  4. $sth->dump_results;
  5. $dbh->disconnect;
  6.  
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.
Expand|Select|Wrap|Line Numbers
  1. my @RunThese = (
  2. "DROP TABLE [##info];",
  3. "SELECT [Employee Info].[Employee Name], [supervisorIDlookup].[email], [Employee Info].[Status], [Employee Info].[Employee ID], ".
  4.  " [Employee Info].[Department],  [supervisorIDlookup].[EmployeeID], [Employee Info].[Status Date], [Employee Info].[Position Title], ".
  5. # " [Employee Info].[SkillSoft:Bus-Select], [Employee Info].[SkillSoft:Bus-Full], [Employee Info].[SkillSoft:Desktop], [Employee Info].[SkillSoft:IT], ".
  6.  " [Employee Info].[Working on Degree]  INTO [##info] FROM [Employee Info] INNER JOIN [supervisorIDlookup] on [Employee Info].[Supervisor] = [supervisorIDlookup].[LastName];",
  7.  
  8.  
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.
Feb 25 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.