Chapter 12
Other Data Changes

VFP has the fastest database engine of all desktop application databases. As Microsoft enhances other areas of the product, it makes sense they would also enhance the database engine. This chapter outlines some miscellaneous data changes included
in VFP 9.

Transaction support

One major enhancement in VFP 9 is the support of transactions for free tables and cursors (created with CREATE CURSOR). Two new functions were added for this purpose, MAKETRANSACTABLE() and ISTRANSACTABLE(). MAKETRANSACTABLE() accepts a work area number or alias as a parameter and returns a logical value indicating whether transactions are supported for the table or cursor. Once transactions are turned on,
use BEGIN TRANSACTION, END TRANSACTION, and ROLLBACK to manage the transactions for the free table or cursor in the same way they work with DBC-bound tables
and views.

MAKETRANSACTABLE() turns on transactions for all aliases of a table in the current data session. If the table or cursor is open in another data session when MAKETRANSACTABLE() is executed, the error 2191, “File is in use in another data session,” occurs. Therefore, transactions must be enabled the first time a table or cursor is opened if it is to be opened in multiple data sessions. Once transactions are activated for a table, all subsequent calls to MAKETRANSACTABLE() does not generate an error, but it will not do anything because transactions are already enabled.

Transactions on free tables and cursors are only supported when using row buffering. If the table or cursor is table buffered when issuing MAKETRANSACTABLE(), the error 1579, “Command cannot be issued on a table with cursors in table buffering mode,” occurs.

There is no way to turn off transactions for free tables or cursors once activated without closing the table in all data sessions where it is open. The program code in Listing 3 (in the “AUSED()” section) might be helpful in doing this.

The ISTRANSACTABLE() function returns a logical value indicating whether or not the alias supports transactions. Pass the work area number or alias to this function. This function works for all open aliases, not just free tables. It always returns .T. for tables contained in a database container.

These new functions are supported through the VFP OLEDB provider in addition to the VFP development environment and runtime.

BLANKing data

The BLANK command is used to clear out the data in some or all fields in the currently selected record. This command has been enhanced to allow for resetting fields to their default value as specified in the database container rather than to an empty value. Two new optional arguments have been added for this feature, DEFAULT and AUTOINC. The new syntax for this command is:

BLANK [FIELDS FieldList] [DEFAULT [AUTOINC]] [Scope] [FOR lExpression1]

[WHILE lExpression2] [NOOPTIMIZE] [IN nWorkArea | cTableAlias]

The DEFAULT clause indicates the fields should be initialized to the default value defined
in the DBC. It applies to the fields list in the FieldList argument or all fields if the FieldList argument is absent. The DEFAULT clause alone will not reset autoincrementing fields to
their default value. In order to reset autoincrementing the AUTOINC clause must be included in addition to the DEFAULT clause. This forces autoincrementing fields to be initialized to
the next available value. Listing 1 below shows an example using the new clauses on the BLANK command.

The Developer Download files, available at www.hentzenwerke.com, include the database, Chapter12.dbc, table Names.dbf, and program BlankExample.PRG.

Listing 1. Blank fields and reset generated fields to their Default value.

SET MULTILOCKS ON

USE Names

INSERT INTO Names ;

(cName) ;

VALUES ;

([Tom])

INSERT INTO Names ;

(cName, dAdded) ;

VALUES ;

([Mary], DATE(2004,12,31))

BROWSE

BLANK DEFAULT AUTOINC

BROWSE

Because the autoincrementing fields must lock the table header, SET MULTILOCKS must be on if the table is opened SHARED. If SET MULTILOCKS is OFF, error 2183, “Operation requires that SET MULTILOCKS is set to ON,” is thrown. In addition, the DEFAULT clause can be used without the AUTOINC clause, but AUTOINC requires DEFAULT.

The last generated value

VFP 8 introduced autoincrementing fields. However, if you work with local views or remote views of VFP tables, there is no way to know the last value generated until the data is saved and the view requeried. VFP 9 includes a new function, GETAUTOINCVALUE(), to return the last value generated for an autoincrement field. The syntax for this new function is:

GETAUTOINCVALUE( [ nDataSessionNumber | 0 ] )

You can pass a data session number to the function if the incremented value is in a different data session from the current one. If no value is passed, the current data session is assumed. Passing a value of zero returns the last incremented value within the currently executing program, procedure, method, or function rather than the data session. Listing 2 demonstrates using the new GETAUTOINCVALUE() function.

The program GetLastValue.PRG and supporting files are included
with the Developer Download files for this chapter, available at www.hentzenwerke.com.

Listing 2. Use GetAutoIncValue to retrieve the last value generated for a parent view in order to populate the foreign key for a child view.

LOCAL ;

liParentId AS Integer, ;

llEndTrans AS Boolean

llEndTrans = .F.

CLOSE ALL

SET MULTILOCKS ON

OPEN DATABASE Chapter12

USE NamesView NODATA

CURSORSETPROP("Buffering", 3)

SELECT 0

USE ChildView NODATA

CURSORSETPROP("Buffering", 5)

INSERT INTO NamesView ;

(cName) ;

VALUES ;

([Dee])

INSERT INTO ChildView ;

(cChildName) ;

VALUES ;

([Tiffani])

INSERT INTO ChildView ;

(cChildName) ;

VALUES ;

([Erika])

BEGIN TRANSACTION

IF TABLEUPDATE(0,.F.,"NamesView")

liParentId = GETAUTOINCVALUE()

IF NOT ISNULL(liParentId)

REPLACE ALL iParentId WITH liParentId IN ChildView

llEndTrans = TABLEUPDATE(1, .F., "ChildView")

ENDIF

ENDIF

IF llEndTrans

END TRANSACTION

ELSE

ROLLBACK

ENDIF

SELECT NAMES

BROWSE

SELECT Children

BROWSE

REFRESHing your data

The SET REFRESH command controls how often VFP data stored in buffers is updated based on the physical data. It does so in two ways. First, it controls how often a BROWSE, EDIT, or CHANGE window is updated with current data. Secondly, it controls how often the local workstation buffers are updated with current data changes. Prior to VFP 9, the number of seconds ranged from 0 to 3600 but could only be whole numbers. VFP 9 changes this limitation for the second clause on the SET REFRESH command, the one that controls
buffer updates. This value can now range between .001 and 3600 seconds and does not
have to be a whole number. In addition, use -1 to specify always reading data from disk
and never buffered. Keep in mind that a lower setting can reduce the overall performance
of the application.

Because the SET REFRESH clauses changed, the VFP development environment has also changed to support this new clause.

Figure 1 shows the Data page of the Options dialog. You will notice the Table refresh interval setting now allows fractional values.

Figure 1. Table refresh interval, specified in seconds, now supports decimal values.

In addition to the changes to SET REFRESH, the CURSORSETPROP() function now includes a Refresh property. This property allows you to establish different refresh settings for each open cursor or for all newly opened cursors within a data session. The syntax for CURSORSETPROP() is:

CURSORSETPROP( cProperty [, eExpression ] [, cTableAlias | nWorkArea ] )

If the work area parameter is passed, the function changes the refresh value just for the specified cursor. If the last parameter is 0, the refresh rate for the existing cursors in the data session do not change, but all newly opened cursors use the new refresh rate.

The default value for the Refresh property is -2. This value indicates the cursor should refresh at the rate specified by the SET REFRESH command. Similarly, if SET REFRESH is 0, the refresh setting of CURSORSETPROP() is disregarded. This is because SET REFRESH TO 0 indicates the data in the buffers is always used and not updated by the physical data.

Flushing your data

The FLUSH command has been enhanced for VFP 9. Prior to this version of VFP, the FLUSH command had no clauses. When FLUSH was issued, VFP would flush the buffers at the next convenient time. Now, there are two clauses on the FLUSH command.

FLUSH [ [ IN nWorkArea | cTableAlias ] | [ cFileSpec ] ] [ FORCE ]

