VFP to SQL2005 OLE DB "errors"

I'm trying to bulk load tables in SQL 2005 from a Visual FoxPro 7 database. We have the latest VFP OLE DB driver loaded and have run into an odd issue.

When viewed from the table designer in VFP the tables have all columns set to not null (null column is not checked). However there are lots of rows with "empty" values for columns with both numeric and date data types. When I run a select on the tables from SQL these "empty" values come back as a default value based on the columns data type; 0 for numeric columns and 12/30/1899 for date fields.

One question, how are these fields that are set to not allow a null value allowed to be empty When I select them with an OLD ODBC 2.0 driver in an ODBC test app they are filled with spaces. Numeric fields should not be filled with spaces... Is this a FoxPro thing

Next, is there a way to turn off the insertion of default values in the OLE DB driver Or if not, can the old odbc driver be used from SQL 2005 I have a system DSN defined that works from an ODBC test app when using ODBC 2.0 syntax, but SQL 2005 doesn't seem to want to connect to it.

TIA,

Ed

Foxpro can have "empty" values instead of nulls. Empty is not null but generally they meant to be null w/o having the problems of null in other backends (thinking all that frustration that developers need to face just to handle a null for a datetime on a form it is a good thing). Who really would want to use null in SQL server for a datetime if they had a chance of using empty instead

You can handle it in your sql like:

select cast( iif( myDateTime = {}, null, myDatetime ) as datetime) as myDatetime, .... from myTable ...

You can turn off insertion of "empty" values instead of null values in OLEDB driver and it is the default. Though it's the default you can explicitly set it with:

set null on

which is supported by the VFPOLEDB.

Do not use old ODBC driver. Use VFPOLEDB driver instead. VFPOLEDB driver can use an existing DSN as the connection but I'd simply create my own connection (say enabling ad hoc queries and using OpenRowSet()).

In SS2005 the following doesn't work

set null on

you can set ansi_nulls, or ansi_null_dflt_on, or ansi_null_dflt_off but not just null. Or am I missing something here Is there some other way to do it

The datetime thing, while anoying, is fixable (can also do a case statement). My problem is that numeric/decimal fields that are empty in VFP are coming in with a default of 0. If it is empty, then it should be null. A value of zero implies that someone entered it, while empty/null implies "no answer". And "no answer" should never be implied to mean zero.

As for default behaviours, I've done nothing to change the VFPOLEDB driver's behaviour so far. What I'm seeing is, to me at least, the default behaviour. And that is that I'm getting manufactured data where it shouldn't exist.

Thanks again,

Ed

(I wrote a long reply but lost when it couldn't connect, I'll keep short this time).

You're missing a series of things:

-I meant to use "set null on" on VFPOLEDB connection. It helps during inserts and your source should allow nulls for fields yuou need it.

-That are empty and not null should have a value, right So VFP simply provides that value. A value of zero do not imply that someone entered it. It depends on application. If you want to differentiate an enetered value vs one never entered allow nulls (strangely nulls can be entered too - for example if a foreign key has null value does that mean it was never entered). As they say nulls are evils.

-You're not getting manufactured but stored data.

You do not set "allow nulls" on all your fields that are optional, do you.

Still not getting it I'm afraid. I've set up a linked server in SS2005 using the VFPOLEDB driver pointing to a free table directory and that is my connection. I don't see any way to "set null on" (which if it is the default should already be the case). There doesn't appear to be a property in the properties box for the linked server settings at least. Beyond that it is just plain old sql in a query window...

select * from linkedserver...tablename

If there is some other way or place to do this, I don't know it. What do you mean when you say to set null on for the connection

Beyond that, I don't mean to start a discussion on the semantics of null vs. empty vs. not empty or null. As you note, it depends on the application. In this case, empty needs to be null when moved to SS2005 and not converted to 0.

would be automatically filled with empty values when not specified in insert statement.

I repeat you can do the conversion on your select statement:

select cast( iif( empty( myField ), null, myField ) as integer ) as myField, cast( iif( empty( myDate ), null, myDate ) as date ) as myDate, .... from linkedserver....

