SQL Pass-Thru

SQL Pass-Thru

9.      SQL pass-through

Pass though queries bypass FoxPro functionality and execute a query or command directly on the server. The results are returned from the server into a read-only local workstation cursor.

SQL pass through queries can exploit functionality built into the ODBC driver to add features such as outer joins or date manipulations to the query. These ODBC extensions are interpreted by the driver so that the same syntax can be applied across multiple database servers.

SQL pass though queries can also bypass the ODBC driver to perform operations on the server in the native server syntax. This can be used to optimise queries by using specific server syntax and also to run housekeeping and other functions that need not necessarily return a results set.

Cursors created with a SQL pass through command do not automatically update on the server

Pass through queries often use a combination of commands:

·                  SQLCONNECT is used to create a connection to the server using either an ODBC datasource or more often a connection from the current database container.

·                  SQLSTRINGCONNECT is used with a complete connection string to avoid having to define a datasource on the workstation.

·                  SQLEXEC will execute a query directly on the server.

·                  SQLCANCEL will cancel a query on the server.

·                  SQLMORESULTS is used if more than one results set is returned from the server in non-batch mode.

·                  SQLPREPARE can be used to prepare (compile) statements on the server for subsequent re-execution.

·                  SQLDISCONNECT  is used to close the connection.

The SQLCONNECT function is passed the name of an ODBC datasource or connection in the current database container and will return a positive number representing the connection handle if the connection with the server is made successfully.

m.ln_connect =  SQLCONNECT( 'conPubs' )

IF m.ln_connect < 1

   * Error

   ….

ENDIF

The connection handle returned from SQLCONNECT is now used to execute the pass through query onto the server. This example executes a housekeeping function on the server that does not return a results set:

m.ln_exec = SQLEXEC( 2, 'UPDATE STATISTICS authors' )

A negative value returned from SQLEXEC indicates an error which requires the AERROR() function to provide further information. A value of one indicates that the query has completed and a value of zero indicates that the query is still processing.

The following pass through query will return a read only cursor called CURSALES:

m.ln_exec = SQLEXEC( 2, 'SELECT * FROM sales' , 'cursales' )

The SQLEXEC can be used to create more than one results set simultaneously by separating the queries with a semi-colon. The following example will return two results sets from the server, one containing a read only view of the sales table, and the second a read only view of the authors table:

? SQLEXEC( 1, 'SELECT * FROM sales;SELECT * FROM authors' , 'cursales' )

In this case the SQLEXEC returns a 2 indicating that two results sets have been created on the workstation (CURSALES and CURSALES1).

Asynchronous Mode

SQL Pass Through queries may use an asynchronous connection to pass results onto the application before the query has finished processing.

The Asynchronous property of the connection can be set using the connection designer or programmatically with the SQLSETPROP() command:

? SQLSETPROP( 1, 'asynchronous', .T. )

The SQLEXEC command may now be issued and will return zero while still executing.

m.ln_exec = SQLEXEC( 1, 'SELECT * FROM sales', 'cursales' )

IF m.ln_exec < 0

   * Error

   …

ELSE

   IF USED('cursales')

      BROWSE

   ENDIF

ENDIF

The command is issued again periodically to check if processing has finished when one is returned.

IF SQLEXEC( 1, '' ) = 1

   * Finished

   …

ENDIF

The query can be cancelled when enough records have been retrieved with the SQLCANCEL command:

? SQLCANCEL( 1 )

Batch Mode

Batch mode can be set on the Connection so that multiple queries are not returned simultaneously but one after another. This allows the first table to be processed before receiving the second.

The Batch property of the connection can be set using the connection designer or programmatically with the SQLSETPROP() command:

? SQLSETPROP( 2, 'batch', .F. )

The non-batch pass through query is now executed and only the first results set is returned.

? SQLEXEC( 1, 'SELECT * FROM sales;SELECT * FROM authors' , 'cursales' )

The second results set is waiting to be sent to the server and must be retrieved with the SQLMORERESULTS function which used the connection handle as a parameter and will return 2 because the second results set is created. This function is called until the final set is retrieved.

? SQLMORERESULTS( 1 )

The SQLCANCEL command is used to cancel a query whenever required:

? SQLCANCEL( 1 )

SQL Pass Through and Data Buffering

SQL Pass Through queries execute a statement directly on the server and often return a cursort. This cursor might be the product of a system procedure or a standard SQL SELECT statement. Whatever the nature of the cursor it is read-only.

Sometimes it is useful to make a results set from a pass through query updateable. A remote view may not be possible if some functionality specific to the server is included in the SELECT statement or if a more flexible approach than parameterised queries is required.

A pass through query is used to create a results set which is represented as a cursor in FoxPro. The cursor has many properties that may be set with the CURSORSETPROP command. A remote view automatically sets these properties to allow the cursor to be updateable. With a results cursor from a pass through query these properties must be set programmatically.

The simple example where a remote view was created to show authors with contracts can be implemented as an updateable pass through query by following a fairly complex procedure.

First make a connection handle to the required datasource or connection. Various connection properties can be set to create asynchronous queries and so forth. These examples use the default settings.

m.ln_Connect = SQLCONNECT( 'odbpubs' )

IF m.ln_Connect < 0

   * Error
   …

ENDIF

Now create the results set as required using the SQL pass though commands. In this instance the value for the Contract field will not be a logical because it is stored as a Bit field on the server. The SELECT statement is presented directly to the server and will not automatically translate datatypes (unless a parameter is specified):

m.ln_exec = SQLEXEC( m.ln_connect, ;

   'SELECT * FROM authors WHERE contract = 1', ;

   'curauthorscontract' )

IF m.ln_exec < 0

   * Error
   …

ENDIF

