|
|
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 equivalent, 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 table 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:
- m.Connect
- is the connection code returned by SQLConnect() or SQLStringConnect()
- SQL_Error()
- is the error handling function
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:
- Create a character variable which would
describe the structure;
- Insert ~ character before every
definition;
- If the command is executed by SQL,
convert the structure by E_SQL_Create_Stru_Convert() function.
- If the command is executed by FoxPro, -
convert the structure by E_SQL_Remove_Directives() function or
StrTran(cmd,'~','') function.
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 named 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
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:
-
Table opening
procedures are changed to database connection.
-
As one cannot directly
address tables located in SQL Server or Oracle databases, execution of
some FoxPro commands becomes impossible (LOCATE,SEEK,GO,BROWSE,RELPACE,SCATTER,GATHER,...).
-
Our program should
work with DBF in file-server mode as well as with SQL databases. The
application code changes should be reduced to minimum. Actually, we
need to abstract application data domain from data type.
|
|
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:
-
When opening tables,
you're supposed to use USE if you access DBF directly and create
CursorAdapter class object if you work in Client-Server mode.
-
If you work in
Client-Server mode, before you issue direct table access commands (LOCATE,SEEK,GO)
you are offered to write the statement into SelectCmd property and
execute FillCursor method. Actually you are offered to formulate a
parametrized query. It goes without saying, for LOCATE and SEEK the
WHERE clause in SelectCmd should comply with the search criteria.
-
In any other cases we
have to think over an algorythm. It doesn't sound a wise idea to
select a whole large table into a cursor.
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.
|
|
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:
- If LOCATE or GO_TOP methods didn't
create a temporary view in a temporary DBC or a cursor for this
cAlias, a temporary view will be created in temporary DBC base.
- If a list of key fields separated by
comma was passed to the method, the view will be updatable and all
changes will be saved in the source table.
- FetchAsNeeded property of the view
will be set to .T. to quickly select first records of the table into
a local cursor and further select records as they are being viewed
in GRID (BROWSE)
- View alias is selected; BROWSE
command is executed.
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:
- easysql_customs.dbf -
a list of customers (pretty simplified)
- easysql_invoices.dbf
- a list of their invoices
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 sample form displaying how to use EFox_Data_Adapter class
ClassLib \ ISample.SCX(SCT)
is called from Sample.scx
EasySQL4Fox.CHM
help file.
|
|
|
|