OK. Sorry I misunderstood your comment on set null. I understand now, and knew about, how that functions for inserting new rows. That is not an issue here. I'm just pulling from the VFP database, not pushing to it.

your select however won't work though.

I can do

select case dateField when '12/30/1899' then null else dateField end as dateField from [linkedserver]...table

and that will work for Date fields because the OLE DB driver is pushing 12/30/1899 into the data when it is empty in the VFP database. (12/30/1899 isn't likely to be a real value in this database.) I cannot, as you suggest, test for empty though. There is no empty when it gets to SS2005, the default has already been pushed into the data by the OLE DB driver.

For the numeric fields, it doesn't come across as empty. It comes across as 0. So I have no way to tell that it was empty in the VFP database. A value that was empty in VFP looks exactly like a row where someone explicitly put a value of 0. And since they are not the same thing in this application, we need a way around the OLE DB behaviour.

My problem isn't that VFP has this concept of empty but not null. It is that the VFP OLE DB driver is CHANGING data which is empty to some other default value based on the data type without asking. Most of the columns in the VFP tables have been set not null and depend on VFP's concept of empty. So I, from within SS2005, cannot see that a field was empty and then make my own decision about whether to call it null or zero or pi or whatever.

Sorry if my original question was misunderstood.

My select won't work Why

You should understand that VFPOLEDB is not changing nor pushing crafted values but it seems hopeless. As per the date it's explicitly testable outside VFPOLEDB, just try casting '' to a datetime to see who is manufacturing in fact

select cast('' as datetime)

or:

select cast(0 as datetime)

and voila SQL server doesn't give null but a manufactured datetime value as you say. However I won't discuss further if that's VFP or IEEE or MSSQL guilty about this phenomenon. Let's just solve your case.

VFP can differ between "empty" and "blank" fields (not for all datatypes). So the ones as you see as not filled during browsing of a VFP table might have a chance to be differentiated by isblank() function. You could utilize that.

I have doubts that you worked with linked servers before.

select case dateField when '12/30/1899' then null else dateField end as dateField from [linkedserver]...table

is not the only way to query linked servers. Look at OpenQuery() in books online. You can surely use provider specific SQL. Now with a scenario I can at least show you:

-Assume you have a copy of orders table in c:\temp (free table or not doesn't matter)

-In that table many to_region fields are already not filled (not touched, IsBlank() case)

-Near the end of the table there are some shipped_on fields which are Empty() - if not assume manually set some of them to be empty

-And finally you have linked server named "MYVFPSERVER" (its properties doesn't matter as long as it's really a VFP server using VFPOLEDB). For example:

EXEC master.dbo.sp_addlinkedserver

@server = N'MYVFPSERVER',

@srvproduct=N'My VFP Source',

@provider=N'VFPOLEDB',

@datasrc=N'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data'

OK now we are set to do a linked server OpenQuery().

select * from openquery(myvfpserver,

from c:\temp\orders')

OK, now we're getting somewhere...

as for..

select cast('' as datetime)

or:

select cast(0 as datetime)

both of these do not produce the same default value that comes from the VFPOLEDB driver. They give 1/1/1900 rather than the 12/30/1899 from VFPOLEDB (why 12/30 and not 12/31 hmmm). But either way, the who, what, why doesn't really matter.

Contrary to your assumptions, I have worked with linked servers before. Only it has always been to other instances of sql server (possibly oracle once too, I can't remember). Everything worked as I expected it to and there was no need to find out about OpenQuery. I've been working with sql server since 1999 and have not seen that before. (It's been longer than that since I've had to look at FoxPro, yeah... :-) Sorry, not a fan of VFP or just plain FP for that matter.)

So now, in it's proper context, yes, your query makes sense and does work. Didn't mean to sound obtuse, just a lack of exposure...

Now I just need to write the stored proc to examine the table structure and dynamically build the query, accounting for datatypes that may be empty but not null, and we're good to go... trivial...

thanks again.

Ed