The first clause specifies the work area or alias for the cursor to be flushed. Additionally, this clause can be the name of an individual file to flush, such as an index (CDX file). It can also include files open with low-level file functions.

The second clause FORCE passes the flush call to the Windows FlushFileBuffers() function in order to have the flush happen at once. Temporary files and ReadOnly files are not flushed using this option because there cannot be local changes to these files.

If no clauses are included on the command, VFP flushes the contents of all tables, memos, and indexes in the current data session as soon as possible.

The FFLUSH() function also has a new parameter.

FFLUSH( nFileHandle [, lForce ] )

Passing .T. for the lForce parameter tells VFP to flush the file immediately using the Windows API. This is the same behavior as the FORCE option on the FLUSH command.

AUSED()

AUSED() is improved for VFP 9. An optional third parameter was added to specify the array only include rows for aliases of a specified table or view.

Before the addition of the cTableName parameter, the current datasession was assumed when the nDataSessionNumber parameter was omitted. In order to specify the current datasession and also specify the cTableName parameter, pass .NULL. for the nDataSessionNumber parameter.

The new syntax for AUSED() is:

AUSED( ArrayName [, nDataSessionNumber [, cTableName ] ] )

The new parameter, cTableName, can be in one of several formats:

· DBC!Table

· DBC!View

· Path\DatabaseName!TableName

· Path\DatabaseName!ViewName

· Table – In the current DBC

· View – In the current DBC

· Table (with Path if necessary)

This parameter could prove useful when you need to close all open instances of a table so the table can be opened EXCLUSIVE for another purpose such as pack or reindex. The program in Listing 3 shows an example using this new parameter.

The program Ex_Aused.PRG is included with the Developer Download files for this chapter, available at www.hentzenwerke.com.

Listing 3. Using the AUSED() function to close all instances of the Customer table in all data sessions.

LOCAL ;

lnSessions AS Integer, ;

laSessions[1], ;

lnI AS Integer, ;

lnCursors AS Integer, ;

laCursors[1], ;

lnJ

lnSessions = ASESSIONS(laSessions)

FOR lnI = 1 TO lnSessions

SET DATASESSION TO (laSessions[lnI])

lnCursors = AUSED(laCursors, lnI, "Customer")

FOR lnJ = 1 TO lnCursors

USE IN (laCursors[lnJ, 1])

ENDFOR

ENDFOR

Production code would require a little more testing to insure there are no changes pending before closing the table.

SET TABLEPROMPT

Many Visual FoxPro commands such as SQL – SELECT require a table be open or at least in the current Path. If the table is not available, the Open File Dialog is displayed so the user can select the table. This is not always desirable in an application, so a new SET command, SET TABLEPROMPT, was added to control the display of the Open File Dialog. The valid values are On or Off.

If SET TABLEPROMPT is ON, the open file dialog will display. If SET TABLEPROMPT is OFF, it will not display. SET(“TABLEPROMPT”) can be used to determine the current setting of SET TABLEPROMPT.

Care should be taken when using SET TABLEPROMPT ON in COM server applications as it could place the server in a modal wait state. SET TABLEPROMPT should probably be set to OFF or SYS(2335) used to establish unattended server mode for the application.

SET(“REPROCESS”)

The SET REPROCESS command specifies how many times or for how long a record lock is attempted. Prior to VFP 9, you could determine the numeric value for SET REPROCESS using the SET() function, but not the type, attempts, or time. VFP 9 adds two new parameter values to SET(“REPROCESS”). Passing 2 for the second parameter returns the type of REPROCESS for the current data session. Passing 3 returns the setting type for the system data session. The function returns 0 when the setting type is attempts and 1 when the setting type is seconds. The following code illustrates these new parameter values.

SET REPROCESS TO 10

? SET("REPROCESS") && Returns 10 which is the value.

? SET("REPROCESS", 2) && Returns 0 which is the type.

SET REPROCESS TO 10 SECONDS

