EasySQL4Fox

Tools to quickly adapt a FoxPro application to working with SQL Server (MySQL, ORACLE)

FoxPro to NET migration     FoxPro to NET pre-conversion     FoxPro to NET rewriting     VFP to NET migration   VFP to NET pre-conversion   VFP to NET rewriting

Send message to developer ...

DBC+DBF or SQL Server? "Pros" and "Cons". Selection criteria.

Everything started when Visual Foxpro got means of working with external databases with the help of client-server tecnology. Many developers as well as their customers found it promising to store data in databases of SQL-Server, Oracle, MySQL and MS Access.

As Microsoft -naturally - promotes it very own SQL Server by creating means for binding VFP with this product, let's stick to the point and have a look at their promotion tactics.

For many years we've heard many opinions on Microsoft policy which can sacrifice whole products (not the worst ones) to the altar of their strategic plans. It's evident, that FoxPro is actually a considerable obstacle to worldwide SQL Server victory as well as (and this is much worse) NET-technologies. Microsoft may work really hard on developing ADO.NET, but if we give just a quick look to both products - and compare them - we'll clearly see that it will take Microsoft about a hundred years to introduce VFP functionality to Visual Studio.NET.

Still, let's be unprejudiced in discussing products. Our task is to study them and find their combinations optimal for certain tasks.

Well, does FoxPro need SQL Server? If it does, how much does it need it?

1. When you won't need SQL Server

We wouldn't recommend to migrate to SQL Server if the size of your largest table is less than 1 gigabyte. The nominal productivity of the local network is 100Mb/s, the actual productivity is about 50Mb/s.

Here you should first evaluate available resources and plan development costs.

Consider typical amount of memory on your computers; try and analyze how much memory is needed for typical quieries of your programs. Just examine how much memory is used when the quiery is being carried out. You may monitor this via Windows task manager. If it takes too much time to perfom a quiery, try adding some (temporary) memory to your computer. The fact is FoxPro optimization heavily depends on the amount of memory. This results in the speed of quiery, which may vary greatly. In other words, if you use up all available memory, you'll end up with a slowest application, as optimization becomes unavailable.

It is also useful to analyze quiery optimization with the help of Sys(3054) and Sys(3092) functions.

So, we can say that on one computer (or in fast network) VFP data access is faster than the one of SQL Server. The reason for this is simpler technologies of memory allocation, caching and data access, as well as absence of ODBC costs.

If the stumbling block is network productivity, try introducing mandatory buffering with the help of local cursors. Do it everywhere. For example, if your list of clients contains about 100 000 records and you reach it from GRID (or BROWSE) when you work with Documents table, it might be a wise idea to create a local cursor containing indexes pointing to keys and check this cursor for the needed values before you address the source. When you quiery the source table, save quiety results to the local cursor. Try avoiding LOCATE or SEEK commands; use SET RELATION only. Surely, we're not talking of ALL the rules needed to ensure correct operations of RUSHMORE technology.

Practice shows that slowness of FoxPro in many cases depends on the fact that the developer doesn't know (or doesn't stick to) the rules of successful implementation of RUSHMORE technology and thus blackens the good name of this product.

If you have to work with several remote locations (e.g. using modems), you still don't have to migrate to SQL Server. It would be easier to make separate applications for these locations using a simple FoxPro-based Automation server, than to update all the code of your application to SQL Server syntax.

2. When you will have to use SQL Server or Oracle

Some of your tables exceed the limit of 2 Gb or may exceed this limit in future, as FoxPro table size is (deliberately) limited to 2Gb. On the other hand, if table size is about (but less than) 2Gb and there are a substantial number of simultaneous sessions, FoxPro productivity becomes inferior to that of SQL Server. This is the case when you really need some more complicated technologies.

If your network is slow or most operations are carried out at remote locations, you'll have to switch to Client-Server technology. In this case we recommend that you should not waste your time and forget about FoxPro Automation and DBF. Such technology can be realized best with SQL Server or Oracle. Practice shows that only access to COM-objects (Automation servers) is slower than ODBC, let alone SQL Server productivity when it comes to large data amounts.

Also, if you make distributed products, SQL Server compatibility will be one of the first evaluation criteria.

Another plus for SQL Server is its reliability (especially with indexes).

3. When FoxPro and SQL Server need THE THIRD PARTY - ADO.NET

This situation is uncommon, but quite possible.

Most FoxPro developers who start working with SQL server after they have worked with FoxPro call it "too stupid database" or say that "SQL Server - it is just SELECT, INSERT, UPDATE, DELETE". As a rule, people say this when trying to create stored procedures with T-SQL or PL-SQL(Oracle).

The problem does exist, and the problem is serious. It is simply too difficult to write a complicated T-SQL procedure that would do what a FoxPro program does. We do agree with the opinion that T-SQL and PL-SQL are tools of torturing the developer, and we really have our reasons. It goes without saying that T-SQL and PL-SQL support complicated functionality, but such procedures will cost you much effort (and health) both at design time and runtime. So our advice is: don't waste your time. Create a COM (automation) server in VFP, place it on your SQL server and write procedures which will select data from SQL Server databases, process the query results with the help of previously written FoxPro codes; then send the result to the client. Writing the result into SQL Server temporary databases - as you cannot switch transactions off - will also kill your application. Using FoxPro for compicated - as well as any - algorithms has one more advantage: if you use T-SQL inside SQL Server to access and modify data, you will have to select data into internal cursors. It's much easier to select the data into FoxPro cursors, as we cannot doubt FoxPro abilities when used as a remote server in "Client-Server" technology.

We've seen many websites created with FoxPro only, where FoxPro is used as SQL Server (data access) and ASP (HTML generator). But that's where 2Gb limitation comes into view.

For example, if the size of your database is ~ 70 Gb (and this will happen if your application works with really large numbers of customers and monitors every transaction), and you select a large amount of data into a FoxPro cursor, the size of the resulting cursor may exceed 2Gb and your application will happily crash. One may think of using several (dozens) of FoxPro cursors, but we firmly believe that in this case it is better to use ADO.NET instead of FoxPro automation server,as actually, when FoxPro works together with SQL Server  as an automation server, it does the job of ADO.NET.

Problems you will face if you choose SQL Server and don't want to rewrite your whole application.

So you have decided to switch to SQL Server. It might be a wise idea to look for some solutions which will help introduce needed functionality into your FoxPro application. Take your time - we've already done it for you, and here's the result of our research:

Most solutions will need a completely rewritten code - it means, you will still have to rewrite thousands of lines. Mere Mortals Framework and CodeMine Framework are good products indeed, but they give you basic means you'll have to build your application on.

What is there to be done if you have already built your application and don't want to change its basics? Can we adapt a FoxPro application so that it could easily work with data stored in SQL Server databases ?

Practice shows that we surely can. We should only take into consideration the following facts:

You will hardly want to switch from FoxPro tables to SQL so that your application would lose the ability of working with FoxPro tables and become able to work with SQL databases. Even if you plan to abandon FoxPro tables, the whole process will take much time and you will have to plan several stages of carrying it out; what's more important, you'll have to successfully implement these stages. Surely you can work with two parallel applications: one of them may be a FoxPro application working with FoxPro DBF, and a copy of this application working with SQL Server. But is it really the right way to develop and support two similar applications at the same time? We think it would be more appropriate to grant the existing application new functionality without influencing the existing one. Our goal was to ensure that the same application can work with data contained in ANY tables and databases (FoxPro tables, SQL Server, Oracle) and have direct access to DBF, ODBC, Automation, ADO, etc. In other words, such application can work with a set of objects and functions regardless of data types and transport types. The same application can work with the central SQL Server database in the central office of a company and with FoxPro tables (presented as materialized views containing sets of region-related data) in the regional offices of the same company. The product we describe here is actually built up from several basic elements of development framework. Its goal is to provide the developer with means of adapting an existing application to working with data in FoxPro tables as well as in SQL Server database.

Troubles which you will face when trying to adapt your FoxPro application to SQL Server can roughly be divided into 2 groups:

 

Transforming syntax of SQL statements

FoxPro syntax is much more free than allowed by SQL standard. Most codes written in VFP versions up to 7.0 will surely contain truncated words, e.g. SELE ... FROM ... WHER ... . The order of statements in FoxPro is also free while it is strict in SQL.

Besides, using functions in statements is quite common with VFP programmers. Many functions do have their T-SQL eqiuivalent, but names and function syntax may vary greatly.

Also, you cannot perform all manipulations with FoxPro variables in statements by transmitting their values to ODBC with "?" prefix. Code texts may contain static links to current cursor or tabble fields, links to various object properties and even FoxPro-specific expressions.

Before we start examining means offered for syntax conversion, we should stress that developers use two ways of syntax conversion: runtime syntax conversion and design time syntax conversion. In both cases one may prefer using EasySQL4Fox functions to any other conversion means.

EasySQL4Fox (EFoxSQL.FLL library) and its functions

Main functions of converting FoxPro syntax to T-SQL syntax are:

The library is included into the project by the following command: SET LIBRARY TO ... EFOXSQL.FLL ADDITIVE

E_SQL_SELECT_CONVERT() function

Syntax:

T-SQL syntax statement = E_SQL_SELECT_CONVERT(FoxPro syntax statement)

This function does the following:

1) Finishes keywords in query clauses if they are truncated (up to 4 characters):

Initially, FoxPro allowed using any keywords and function names truncated to 4 characters. VFP versions starting with 7 feature default syntax check which tries to finish the clauses when they are being written, but truncated clauses are still OK. Many of your statements might have been written in earlier versions of FoxPro. So, E_SQL_SELECT_CONVERT function finishes keywords, and if your expression looked like:

    SELE DIST ... FROM ... GROU BY ...

when processed by the function, it will look like:

    SELECT DISTINCT ... FROM ... GROUP BY ...

2) Field names which coincide with T-SQL reserved words are enclosed in square brackets: 

In T-SQL there is a list of reserved words which can often coincide with FoxPro keywords. Unlike FoxPro which prefers to differentiate field names and keywords, T-SQL needs these words to be enclosed into square brackets. So, if you use such words as USER or TABLE without square brackets,  SQLExec() will return an error.

E_SQL_SELECT_CONVERT function searches for such field names and encloses them into square brackets:

    SELECT USER,SUM,ORDER,NAME ...

And here's how it looks when processed by the function:

    SELECT [USER],SUM,ORDER,NAME ...

 

3) SELECT, INTO, FROM, WHERE, GROUP BY, HAVING and ORDER BY clauses which aren't strictly placed in FoxPro statements are organized in fixed SQL order

One can often see such clause order in FoxPro:

Select a.F1,b.F2, ... From table1 a Join table2 b On a.FF=b.FF Order By 1 Group By a.F1
Select a.F1,b.F2, ... Order By 1 Group By a.F1 From table1 a Join table2 b On a.FF=b.FF
Select a.F1,b.F2, ... Order By 1 From table1 a Join table2 b On a.FF=b.FF Group By a.F1

Well, have you ever thought of keeping the order of these clauses strict?  E_SQL_SELECT_CONVERT function converts such clauses order to:

   SELECT A.F1,B.F2, ... FROM TABLE1 JOIN TABLE2 ON A.FF=B.FF GROUP BY A.F1 ORDER BY 1

Three ways of application described above have actually made EasySQL4Fox popular with developers. This allows to avoid creating special code blocks for T-SQL statements. Still, this is a minor part of what EasySQL4Fox can do.

4) FORCE option in FROM clause is moved to the OPTION() section of the statement

In FoxPro, FORCE clause after FROM clause allows to tell the optimizer that JOINs should be executed in the order they are specified in the statement. Otherwise, the optimizer will use its own principles in selecting the order of JOIN. In T-SQL, this directive is specified at the end of the expression with OPTION(FORCE ORDER) syntax.

5) In FoxPro, you can specify grouping criteria of GROUP BY clause using numbers of fields in the result of the query. T-SQL doesn't understand this syntax. So E_SQL_SELECT_CONVERT() changes numbers of fields into their expressions.

For example, the following statement:

    Select F1,F2+F3 as F4,F5,Sum(F6) From table Group By 1,2,3

will be translated to

    SELECT F1,F2+F3 AS F4,F5,SUM(F6) FROM table GROUP BY F1,F2+F3,F5

6) If GROUP BY is specified in the statement, a Transact-SQL SELECT clause cannot address fields which are neither returned by aggregate functions (SUM(),MIN(),MAX(),...) nor included into GROUP BY clause.

The same problem exists in FoxPro starting with 7.0 version. Usually, it is solved by SET ENGINEBEHAVIOR 70 command

