Could you point a way to programatically change a DBF field type from Character to Character binary?

Hi Experts

Could you point a way to programatically change a DBF field type from Character to Character binary?

Or optionally how to programatically create a Character (Binary) column by using CREATE TABLE(...

Thanks in advance.
Eduardo FuerteDeveloper and AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
ALTER TABLE command should do it:

ALTER TABLE <YourTable> ALTER COLUMN <YourColumn> <YourDataType> NOCPTRANS

Of course, the table backup is highly recommended before this operation.

CREATE TABLE also supports NOCPTRANS keyword, e.g.:

CREATE TABLE xxx2 CODEPAGE = 1252 (aaa char(10) NOCPTRANS, bbb int, ccc memo NOCPTRANS, ddd char(20))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Hi

The problem here is that

<YourDataType>  

if I use something like  Character(Binary)  it's not an acceptable value....
0
pcelbaCommented:
No, the Character(Binary) is not correct data type in SQL command.

You have to use correct SQL syntax in CREATE/ALTER TABLE, so:

C(10)
char(10)
character(10)

and add the keyword NOCPTRANS when you need the "(binary)" functionality:

C(10) NOCPTRANS
char(10)  NOCPTRANS
character(10) NOCPTRANS

The Character (Binary) is just Table designer equivalent of the SQL char(nn)  NOCPTRANS.
Also the DISPLAY STRUCTURE shows the data type as Character (binary) but SQL is another world and its incorporation into Visual FoxPro is outside commonly used SQL standards sometimes but functional.
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Eduardo FuerteDeveloper and AnalystAuthor Commented:
That worked!

Just one more thing

How to check if a field is Character(Binary) before to alter it?

If Type('PESSOAS.NOME_PES') <> "C(100) NOCPTRANS"
       ...
       ALTER TABLE pessoas ALTER COLUMN nome_pes C(100) NOCPTRANS
       ...
  

Open in new window


The type function always consider it like "C" with no diferentiation if it's binary.
0
pcelbaCommented:
Yes, the TYPE() is suitable for other tasks only...

You may use following code for more ideas:
SELECT 0
CREATE TABLE xxx2 (aaa char(10) NOCPTRANS, bbb int, ccc memo)
LOCAL ARRAY laStru(1)
LOCAL lnI, lnFlds

lnFlds = AFIELDS(laStru)

FOR lnI = 1 TO lnFlds
  ? "Name: ", laStru[m.lnI, 1]
  ?? "  Type: ", laStru[m.lnI, 2]
  ?? "  NOCPTRAN: ", laStru[m.lnI, 6]
NEXT

Open in new window

0
pcelbaCommented:
Optionally you may use following function:
FUNCTION IsNoCPtran
LPARAMETERS lcFldName, lcWorkarea

IF ISNULL(m.lcFldName) OR EMPTY(m.lcFldName) OR VARTYPE(m.lcFldName) <> 'C'
  RETURN .F.
ENDIF

IF ISNULL(m.lcWorkarea) OR EMPTY(m.lcWorkarea) OR VARTYPE(m.lcWorkarea) <> 'C'
  IF EMPTY(ALIAS())
    RETURN .F.
  ENDIF
  lcWorkarea = ALIAS()
ENDIF

LOCAL ARRAY laStru(1)
LOCAL lnI, lnFlds
lnFlds = AFIELDS(laStru, m.lcWorkarea)

lnI = ASCAN(laStru, m.lcFldName, 1, -1, 1, 15)

IF m.lnI > 0
  RETURN laStru[m.lnI, 6]
ENDIF

RETURN .F.

Open in new window

0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
In the meanwhile I just did...

	Local Array laStru(1)
	Local lnI, lnFlds

	lnFlds = Afields(laStru)

	For lnI = 1 To lnFlds

		If laStru[m.lnI, 1] = "NOME_PES"
			If laStru[m.lnI, 6] = .F.

				Alter Table pessoas Alter Column nome_pes C(100) NoCPTrans

				Scan
					Replace nome_pes With encripta_nome(pessoas.nome_pes)
				Endscan

				Exit

			Endif
		Endif

	Next

Open in new window


That worked out very well.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Pcelba

Problem elegantly solved.

Thank you for so good assistance!
0
pcelbaCommented:
You are welcome!

Just remember ALTER TABLE requires exclusive table opening.
0
Eduardo FuerteDeveloper and AnalystAuthor Commented:
Certainly, I use to do that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.