Print Page | Close Window

DevForce with Using VFPOLEDB

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1233
Printed Date: 23-Apr-2025 at 9:07pm


Topic: DevForce with Using VFPOLEDB
Posted By: BrooksAdair
Subject: DevForce with Using VFPOLEDB
Date Posted: 24-Apr-2009 at 7:53am
I have a problem I don't expect most DevForce users have had to deal with. I am just looking for any information I can get that can help me get closer to solving it.

I am accessing Visual FoxPro data through DevForce. I have been using Sybase's Advantage Database Local Server (ADS) OLEDB driver which works fine with DevForce and accesses VFP data. However, ADS will cost money for our customers and I need a free alternative for the smaller clients. So I have attempted to work with Microsoft's VFPOLEDB driver.  Currently, with VFPOLEDB, all the correct records are queried properly. The problem is any field which I have renamed in the Object Mapper comes over empty. For example: I have Addr1 and City in the table. I rename Addr1 to Address1 and leave City as is. City is populated with the correct data, but Addr1 comes over blank. If I change the field in the Entity back to Addr1 it then is populated. Of course if I switch back to the ADS driver everything works fine. Any ideas as to where I can start looking to figure out what is wrong with VFPOLEDB?

Some additinal information. VFPOLEDB is known as not being 100% SQL92 compatable. I am using a sub class of DataSourceMappingInterceptor and OleDbProviderHelper written by a developer named Mark Miller to work around the VFPOLEDB problems. These classes do the following:

DataSourceMappingInterceptor.UpdateSourceMapping takes the passed pEntityTable and sets pEntityTable.TableMappingInfo.SourceOwnerName=null;

OleDbProviderHelper.IdentifierPrefix.get returns string.Empty

OleDbProviderHelper.IdentifierSuffix.get returns string.Empty

With out the new Helper and Interceptor the queries would not work at all from VFPOLEDB. I figure I might need to override some other properties on DataSourceMappingInterceptor or OleDbProviderHelper but I am not sure which ones.

I have looked at DebugLog.xml and the queries are going to the database with the proper, short field names. So, I would expect that the VFPOLEDB driver would not even know what the field names are in the Entity.  Obviously there is something going on that I don't understand.

Any advice?


Replies:
Posted By: BrooksAdair
Date Posted: 05-May-2009 at 11:30am
I have done some more testing. Apparently the problem is caused by changing OLEDB providers on an existing data source key. I created a new data source key using VFPOLEDB that mapped to the same database. Then created another Entity that mapped to the same table. With that entity I could rename properties in the mapping tool and it ran fine. If I then switch that data source key to the ADS OLEDB provider I get the same problem.

I have also noticed that if I change OLEDB providers on an existing key the mapping tool automaticly clears out all changes to property names. Is this normal? 

In the real world I will need to be able to use both these OLEDB providers by just changeing the connection string. What about changeing an OLEDB provider would cause the objects to have to be remaped in the Object Mapping Tool?

Posted By: kimj
Date Posted: 06-May-2009 at 12:24pm
Sorry, I don't understand whether this property name clearing is occurring at design time in the Object Mapper, or at run time.
 
Clearing out property names in the Object Mapper when you change the connection string for a key isn't normal, but it could occur if the Schema Update Options are set to validate against the data source and the table (or what the provider says about the table) differs from the schema previously retrieved.
 
If you have a single data source key defined via the Object Mapper, at run time you should be able to accomplish what you need either via data source key extensions or a custom DataSourceKeyResolver. 
 
Which version of DevForce Classic are you on?  We gave Mark a "one-off" to support his FoxPro efforts back in 2007, but have made a number of modifications since then. 
 
I think we will need to see your code to better understand what's happening here.  If possible, please send support a zipped example, or as many supporting files as needed to help understand your use case.
 

Posted By: BrooksAdair
Date Posted: 07-May-2009 at 2:09pm
We are using DevForce Classic Professional 3.6.3.1.

I am having problems with both the design time Object Mapper and run time. If I change the data provider in the Object Mapper, all the customizations I have made to the property names are lost. Even primary keys that I flag manually are reset.