If there's a GROUP BY clause in the statement, E_SQL_SELECT_CONVERT() function will call MAX() function for every field which is not specified in GROUP BY clause and doesn't result from an aggregate function (SUM,MIN,MAX,...). Note: you have to fix logical fields manually, as MAX() function cannot apply to bit-type data. If grouping by this field isn't important, refer to a constant of CAST(0 as bit) type instead of referring to the field. Otherwise you'll have to transform this field type to numeric and include it into GROUP BY clause.

7) In T-SQL you cannot refer to fields from GROUP clause by their alias

All alias references to fields in GROUP BY clause will be chaged to field expressions. E.g:

    Select F1,F2+F3 as F4,F5,Sum(F6) From Table Group By F1,F4,F5

will be changed to

    SELECT F1,F2+F3 AS F4,F5,SUM(F6) FROM Table GROUP BY F1,F2+F3,F5

8) In T-SQL you cannot refer to fields from HAVING clause by their alias

All alias references to fields in HAVING clause to field expressions. E.g.:

    Select F1,F2+F3 as F4,F5,Sum(F6) From Table Group By F1,F4,F5 Having F4>0

will be changed to

    SELECT F1,F2+F3 AS F4,F5,SUM(F6) FROM Table GROUP BY F1,F2+F3,F5 HAVING F2+F3>0

9) If AND, OR and NULL keywords are written with dots (.AND. , .OR. , ...), these dots will be removed.

10) By default, SQL Server uses READ COMMITED TRANSACTION ISOLATION LEVEL. It means that any statements will block records. FoxPro does it in a different way.

If statements block records, this will result in DEAD LOCK error if many users are working simultaneously and somebody tries to modify records. By default, FoxPro doesn't do this. To cancel record blocking, declare the following variable in FoxPro before you call E_SQL_SELECT_CONVERT() function:

    SETLOCSYS_SQL_Server_Select_NoLock     = .T.

If the function sees this variable and its value is set to .T., it will add WITH(NOLOCK) directive after every reference to a table in FROM clause. In this case, the statement execution won't block records in tables.

We do not recommend changing READ COMMITED TRANSACTION ISOLATION LEVEL in SQL-Server. This is likely to cause data loss!

11) In T-SQL you cannot refer to logical values (bit type) in conditions without equals sign.

In FoxPro, you can simply refer to the logical field F1 in filtering condition (although it is not recommended if you want to optimize your code)

    SELECT * FROM tbl WHERE F1 and F2>3

T-SQL will not understand this syntax. E_SQL_SELECT_CONVERT() function will find these situations in SELECT and WHERE clauses and supply it with equals sign

    SELECT * FROM TBL WHERE F1=1 AND F2>3

12) The following FoxPro functions are replaced with their T-SQL equivalents:

ALLTRIM() ; SUBSTR() ; IIF() ; AT() ; MINUTE() ; SEC() ; DTOC() ; STRTRAN() ;

ALLTRIM() function will be changed to: LTRIM(RTRIM())

SUBSTR() function will be changed to: SUBSTRING()

IIF() function will be changed to CASE construction, e.g: 

SELECT *,IIF(F1>F2,F3,F4) AS F5 FROM tbl

will be translated to

SELECT *,CASE WHEN F1>F2 THEN F3 ELSE F4 END AS F5 FROM TBL

AT() function will be changed to: CHARINDEX()

VAL() function will be changed to: CONVERT(numeric(10),expr). e.g: 

Select F1,Val(F2) as F2 From tbl

will be translated to

SELECT F1,CONVERT(NUMERIC(10),F2) AS F2

NVL() function will be changed to: ISNULL()

HOUR function will be changed to: DATEPART(hour, var). E.g: 

Select F1,HOUR(F2) as F2 From tbl

will be translated to

SELECT F1,DATEPART(HOUR,F2) AS F2

MINUTE() function will be changed to: DATEPART(minute,expr)

SEC() function will be changed to: DATEPART(second,expr)

DTOC() function will be changed to: CONVERT(char(10),expr,102)

!!! To change 102 date style to any other (see T-SQL documentation), declare ___EFOXSQL_Set_DToC_Style variable and assign the code of the needed style to it (e.g. 103) before you call E_SQL_SELECT_CONVERT() function.

STRTRAN() function will be changed to: REPLACE()

!!! Only first three paremeters are allowed!

PADR() and PADL() functions will be changed to dbo.PADR() and dbo.PADL()

It is supposed that PADL and PADR functions are defined in the queried database. To check whether this functions exist and declare them use this code at the beginning of a FoxPro program:

LOCAL m.__Mac__,     m._Res
m.__Mac__ = [SELECT name FROM dbo.sysobjects WHERE name = 'PADL' AND type = 'FN']
m._Res = SQLExec (m.Connect,m.__Mac__,'_flist')
IF m._Res <= 0
    = SQL_Error(m.__Mac__,PROGRAM(),LINENO())
   RETURN .F.
ENDIF
Select _flist
IF RecCount () = 0
   USE
    m.__Mac__ =;
        [Create Function dbo.PADL (@_String char(255),@_Len int)] + CHR(10) + CHR(13) + ;
        [    Returns char(255)] + CHR(10) + CHR(13) + ;
        [AS] + CHR(10) + CHR(13) + ;
        [BEGIN] + CHR(10) + CHR(13) + ;
        [    Set @_String     = LTRIM(RTRIM(@_String))] + CHR(10) + CHR(13) + ;
        [    RETURN SPACE(@_Len-LEN(@_String)) + @_String] + CHR(10) + CHR(13) + ;
        [END]
    m._Res = SQLExec (m.Connect,m.__Mac__)
   IF m._Res <= 0
        = SQL_Error(m.__Mac__,PROGRAM(),LINENO())
      RETURN .F.
   ENDIF
ELSE
   USE
ENDIF
m.__Mac__ = [SELECT name FROM dbo.sysobjects WHERE name = 'PADR' AND type = 'FN']
m._Res = SQLExec (m.Connect,m.__Mac__,'_flist')
IF m._Res <= 0
    = SQL_Error(m.__Mac__,PROGRAM(),LINENO())
   RETURN .F.
