• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

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.
0
Eduardo Fuerte
Asked:
Eduardo Fuerte
  • 5
  • 5
3 Solutions
 
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
 
Eduardo FuerteAuthor 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Eduardo FuerteAuthor 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 FuerteAuthor 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 FuerteAuthor 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 FuerteAuthor Commented:
Certainly, I use to do that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now