Access DB and spaces in column names

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
DeepakCorning
Premium Member
Premium Member
Posts: 503
Joined: Wed Jun 29, 2005 8:14 am

Access DB and spaces in column names

Post by DeepakCorning »

--- Moderator add on ---
Originally split from this post
(release, job type and Os were arbitrarily selected)
--- End moderator add on ---
I will like to add up my question here itself. Correct me I am wrong ,So accroding to the reply I can use MDB as a source if I create a ODBC to it?
I have to use it but problem with the MDB file is that the MDB file has column names with spaces something like "a b c". so when I try to use it as a ODBC stage the query which gets generated is select 'xyz'.'a''b''c' from 'xyz' , which is obviously wrong and doesnot work. I tried doing select 'xyz'.[a b c] from 'xyz' but stil it does not work. I dont have much knowledge of Access so dont know how to the best I can use it as a source in datastage.
Anyone who can help me in this?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

That's very close to hijacking the thread!

Set up the ODBC stage, with the DSN filled in. Then click GetSQLInfo. This goes out to the DSN and retrieves the SQL quote character (which is "`" for MS Access) and delimiter character.

So access needs queries like

Code: Select all

select `table`.`col1`, `table`.`col2` from `table`; 
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DeepakCorning
Premium Member
Premium Member
Posts: 503
Joined: Wed Jun 29, 2005 8:14 am

Post by DeepakCorning »

Yeah thats true but I dint want to create a seperate thread for the same topic.

Thats true ray but the thing which is bothering is that the column names have a space in between which I am not able to decipher in the ODBC stage as it puts quotes everywhere it finds space. I tried User define query but that also give sme the same error. Is it like Column Name should not have spaces in there??
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

The ODBC standard forbids spaces so the ODBC driver enforces the rule. That MS Access does not is just Microsoft not following its own standards!

About your only choice is to use user-defined SQL. Let DataStage generate the SQL and then modify it.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DeepakCorning
Premium Member
Premium Member
Posts: 503
Joined: Wed Jun 29, 2005 8:14 am

Post by DeepakCorning »

Thats the problem. That was th efirst thing that came in to my mind but it doesnot work.
crouse
Charter Member
Charter Member
Posts: 204
Joined: Sun Oct 05, 2003 12:59 pm
Contact:

Post by crouse »

Use User Defined SQL as Ray says, then remove all quotes from the SQL, enclose in square brackets ("[" and "]" ) the column names that contain spaces.

The DataStage column names must be renamed to contain no spaces.

Example User Defined SQL :

SELECT Table1.FirstColumn, [Table1.Second Column] FROM Table1;
Craig Rouse
Griffin Resouces, Inc
www.griffinresources.com
gpatton
Premium Member
Premium Member
Posts: 47
Joined: Mon Jan 05, 2004 8:21 am

Post by gpatton »

Another option is to create a view in ACCESS and rename the columns in the view so that they do not contain spaces.

Reference the view not the table using ODBC.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Dates can also be strange in Access; depending on the circumstances you may need to surround with "#" characters. For example

Code: Select all

select * from `Parts_Table` where `Parts_Table`.`Last_Updated` between ##FirstDateOfSample## and ##LastDateOfSample##
The inner "#" characters identify a reference to a job parameter, the outer "#" characters identify an MS Access date value.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Sameer1126
Participant
Posts: 2
Joined: Wed Oct 12, 2005 6:34 am

Post by Sameer1126 »

DeepakCorning wrote:Thats the problem. That was th efirst thing that came in to my mind but it doesnot work.
I ran into the same problem as Deepak today, and I simply did what ray and crouse suggested.

Code: Select all

SELECT `Table 1`.FirstColumn, `Table 1`.`Second Column` FROM `Table 1`;
It worked without any problems. Are you sure you didn't leave any extra "`" inside a column name? Those things are a bit hard to spot sometimes :)
rohithmuthyala
Participant
Posts: 57
Joined: Wed Oct 21, 2009 4:46 am
Location: India

Post by rohithmuthyala »

While connecting to SQL server while using the ODBC connector, it worked when I used the brackets "[" , "]" when the field names had spaces and '#'
Post Reply