ENDIF
Select _flist
IF RecCount () = 0
   USE
    m.__Mac__     = ;
        [Create Function dbo.PADR (@_String char(255),@_Len int)] + CHR(10) + CHR(13) +;
        [    Returns char(255)] + CHR(10) + CHR(13) +;
        [AS] + CHR(10) + CHR(13) +;
        [BEGIN] + CHR(10) + CHR(13) +;
        [    Set @_String     = LTRIM(RTRIM(@_String))] + CHR(10) + CHR(13) +;
        [    RETURN @_String + SPACE(@_Len-LEN(@_String))] + CHR(10) + CHR(13) +;
        [END]
    m._Res                  = SQLExec (m.Connect,m.__Mac__)
   IF m._Res <= 0
        = SQL_Error(m.__Mac__,PROGRAM(),LINENO())
      RETURN .F.
   ENDIF
ELSE
   USE
ENDIF

Where:

 

E_SQL_FIND_VARIABLES() function

Syntax:

Statement  = E_SQL_FIND_VARIABLES(cStatement,lAsValues)

It has already been mentioned above that this function translates references to FoxPro variables and expressions in SQL statements at runtime. In almost all cases, you can transfer values of variables, object properties and even constant expressions to ODBC with the help of "?" prefix, for example:

... Where a.F1 = ?m.V1 or a.F1=?V2    && variable references

... Where a.F1=?Object.Container.Property    && object property reference

... Where a.F1<=?(m.V1+1) and a.F2>=?(m.V1-1)    && constant expression reference (Table fields are not included into these espressions. These expressions can be calculated before the statement is executed)

In most cases, this syntax allows transferring values for an ODBC query, so you won't need E_SQL_FIND_VARIABLES() function. Still, there are two cases when this approach just won't work:

1) The statement selects the data to a temporary table in TEMPDB database, for example:

    =SQLExec(m.Connect,[Select * Into #ttt From table a Where a.F1=?m.V1])

will be executed but you will fail to find the result of the execution, namely #ttt table. We think this results from temporary data session change while ODBC receives FoxPro variables. As all temporary tables exist for the session they were created in, they are lost when the session is changed.

2) Data access technology doesn't use ODBC. For example, the query string is passed to a NET-application on the server which works with the server via ADO.NET

E_SQL_FIND_VARIABLES() function allows transforming references to variables into their values. To ensure correct operation of the function, you should change "?" prefix to "~" prefix. If you omit the second parameter or just pass .F., the function will simply change  "~" to "?". When .T. is passed as the second parameter, all references to variables, properties or expressions with "~" prefix will be changed to their values. For example:

m.F1 = {^2004-01-24}

m.F2 = 12345.6789

SELECT * FROM tbl WHERE F1=~m.F1 AND F2=~m.F2 Into #temporary_table_name
will be translated to
SELECT * FROM tbl WHERE F1='2004-01-24' AND F2=12345.6789 Into #temporary_table_name

Calculated fields of bit type will have numeric result in T-SQL by default. It is necessary to specify bit type for them. For example, if your query looks like:

SELECT nF1,lF2 ... and lF2 is of BIT type, you'll face no problems.

But, if you write the following query:

Select F1,~m.True as F2 ...

where m.True - is a FoxPro logical variable, the resulting column will be of N(1) type, as SQL will treat constant 1 as  numeric type. E_SQL_SELECT_CONVERT() function will detect these cases and apply CAST T-SQL function to convert types:

Select F1, cast (1 as bit) as F2 ...

 

E_SQL_REMOVE_DIRECTIVES() function

If syntax is translated at runtime, you will need this function to execute queries in FoxPro with DBF data access:

m._Cmd = [Sele * From table Where F1=?m.V1 and F2=?m.V2]
IF m._SQL && data access selection
    m._Cmd = E_SQL_Select_Convert(m._Cmd)
    IF SQLEXEC(m.Connect,m._Cmd,'cursor') <= 0
   * Error handling
    ENDIF
ELSE
    m._Cmd = E_SQL_Remove_Directives(m._Cmd)
    &_Cmd
ENDIF

E_SQL_Remove_Directives() just removes "?" and "~" prefixes from the expression. You can surely do this manually, but don't forget about possible character constants which may contain these characters.

  E_SQL_CREATE_STRU_CONVERT() function

If you have to programmatically create tables with large data structure, it wouldn't be very wise to support two copies of these commands with structure description. Here's what we offer:

E.g.:

Here's some source code in FoxPro:

CREATE TABLE t1 (IDENT C(10),CODE C(10),NAME C(100))

Let's create a character variable and add ~ to it

m._Cmd = [IDENT ~C(10),CODE ~C(10),NAME ~C(100)]

Then, depending on the data type:

m._Cmd = [IDENT ~C(10),CODE ~C(10),NAME ~C(100)]
IF m._SQL
    m._Cmd = [CREATE TABLE t1 (] + E_SQL_Create_Stru_Convert(m._Cmd) + [)]
    IF SQLEXEC(m.Connect,m._Cmd) <= 0
   * Error handling
    ENDIF
ELSE
    m._Cmd = [CREATE TABLE t1 (} + E_SQL_Remove_Directives(m._Cmd) + [)]
    &_Cmd
ENDIF
E_SQL_Create_Stru_Convert() does the following:
Changes ñ ~ type description to SQL Server data type
Adds DEFAULT clause with an empty constant value corresponding to the type;
Adds NOT NULL clause
The resulting string will be:
IDENT CHAR(10) DEFAULT '' NOT NULL,CODE CHAR(10) DEFAULT '' NOT NULL,NAME CHAR(10) DEFAULT '' NOT NULL

 

Transforming SQL syntax at runtime

The functions we described above are written in C - and not because we wanted to hide the source code. Initially, it was supposed that syntax should be transformed at runtime - and that's where we need SPEED. In our applications, syntax is transformed in GRID column expressions without slowing down the application (this mechanism is much more complicated than a simple selection from database. Here we have to use forced buffering. If you work with DBF, you will do well with SET RELATION only and won't to call LOCATE many times. Read other articles for detailed description).

The most important thing about syntax transformation is not reducing the amount of code you have to write, but creating a single code module capable of working with various data types. Sometimes it is difficult to write a comlicated statement without mixing up fields and relations, and supporting two versions of such a complicated code is generating errors. So, general steps of adapting your code to DBF tables as well as SQL databases will be as follows:

Store the query text to a character variable.

