Solved

I can't execute an SQL query cause of the column name contains spaces, db paradox table

Posted on 2009-05-08
8
666 Views
Last Modified: 2013-11-23
I can't execute a simple query cause of the freaking column name contains one 'space' character..
It's a dbBase paradox..

I can't rename the column name cause it's being used by another program(delphi i assume) which needs the format "Column Name " and not "Column_Name", else it crashes...

I've tried all of the above ending with error messages.

At all the other columns i can query everything BUT this one with the space is the most importan to me..

--------------------------
*Also i was thinking if i could just query without the Column_Name BUT with let's say the Column_Number(Number order from left to right,Duh :p, the stupid column is the 12th)
But dunno if that's possible.

Link For the db example file "h@@p://rapidshare.com/files/230795199/CYBER6.db"

Anyway
Thanks for reading...
This is my query, as u can see i've tried everything...Or Dunno
----------------------------------------------------------
Update cyber6.db
set Rem Time='00:00:00' where MEMBERID=3
----------------------------------------------------------
Update cyber6.db
set [Rem Time]='00:00:00' where MEMBERID=3
-------------------------------------------------------------
Update cyber6.db
set `Rem Time`='00:00:00' where MEMBERID=3
------------------------------------------------------------
Update cyber6.db
set Rem%20Time='00:00:00' where MEMBERID=3
--------------------------------------------------------------
Update cyber6.db
set "Rem Time"='00:00:00' where MEMBERID=3
-----------------------------------------------------------------
Update cyber6.db
set @"Rem Time"='00:00:00' where MEMBERID=3
--------------------------------------------------------------
Update cyber6.db
set "Stupid@Rem@@@@Time"='00:00:00' where MEMBERID=3  <------- I'm kidding

Open in new window

asdasdasd.JPG
0
Comment
Question by:Freeboss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24341661
Enclose the namen of the column with square bracket...

example:

Update cyber6.db
set `[Rem Time]`='00:00:00' where MEMBERID=3
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24341666
oopss... Sorry didn't see the code...
0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 55 total points
ID: 24341669
You don't say what database engine you're using (you say you're using a paradox database, but, are you connecting through the BDE or not?)
Try preceeding the column name with a table name, e.g.
Set MyTable."Rem Time" =
Here's the explanation from the BDE's Local SQL Help:
COLUMN NAMES
ANSI-standard SQL confines each column name to a single word comprised of alphanumeric characters and the underscore symbol, "_". Local SQL, however, is enhanced to support the Paradox and dBASE feature of non-alpha characters and spaces embedded in column names. Local SQL supports Paradox multi-word column names and column names that duplicate SQL keywords as long as those column names are
 
-Enclosed in single or double quotation marks
-Prefaced with an SQL table name or table correlation name
 
For example, the following column name consists of two words:
 
SELECT E."Emp Id"
 
FROM Employee E

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 22

Expert Comment

by:senad
ID: 24341837
"I can't rename the column name cause it's being used by another program(delphi i assume) which needs the format "Column Name " and not "Column_Name", else it crashes"

You can open your table with database desktop which is Delphi independent.
So I do not see the problem of renaming the column.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24341849
Yes you should be able to change the column names, but with paradox (and a very buggy Database Desktop) you could often have locked tables that would prevent you from opening Database Desktop. Sometimes you would have to go and delete all .NET and .LCK files - you can find them in a few places, such as:
-In the Database Desktop directory and subdirectories, usually located at C:\Program Files\Common Files\Borland Shared\Database Desktop (and check subfolders)
-The directory of your delphi project / program
-C:\ (the root of your hard drive).
-The "Working Directory" of Database Desktop which you set inside Database Desktop - which is usually a folder inside your Database Desktop directory, but, you could actually set it to any directory.

Also close any open datasets in your Delphi IDE, or better yet close everything in Delphi, and in some cases you have to close Delphi.
This is why so many people just reboot.
0
 

Author Comment

by:Freeboss
ID: 24342824
Hm......
I just woke up, I'll take a coffe and then post again but at first site I think i didn't give u the general idea about the connection between the *.db file and the delphi program..

The Db is accessed by a Net cafe program which at the column name "Rem Time" adds. or removes some prepaid time from a member..

The thing isn't that i can't Change the column name, Cause Of an error message sasying "is being used by another program" etc. etc.(I'm not that of a noob)

It's That IF I DO, When i open the program and let's say i want to add 5 hours to a member the program crashes cause it's supposed for it to read the //Remaining Time\\ Of a coustomer from the "Rem Time" column and not the "RemTime" or anything else..

Sorry for the missunderstanding.
*I don't have the source btw:(
--------------------------------------------------
Mr rfwoolf I'm connecting through BDE..
---------------------------------------------------
I really need a coffe, I try sth u said at your 1st post and repost l8.....

Thanks
0
 

Author Comment

by:Freeboss
ID: 24342847
I found it and all goes thanks to "rfwoolf"
That's the difference of a MASTER...

I'm gratefull, thank u very much man..
Working Code
------------------
Update cyber6.db
set Cyber6.db."REM TIME"='00:00:00' where Cyber6.db.MEMBERID=3
-------------------

Open in new window

0
 

Author Closing Comment

by:Freeboss
ID: 31579693
You really are a master, Grats !!!
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question