Column names have spaces in them!!!

I am using the ODBC module to talk to an Access database. In that
database some of the column names have spaces in them (something like
EMPLOYEE NAME).

I have tried a bunch of stuff (backticking, brackets, braces, variables)
but I cannot seem to do a select on that table.

my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
`EMPLOYEE NAME` = 'NAME HERE'" );

How do I use a column name with spaces in it?!

I cannot change the table name itself.

Robert

Re: Column names have spaces in them!!!

I believe you want square-brackets for Access:

my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE [EMPLOYEE NAME]
= ?");

Paul

Quoting Robert Hicks :

> I am using the ODBC module to talk to an Access database. In that
> database some of the column names have spaces in them (something like
> EMPLOYEE NAME).
>
> I have tried a bunch of stuff (backticking, brackets, braces, variables)
> but I cannot seem to do a select on that table.
>
> my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
> `EMPLOYEE NAME` = 'NAME HERE'" );
>
> How do I use a column name with spaces in it?!
>
> I cannot change the table name itself.
>
> Robert
>

Re: Column names have spaces in them!!!

You need to pass the quotes to the SQL engine. And by the way, you
should either use parameters or the quote function for values:

my $sth=$dbh->prepare('select * from taskhours_per_date where "employee
name"=?');
$sth->execute('NAME HERE');

Maybe MS Acesss has other ways to do this, especially old Access
versions have some very strange behaviours.

Alexander

Robert Hicks wrote:

> I am using the ODBC module to talk to an Access database. In that
> database some of the column names have spaces in them (something like
> EMPLOYEE NAME).
>
> I have tried a bunch of stuff (backticking, brackets, braces,
> variables) but I cannot seem to do a select on that table.
>
> my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
> `EMPLOYEE NAME` = 'NAME HERE'" );
>
> How do I use a column name with spaces in it?!
>
> I cannot change the table name itself.
>
> Robert



--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: Column names have spaces in them!!!

Ugg!!

first coice is to rebuild the table

second choice

I found that wrapping the offending field name in [ ] worked with ODBC and
OLE but I am not sure how this will workd with DBI?

something like this

SELECT * FROM taskhours_per_date WHERE [EMPLOYEE NAME] = 'NAME HERE'

might work

option three

Create a number of select querries on the access DB without the the spaces,
Been years since I did this but I remember it was a very messy solution and
slow.


----- Original Message -----
From: "Robert Hicks"
To:
Sent: Tuesday, January 31, 2006 11:27 AM
Subject: Column names have spaces in them!!!


> I am using the ODBC module to talk to an Access database. In that
> database some of the column names have spaces in them (something like
> EMPLOYEE NAME).
>
> I have tried a bunch of stuff (backticking, brackets, braces, variables)
> but I cannot seem to do a select on that table.
>
> my $sth = $dbh->prepare("SELECT * FROM taskhours_per_date WHERE
> `EMPLOYEE NAME` = 'NAME HERE'" );
>
> How do I use a column name with spaces in it?!
>
> I cannot change the table name itself.
>
> Robert
>

Re: Column names have spaces in them!!!

Alexander Foken wrote:
> You need to pass the quotes to the SQL engine. And by the way, you
> should either use parameters or the quote function for values:
>
> my $sth=$dbh->prepare('select * from taskhours_per_date where "employee
> name"=?');
> $sth->execute('NAME HERE');
>
> Maybe MS Acesss has other ways to do this, especially old Access
> versions have some very strange behaviours.
>
> Alexander

Could it be that DBD::ODBC just cannot handle it? I tried it that way, I
tried it with brackets, backticks, double quotes, etc. and no go.

I was hoping just to slap a small web frontend to it but I guess I will
go the heavier route and move it over into Oracle using my own schema.

Thanks for the replies.

Robert

Re: Re: Column names have spaces in them!!!

use backticks instead of double quotes:

my $sth=$dbh->prepare('select * from taskhours_per_date where `employee
name`=?');

Regards,
Renee

Am 31.01.2006 um 23:58 Uhr haben Sie geschrieben:
> Alexander Foken wrote:
> > You need to pass the quotes to the SQL engine. And by the way, you
> > should either use parameters or the quote function for values:
> >
> > my $sth=$dbh->prepare('select * from taskhours_per_date where
"employee
> > name"=?');
> > $sth->execute('NAME HERE');
> >
> > Maybe MS Acesss has other ways to do this, especially old Access
> > versions have some very strange behaviours.
> >
> > Alexander
>
> Could it be that DBD::ODBC just cannot handle it? I tried it that way,
I
> tried it with brackets, backticks, double quotes, etc. and no go.
>
> I was hoping just to slap a small web frontend to it but I guess I
will
> go the heavier route and move it over into Oracle using my own schema.
>
> Thanks for the replies.
>
> Robert
>
>
>

Re: Column names have spaces in them!!!

Robert Hicks wrote:

> Alexander Foken wrote:
>
>> You need to pass the quotes to the SQL engine. And by the way, you
>> should either use parameters or the quote function for values:
>>
>> my $sth=$dbh->prepare('select * from taskhours_per_date where
>> "employee name"=?');
>> $sth->execute('NAME HERE');
>>
>> Maybe MS Acesss has other ways to do this, especially old Access
>> versions have some very strange behaviours.
>>
>> Alexander
>
>
> Could it be that DBD::ODBC just cannot handle it? I tried it that way,
> I tried it with brackets, backticks, double quotes, etc. and no go.

I think the main problem here is MS Access. I've worked a lot with
DBD::ODBC, and except with Unicode data, I never had problems. You did
not tell us wich version of Access you are using, there are a lot of
differences between the various versions, older versions (like 2.0 or
95) are just plain pain, newer versions (like 2000) slowly evolved to
something comparable to an SQL database. Don't get me wrong, Access is a
nice frontend for a database, but its own "database" is nothing I would
use for more than 100 records in one or two tables.

>
> I was hoping just to slap a small web frontend to it but I guess I
> will go the heavier route and move it over into Oracle using my own
> schema.

You can use Oracle and Access, just install the Oracle ODBC drivers onto
each client and use Access just for the forms. DON'T let Access handle
the business logic, this is something you definitly want to do on the
(Oracle) server. If you don't have an Oracle License, you could also use
the free (as in beer) MSDE, or the free (as in beer and speech) PostgreSQL.

You could slowly migrate your system, a first step would be to make sure
all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/. Next, move
the tables and the business logic to a "real" database. Then finally,
get rid of ODBC drivers and Access on the clients and switch to a web
frontend using a native driver like DBD::Oracle or DBD::Pg.

Alexander

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: Column names have spaces in them!!!

At 9:44 AM +0100 2/1/06, Alexander Foken wrote:
>You could slowly migrate your system, a first step would be to make
>sure all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/.
>Next, move the tables and the business logic to a "real" database.
>Then finally, get rid of ODBC drivers and Access on the clients and
>switch to a web frontend using a native driver like DBD::Oracle or
>DBD::Pg.

Any "real" database can use all of the same identifiers that Access
can, and in fact you can use any character at all in one. You just
bound them in double-quotes everywhere they are referenced, at least
with databases conforming to the SQL standard of delimited
identifiers; some products may use alternate delimiters. Chopping
out the spaces and stuff is only crippling yourself. -- Darren Duncan

Re: Column names have spaces in them!!!

Darren Duncan wrote:

> At 9:44 AM +0100 2/1/06, Alexander Foken wrote:
>
>> You could slowly migrate your system, a first step would be to make
>> sure all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/.
>> Next, move the tables and the business logic to a "real" database.
>> Then finally, get rid of ODBC drivers and Access on the clients and
>> switch to a web frontend using a native driver like DBD::Oracle or
>> DBD::Pg.
>
>
> Any "real" database can use all of the same identifiers that Access
> can, and in fact you can use any character at all in one. You just
> bound them in double-quotes everywhere they are referenced, at least
> with databases conforming to the SQL standard of delimited
> identifiers; some products may use alternate delimiters.

Right. But using a restrictive set of characters for table and column
names makes things easier. "The column for the number of hours worked
for that day and that client, including phone support, but without
travel time" may be a perfectly valid and very speaking column name, but
"Hours_Worked" is shorter, easier to type, does not run easily into name
length limits, and has no need for quotes. I do not propose to use
stupid abbreviations like "hsw", but to use reasonably short and
readable names (two to three words), using only letters and digits and
the underscore instead of the space. In the original example,
"EMPLOYEE_NAME" instead of "EMPLOYEE NAME"

> Chopping out the spaces and stuff is only crippling yourself. --
> Darren Duncan

I don't want to remove spaces, I want to replace them with a character
that does not need quoting. Column and table names are identifiers for
the database and the programmer, not pretty and nice names for the end
user. Nice names and providing help is the job of the front end, not the
job of the database.

Alexander

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: Re: Column names have spaces in them!!!

On Wed, Feb 01, 2006 at 02:08:32AM +0100, perl@renee-baecker.de wrote:
> use backticks instead of double quotes:
>
> my $sth=$dbh->prepare('select * from taskhours_per_date where `employee name`=?');

That's not portable.

The DBI has a $dbh->quote_identifier method to abstract this and
do-the-right-thing for the database being used.

Tim.

> Regards,
> Renee
>
> Am 31.01.2006 um 23:58 Uhr haben Sie geschrieben:
> > Alexander Foken wrote:
> > > You need to pass the quotes to the SQL engine. And by the way, you
> > > should either use parameters or the quote function for values:
> > >
> > > my $sth=$dbh->prepare('select * from taskhours_per_date where
> "employee
> > > name"=?');
> > > $sth->execute('NAME HERE');
> > >
> > > Maybe MS Acesss has other ways to do this, especially old Access
> > > versions have some very strange behaviours.
> > >
> > > Alexander
> >
> > Could it be that DBD::ODBC just cannot handle it? I tried it that way,
> I
> > tried it with brackets, backticks, double quotes, etc. and no go.
> >
> > I was hoping just to slap a small web frontend to it but I guess I
> will
> > go the heavier route and move it over into Oracle using my own schema.
> >
> > Thanks for the replies.
> >
> > Robert
> >
> >
> >
>
>

Re: Column names have spaces in them!!!

On Wed, 01 Feb 2006 13:36:41 +0100, Alexander Foken wrote:

Hi Alexander

> Right. But using a restrictive set of characters for table and
> column names makes things easier. "The column for the number of

Just as I raved about back in 2003 :-):

http://savage.net.au/Ron/html/naming-database-objects.html

Now I'm moving towards Rose::DB, I think I'll be adopting plurals for table=

names, but apart from that...
--
Cheers
Ron Savage, ron@savage.net.au on 2/02/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: Column names have spaces in them!!!

At 9:30 AM +1100 2/2/06, Ron Savage wrote:
>On Wed, 01 Feb 2006 13:36:41 +0100, Alexander Foken wrote:
>
>> Right. But using a restrictive set of characters for table and
>> column names makes things easier. "The column for the number of
>
>Just as I raved about back in 2003 :-):

Now, I understand the arguments for limiting names to certain
characters that don't need delimiting, and that's fine for some
contexts. But I also wanted to make clear that there are some
contexts where such limiting is counter productive, and so it is good
for database engines to be flexible, regardless of how users decide
to design databases.

I considered it important to be able to support spaces and other
arbitrary characters, because I see this support as making things
easier to use from a non-programming user's point of view.

Say we're making a consumer database application that is of the
point-and-click GUI variety and has little to no structural
abstraction (such as with Filemaker Pro or MS Access or perhaps
Oracle Forms), where users can define their own tables and columns
and such in the same ways they can define category names or folder or
file names, where they just type the name into a text input box.
These users would never see anything resembling programming code,
such as SQL, but just know they're making a database.

It would be quite natural for such users to make identifiers like
'Person' and 'Home Address' and 'Home Telephone' and 'Work Telephone'
and so on; it isn't natural for them to say 'Home_Telephone' and such.

So if we're making this consumer application and it is implemented on
top of a generic SQL database, and assuming there is very little
abstraction involved, we would probably be generating SQL code that
lines up the names the users chose with identical table and column
and such names, including the spaces. There would be no translation
necessary since the identifiers in the database are the same as what
the users see.

Sure, we could impose naming restrictions on the users, so we can
generate SQL without delimited identifiers, but then that would be
making things more difficult for the users to make it easier for
ourselves. And it isn't even that much extra work to support this,
or it may be less work.

From users' point of view, I see allowing this flexability to be like
allowing them to name their file system files anything they want, and
often they like to put spaces in file names. The users aren't
creating programming code, there creating boxes to put their data in,
conceptually not far off from folders and files.

I'm also not suggesting that identifiers have to be arbitrarily long,
like whole sentences; rather, just about the same lengths as are
valid for folder or file names; just they should be allowed to
contain spaces and such.

-- Darren Duncan

Re: Column names have spaces in them!!!

On Wed, 1 Feb 2006 15:46:31 -0800, Darren Duncan wrote:

Hi Darren



> It would be quite natural for such users to make identifiers like
> 'Person' and 'Home Address' and 'Home Telephone' and 'Work
> Telephone' and so on; it isn't natural for them to say
> 'Home_Telephone' and such.

I agree. My document may not spell it out, but it's simply a statement of what I
choose to limit myself to when I have control over the names.
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html

Re: Column names have spaces in them!!!

Alexander Foken wrote:

> You could slowly migrate your system, a first step would be to make sure
> all table and column names match /^[A-Za-z][A-Za-z0-9_]+$/. Next, move
> the tables and the business logic to a "real" database. Then finally,
> get rid of ODBC drivers and Access on the clients and switch to a web
> frontend using a native driver like DBD::Oracle or DBD::Pg.
>

That is what I am going to do. The guy that built the original had one
foot out the door and the more I look at what he did the harder it is to
keep from barfing.

I am going to redo the schema a bit and just migrate the actual data
over. We have an ent. license for Oracle so I may as well use it.

Robert