Home Page

This site is under construction!

A small component that convert a VFP simple SQL statements into an equivalent ODBC compatible statement. It is very configurable and allows you to reuse your VFP code you put in your SQL queries on DBF tables. More important, it allows you to develop application that can run, without modifications, on more ODBC datasources types.

What's new
How it Works
Why to use it
What it does
Examples
Configuration
The built-in configuration
Limitations
Download
Legal stuff
SourceForge project status

How it works

The compoent is a COM object. You need to create an instance of it in order to use it. You create the object by calling the CreateObject function, passing 'VFPSQLPARSER.PARSER.1' as the argument. The object has a method, Convert(sSQL), that converts an SQL statement. It returns the converted statement. The object is simply a text parser, it does NOT call the server or execute the statement. You still have to use SQLExecute(...) to actually send the statement to the server.

Back to start

Why to use it

There are three primary uses for the parser:

If you're not familiar with the functions available on the server you are targeting. You can use the familiar VFP syntax and let the converter to change this syntax to the syntax expected by the server.
If your application is targeted to run on more server platforms. In this case, the convertor can be used to change the statements according to the targeted server at each site.
For applications developed to use generic ODBC datasources. In this case, you can relly on the converter capability to use ODBC scallar functions and ODBC function escape sequences for generic ODBC compatibility.

Back to start

What it does

The convertor it parses the given statement and searches function calls. It then replace the function with a function that is recognized by the server. The recognized functions and they replacements are fully configurable. The parser also recognizes and replaces certain operators, like $, ! or +. The parser is not simply a smarter STRTRAN(), it is a true grammar parser. Functions or operators wich are not configured to be replaced are left as they are.

When parsing a full SQL statement, the parser also does several aditional 'services':

It replaces the names of local aliases with the name of the table on the remote server. Note that you'll need to configure the parser to support this.
It replaces the datatypes used in CREATE TABLE and ALTER TABLE statements with the types as they're named on the server. Also, you'll need to configure the parser to support this.

Back to start

Examples

m.oConvertor=createobject("VFPSQLPARSER.PARSER.1")

m.sSQL="SELECT * FROM sometable WHERE 'John'$NAME"

m.sSQL=m.oConvertor.Convert(m.sSQL)

SQLExec(m.hSomeHandle,m.sSQL)

This example will convert the VFP specific operator $ into a function recognized by the server. If the default configuration is left, the conversion will look like SELECT * FROM sometable WHERE {fn LOCATE('John',NAME)}>0. If the supplied Oracle configuration is used the result will be SELECT * FROM sometable WHERE (INSTR(NAME,'John')>0).

m.oConvertor=createobject("VFPSQLPARSER.PARSER.1")

m.sSQL="SELECT * FROM sometable WHERE len(NAME)>5"

m.sSQL=m.oConvertor.Convert(m.sSQL)

SQLExec(m.hSomeHandle,m.sSQL)

This example will convert the VFP specific operator $ into a function recognized by the server. If the default configuration is left, the conversion will look like SELECT * FROM sometable WHERE {fn LENGTH(NAME)}>5. Again, if the supplied Oracle configuration is used the result will be SELECT * FROM sometable WHERE LENGTH(NAME)>0.

m.oConvertor=createobject("VFPSQLPARSER.PARSER.1")

m.sEXP="allt(NAME)+dtoc(BIRTH)"

m.sEXP=m.oConvertor.Convert(normalize(m.sEXP))

m.sSQL="SELECT * FROM sometable order by "+m.sEXP

SQLExec(m.hSomeHandle,m.sSQL)

This example shows how you can convert a separate expression and then use it to build an SQL statement. It also shows how a simple expression can result in more complicated expression. The conversion will result in an expression like {fn CONCAT({fn RTRIM({fn LTRIM(NAME)})},{fn CONVERT(BIRTH,SQL_CHAR)})}. If the Oracle configuration is used, the result will be LTRIM(RTRIM(NAME))||TO_CHAR(BIRTH). Note the use of NORMALIZE before passing the expression to the parser. This is required, because the parser does not uderstand the usual VFP short forms of functions, like allt instead of alltrim.

