Use .NULL. instead of {} for DefaultValue dates, datetimes (VFP-to-SQL Server)

I updated my post from yesterday to set the default value for date and datetime fields. A better choice than {} is .NULL., since SQL Server doesn’t understand empty dates the same way VFP does.

Advertisements

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.