? SET("REPROCESS", 2) && Returns 1 which is the type.

Data conversion

Several enhancements have been made in VFP 9 to assist in data conversion issues. The first change is to the ALTER TABLE command. This command now correctly converts memo data to character data. In VFP 8, the ALTER TABLE command does not convert memo data to character data at all, resulting in loss of all data. Similarly, memo data values are correctly converted to character data when making structure changes using the Table Designer. If the character field is not defined large enough to store all of the data in the memo field, the extra characters are truncated when the data is converted.

In VFP 8, the BINTOC() and CTOBIN() functions are used to convert integer values to binary character values and vice versa. These functions are extremely useful for numeric indexes because the character representation can be smaller and faster. Due to user requests, these two functions have been enhanced to support more than just integer values. For VFP 9, all numeric data types are supported. For more information on the changes to BINTOC() and CTOBIN(), see Chapter 14 “Language Improvements.” The example in Listing 4 shows how you might use BINTOC()when indexing and SEEKING values.

The program Ex_Bintoc.PRG is included with the Developer Download files for this chapter, available at www.hentzenwerke.com.

Listing 4. An indexing example using BINTOC()

CREATE TABLE indextest (iId I AUTOINC, nQTY N(5, 2), yAmount Y)

INDEX ON BINTOC(iId) TAG Id

INDEX ON BINTOC(nQty, 8) TAG Qty

INDEX ON BINTOC(yAmount, 8) TAG Amount

INSERT INTO IndexTest ;

(nQty, ;

yAmount ) ;

VALUES ;

(10.2, ;

$299.95)

? SEEK(1, "IndexTest", "Id") && Generates an error.

? SEEK(BINTOC(1), "IndexTest", "Id") && Returns .T.

? SEEK(10.2, "IndexTest", "Qty") && Generates an error.

? SEEK(BINTOC(10.2, 8), "IndexTest", "QTY") && Returns .T.

? SEEK(299.95,"IndexTest", "Amount") && Generates an error.

? SEEK(BINTOC($299.95, 8), "IndexTest", "Amount") && Returns .T

With many options available now when indexing numeric data, Microsoft has the following recommendations when using BINTOC() for index expressions:

1. When the index should be 8 bytes, use 8 as the parameter rather than “B”.

2. Only use 4 when the 4 byte index expression is being generated from an integer data type. Otherwise, “F” should be used for floating point types.

International issues

An nCodePage clause has been added to the CREATE TABLE and CREATE CURSOR commands. This allows the code page to be programmatically specified as the cursor or table is created. Prior to this enhancement, the code page was established when a table or cursor was created based on the currently active codepage, which can only be specified through the CODEPAGE = setting in the Configuration file (Config.FPW). Therefore, creating multiple tables using different code pages was very cumbersome before now. In VFP 9, code like the following can be used.

CREATE CURSOR Fees CODEPAGE = 1251 (cName C(10), yAmount Y)

The VFP Help file has a complete listing of the supported code pages. If an invalid code page number is specified, error 1914, “Code page number is invalid,” is generated.

Long type name support

When referring to VFP data types, only single character references were supported before VFP 9. In an attempt to make the VFP data language consistent with other languages, VFP 9 introduces long type name support. The long name for each data type is the one you see in the Type combobox found in the Table Designer. The following now support the full data type name along with the single character reference:

· ALTER TABLE

· CREATE CURSOR

· CREATE TABLE

· CREATE FROM

· CAST()

· CursorSchema Property of CursorAdapter

· Datatype property of XML Field

For example:

CREATE CURSOR TempCursor ;

(cName Character(30), ;

iId Integer, ;

yAmount Currency)

In addition, Character, Numeric, and Integer field types support a third notation; Char, Num, and Int respectively.

If the long name supplied is not valid, VFP uses just the first letter as the field type. If the first letter is not a valid field data type, the error “Function argument value, type or count is invalid” occurs.

The example below generates an error because Alphabetic and A are not valid field
data types.

CREATE CURSOR TempCursor ;

