I'm converting a DBC to SQL using DDT...first time - so - having lots of questions!!!



What do you guys do with the default values? Do you enter them in the DDT and have that stored in the SQL database - or - is it best to leave that blank?



Also, what's best choice with regard to default values for primary keys and foreign keys - (-1 or 0)?



Lastly, what's the best way to get the data from VFP to SQL? Is there something in DDT that does that - or - am I on my own to write that code?



I figured some of you have already done this and would be able to help with some "gotchas" and things to avoid. Any help is greatly appreciated!



HB

Since I've done a lot VFP -> SQL conversions and bring a VFP backgound to SF I'll take a poke at this.



First, converting actual data from VFP to SQL is not a DDT function. DDT is primarily for keeping a schema in sync - think Stonefield for SQL.



I used a product called Full Convert Enterprise from Spectral Core http://www.spectralcore.com/order.php with great success. You might also try SQL2008 SSIS with the VFPOLEDB driver but I haven't actually done that. If your structures are pretty simple - and especially if they do not use GUID keys - you might even try the improved Upsizer Rick Schummer and Doug wrote in VFPX.



As far as default values, that is best done in the business object (there is an event handler there). The BOMapper will let you handle nulls when creating the strongly typed properties. For example, VFP dates that port over as null ( you need to change all your empty dates to null before porting to sql and allow for those nulls in the sql schema ) In the BOMapper you say you wwnat to replace null values in datetime fields with #1/1/1800# and this will cause SF controls to see that as an "empty" date.



In the DDT, for integer keys just make sure the PKs are marked as Primary Keys and you enable autoincrementing. The framework will handle everything else including using temporary -1 etc in parents and children before the save goes to the backend and then populating with the correct server generated incremental keys ( it is very cool )



Lots of us here speak Fox and its various frameworks ( I'm VFE, Edhy is VPM and I think we have Maxframe and even MM guys as well ) so just ask whenever you get stuck.



I can help quite a bit with Full Convert if you go that route and perhaps can give you some tips on how to massage your VFP data before feeding it to a converter into SQL to get easiest results.

Thanks for your response - very helpful!



I do have Full Convert and it seemed to work well - but - I was afraid of all the NULL values...there seemed to be a lot of people who warned against using NULL's. I seem to recall that some of the tutorials from SF mentioned not allowing NULL's - maybe I'm dreaming that.



But, let's say that I've decided to go ahead with using DDT for the structure and I'm going to write code to take the data from VFP and copy it to the SQL tables (because I've made a lot of progress toward that goal.) If I don't allow NULL's - am I okay - or - do I need to allow for NULL values?



Also, if I do put default values in the actual table structure - is that okay? That is do I have to do that again in the BO Mapper?



Thanks again!



HB

Hi Howard,

Adding to the good posting from Charles, let me help you clear some issues...

The data in SF is view through the SF Business Object's properties (much like a VFP cursor or view) in the SF BOM you have a facility to tell those properties how to handle null values so it will not generate an error while using the BO's properties in your forms, reports, etc. In other words you can tell the BO property that when a database null value is found to replace it with a default value that you will specify like in Charles example above with the datetime field.

Yes that is OK, but if these tables will not be used outside of SF, it is better to have all those settings in the business object's itself, so when you use the BO from a WinForm or a Web application it will enforce the rules you defined at the BO level.

On a side note, be aware that SF supports reading/writing to VFP tables, so if this is a commercial application type which will be installed in several places, you could just build the VFP import process in the SF application by creating VFP business object and do all your logic in SF.  I have not done that, but I will use that logic in one of my projects for a VFP application I am re-creating in SF.

Edhy Rijo