Insert "?" or "~" prefix (use the latter if you're going to use E_SQL_Find_Variables() function) before references to variables, object properties and constant expressions of FoxPro (enclose expressions into brackets)

Manually convert code parts which E_SQL_Select_Convert() cannot convert (those supporting DBF and SQL syntax)

Analyze the global attribute which specifies data type

If data are contained in an SQL Server database, then:

If needed, convert references to variables with the help of E_SQL_Find_Variables() function

Convert syntax of character variables with the help of E_SQL_Select_Convert() function

Execute the command contained in the character variable with the help of SQLExec() function

Handle errors if any

If data are contained in DBF

Remove "?", "~" prefixes with the help of E_SQL_Remove_Directives() function

Execute the command with the help of & directive

Your final code will look like this:

m._Cmd = [Original Select SQL with inserted "?" before FoxPro variables/expressions]
IF m._UsingSQL
    m._Cmd = E_SQL_Select_Convert(m._Cmd)
         *   Additional editing manually
    IF SQLEXEC(m.Connect,m._Cmd) <= 0
   * Error handling
    ENDIF
ELSE
    m._Cmd = E_SQL_Remove_Directives()
    &_Cmd
ENDIF

or

m._Cmd = [Original Select SQL with inserted "?" or "~" before FoxPro variables/expressions]
IF m._UsingSQL
    m._Cmd = E_SQL_Select_Convert(E_SQL_Find_Variables(m._Cmd))
         *   Additional editing manually
    IF SQLEXEC(m.Connect,m._Cmd) <= 0
   * Error handling
    ENDIF
ELSE
    m._Cmd = E_SQL_Remove_Directives()
    &_Cmd
ENDIF

E_SQL_Select_Convert() won't convert your code in all possible situations. Sometimes the complexity of code is so high that the only thing that can be done is manual conversion. Still, in our projects this function successfully converted 50%-70% of statements without any manual conversion. If you see any other conversion possibilities which should be included into E_SQL_Select_Convert(), feel free to inform us. We'll think over enhancing functionality of next versions.

 

Transforming query syntax at design time

Some EasySQL4Fox users wish to create two versions of statements at design time. Many still think that it is more convenient to see the converted code at design time than to trust E_SQL_Select_Convert() at design time. So Pinter Consulting have created a class called DataTier, whose methods are called to access SQL Server databases. Methods of this class can accept only syntax already converted for SQL, so another conversion tool is used to convert statements in program code - that is, E_SQL_Text_Convert.prg

As we don't need much speed in this case, the program is written FoxPro, although it uses EFoxSQL.FLL functions. The following is to be done here:

Just like in previous examples, we start with the idea that there's a global attribute specifying whether the data are located in DBF or SQL. In previous examples we supposed that it was m._SQL or m._UsingSQL variable. In DataTier class designed by Pinter Consulting there's AccessMethod field for this purpose.

IF oDataTier.AccessMethod = [SQL]

When you define your global attribute and create your own error handler for SQL errors, open your copy of E_SQL_Text_Convert.prg for editing and change pre-process declarations to your own ones:

#Define DATA_IF "IF m._SQL" && Replace this definition with valid

#Define SQL_ERROR_ROUTINE "Do SQL_Error With m.Cmd" && Replace this definition with valid

Also, specify correct path to EFoxSQL.FLL library.

Then, before you start modifying the text of the program, assign  E_SQL_Text_Convert call to F3 button.

ON KEY LABEL F3 Do ....\E_SQL_Text_Convert

Then, just go through the text of the program and when you see a statement which selects data from work tables (not from cursors), for example:

SELECT DISTINCT C.pref_no, D.ref_id, ;
            ALLTRIM(D.org) + IIF(Empty(D.org), "", " - ") + ALLTRIM(D.tle) + " " + ;
            ALLTRIM(D.giv) + " " + ALLTRIM(D.sur) AS Name, ;
                C.Guarantee, D.id ;
FROM coappref C, Demo D;
WHERE C.ID = pnID;
        AND C.mod_id = pnModID;
        AND C.Pref_id = D.Id;
        AND C.Pref_id > pnPId+1 ;
ORDER BY 1;
INTO CURSOR cursSpec1

do the following:

Manually make changes which E_SQL_Select_Convert() cannot make. These changes must support FoxPro syntax as well as SQL syntax. In our case, we should get rid of Empty() function.

SELECT DISTINCT C.pref_no, D.ref_id, ;
            ALLTRIM(D.org) + IIF(RTrim(D.org)="", "", " - ") + ALLTRIM(D.tle) + " " + ;
            ALLTRIM(D.giv) + " " + ALLTRIM(D.sur) AS Name, ;
                C.Guarantee, D.id ;
FROM coappref C, Demo D;
WHERE C.ID = pnID;
        AND C.mod_id = pnModID;
        AND C.Pref_id = D.Id;
        AND C.Pref_id > pnPId+1 ;
ORDER BY 1;
INTO CURSOR cursSpec1

Then, place '?' mark before any references to variables, object properties or constant expressions of FoxPro. Be sure to enclose expressions into brackets.

SELECT DISTINCT C.pref_no, D.ref_id, ;
            ALLTRIM(D.org) + IIF(RTrim(D.org)="", "", " - ") + ALLTRIM(D.tle) + " " + ;
            ALLTRIM(D.giv) + " " + ALLTRIM(D.sur) AS Name, ;
                C.Guarantee, D.id ;
FROM coappref C, Demo D;
WHERE C.ID = ?pnID;
        AND C.mod_id = ?pnModID;
        AND C.Pref_id = D.Id;
        AND C.Pref_id > ?(pnPId+1) ;
ORDER BY 1;
INTO CURSOR cursSpec1

Mark the text of the query:

SELECT     DISTINCT C.pref_no, D.ref_id, ;
                ALLTRIM(D.org) + IIF(RTrim(D.org)="", "", " - ") + ALLTRIM(D.tle) + " " + ;
                ALLTRIM(D.giv) + " " + ALLTRIM(D.sur) AS Name, ;
                C.Guarantee, D.id ;
FROM    coappref C, Demo D;
WHERE     C.ID = ?pnID;
        AND    C.mod_id = ?pnModID;
        AND    C.Pref_id = D.Id;
        AND    C.Pref_id > ?(pnPId+1) ;
ORDER     BY 1;
INTO CURSOR cursSpec1

Press F3

Get the message:

EasySQL4Fox_Text_Convert.gif (15066 bytes)

If you cancel the operation, the converted text will remain in _CLIPBOARD . If you confirm the operation, the program converts the source code in this manner:

*!* <<CHANGE>>
*!* SELECT DISTINCT C.pref_no, D.ref_id, ;
*!*        ALLTRIM(D.org) + IIF(RTrim(D.org)="", "", " - ") + ALLTRIM(D.tle) + " " + ;
*!*        ALLTRIM(D.giv) + " " + ALLTRIM(D.sur) AS Name, ;
*!*        C.Guarantee, D.id ;
*!* FROM coappref C, Demo D;
*!* WHERE C.ID = ?pnID;
*!*        AND C.mod_id = ?pnModID;
*!*        AND C.Pref_id = D.Id;
*!*        AND C.Pref_id > ?(pnPId+1);
*!* ORDER BY 1;
*!* INTO CURSOR cursSpec1
*!*
IF m._SQL
    Cmd =   "SELECT DISTINCT C.PREF_NO, D.REF_ID, LTRIM(RTRIM(D.ORG)) + CASE WHEN RTRIM(D.ORG)='';
               THEN '' ELSE" + " ' - ' END + LTRIM(RTRIM(D.TLE)) + ' ' + LTRIM(RTRIM(D.GIV)) + ' ';
                    + LTRIM(RTRIM(D.SUR)) AS NAME," + " C.GUARANTEE, D.ID "+;
            " FROM COAPPREF C WITH (NOLOCK) , DEMO D WITH (NOLOCK) "+;
            " WHERE C.ID = ?PNID AND C.MOD_ID = ?PNMODID AND C.PREF_ID = D.ID AND C.PREF_ID;
            > ?(PNPID+1) "+;
            " ORDER BY 1 INTO [CURSOR] CURSSPEC1 "
   * IF SQLExec(nHandle,Cmd,cAlias)<=0
   *   Do SQL_Error With m.Cmd
   * ENDIF
ELSE
   SELECT DISTINCT C.pref_no, D.ref_id, ;
            ALLTRIM(D.org) + IIF(RTrim(D.org)="", "", " - ") + ALLTRIM(D.tle) + " " + ;
            ALLTRIM(D.giv) + " " + ALLTRIM(D.sur) AS Name, ;
                C.Guarantee, D.id ;
   FROM coappref C, Demo D;
   WHERE C.ID = pnID;
                AND C.mod_id = pnModID;
                AND C.Pref_id = D.Id;
                AND C.Pref_id > (pnPId+1);
   ORDER BY 1;
   INTO CURSOR cursSpec1
ENDIF

The only thing to do is :

 

Changes to be made manually

E_SQL_Select_Convert() won't convert your code in all possible situations. Sometimes the complexity of code is so high that the only thing that can be done is manual conversion. Still, in our projects this function successfully converted 50%-70% of statements without any manual conversion. If you see any other conversion possibilities which should be included into E_SQL_Select_Convert(), feel free to inform us. We'll think over enhancing functionality of next versions.

Here's the list of changes you should introduce manually:

- Change cSubstr $ cString syntax to  At(cSubstr,cString)>0 . E_SQL_Select_Convert() changes it to CHARINDEX.

- Change references to MEMO and GENERAL fields in SELECT statement to '' if GROUP BY clause is present.

- Change references to logical values like VAR=VAL as FIELDNAME in SELECT statement to IIF(VAR=VAL,?True,?False) as FIELDNAME. Before you do it, declare m.True=.T. and m.False=.F.

- If the result of the query will include logical fields (of bit type) and GROUP BY grouping, include these fields into GROUP BY or use constant expressions like ~m.True as ... or ~m.False as ... (m.True=.T.  and m.False=.F.)) as MAX() function, as well as any other aggregate function, won't work with bit type