If I leave the connection string alone in the Object Mapper but change the connection string in IdeaBlade.ibconfig to use a the other provider, I have problems at run time. Any property that has a different name from the field name in the database returns blank. So I have a field Addr1 that I renamed to Address1, it returns blank. But City was unchanged so it returned the proper city.

I am not sure what code to send as any code I have works propery if I use the data provider I maped the object with. I think though its probably what you said about diffrences from the schema. Each provider probably describes the tables slightly diffrent.

I am going to try changing my test Entity's provider, remap it, and compare the DataRow class and orm file to see what is diffrent. Any ideas as to what else I can try?

Posted By: kimj
Date Posted: 07-May-2009 at 2:24pm
Comparing the generated entity definitions should be a good place to start.
 
Ideally you should be able to define your model once, and at run time choose the provider.  It might be that your DataSourceMappingInterceptor and custom OleDBProviderHelper need the appropriate logic to handle the differences in the providers.  I'd take a look at those next.  Are you also using a custom DataSourceKeyResolver?
 
 

Posted By: BrooksAdair
Date Posted: 08-May-2009 at 7:44am
Ok, I have compared the ORM file. The differences are the propertyID changes with each data provider. Also, VFPOLEDB has allowNull=true, and sourceAllowNull=true where ADS has false for both properties. (The actual tables do not allow VFP null). rdbColumn and DisplayName are both lower case for VFPOLEDB and upper for ADS.

I would think changing from nullable to non nullable should not cause the Object Mapper to loose any settings. After all, with out changing providers, the nullablity of a field could be changed on the actual database.  Is the ORM tool case sensitive on the database field names?

The only diffrence in the DataRow is the new EntityColumn() call varies the value of the IsNullable paremeter and GetColumnValue() calls also vary the AllowNull parameter.

Any Ideas?

Posted By: kimj
Date Posted: 08-May-2009 at 10:47am
It's not the nullability which is causing the problem in the Object Mapper, it's the different cases of the column names as given by the two providers.  When the Schema Update Options say to validate and update, the property validation is actually case-dependent.  When you switch providers in OM, the validation is effectively replacing the entity definitions and your prior changes are lost.  You can turn validation off from the Options menu.
 
You really should define your model once only, though, using whichever provider you feel is the most accurate.
 
Once you have a model with all your desired changes, you can then swap providers at run time.   You mentioned that you also had difficulties here.  This is where we need to take a look at your code for any custom DataSourceMappingInterceptor, OleDBProviderHelper and DataSourceKeyResolver implementations to see what's happening.

Posted By: BrooksAdair
Date Posted: 08-May-2009 at 11:57am
I figured out what the difference in the code is. (My compair program ignored case diffrences.) In the DataTable.CreateTableMappingInfo() method columnMappings.Add() is called for each field. Apparently the case is important if the source column and data set column are different. I found that the fields that were not displaying at run time would be fine if I changed the case of the source column.  The problem is that the DataTable is in an auto generated code file.

Is there a way I can intercept the TableMappingInfo and change the case of the source column at run time? It looks like the Table class in the generated code file is the final version. Is there another way besides subclassing?

Posted By: kimj
Date Posted: 08-May-2009 at 2:29pm
Yes, this is where your custom DataSourceMappingInterceptor comes in.  You've said you've already implemented the UpateSourceMapping method to set the SourceOwnerName, but you probably need to add some additional logic to ensure it's doing the right thing for the given provider.  The data source key is passed into this method, so you can use that to determine which key and/or provider is in effect.

Posted By: BrooksAdair
Date Posted: 11-May-2009 at 7:57am
Thanks. I added code to UpdateSourceMapping to change the case and everything works now. If I had implemented the DataSourceMappingInterceptor my self I would have automatically thought to put code there. Since I was using Mark's code it had not dawned on me exactly what all I could do with that.

Well, I now have DevForce working with VFP data using both Advantage Database Server and VFPOLEDB. If any one else gets stuck having to do this and are having problems, contact me.


Print Page | Close Window