How to be back end RDBMS agnostic?

[wgcs]

This is a really big question: How do you design a VFP program so that it is easy to change what data store it uses?

I'd like to design a new program I'm working on so that it normally uses DBF's, but can be switched to use SqlServer, MSDE or MySql as a back end.

I think many may jump at the quick answer "Use views in a DBC"... but I don't think that's a complete answer:
1) What do you have to do to a view to adjust it from accessing DBF's to accessing MySQL or SqlServer?
2) How do you manage using SQLEXEC for running SQL UPDATE and INSERT commands where, I think, the syntax differs from one DBMS to another?
3) How do you deal with the different Type identifiers in CREATE TABLE commands?


Another "quick answer" many might want to say is "Use business objects"... however, that just avoids the question...: How do you design business objects that handle the above issues?

I'm sure I'll come up with more questions along this line as I work on this... thanks everyone for considering it!

 

[Mike Lewis]

Wgcs,

I think you have correctly identified the key issues here.

The question is not whether to use remote views or SQL pass-through or cursor adapters or whatever. That choice does not address the main issue.

The actual switch from, say, SQL Server to MySQL is easy. Just change the data source specified in the DSN. Or, better still, change the VFP connection object held in your DBC.

The problem is that different back ends have different data types, different SQL syntax, different error codes and different functions. This is especially pronounced if one of the options you want to keep open is to use VFP itself as the back end.

To take a simple example, if you want to get all overdue invoices, you would do this with a VFP databases:

SELECT * FROM Invoices WHERE PayDate < DATE()

but the DATE() function isn't supported in SQL Server, so you would do this instead:

SELECT * FROM Invoices WHERE PayDate < GETDATE()

I guess the only way to be sure that your application is portable would be to stick to absolutely standard SQL, and don't use any built-in functions like GETDATE(). Even then, you would have to take care with data type mapping.

This is an interesting discussion. I would be interested in hearing your own futher views and those of other forum members.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[hoosier121]

I would think to be truly agnostic of the back end you would have to design the FE to consume XML.

John

 

[wgcs]

Hi Mike,

I'm glad to find someone as interested as I am...

The difficulty with &quot;absolutely Standard SQL&quot; is that the more I learn about it, the more I see how VFP SQL differs from it... Particularly in data types.

I've found that VFP6 is somewhat tolerant on the create table function of the &quot;standard SQL Types&quot;, but all it really does is look only at the first type character:

CREATE TABLE test ( ;
  FldOne   INTEGER,       ; -> I
  FldTwo   FLOAT(5,3),    ; -> F(5,3)
  FldThree DATE,          ; -> D
  FldFour  DATETIME,      ; -> D  !!!! DATE..not compatible
  FldFive  CHAR(10),      ; -> C(10)
  FldSix   MONEY,         ; -> M  !!!! MEMO..not compatible
  FldSeven TEXT )           -> T  !!!! DATETIME..not compatible

This might make it easier, but clearly isn't transparent.


To have truly transparent access to varying DBMS's, it seems, you need a data access layer which can translate programmatic (object.method calls and object.property access/assigns) data access into SQL statements routed either over an ODBC connection (through SQLEXEC) or directly to VFP. This is exactly what ADO/DAO/RDO/OLE-DB, whatever MS calls it this week, does; and, this is exactly where I preferred VFP's data access functions (APPEND BLANK, REPLACE, RECALL, etc....).

It seems VFP8's CursorAdapter can be used for this data access layer, though it probably would be a better design to have a data access layer first, then have a CursorAdapter use IT to get to the data. (the CursorAdapter provides an interface in VFP to &quot;trap&quot; the APPEND, REPLACE, etc function calls and handle them through whatever means necessary)

Some arguments for and against the CursorAdapter (which leads me to think that it should be &quot;on top of&quot; another data access layer) are here:

http://fox.wikis.com/wc.dll?Wiki~CursorAdapterOrNot

In the discussion there, it seems that there are as many different approaches at the data layer/business objects as there are programmers... this indicates to me that we'll forever be looking for the &quot;best&quot; way...

Does anyone have experience with &quot;commercial&quot; VFP frameworks that contain a data access layer? How do they work?

 

[wgcs]

XML was very tempting when I first learned about it... however the processing overhead is a horrible drawback.

Also, however, it doesn't really answer the question: Oracle, SqlServer, VFP, MySql all serve up different kinds of XML, so you still need a layer that can translate for your program.

The statement &quot;have to use XML&quot; is too restrictive too: ODBC is a great medium for transfering data; so is OLE-DB. Either of these take data from a given back end, and present them similarly.


The real issue is the communication with the Back End: Even if Sql Server and Oracle would serve up compatible XML schemas, the SQL syntax for asking for the data differs between them (and, differs alot from VFP SQL).