The cursor has not been created but is read-only. Several cursor properties need to be set in order for the cursor to be updateable:

·                  SENDUPDATES  must be set True for updates to be passed back to the server.

·                  TABLES must specify the server tables used in the view.

·                  KEYFIELDLIST must contain a comma separated list of key fields.

·                  UPDATENAMES should contain a comma separated list with the FoxPro field name then a space and the full serve alias table and field name. This must include the primary key fields.

·                  UPDATABLEFIELDLIST must contain a comma separated list of updateable fields.

A useful technique to determine these settings is to use the CREATE SQL VIEW command to create a similar remote view on the database and then use DBGETPROP() to determine the required property settings.

The following code, executed at the command line, will set all the required properties on the cursor to allow updates to be made to the forename and surname fields:

? SQLEXEC(1,'select * from authors','autest')

SELECT autest

 

? CURSORSETPROP( 'sendupdates', .T. )

? CURSORSETPROP('tables','authors')

? CURSORSETPROP('updatename', 'au_id authors.au_id,au_lname authors.au_lname,au_fname authors.au_fname' )

? CURSORSETPROP( 'updatablefieldlist','au_lname,au_fname')

? CURSORSETPROP( 'keyfieldlist', 'au_id' )

There are many other properties that can be set against both the cursor and the connection using CURSORSETPROP and SQLSETPROP respectively. Refer to on-line help for more details.

Preparing SQL Statements

SQLPREPARE can be used to prepare SQL statements on the server. The statement is prepared on the server and will execute faster when subsequently executed and is therefore of most benefit for complex select statements with parameters that are run repetitively:

Complex select statements may be better implemented as a stored procedure on the server as the server may be able to store and monitor an optimised plan for executing the SQL efficiently.

A connection is used for the prepared statement and the statement must be prepared again if another pass through is executed on the connection. The SQLPREPARE() command is then used to pass the statement through to the server. Any parameters must have been previously defined.

m.ln_Connect = SQLCONNECT( 'conpubs' )

m.lc_surname = SPACE(0)

m.ln_prepare = SQLPREPARE( m.ln_connect, 'SELECT * FROM authors WHERE au_lname LIKE ?lc_surname','cursurname' )

The statement is now prepared and the SQLEXEC can be used to run the query. The SQLEXEC behaves normally and may need to be run several times until a 1 is returned depending on the size of the query:

m.lc_surname = '[a-d]%'

? SQLEXEC( m.ln_connect )

Subsequent changes to the parameter and execution will use the prepared statement.

The statement needs to be prepared again if the connection is used for another query.

ODBC Extensions

ODBC Drivers have various extensions to the SQL Syntax that allow functions to be implemented transparently without relying on specific functionality of the back end server.

There are various scalar functions, for example, that allow string and date conversion and other formatting to be implemented without relying on the native syntax of the database server. SQL pass through statements that use these extensions will function with any ODBC driver that supports them.

The {} brackets indicate to the ODBC driver that ODBC extensions are in operation and the driver will interpret the function and pass it onto the server.

Date functions are useful when passing date values to the database server:

? SQLEXEC(1,[select * from sales where ord_date = {d '1992-06-15'}])

The following example will convert the surname field to upper case and provide a SOUNDEX value for the surname without using server resources:

m.ln_sql = SQLCONNECT( 'odbpubs' )

? SQLEXEC(m.ln_sql, 'select au_id, { fn ucase(au_fname)}, au_lname, {fn soundex(au_fname)} as soundex from authors', 'curauth01')

Note that in the above example, SOUNDEX is also a SQL Server function and would work without the ODBC {} brackets. The UCASE function however is an ODBC extension as the SQL Server syntax is UPPER.

Another example uses date functions to determine the year and week of employment using the HIRE_DATE datetime field in the EMPLOYEE table:

? SQLEXEC( 1, 'SELECT *, {fn year(ord_date)} year, {fn week(ord_date)} week, {fn dayofweek(ord_date)} dow FROM sales' )

Date conversion functions are amongst the most useful of the ODBC scalar functions as each server seems to have its own date format. The following pass through query uses the ODBC CONVERT function to query all employees hired on or since Christmas 1992:

? SQLEXEC( m.ln_sql, [select * from employee where hire_date >= { fn CONVERT ('1992-12-25', SQL_TIMESTAMP)}] )

Outer Joins may also be specified with ODBC extensions as follows:

? SQLEXEC(1, [SELECT authors.*, titleauthor.* FROM {oj authors LEFT OUTER JOIN titleauthor ON authors.au_id = titleauthor.au_id}] )

Stored Procedures

One of the best ways to improve application performance is to implement server side functionality using stored procedures. These can be called easily using the SQLEXEC command and often return results sets.

The PUBS database has a stored procedure which can be called with a SQLEXEC to return a results set of authors with a particular royalty rate:

m.ln_handle SQLCONNECT('conpubs')

m.ln_exec = SQLEXEC(m.ln_handle, [exec byroyalty 40])

The stored procedure may also be called with a parameter:

m.ln_royalty = 40= SQLEXEC(1, [exec byroyalty ?m.ln_royalty])

 

Some stored procedures may return values by reference. The following example requires three integer parameters. The first parameter is passed by reference and receives the result of adding the remaining two parameter values together.

The parameter may be passed by reference, as an OUTPUT parameter, using the ODBC extensions for calling a stored procedure as shown below:

m.ln_result = 0

m.ln_exec = SQLExec(1, "{CALL stpmathtutor (?@m.ln_result, 2, 4 )}" )

Be careful when using output parameters with asynchronous queries as the parameter may not be updated until the final records are retrieved.

Stored procedures often return an integer value to indicate the success status. I have yet to find a method to obtain this value using ODBC. You need to use ADO to get the result back from a stored procedure

 

18/11/2017 06:27:56