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

DefaultValue (VFP-to-SQL Server)

File this one, too, under the category “blindingly obvious…at least in hindsight” in the never-ending VFP-to-SQL Server saga.

Balancing up front cost and with cost of a change: use DefaultValues. For every field.

The end.

ETA: Be aware, the upsizing wizard doesn’t seem to carry over the DefaultValue property when it creates remote views on data.

DbgetProp( cName, “View”, “Tables”) fails…more often than I thought (VFP 9.0)

In an earlier post, I noted what  I thought was an anomoly: DBGetProp(cName,”VIEW”,”Tables”) unexpectedly returned an empty string. I rebuilt the view, and since it seemed to be an isolated issue in a view that had been around for years, I dismissed it as a glitch. Today I’ve discovered it again in a view I’ve been modifying. I don’t know at what point the DBC stopped recognizing all the tables, or even if there is expected, albeit inconvenient, behavior. The documentation sure doesn’t seem to cover it.

Is it an unreliable command? Or am I misusing it? Or just misunderstanding it? For now I’ll have to assume it’s unreliable and hard code the constituent cursors in the interest of moving along.

Phooey.

DBGetProp fails (VFP 9.0)

Ugh. Ever have DbGetProp( cViewName, “VIEW”, “TABLES”) return an empty string? Yes, the view is really a view, in the current database. Works perfectly fine in every regard. (It’s a very simple query that selects the year of a date field, and groups by it.) One table. One field. Weird. Took out the year function, and the DbGetProp still returned blank.

Rebuilding the view fixed it. But, um, what the hell?

NoData, requery, and views of views (VFP-to-SQL Server)

I’m working on a project converting a client’s VFP 9 data to SQL Server. The VFP app uses local views (via data objects) that use native VFP data and local views. Swapping the native VFP data for SQL Server data isn’t as simple as redirecting a view from a DBF to a remote view. I should have realized this since, as I well know, VFP doesn’t automatically requery underlying views.

Say I have a view on a People table that selects the last name–call it lv_PeopleByLastName. It’s trivial to redirect the view to query remote data instead of local data. But if I then use lv_PeopleByLastName with NODATA, the remote view is also opened NODATA. That makes sense. The gotcha is that requering lv_PeopleByLastName does not also requery the remote view. So, even after Requery() the local view will still be empty since the remote view is empty. One must requery the remote view, then the local view.

IOW, once open every data source operates independently. VFP doesn’t automagically maintain dependency info about an open datasource (except through the database container, of course).

This should not have surprised me. It’s not fatal, just a complication I should have foreseen. So, I can’t simply redirect a view from local data to a remote view without changing either the views or the way dataobjects manage requerying.

Here are my options as best I can tell:

  1. Change the views so they don’t use other views. To be frank, SQL syntax and I are not BFFs. I’d rather stick a fork through my hand than work as a database manager. SQL feels like one is writing entire programs in a single line of code–the antithesis of maintainability. So, I like SQL streamlined as much as possible. Luckily most queries are fairly straightforward.
  2. I could rework the data objects and the views so that data objects cascade other data objects representing underlying views that would be requeried whenever the parent object is requeried. Okay, I confess. I just threw that in there because options should always come in threes.
  3. The data objects could make note of what underlying views are involved and requery them when the object is requeried. That works. It’s not elegant, but it is effective, and considering the bottle neck is generally fetching remote data, doesn’t add significant processing time. However, if more than view uses the same underlying data, it could make for some unnecessary requerying.

It looks like option 1, with option 3 held in reserve. Which I could have had done by now instead of posting. But I’ll call this organizing my thoughts rather than procrastinating on the inevitable.

Looking to bookmark existing PDF, programmatically

I’ve used Ghostscript for some time to programmatically create and merge PDFs from Visual FoxPro. I’ve never been able to suss out how  to add bookmarks to an existing PDF document, and I’ve come to believe it’s not supported.

There are code solutions around, naturally, but so far I haven’t found a solution for automating it within VFP.  There are libraries, but not all libraries play nicely with VFP. It might be possible, but most are overkill also, since I don’t need a library full of functions. Just this one function.

Do I have to bite the bullet?

Cue tumbleweeds crossing in foreground of dust western ghost town.