- As to stored procedures, if you create temporary tables by SELECT statement with INTO clause and plan to order records by char fields with the help of ORDER BY clause, convert these fields to binary type in ORDER BY, for example:  Select CHARFIELD1,CHARFIELD2 Into #temptable Order By Cast(CHARFIELD1 as binary(10)),Cast(CHARFIELD2 as binary(15)) You have to do it as SQL doesn't use ASCII sorting by default.

Client-Server technology vs direct data access

As we have dealt with syntax transformation problems, it's time to start with Client-Server technology. This step may be more important and laborious, but it still uses syntax conversion means described above.

Well, let's list problems which we have to solve to adapt our application to Client-Server technology:

CursorAdapter - a Visual Foxpro base class to ensure data access in Client-Server technology

To work with data in SQL or other databases in Client-Server mode, FoxPro offers its CursorAdapter class. This is how it It offers to solve problems described above:

To avoid creating two copies of queries  - one (source) for direct selection from DBF and another for selection from SQL - use E_SQL_Select_Convert() function to convert your existing and debugged query to SQL standard.

Detailed class description is to be found in VFP 9 manual.

EFox_Data_Adapter - a class to abstract application data domain from database type which transforms query syntax at runtime.

The approach we offer is a bit different from CursorAdapter approach. You don't have to create a separate object for every alias. Direct access commands are changed into calls to class methods, and the final desicion is made inside the method. Also, LOCATE condition text or query text is sent to the method and the method will convert it withE_SQL_Select_Convert() for SQL data.

You may use the current version of the class; still you may have to inherit it and introduce further development into it. The idea is as follows:

You create an object based on this class or inherit this class (e.g. with "ACCESS" name) and add it to a container, form or use it separately.

All tables of the session should be opened by USE method of this object. If the table is a DBF table and is used directly, it will be opened by USE command and the object will close it in DESTROY event, so you don't have to close it. If the table ids in SQL database, first you need to establish connection to the source with SQLConnect() function or SQLStringConnect() function, and the identifier of this connection should be passed as a third parameter to USE method.

Search your form methods and programs for LOCATE, SCATTER, GATHER, REPLACE, GO TOP, BROWSE, INSERT commands and change them to calls to corresponding methods of ACCESS class observing rules individual for each method.

Inherit this class and create your own methods equal to SKIP, SEEK, ... commands basing on how it is done in existing methods. 

If you plan to use Pass-Through technology for data access instead of views, you will find SELECTSQL and SENT_UPDATES methods pretty helpful.

Have a look at the sample (SAMPL.SCX) to fully understand class methods and how the class works. Below is a short description of what these methods do:

