>>SQL server or one of the other well known backends, all have datatype support for VFP and beyond (even there is support for the general field but I believe nobody use general in VFP). You are asking this specifically for SQL server. You should be aware of depending on driver and what you use (RV,SPT, CA) you might not get what you expect from the backend. There is no "empty" date\datetime and FWIW you must set your date\datetime to accept null.
>
>Does this mean that one has to change all the empty date fields to hold a null before uploading and then change all coding to check for ISNULL() instead?
>


Not exactly. You can set the field to accept nulls and use a function to convert empty to null. This would work with cursoradapters where you can use ConversionFunction. Here is the e2n.prg I use for this purpose:

Lparameters tuValue
If Empty(NVL(m.tuValue,0)) OR ;
	(Vartype(m.tuValue) = 'C' And Trim(m.tuValue) == '{}')
	Return .Null.
Else
	Return m.tuValue
Endif

In your coding you wouldn't check for isnull(). You want it to be NULL, not empty. For data transfers if you use CursorAdapter then a function like the above would take care of converting empty to null. If your data is not small then you would want doing bulk updates and then you need to pass NULL.



>
>
>>If I were you, I would choose postgreSQL as the backend. If you ask why, there are plenty of reasons that may be too much to discuss here, so maybe just to keep it short it is said to be "most advanced open source RDBMS" - a truly open source one, you are free to do anything with it commercial or not. (as per the datatypes it has much more types than MS SQL server and not only that, much more built-in functions for them).
>
>Interesting...I had thought of it as this client also uses a document management solution from another company and the document meta data is stored in a PostreSQL server. The IT guy at this company has said that I should just take it over and rewrite it because a) the developer maintaining it is hard to get in contact with at times and b) most of the documents going into the documents store are pushed in via the VFP app. The only thing we have run into is that VFP has the 16MB limit on strings so on large documents, the users still need to use the browser interface to this other app.
>

VFP has that limit only in document for strings. You can pass a memo handle which contains hundreds of Mbs.


>>Structures DO need to change. Generating keys from a table can of course stay. (and AUTOINC is not the only way for generating keys automaticall). In SQL server there is SEQUENCE too for example. But that is relatively new and most of the SQL server developers don't use it (not so easy to use in MS SQL server - in postgreSQL however it is as easy as 1,2,3 and behind the scenes it keeps the values and some other info for keys in a table).
>
>Can you elaborate about "structures need to change" - if the table design is correct (which it is), is it only some field types that need to change? I still was not sure what to do about memo fields in the current table as someone said they are replaced by varchar/binary but it seemed like the limit was 8000 chars (which I think some of them are). Does postgres have a field type that corresponds to memo?
>
>Albert

It is field types. For example in VFP we use C a lot, which is a fixed length text field. In other backends that is a waste of space if nothing else and varying lenght character is used. In VFP there is "so called" varchar (but not in fact a varchar).
What you would use for memo, primarily depends on the backend you use and what you store in there. In VFP, a memo field can store anything, be it plain text or a binary data such as a complete file (say a JPEG file). In SQL server, some types are being depreceated, so you would use Varchar(MAX) and Varbinary(MAX) if the data might be over 8000 bytes. Anything less than 8000 Varchar\Varbinary(N) is the data type to choose.
In case of postgreSQL, varchar is there but "text" is a better and simpler choice for large data (as per doc largest string is around 1 Gb). For binary there is bytea and blob.

(PostgreSQL has more types to cover not only VFP but MS SQL server. It has more types, that a for VFP developer unexpected to have in a database - like json, hstore, range, interval, enum, internet address ...).

For storing documents, there are also other ways, but mostly with No-SQL databases or distributed file systems managed by other languages.