Wiki Home

Vfe Pri Key


Namespace: Wiki
Here is another helpful function for VFE developers.

It sets the default value to generate keys using vfenewkey() for all of your primary keys.

It is the equivalent of opening the dbcx explorer, click open each table, click open fields, click on the primary key, click on "Default Value Generation Key", click on the builder, click on Incremental, click on Last Value, enter in the right number of 0's, click Ok. click, click, click... Now I just run this function. done.

Bug: I think the database needs to be validated one more time when it is done. still looking into this. Any comments? The second call to loMeta.Validate( lcDbc, "database" ) seams to do the trick.

Just to make things even more confusing, VFP "fixes" the expression you put into default value. It intelligently (?) forces some things to lower and others to upper.

Note: I am leaving this code here as an example of working with Dbcx. I have given up trying to use VfeNewKey() (The dbcx code is sound, but VfeNewKey dosn't play nice when you try and set it up like this. That may be fixxed soon.)


lPriKey( ;
 "C:\vfe6\dev\test\data\test3.dbc", ;
 "C:\vfe6\dev\test\metadata\", ;
 "C:\vfe6\dev\test\data\", ;
 "C:\vfe6\vfeframe\libs\" )

return

function lPriKey( tcPnmDbc, tcPthMta, tcPthAppIds, tcPthDbcx )

* Sets all of the primary keys to increment, base 10, seed 000000
* if they have not been set
* Parameters:
* tcPnmDbc - Path and Name of dbc
* tcPthMta - Path to Metadata
* tcPthAppIds - Path to AppIds.dbf
* tcPthDbcx - Path to DbcxMgr.vcx

local ;
	lcPnmDbcx, ;
	loMeta, ;
	lcCurDir, ;
	lcPnmAppIds, ;
	lcDbc, ;
	lnTbls, laTbls(1), lcTbl, ;
	lnTags, laTags(1), lcTag, ;
	lcKeyFld, ;
	lcObjNam, ;
	lcDefault, ;
	lnSiz

lcPnmDbcx = addbs( tcPthDbcx ) + "DbcxMgr.vcx"
loMeta = NewObject( "DbcxMgr", lcPnmDbcx, "", .t., tcPthMta, .t. )
assert vartype( loMeta ) = "O"

lcPnmAppIds = addbs( tcPthAppIds ) + "AppIds.dbf"
* If the AppIds.dbf table dosn't exist, create it.
if !file( lcPnmAppIds )
	create table ( lcPnmAppIds ) free ;
		( cKeyName c(200), ;
		cValue c(30) )
	index on upper( ckeyname ) TAG cKeyName for !deleted()
	* Add to dbcx - when I figure out how...
	lcCurDir = sys(2003)
	cd (tcPthAppIds)
	loMeta.Validate( "AppIds", "table" )
	cd (lcCurDir)
endif

open database (tcPnmDbc)
lcDbc = lower( juststem( dbc() ) )

* Validate the dbc. This will add in any new tables.
loMeta.Validate( lcDbc, "database" )

* Get a list of Tables in the dbc
lnTbls = loMeta.dbcxgetallobjects( lcDbc + "!tables", @laTbls )

for each lcTbl in laTbls

	* Get the primary key for this table
	lnTags = loMeta.dbcxgetallobjects( lcDbc + "!indexes " + lcTbl, @laTags, "","CBCTAGTYPE", "P" )
	assert inlist( lnTags, 0, 1 )

	* Strip the table name from the tag - leaves just the field.
	lcKeyFld = substr( laTags(1), at( ".", laTags(1) ) + 1 )

        * If the table has a primary key
        if !empty( lcKeyFld )

            * Check to see if it is set to generate ID's

            lcObjNam = lower( lcDbc + "!" + lcTbl + "." + lcKeyFld )
            lnDfltType = loMeta.DbcxGetProp( lcObjNam, "field", "nDfltType" )

            * If it is none, set it
            if lnDfltType = 1

				loMeta.DbcxSetProp( lcObjNam, "field", "mDefault", 'VFENEWKEY("'+lcObjNam+'")' )
				loMeta.DbcxSetProp( lcObjNam, "field", "cDfltKey", lcObjNam )
				loMeta.DbcxSetProp( lcObjNam, "field", "nDfltType", 3 )

				* Get Curent High key to seed counter -- Hope this works!
				lcSqlCmd = "select max(" + lcKeyFld + ") " ;
					+ " from " + lcDbc + "!" + lcTbl ;
					+ " into array laMaxKey"

				&lcSqlCmd

				* Close the table that was just opened by SQL
				use in (lcTbl)

				* If there are currently no records
				if vartype( laMaxKey ) = "U"
					* Seed with 0
					lxMaxkey = 0
				else
					lxMaxKey = laMaxKey(1)
				endif

				* Get the size of the field
				lnSiz = loMeta.DbcxGetProp( lcObjNam, "field", "CbNSize" )

				lcMaxKey = padl( lxMaxKey, lnSiz, "0" )				
				insert into (lcPnmAppIds);
				  ( cKeyName, cValue ) ;
				  values( lcObjNam, lcMaxKey )

            endif

        endif

endfor

* Validate the dbc.
* This will push the mDefault values into the dbc, I think.
loMeta.Validate( lcDbc, "database" )

return





Contributors Carl Karsten

Category Visual FoxExpress Category Visual FoxExpress Tips And Tricks Category DBCX Category Code Samples
( Topic last updated: 1999.10.15 12:40:13 PM )