So, I was thinking: Perhaps in VFP you'd need to use a &quot;generic&quot; SQL that is not specific to any of them, then use CHRTRAN()'s to convert it to the appropriate back end.... kind of like a &quot;Meta-SQL&quot;.

 

[Mike Lewis]

Wgcs,

Yes, I pretty well agree with all of that.

As for XML, it is worth considering as a possible mechanism for transfer of data, but it still does not address the issues of differences in SQL syntax. The problem is how you access the data, not so much how you store or transfer it.

Re your idea for &quot;meta-SQL&quot;. I like the sound of that. However, you are still left with the differences caused by different functions and the like. It is not the lack of standard in SQL that is the problem, but the wide variety of supporting language elements, like built-in functions and the like.

Mike

Mike




Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[wgcs]

Is there a good SQL comparison reference anywhere, that shows what functions are equivalent to each DBMS?

If not yet, it might be good to build one on the fox wiki.
(see

http://fox.wikis.com/wc.dll?Wiki~SQL

)

( I think one of the most indespensible server-side functions is a case-insensitive string comparator:

in VFP: SELECT * FROM mytable WHERE UPPER(name)='JOHN'
in MySql, Oracle, or SqlServer, how? is Upper() a standard function? Why do I keep seeing string comparisons using LIKE in MySql docs? (ie: SELECT * FROM mytable WHERE name LIKE &quot;JOHN&quot; ) It seems that VFP treats the LIKE operator (not the LIKE function) as &quot;==&quot;

 

[Mike Lewis]

Wgcs,

I don't know of any such comparison. When I was working on my first SQL Server app, I had to gather that sort of information painstakingly and by trial and error.

By the way, if I'm not mistaken, you are a member of MSDEVAPPS on Compuerve (although I haven't seen you there for a while)? If you posted your original question there, I'd bet you'd get some interesting replies from the likes of Andy Kramek and Christof Lange.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[wgcs]

I may be a member of MSDEVAPPS, but when I try to log in to compuserve forums, I'm given the message:
&quot;This area can not be accessed by your account.&quot;

I may be trying to log into the wrong part of compuserve, ( forums.compuserve.com ) but I can't find a better address...

 

[DSummZZZ]

wgcs,

You have a good idea, but something you're thinking or at least hoping for, is that the current 'standards' are going to stay in place somewhat. The way this industry moves, the standards or at least commonalities we are using or are used to, will change in the next few years, making anything we come up with now outdated.

An all-encompassing data connection of some sort is a nice idea but a futile effort. Granted, there are a lot of current data platforms that syntax could be developed for to somewhat consolidate a language set, but there are many more that are somewhat if not totally proprietary that people are still going to want or have to use either for personal or business reasons, which are beyond the reach of the long arm of Microsoft.

Chances are, you would have to enlist the help of hundreds to write an ODBC driver (of sorts) for all of these different platforms in order to have a generic data socket.

It makes my head hurt.



-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[wgcs]

However, VFP is very stable in being backward compatible with itself; SqlServer, MySql & Oracle should similarly try to continue to support their own older SQL standards.

You're right that an all-encompasing ODBC driver would be very difficult (impossible) to write...

I'm looking for a &quot;lowest common denominator&quot; VFP connector that supports the basic necessities: INSERT, UPDATE, (simple) SELECT, DELETE, CREATE TABLE.

I really only care about VFP, MySql and maybe MSDE/SqlServer.

 

[Mike Lewis]

Wgcs,

This is the link I use to access MSDEVAPPS:

http://forums.compuserve.com/vlforums/default.asp?SRV=MSDevApps&loc=us

In general, though, accessing those forums is often a headache. They seem to put every obstacle in your way. Thank goodness for Tek-Tips.

MIke


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[wgcs]

That's how I feel about most forums I've tried: ExpertsExchange, Compuserve, UniversalThread, particularly. The management at Tek-Tips really does a good job and has a very good interface.

Thanks for the link: That one works!

 

[Mike Lewis]

Wgcs,

Well, don't give up with MSDEVAPPS completely. There are some interesting discussions there. But, in general, Tek-Tips is so much easier.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[wgcs]

I found out why I've had such trouble logging in: With your URL, I could log in and read the forums, but I'm only a &quot;guest&quot;: I'm not a &quot;member&quot; (AOL/CompuServe customer), so I can't post questions... Though I can post replys. I most likely first signed up to post a reply to a question I found through Google when looking for an answer to my own questions....

Feel free to post the question yourself to the MSDevApps forum

(Just today I notices some very well known VFP names answering questions there, so I'm sure you'd get some very interesting (and practical) answers...)

 

[Mike Lewis]

Wgcs,

I guess you have to be a Compuserve member to get full access to the forum. I've been a member for about 12 years --- long before it was available on the Internet. Even so, I found it very difficult to access the forum the first time I tried going via the Internet.