BROWSE (cAlias, cBrowseCommand [,cKeyFieldList)

If cAlias refers to DBF table with direct access, BROWSE command is executed. If cAlias refers to a table in SQL Server database, the method does the following:

To get detailed information, see "Browse" and "Refresh and Browse" buttons in SAMPLE.SCX sample.

BROWSE_PREPARE (cAlias, cBrowseCommand [,cKeyFieldList)

Does the same as BROWSE method, but skips BROWSE command. Actually, BROWSE method first calls BROWSE_PREPARE method and executes BROWSE command. Use this method to prepare aliases for GRID objects.

CLOSE (cAlias)

Closes a separate table opened with USE method

CONTINUE (cAlias)

Is executed after LOCATE method. If cAlias is a DBF table with direct access, CONTINUE command is executed. The method returns Found() value. If cAlias refers to a table contained in SQL database, the cursor created by LOCATE command will be selected and SKIP command will be executed. The method returns NOT EOF() value

GATHER (cAlias [,cFieldList])

If cAlias refers to a DBF table with direct access, command GATHER ... MEMVAR ... will be executed. If cAlias is a cursor or a view of SQL database table created by LOCATE, GO_TOP, BROWSE and this cursor or view are updatable, GATHER ... MEMVAR ... will also be executed. To send changes to the source tables the method will go to the next record in the view/cursor and return back (record buffering). If the local view or a cursor aren't found, you'll see an error message.

GET_TEMP_FILE (cAlias [,cAsAlias] [,nDataSession])

Generates a temporary file, registers it in the internal list and returns the unique name of the file. This temporary file will be deleted in the DESTROY event.

GO_TOP (cAlias [,cKeyFieldList] [,cOrderBy])

If cAlias refers to a DBF table, Go TOP is executed. If cAlias refers to a table contained in SQL Server database and the alias exists,  GO TOP will also be executed. If the alias doesn't exist, the method will create a temporary view with the name cAlias in the temporary database and sets its "FetchAsNeeded" property to  .T. for faster selection of first records into a temporary cursor. If a list of key fields is specified, the temporary view will be updateable and all changes will be saved in the source table. If cOrderBy is specified, records will be selected into the cursor in the specified order.

INSERT (cAlias [,cFieldList])

If cAlias refers to a DBF table, INSERT INTO (cAlias) FROM MEMVAR or (if you pass the field list) INSERT INTO cAlias (cFieldList) VALUES (cFieldList) will be executed . If cAlias  refers to a table contained in SQL Server database and the alias exists (Used(), created by LOCATE,GO_TOP,BROWSE methods) and cAlias is updatable, then INSERT INTO command will aalso be executed for the alias. Then, to send changes to the source tables, record buffering will be carried out. If the alias doesn't exist but cAlias refers to a table in SQL database, INSERT INTO command will be executed in the source by SQLExec() function. All variable references will be converted.

IS_UPDATABLE (cAlias)

Returns .T. if the alias is updateable, i.e. all changes will be saved in the source table.

LOCATE (cAlias, cCondition [,cKeyFieldList])

References to variables, object properties and constant expressions of FoxPro in cCondition should start with ? prefix. If cAlias refers to a DBF table, LOCATE with a specified condition is carried out. All needless characters will be removed from cCondition by E_SQL_Remove_Directives() function. If cAlias  refers to a table contained in SQL Server database, the syntax will be converted by E_SQL_Select_Convert([Select * Where ] + cCondition) function. If the previous selection cursor exists, it will be closed. Then, SQLExec() function will select data into cursor named cAlias. If a list of key fields (cKeyFieldList) is passed, the cursor will be updateable, i.e. all changes will be saved to the source table. After LOCATE method, you can call CONTINUE, GATHER, SCATTER, INSERT, BROWSE, GO_TOP methods which will work with the created cursor.

LOCATE_BF (cAlias, cCondition ,cKeyFieldList)

Does the same as LOCATE, but uses forced buffering. If a local buffer hasn't yet been created for the cursor, an empty cursor with the structure identical to that of the table will be created. During every call, LOCATE_BF method will first look for the needed value in this cursor. If the value isn't found, it will search the source table. Each search result is saved in a local buffer.

Makes BROWSE and GRID work very fast!

!!! cKeyFieldList field list is mandatory. Still, when the method has finished working, the current alias will be the alias of the local buffer with the found record, not cAlias. Also, buffer cannot be updateable.

ACCESS object deletes buffers in its DESTROY event.

As an example, see"Browse Invoices" button in SAMPLE.SCX

REPLACE (cAlias, cFields, cFor)

Pass FIELD1 With Value1,FIELD2 With Value2,... as cFields. You can refer to variables, object properties and constant expressions of FoxPro with the help of '?' prefix. If cAlias refers to a DBF table, Replace ... For ... command is executed.   Previously,  E_SQL_Remove_Directives() function will remove '?' marks. If cAlias refers to a table contained in a SQL Server database and there's a cursor or a view created by LOCATE, GO_TOP, BROWSE methods,  REPLACE ... FOR ... command for cAlias will also be executed. If cAlias doesn't exist, but refers to a table in SQL Server database, UPDATE ... WHERE ... command will be executed in the source. 'With' clause will be changed to '=' and the generated UPDATE command will be passed to SQLExec() function for execution.

SCATTER (cAlias [,cFieldList] [,lBlank])

If Used(cAlias), SCATTER command will be executed, else - an error is returned.

SELECTSQL (cFromAlias, cCommand, cResultAlias [, cUpdateTable [, cKeyFieldList]] [,lRemote [,lTemporary [,lReadOnly]]] [,lTmpTable] [,lFetchAsNeeded] )

It is a Select SQL query. It selects data from source tables to a local cursor or view. In cCommand, you should mark all references to variables, object properties and constant expressions with '?' or '~' marks.

If  cFromAlias refers to DBF, cCommand + [ INTO CURSOR ] + cResultAlias + [ READWRITE] command will be executed. Extra syntax will be removed by E_SQL_Remove_Directives() function.

If cFromAlias refers to a table contained in SqL Sever database, the query is passed via the connection defined for  cFromAlias by passing the third parameter to USE method.  cCommand will be processed by E_SQL_Find_Variables() function and then by E_SQL_Select_Convert() function.

If you need an updateable cursor as the result of the query, pass the name of the source table as cUpdateTable and a list of key fields as cKeyFieldList. If you select from DBF, changes in cursor may be saved in the source table only by calling SENT_UPDATES method. For SQL, it will be an updateable cursor or a view with record buffering.

To select into a remote table, pass lRemote=.T. If you pass lTemporary=.T., CURSOR (cResultAlias) READWRITE is created if you work with DBF, and a temporary table '#' + cResultAlias in TEMPDB base is created if you work with SQL.

!!!If this query contains references to local variables, they must be marked with '~' mark only. To create a readonly cursor, pass lReadOnly=.T.

If you select from DBF and want to select data into a temporary table instead of cursor, pass lTmpTable=.T. . In this case you will be able to perform ZAP and PACK.

If you pass lFetchAsNeeded, then if you select from SQL Server database, the method will not create a cursor - it will create a temporary view in a temporary DBC and set its FetchAsNeeded property to .T.  First records will be selected faster while other records will be added when you scroll the GRID or the view by SKIP command.

!!!GO command will result in immediate selection of all remaining records.

SEND_UPDATES (cAlias [,cCurRec [,cField]])

Call this method to send updates to the source, if cAlias was selected by SELECTSQL method and cUpdateTable and cKeyFieldList were passed. If it is DBF, data will be sent to the table by the method code; if it is SQL, record buffering will be carried out (the pointer will go to the next record and back).

USE (cTable [,cAlias] [,nConnect])

Every table should be opened by calling this method. If cAlias isn't specified, it will be JUSTSTEM(cTable). All DBF files opened by this method will be closed in DESTROY event. If the table is located in SQL Server database, you should first establish connection with SQLConnect() or SQLStringConnect() function; connection identifier must be passed to this method as a third parameter.

Example of using EFox_Data_Adapter class - Sample.scx

Before you activate the form, make its directory the current one. When you launch the form, it will ask where the data are located - whether in DBF (direct access) or SQL database. The form works with 2 tables:

If you select DBF, you'll be offered to specify the directory where these tables are located. If you decide to work with SQL Server, you'll see a standard dialog box of connecting to SQL Server. If the tables weren't created beforehand, specify any directory or any SQL Server database. The form will offer to create tables in selected locations.

The top part of the form displays code and name boxes for the current customer. If there's no customer, these boxes are disabled.

In this form, you can do the following:

To obtain more information, you may study codes of methods.

 

What is included into EasySQL4Fox shipment:

EFoxSQL.FLL    the library containing functions updating the syntax of SQL statements created in FoxPro to the syntax of Transact-SQL used in MS SQL Server.
E_SQL_Text_Convert.prg    the program which converts the syntax of statements in text with FoxPro code
E_Check_UDFs_For_Connect.prg checks whether UDFs dbo.PADL() and dbo.PADR exist in  SQL Server database, creates them if needed.
Classlib\                 this directory contains EFox_Data_Adapter class and the sample.
Classlib \ EFoxSQL.VCX(VCT)    the library with EFox_Data_Adapter class
ClassLib \ Sample.SCX(SCT)      a saqmple form displaying how to use EFox_Data_Adapter class
ClassLib \ ISample.SCX(SCT)      is called from Sample.scx
EasySQL4Fox.HTML                     This file

 

Release november 2007. What's new.

1. This release allows SQL queries conversion for MS SQL, ORACLE, MySQL

Table of conversion functions in EFOXSQL.FLL october 2007:

SQL Server ORACLE MySQL
E_SQL_CREATE_STRU_CONVERT E_ORACLE_CREATE_STRU_CONVERT E_MYSQL_CREATE_STRU_CONVERT
E_SQL_SELECT_CONVERT E_ORACLE_SELECT_CONVERT E_MYSQL_SELECT_CONVERT
E_SQL_RESERVED E_ORACLE_RESERVED E_MYSQL_RESERVED

VFP functions and their equivalents:

VFP SQL Server ORACLE MySQL
ALLTRIM RTRIM(LTRIM()) RTRIM(LTRIM()) RTRIM(LTRIM())
ASC ASCII ASCII ASCII
AT CHARINDEX INSTR INSTR
CHR CHAR TO_CHAR CHAR
DATE GETDATE   CURRENT_DATE
DAY   EXTRACT EXTRACT
DELETED 1=1 1=1 1=1
DTOC CONVERT TO_CHAR CONVERT
DTOR RADIANS 0.017453293* RADIANS
HOUR DATEPART EXTRACT EXTRACT
IIF CASE WHEN ...THEN ... ELSE ... END CASE WHEN ...THEN ... ELSE ... END CASE WHEN ...THEN ... ELSE ... END
INT FLOOR FLOOR FLOOR
LEN   LENGTH CHAR_LENGTH
MINUTE DATEPART EXTRACT EXTRACT
MOD E_Check_UDFs_For_Connect.prg creates an UDF dbo.MOD    
MONTH   EXTRACT EXTRACT
NVL ISNULL   COALESCE
PADL E_Check_UDFs_For_Connect.prg creates an UDF dbo.PADL LPAD LPAD
PADR E_Check_UDFs_For_Connect.prg creates an UDF dbo.PADR RPAD RPAD
RTOD DEGREES 1/0.017453293* DEGREES
SEC DATEPART EXTRACT EXTRACT
STR     LPAD(TRIM(FORMAT(",18)
STRTRAN REPLACE REPLACE REPLACE
SUBSTR SUBSTRING SUBSTR SUBSTR
TTOD CONVERT(datetime,Floor(CONVERT(float,...)) TRUNC DATE
VAL E_Check_UDFs_For_Connect.prg creates an UDF dbo.VAL TO_NUMBER CONVERT
YEAR   EXTRACT EXTRACT

 

2. There are some things about using of function E_SQL_FIND_VARIABLES in situations when you need convert variables in SQL string into their values. You should pass .T. as second parameters in this purpose. But there are specifics when you use date literals in ORACLE and MySQL. We recommend that you should change default ORACLE session setting :

= SQLEXEC(m.Connect,[Alter Session set NLS_DATE_FORMAT = 'YYYYMMDD HH24:MI:SS'])

For MySQL we added a third parameter into E_SQL_FIND_VARIABLES syntax. Pass 2 if you need that E_SQL_FIND_VARIABLES should convert a dates into YYYY-MM-DD format

 

3. Look at DataOdyssey Express developing solution as example of working with several types of data with using efoxsql.fll library.

 

 

Download EasySQL4Fox (free)

FoxPro to NET migration     FoxPro to NET pre-conversion     FoxPro to NET rewriting     VFP to NET migration   VFP to NET pre-conversion   VFP to NET rewriting

Send message to developer ...