Back to start

Configuration

In order to configure the parser, you must use another COM object of class VFPPARSER.CONFIGURATION.1 Create this object, then call a parser's ConfigureWith method, passing the configuration object. The configuration object has two methods, Add and Remove. Use Add to add a new function to be recognized by the parser. Remove removes a recognized function.

Add(sFunction,sReplacement) You must pass the function name as it appears in VFP syntax. The sReplacement parameter is what should the parser use instead of the VFP function. In the sReplacement parameter you can use special %1, %2,... symbols to mark the position of the first, second,.. parameter.
Remove(sFunction) takes only one parameter, the name of the function to be removed from configuration.

Note that the parser keeps a reference to a configuration object. any changes made to a configuration object affects the behavior of parsers that are using this configuration. You can use the same configuration object to configure several parsers, altough a typical usage scenario involves a single parser and a single configuration object existing trought the application. More configuration objects make sense if your applications is connected to distinct platform servers.

The parser can be further configured using VFP code to respond to events raised by the parser during a conversion. In order to do this, you'll have to use VFPCOM.DLL (available from MS site) to respond to the events generated by the parser. Both the parser and the configuration object raise events. The configurations object raise the event before the conversion, while the parser is searching for a substitution for a function it found. The parser itself raises the event after the substitution had take place. In the event code, modifying the default conversion found by the parser will result in changing the actual conversion.

If you're using the table name translation service of the parser, you must provide an object to respond to the MapTableName event of the configuration object. Usually, the folowig implementation will sufice:

define class _IConfigurationEvents as custom

...

procedure MapTableName(sTable,sRemote)

sRemote=cursorgetprop('Tables',sTable)

endproc

enddefine

Back to start

The built-in configuration

The parser uses by default a new configuration object. for maximum compatibility, the built-in configuration uses ODBC escape functions. For optimal performance, you should use a customized configuration object. You can use the Remove methods to remove any of the buil-in replacements. A new configuration object has the following built-in functions recognized:

LEFT	{fn LEFT(%1,%2)}
RIGHT	{fn RIGHT(%1,%2)}
AT	{fn LOCATE(%1,%2)}
ATC	{fn LOCATE({fn UCASE(%1)},{fn UCASE(%2)})}
$	({fn LOCATE(%1,%2)}>0)
+	{fn CONCAT(%1,%2)}
LTRIM	{fn LTRIM(%1)}
TRIM	{fn LTRIM(%1)
RTRIM	{fn RTRIM(%1)}
ALLTRIM	{fn RTRIM({fn LTRIM(%1)})}
STRTRAN	{fn REPLACE(%1,%2,%3)}
UPPER	{fn UCASE(%1)}
LOWER	{fn LCASE(%1)}
CHR	{fn CHAR(%1)}
ASC	{fn ASCII(%1)}
SUBSTR	{fn SUBSTRING(%1,%2,%3)}
REPLICATE	{fn REPEAT(%1,%2)}
SPACE	{fn REPEAT(' ',%1)}
PADR	{fn LEFT({fn CONCAT(%1,{fn REPEAT(' ',%2)})},%2)}
PADL	{fn RIGHT({fn CONCAT({fn REPEAT(' ',%2)},%1)},%2)}
LEN	{fn LENGTH(%1)}
STR	{fn CONVERT(%1,SQL_CHAR)}
VAL	{fn CONVERT(%1,SQL_NUMERIC)}
CTOD	{fn CONVERT(%1,SQL_DATE)}
ABS	{fn ABS(%1)}
ACOS	{fn ACOS(%1)}
ASIN	{fn ASIN(%1)}
ATAN	{fn ATAN(%1)}
CEILING	{fn CEILING(%1)}
COS	{fn COS(%1)}
EXP	{fn EXP(%1)}
FLOOR	{fn FLOOR(%1)}
LOG	{fn LOG(%1)}
LOG10	{fn LOG10(%1)}
MOD	{fn MOD(%1)}
PI	{fn PI()}
RAND	{fn RAND(%1)}
ROUND	{fn ROUND(%1)}
SIGN	{fn SIGN(%1)}
SIN	{fn SIN(%1)}
SQRT	{fn SQRT(%1)}
TAN	{fn TAN(%1)}
DATE	{fn CURDATE()}
TIME	{fn CURTIME()}
CDOW	{fn DAYNAME(%1)}
DAY	{fn DAYOFMONTH(%1)}
HOUR	{fn HOUR(%1)}
MINUTE	{fn MINUTE(%1)}
MONTH	{fn MONTH(%1)}
CMONTH	{fn MONTHNAME(%1)}
YEAR	{fn YEAR(%1)}
WEEK	{fn WEEK(%1)}
DTOC	{fn CONVERT(%1,SQL_CHAR)}
TTOC	{fn CONVERT(%1,SQL_CHAR)}
TTOD	{fn CONVERT(%1,SQL_DATE)}
INLIST	(%1 IN (%%))
EMPTY	(%1=' ')

Back to start

Limitations

The parser does not support all of VFPs SQL grammar. Most notably, it does not understand JOIN clauses (maybe I'll add suport for them...). The SELECT statement clauses must be in a certain order:
SELECT [ALL|DISTINCT] field_list FROM tables_list [where_clause] [order_by_clause] [group_by_clause] [having_clause]. Even if the where_clause or any other clause is missing, the order has to be the same.

Back to start

Download

In order to install the parser, download the latest vfpsqlparser.zip file, unzip the DLL into a folder were you want the parser to remain and then register the DLL.

Back to start

Legal Stuff

You may use the SQL Parser for free, as long as you comply with the Right of Use provided with the program in the license.txt file.

Back to start

What's new

March 13, 2001

Fixed broken link to the download zip. Waiting for Sourceforge Release Files Service to become available.

March 09,2001

Moved to SourceForge.net.

Feb 27,2001

Improved the syntax to ignore the VFP ';' line continuation character
Improved syntax to undesrtand the NULL and NOT NULL clauses in ALTER TABLE and CREATE TABLE statements.
Added the LoadDriver method used to load a specific ODBC driver information in order to convert data types from VFP to the server native type in CREATE TABLE and ALTER TABLE statements.

The method takes one parmeter, the result from SQLGETPROP(nSomeConnection,'ODBChdbc').

E.q.:

nHandle=SQLCONNECT()

oParser=createobject('VFPSQLPARSER.PARSER')

oParser.LoadDriver(SQLGETPROP(m.nHandle,'ODBChdbc'))

? oParser.Covert('CREATE TABLE SomeTable (CharField C(10), DateField D, TimeField T, NumericField N(10), NumericField2 N(10,2), IntegerField I, FloatField F, BitField L, MemoField M, GeneralField G)')

This call produces different results, depending on the ODBC driver is loaded. For an Oracle database, the result is
CREATE TABLE SomeTable (CharField CHAR(10) ,DateField DATE ,TimeField DATE ,NumericField DECIMAL(10,0) ,NumericField2 DECIMAL(10,2) ,IntegerField ,FloatField ,BitField ,MemoField LONG ,GeneralField LONG RAW ),
while for an MS SQL server, the result is
CREATE TABLE SomeTable (CharField char(10) ,DateField datetime ,TimeField datetime ,NumericField decimal(10,0) ,NumericField2 decimal(10,2) ,IntegerField int ,FloatField ,BitField bit ,MemoField text ,GeneralField image ).
Note how some for data types, the parser could not deduce from the ODBC driver what type to use (Integer, Float and Logical for Oracle, Float for MS SQL). This may happen for two reasons: either the server does not support the specific type (as in the case of Logical types on Oracle), either the driver does not correctly report the supported data types. In such cases, you may need to use VFPCOM.DLL binding capabilities to customize the parser behavior.