(cName Alphabetic(30), ;

iId Integer, ;

yAmount Currency)

This example does not generate an error; however, the yAmount field is created as a memo field because Money is not a valid data type.

CREATE CURSOR TempCursor ;

(cName Character(30), ;

iId Integer, ;

yAmount Money)

Behavior changes

Microsoft has always done a terrific job of making VFP backward compatible. Applications written in prior versions of VFP should run in the next release without change. However, upon occasion some changes could cause existing applications to break. This section lists two behaviors that may cause your VFP 8 or earlier applications to no longer work as expected.

TABLEREVERT()

The behavior of TABLEREVERT() was changed in VFP 8 SP1. It prohibits the use of TABLEREVERT() while TABLEUPDATE() is in progress and when a CursorAdapter
class is being used. The code in Listing 5 works under VFP 8 and no longer works under
after VFP 8 SP1:

The program, No_TableRevert.PRG is included with the Developer Download files for this chapter, available at www.hentzenwerke.com.

Listing 5: This program would not generate an error under VFP8, but will generate an error when using VFP8 SP1 or later.

loAdapter = CREATEOBJECT('MyAdapter')

loAdapter.CursorFill()

BROWSE && Just to make sure we have a cursor.

INSERT INTO Names_CA ;

(cName) ;

VALUES ;

([Fred])

? TABLEUPDATE(1)

DEFINE CLASS MyAdapter AS CursorAdapter

BufferModeOverride = 5

CursorSchema = [IID I, CNAME C(30), DADDED D]

DataSourceType = [Native]

SelectCmd = [select iId, cName, dAdded from Names]

Tables = [Names]

Alias = [Names_CA]

BreakOnError = .T.

FUNCTION BeforeInsert

LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd

TABLEREVERT(.T.)

RETURN DODEFAULT(cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd)

ENDFUNC

ENDDEFINE

Indexes

VFP has never supported variable length indexes. Indexes are always padded to the maximum length as defined by field sizes and the index expression. In some cases, the length of the index could change from session to session, such as with date and datetime values converted to strings. If SET CENTURY is ON, the value is longer than if SET CENTURY is OFF. The program in Listing 6 outlines an example of this.

The program, NewIndexBehavior.PRG is included with the Developer Download files for this chapter, available at www.hentzenwerke.com.

Listing 6. This program would not generate an error under VFP version 8 and earlier, but does generate an error in VFP9.

SET CENTURY OFF

DELETE FILE AppSecurity.dbc

DELETE FILE AppSecurity.dct

DELETE FILE AppSecurity.dcx

DELETE FILE Applogin.dbf

DELETE FILE Applogin.cdx

CREATE DATABASE AppSecurity

CREATE TABLE AppLogin ;

(cUserId C(30), ;

tLoggedIn T DEFAULT DATETIME(), ;

tLoggedOut T)

INDEX ON UPPER(cUserId) + TTOC(tLoggedIn) TAG UniqueKey

INSERT INTO AppLogin ;

(cUserId) ;

VALUES ;

([TMF])

INSERT INTO AppLogin ;

(cUserId) ;

VALUES ;

([TEG])

SET CENTURY ON

INSERT INTO AppLogin ;

(cUserId) ;

VALUES ;

([DH])

USE IN AppLogin

CLOSE DATABASES ALL

Under VFP 8, this program runs without error. Under VFP 9, the last INSERT INTO stops with error 2199, “Error building key for index <index file name> tag <tag name>,” and the data is not saved.

In this particular example, the problem is corrected by passing 1 as the second parameter to TTOC() so the function returns a value usable in an index. In other situations, the index expression needs to be changed to one that will not return different size values, depending on the system configuration.

Summary

Many times it’s the little things that make our programming lives much easier. Once again, the VFP team has done a great job of adding data-related enhancements that make our applications easier to program, perform better, and fit better in many different environments.

Updates and corrections for this chapter can be found on Hentzenwerke’s website, www.hentzenwerke.com. Click “Catalog” and navigate to the page for this book.