I'll maybe post your question there myself when I get a moment. If I do, I'll let you know so that you can join in the discussion if you wish.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[wgcs]

So far, I have this as the significant method of my Data Access Layer:

PROCEDURE SqlExec
LPARAMETERS pcSQL, pcaDest, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19, P20
DO CASE
  CASE upper(alltrim(pcSQL))='SELECT' AND TYPE('pcaDest[1]')<>'U'
    * Into Array
    &pcSQL INTO ARRAY pcaDest
    RETURN _TALLY
  CASE upper(alltrim(pcSQL))='SELECT' AND VARTYPE(pcaDest)='C'
    * Into Cursor
    &pcSQL INTO CURSOR &pcaDest
    RETURN _TALLY
  CASE upper(alltrim(pcSQL))='UPDATE'
    &pcSQL
    RETURN _TALLY
  CASE upper(alltrim(pcSQL))='INSERT'
    &pcSQL
    RETURN _TALLY
  CASE upper(alltrim(pcSQL))='DELETE'
    &pcSQL
    RETURN _TALLY
  CASE upper(alltrim(pcSQL))='CREATE'
    &pcSQL
    RETURN _TALLY
  OTHERWISE
    &pcSQL
    RETURN _TALLY
ENDCASE

It's used like this:

LOCAL loDta, laResult[1], lnKey, lnRows
lnKey = 5 && this is a local variable that represents the primary key... 
* lnKey will be out of scope inside the SqlExec function, so we pass it as a parameter

loDta = CREATE('clsDataAccess')
lnRows = loDta.SqlExec('SELECT * FROM dataTable WHERE PrimaryKeyField=?P1', @laResult, lnKey)

Or, if you prefer a cursor to work with:

lnRows = loDta.SqlExec('SELECT * FROM dataTable WHERE PrimaryKeyField=?P1', 'CursorName', lnKey)

I plan on designing all queries sent to my clsDataAccess.SqlExec in a generic way that can be easily muted into the specific data types/etc for each RDBMS. The Mutation of the expression will be handled by clsDataAccess.SqlExec

 

[craigsboyd]

wgcs, Mike, and Dave,

Interesting discussion for sure. I think there is a point at which you will receive diminishing returns on your coding investment, so I liked what wgcs posted about a &quot;lowest common denominator&quot; VFP connector

As I roll the idea of a black-box data adapter around in my head, I can see many possibilities and hurdles. Just a few thoughts for the moment...

XML should be either ruled in or ruled out...even though it is a data transfer element it is a very important element when talking n-tier design (imho). Layering it on later can prove difficult and the benefits of XML far outwiegh the limitations when creating a semi-universal adapter. Performance hits can be minimized with a decent parser and compression. It certainly broadens the possibilities for front-ends, among other things. Perhaps this doesn't apply, and we are only talking about VFP as the front-end.

Staying completely away from native functions and certain datatypes is too big a sacrifice. Better in my mind to have access to at least some of them. Some type of SQL string interpreter class may serve, keeping in mind the &quot;least common denominator&quot; scope statement made by wgcs.

If the front-end is to be VFP then I can't help but think that remote views are the way to go for the most part. Can't think of anything else that would give you more bang for the buck...

...wgcs, this is an interesting thread and one I will be coming back to. Takes awhile sometimes for concepts and ideas to gel right in my head.


Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[wgcs]

Slighthaze, Glad to have you join!

It certainly broadens the possibilities for front-ends, among other things. Perhaps this doesn't apply, and we are only talking about VFP as the front-end.

In my mind, I'm thinking entirely about a VFP front end: If the back end is Sql Server or MySql, the VFP front end will (given a good, admin, connection) build and manage the entire database programmatically. (Our customer base doesn't have the resources to have a DB administrator or anyone else touch the back end, beyond installing and following a manual to set up the login ID).

XML should be either ruled in or ruled out... ... very important element when talking n-tier design (imho).

I don't really see XML as an issue... it all depends on what the 'back end' is. If the Back end is the local VFP DBFs, then are you suggesting converting the data to XML then back to create an artificial middle tier? If the back end were MySql, can it even generate XML without extensive SP's?


Presently, I'm going with a 3-tier design approach, with:
UI: VFP on Local machine
DataAccess: VFP classes on Local Machine
Data Store: either VFP DBFs on Local Machine, or MySql, or MSDE, or Sql Server

The DataAccess Classes are responsible for switching between DBFs, MySql, etc.

And it took me a bit to recognize that it's 3 tier, since the first two (UI and MiddleTier) are both always on the local machine.

Potentially, it could be made into a web app, but then it immediately becomes 4 tier:
Thin Client: In local browser
UI: HTML Generated by VFP COM or ASP active server code
Data Access: VFP COM classes
Data Store: Whatever the VFP COM classes are told to use.