Checking Field TAG

[mstrcmtr]

Is there any way to check field has tag or NOT

Before deleting any field from table check this field has any tag

*** Checking then Delete ***

DELETE TAG Field_8 OF Table_test.Cdx

 

[Tore Bleken]

Not directly, but you can use AtagInfo() to find out.

 

[Olaf Doschke]

If it's your database, you should know it, if not, use SDT to get excellent meta data about any aspect of an inherited DBC or at least GenDBC. You'll not only have the definitions of all indexes, but also relations these indexes are used in, those could also break. But then nothing will reveal if SEEKs or SEEK()s or INDEXSEEKS()s will break, if you remove an index and of course a field.

So in the end you'll need to know the side effects of removing a field, with or without index.

In my 16-17 years of foxpro using I have to really think hard about ever removing an established field in production. Even if some design got more complex, the old data was archived and I may have made some field not used for future data NULLable to be NULL from then onwards in all new records, but I don't even remember such a case. Even that can cause errors, when the application code doesn't expect NULLs in such a field.

Overall even a function detecting a field use in any index expression found in the array ATAGINFO creates will not give a definite answer about the use of the field in an application and side effects of removing it.

Bye, Olaf.

 

[mstrcmtr]

Please help how use AtagInfo() for this purpose

 

[Olaf Doschke]

You know no Afunctions? ADIR(), ALINES(), ...?

https://msdn.microsoft.com/en-us/library/aa978983(v=vs.71).aspx

After doing ATAGINFO the array you get has all index tag expressions in it's 3rd column. You search the field name anywhere in there. It's a moot thing to do because of the complexity, you're still far from the goal to decide whether you can remove the field even when also removing any tag it's used in.

Bye, Olaf.

 

[Tore Bleken]

Olaf. I think you make an assumption which may or may not be correct, namely that the OP uses databases. Personally I never use databases since they don't provide any advantage over the functionality I already have in my self created framework.

 

[Olaf Doschke]

That's another case, true, but still many of the things apply, you can also have SEEK on free DBF indexes, fields can be NULLable or not, etc. Things not existing with free tables only are relations. You also don't have primary key indexes, only candidate, but many dependencies of an application on a field still can break and cannot be decided just by inspecting whether a field is used in some index tag to remove that, too.

Well, in the end removing a field is in the responsibility of the developer doing it, just like adding a field. But ignoring a field is most often a better option in my opinion.

Bye, Olaf.

 

[Olaf Doschke]

Another thing is IDX files not necessarily stored in the same path as the table nor necessarily named as the table DBF file. Same goes for secondary CDX files. Just the corner cases. At least make your changes so you can revert them fast. Keep backups of data and application - should be done anyway.

Bye, Olaf.

 

[Mike Lewis]

Olaf. I think you make an assumption which may or may not be correct, namely that the OP uses databases.

Why is that an assumption? Doesn't ATAGINFO() work exactly the same with free tables?

What would be more helpful would be to know if the tag in question relates to a single field or to an expression. If the former, the easiest way would be to use TAGNO(). For example:

[tt]llTagExists = (TAGNO("SomeField") > 0)[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Mike. My comment about databases and assumptions was based on another assumption, namely that a DBC may contain info about index tags which you won't find with Ataginfo(). Since I don't use any DBC, this assumption may be wrong. If that's the case, I apologize for adding to the confusion.

 

[Olaf Doschke]

Well, if you want to make sure removal of a field is possible, you also have to look for expressions. But also for use of the index in relations, for use of the field as foreign key, for use in idx indexes, secondary cdx, SEEKs making use of the index, LOCATEs making use of the field and only indirectly of the index etc. etc. In the end just looking very closely at the tables main CDX you don't detect all dependencies on the field. Another thing coming to mind is views. If a view references a field you remove it'll also break. If you find the field in the primary or a candidate index type, the field should only be removed after establishing a new primary or secondary indexes and then surely code will have to be mended.

Besides I think tore said so because I mentioned SDT and GENDBC. If you have no DBC that's not applicable, of course, but still ATAGINFO will not be the only thing left to check for usage of a seemingly obsolete DBF field.

You don't need to remove a field from a DBF to remove it from a form, from a listbox or from a grid. Binding to a whole table or cursor is not the only way to bind, even when binding to a dbf directly, you have rowsourcetypes/recordsourcetype for fields, individual column controlsources, etc.

Bye, Olaf.

 

[Mike Lewis]

Regarding the tag being on a single field vs part of an expression, the example that the OP gave in his original post ([tt]DELETE TAG Field_8 OF Table_test.Cdx[/tt]) suggests a single field.

But, rather than speculating, maybe we should ask him to give us a bit more background information. Under what circumstances would you want to programmatically delete a field when you don't know at design time what index tags you have?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, tore, I don't see ATAGINFO returning more index info on DBFs part of a DBC, sure only DBC related tables can have the primary index type, but the ATAGINFO doesn't depend on a DBC. The DBC has index related data in it, but some of that is redundant with IDX and CDX header info. That's not the problem. It's all the further dependencies I talked of. Last not least any query done in code and not put into a view or qpr or cursoradapter class...

You can surely write a function detecting a field used in some main cdx index tag and then you can technically also remove that tag and finally the field without getting the error as in the following sample code trying to remove a field used in a tag, but you'll only pave the way to make an error free table altering, that'll not guarantee no further follow up errors you get from the application.

So here is the error mstrcmtr wants to prevent, I assume:

CREATE CURSOR crsTest (iid I, dDate D)
INDEX on dDate TAG xdate
ALTER TABLE crsTest DROP COLUMN dDate

Preventing that error is only preventing the most direct error you get from removing a field, it can be just the tip of the iceberg.

By the way the error 1531 here tells you which index tag references the field, so you might catch the error and fetch the index tag name from the message or use SYS(2018).

But once more said: Even if there wouldn't be an index tag to begin with and a field can be removed without error directly you never know what that causes in an application.

Bye, Olaf.

 

[Olaf Doschke]

Not yet mentioned: Reports can also refer a field or index in some field expression. stored procs, referential integrity rules....
To wnat to DELETE a TAG is a very short sighted solution to get a DROP COLUMN working.

Bye, Olaf.

 

[mstrcmtr]

I have Free table with some fields not in Use from many years making table size heavier and heavier therefore i want to remove those unUsed Fields

Please give sample of command AtagInfo() to solve my issue

Thanks

 

[Olaf Doschke]

If this is just for that case, then just read the error message, it tells you the tag in your way. Remove that from the indexs tab, then remove the field. There is no code necessary for that, just the table deginer.

Bye, Olaf.

 

[Olaf Doschke]

Another very simple solution to apply to any customer having your database or directory of free tables: Create a new file tablev2.dbf without the unwanted fields and append from table.dbf, create only necessary new tags and either finally rename files so your old table.dbf is named tablev1.dbf and your new table is named as the old one. When you're sure none of the removed fields is used in any way you won't get any errors from usage of the new DBF.

Are you getting anywhere close to 2GB? What type is your field? char fields can be shortened, to make room, memo fields just take 4 byte per record, only blow up your fpt, but you can empty the field and PACK MEMO. It's not necessary to drop a column.

Last not least if your customers don't need old data you can also backup the dbf and only keep last years data or whatever is sensible in the production dbf file. Also a way to reduce file sizes. Combined with a way to query all current and archive dbf files you'd partition data like was done in tha infamous euro tunnel foxpro application. It's a well tested way of acting upon larger data sets than are allowed and is much better scalable then dropping colummns. You can repeat it in whatever time intervals or other partition criteria and maintain any amount of data that way.

Bye, Olaf.

 

[Tore Bleken]

https://www.foxite.com/archives/how-to-check-if-specifc-index-exists-0000161795.htm

 

[Olaf Doschke]

Tore,

CREATE CURSOR crsTest (iid I, dDate D)
INDEX on dDate TAG xDate
INDEX on Bintoc(iid)+dtos(dDATE) tag allfields

If NOT IsTag("dDate", "crsTest")
   Try
      ALTER TABLE crsTest DROP COLUMN dDate 
   Catch
      ? "Altering fails"
   Endtry
Endif
DELETE TAG xDate
INDEX ON dDate TAG dDAte
If IsTag("dDate", "crsTest")
   DELETE TAG dDate
   Try
      ALTER TABLE crsTest DROP COLUMN dDate 
   Catch
      ? "Altering still fails"
   Endtry
Endif

FUNCTION IsTag( tcTagName, tcTable )
  LOCAL ARRAY laTags[1]
  LOCAL llRetVal
  *** Did we get a tag name?
  IF TYPE( 'tcTagName' ) # 'C'
    *** Error - must pass a Tag Name
    ERROR '9000: Must Pass a Tag Name when calling ISTAG()'
    RETURN .F.
  ENDIF
  *** How about a table alias?
  IF TYPE( 'tcTable' ) = 'C' AND ! EMPTY( tcTable )
      *** Get all open indexes for the specified table
      ATagInfo( laTags, "", tcTable )
  ELSE
      *** Get all open indexes for the current table
      ATagInfo( laTags, "" )
  ENDIF

  *** Do a Case Insensitive, Exact=ON, Scan of the first column of array
  *** Return Whether the Tag is Found or not
  RETURN ( ASCAN( laTags, tcTagName, -1, -1, 1, 15 ) > 0 )
ENDFUNC

This even doesn't help when having the convention to name index tags as fields, as you can't do so for expressions. You need another function.

Bye, Olaf.

 

[Jockey4]

Hi,

Apart from the other useful replies, here is my 'isTag' function:

Function IsTag(tcTagName,tcTable)
	Local lnArray, lcTable
	Local Array laTags[1]

	If Type('tcTagName')#"C"
		* error
		Return .F.
	Endif

	If Type('tcTable')="C" And !Empty(m.tcTable)
		lcTable = Juststem(m.tcTable)
		Ataginfo(laTags,"",m.lcTable)
	Else
		Ataginfo(laTags,"")
	Endif

	If Type("laTags", 1) = "A"
		lnArray = Ascan(m.laTags,m.tcTagName, -1, -1,1,7)
		If m.lnArray = 0
			lnArray = Ascan(m.laTags,m.tcTagName,-1,-1,3,7)
		Endif
	Else && laTags is not built - table without any index tags
		lnArray = 0
	Endif

	Return Iif(m.lnArray = 0 , .F., .T.)
Endfunc

Regards,

Jockey