Basic, no-frills code to initialize DefaultValue (VFP)

Remote views on SQL Server data must have default values. That’s tedious. Luckily it was fairly straightforward to write code to do it for me based on type.

Warning: If you use this code, use it at your own risk. Back up your data first, and test it before using it on production data. Also, note that in this case, I’m just setting values for fields in views.

_SetDefaults("YourDBC")
Return

Procedure _SetDefaults(tcDbc)
 Local lcDBC, lcDefault, lcType, lcFld
 lcDBC = Forceext(tcDbc, 'dbc')

 Select Alltrim(tbl.objectname) + "." + ;
  Alltrim(fld.objectname) As FieldName ;
  From (lcDBC) tbl ;
  Inner Join (lcDBC) fld On ;
  fld.parentid = tbl.objectid ;
  Where tbl.objecttype="View" ;
  Into Cursor lvw

 Use In Select(Juststem(lcDBC))
 Open Database (lcDBC)
 Set Database To (Juststem(lcDBC))

 Scan
  lcFld = Alltrim(FieldName)
  lcDefault = DBGetProp(lcFld ,"Field","DefaultValue")
  If !Empty(lcDefault)
   Loop
  Endif
  lcType = Left(DBGetProp(lcFld ,"Field","DataType"), 1)
  Do Case
   Case Inlist(lcType, 'C', 'M')
    DBSetProp(lcFld,"Field","DefaultValue","''")
   Case lcType = "L"
    DBSetProp(lcFld,"Field","DefaultValue",".F.")
   Case Inlist(lcType, 'D', 'T')
    DBSetProp(lcFld,"Field","DefaultValue",".NULL.")
   Case Inlist(lcType, 'N', 'Y')
    DBSetProp(lcFld,"Field","DefaultValue","0")
  Endcase

 Endscan

 Close Tables All
 Close Databases All

Endproc

Updated 2009.11.16: default value for dates and datetimes should be .NULL. instead of {}. Dates will come back bogus otherwise, since SQL Server doesn’t understand empty dates the same way VFP does.

Advertisements

One thought on “Basic, no-frills code to initialize DefaultValue (VFP)

  1. Pingback: Use .NULL. instead of {} for DefaultValue dates, datetimes (VFP-to-SQL Server) « Experiencing Life on the Verge

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s