Appendix B: Stored procedure reference
Here are the 14 basic stored procedures that we implemented for both the local and remote versions of the dbc. In most cases, the local version of the stored procedure is just a stub or placeholder so that when the application makes a call to, say, sp_WaitWhileConnectionBusy(), we don't get a "procedure not found error".
sp_GetNewID( cEntity )
All values for new machine-generated primary keys are obtained via this function. This method returns the next available integer unique id for the table or key series specified by cEntity.
|
Local: |
Regular VFP record locking on a table called UNIQUEID is used to ensure multi-user safety. |
|
Remote: |
This method uses sp_GetAvailableConnectionHandle() to obtain a reference to an open connection to SQL Server. If it finds one, it uses SQLExec() to call the remote stored procedure sp_getnewid, and returns the value of the field in the result cursor. This method will invoke sp_GetNewConnectionHandle() if required in order to obtain a handle. |
sp_SQLExecute( cSQL, cResultCursor, iFlags )
This method executes a SQL statement and creates a cursor specified by cResultCursor containing the results. Particularly noteable is that the remote version translates SQL statements that are legal in VFP but not understood by SQL Server into a SQL Server friendly version before executing it.
|
Local: |
This method adds "INTO CURSOR" + cResultCursor to the cSQL statement and macro-executes it. 1 is returned if successful. The iFlags parameter is ignored. |
|
Remote: |
This method scans the statement in cSQL and makes the following substitutions: == with =, .F. with 0, .T. with 1, and double-quotes to single quotes. Then if possible it obtains a reference to a shared connection handle, and invokes SQLExec(), optionally checking that the connection is not busy. Possible flags include: SQLEXEC_FORCE_NEW_CONNECTION and SQLEXEC_NO_BUSY_CHECK. |
sp_Pack( cTable )
This method returns .T. if it can remove all deleted records from the specified table.
|
Local: |
Invokes sp_ExclusiveAction() to PACK the table. |
|
Remote: |
This method returns .T. without doing anything. |
sp_Zap( cTable )
This method returns .T. if it can remove all records from the specified table.
|
Local: |
This method invokes sp_ExclusiveAction() to ZAP the table. |
|
Remote: |
This method returns sp_Delete( cTable ). |
sp_ExclusiveAction( cTable, cActionCode )
This method is only implemented in the local DBC stored procedures. It either PACKs or ZAPs (and could be made to REINDEX also) the specified table, ensuring first that the table can be opened exclusively. If necessary, it will temporarily switch the current SET(‘DATABASE’) setting from the local view version to the DBC that contains the actual Xbase tables in order to perform the operation. Returns .T. if successful.
sp_Delete( cTable, cForClause )
This method removes all records from the specified table that match the FOR.. clause specified by cForClause. If the second parameter is not passed, then the method removes ALL records in the table. Returns .T. if successful.
|
Local: |
This method performs similar code to sp_ExclusiveAction() in order to USE the specified table. Then it does DELETE ALL FOR &cForClause. |
|
Remote: |
This method invokes sp_SQLExecute() to execute a DELETE FROM … FOR … statement on the server. |
sp_GetLocalDate( vDateTime )
This method is used mostly when dealing with SQL Passthrough cursors that have DATETIME fields on remote data, but DATE on local data, to ensure that a DATE data type is obtained.
|
Local: |
This method returns the parameter unchanged. |
|
Remote: |
This method returns TTOD( vDateTime ) |
sp_GetDBType()
This method is used for those rare occasions when you need to check whether your application is using remote or local views. Ideally, you should never require it. The idea is to work within a framework that allows you to develop independent from the back end platform.
|
Local: |
This method returns the string "FOXPRO" |
|
Remote: |
This method returns the string "SQLSERVER" |
sp_GetAvailableConnectionHandle()
This method is used primarily by other remote stored procedures.
|
Local: |
This method does nothing but return 1. |
|
Remote: |
This method uses CURSORGETPROP(‘ConnectHandle’) on currently open remote views to try to obtain and return a positive integer representing a currently open connection handle to the SQL server. It returns –1 if it can not find one. |
sp_GetNewConnectionHandle()
This method is used primarily by other remote stored procedures.
|
Local: |
This method does nothing but return 1. |
|
Remote: |
This method uses ADBOBJECTS() to find the name of the connection in the current DBC, and returns the value returned by SQLConnect(). |
sp_IsConnectionBusy( cViewAlias )
This method can be used throughout your application whenever your code is about to do something that would result in "Error 1541: Connection is busy".
|
Local: |
This method does nothing but return .F. |
|
Remote: |
This method gets the handle from the view specified by cViewAlias (or invokes sp_GetAvailableConnectionHandle()) and performs a SQLGetProp(‘ConnectBusy’) to see if it should return .T. This method contains a loop that continues to poll the connection for its busy state, while displaying a WAIT WINDOW "Connection is busy. Please wait. Or press ESC to cancel". |
sp_WaitWhileConnectionBusy( cAlias )
|
Local: |
This method does nothing. |
|
Remote: |
This method calls sp_IsConnectionBusy(cAlias) but doesn’t bother to return any value. |
sp_SQLCancel( cView | hConnection )
|
Local: |
This method does nothing but return 1. |
|
Remote: |
This method can either be passed a connection handle directly, or a view alias (from which it obtains the connection handle with SQLGetProp()), and returns the result from SQLCancel() on the connection handle. |
IsFlagSet( iFlags, iSpecificFlag )
This method is the same for both remote and local stored procedures. It uses BITAND() to test whether the bit specified by iSpecifiedFlag is set in the number iFlags.
