Differences between the SQL WHERE clause for a personal database and a file database

--------------------------------- The SQLWHERE clause syntax used by ArcGIS10 to help Query file geographic databases is the same as the syntax used to query coverage, shapefile, and other file-based data sources, but some functions are added, for example, subquery is supported. Therefore, the WHERE clause syntax is different from that of a personal database. A

----------------------------------- The SQL WHERE clause syntax used by ArcGIS10 to help Query file geographic databases is the same as the syntax used to query coverage, shapefile, and other file-based data sources, but some functions are added, for example, subquery is supported. Therefore, the WHERE clause syntax is different from that of a personal database. A

----------------------------------- Excerpt from ArcGIS10 help

The SQL WHERE clause syntax used to query a file geographic database is the same as that used to query coverage, shapefile, and other file-based data sources. However, some functions are added, such as support for subqueries. Therefore, the WHERE clause syntax is different from that of a personal database. The create SQL expression dialog box in ArcGIS helps you use the correct WHERE clause syntax for the data to be queried, because they list field names and values with appropriate delimiters. You can also select related keywords and operators. However, if a WHERE clause is defined for a layer in the personal geographic database, once the source data of this layer is moved to the file geographic database, this clause may not work for the same layer. The following are the possible reasons why the WHERE clause does not work:

• For personal geographic databases, field names are enclosed in square brackets, but for file geographic databases, they are enclosed in double quotation marks.
• In a personal geographic database, * represents any number of characters. What are the wildcards? Represents a character. The file geographic database uses % and _ respectively _.
• String search in a personal geographic database is case-insensitive, but case-sensitive in a file geographic database.
• The personal geographic database uses UCASE and LCASE to convert strings in Case sensitivity, but the file geographic database uses UPPER and LOWER.
• In the personal geographic database, use # To separate dates and times, but in the file geographic database, they are separated by wordsDate.

WHERE clause syntax for personal geographic databases	Equivalent syntax used for file geographic database
[STATE_NAME] = 'california'	"STATE_NAME" = 'california'
[OWNER_NAME] LIKE '? Atw.smith'	"OWNER_NAME" LIKE '_ atw.smith'
[STATE_NAME] = 'california '(case-insensitive search is required)	LOWER ("STATE_NAME") = 'california'
UCASE ([LAST_NAME]) = 'Jones'	UPPER ("LAST_NAME") = 'Jones'
[DATE_OF_BIRTH] = #19:30:00 06-13-2001 #	"DATE_OF_BIRTH" = date '2017-06-13 19:30:00'

These examples demonstrate how to change the SQL statements used in the personal geographic database to make them take effect in the file geographic database.

Another reason why the WHERE clause does not work is that the file geographic database supports fewer operators and functions than the personal geographic database, and the file geographic database only provides limited support for subqueries. However, this is unlikely because the WHERE clause does not work. The file geographic database supports the WHERE clause function that most users may need.

---------------------------- ArcObject development -------------------------

If you write an application using ArcObjects and want to switch the data accessed by the application from the personal geographic database to the file geographic database, consider the following:

• Update the workspace factory so that the application operates on the new data source. Change the workspace factory from AccessWorkspaceFactory to FileGDBWorkspaceFactory, and change the geographic database Extension from. mdb to. gdb.
• If your application uses SQL, you may need to update the syntax to make it work for the file geographic database:
  • As mentioned above, the SQL WHERE clause Syntax of a file geographic database differs from that of a personal geographic database. If the application uses QueryFilter or QueryDef, refer to the previous discussions on the WHERE clause to learn which changes may need to be made.
  • File geographic databases do not support all functions and functions that can be used for personal geographic databases. In ArcGIS 9.2, the most common functions not supported BY the file geographic database include DISTINCT, group by, and order, the set functions AVG, COUNT, MIN, MAX, and SUM are not supported outside the subquery. Some functions may be supported in future versions.
  • The file geographic database only supports limited QueryDef connections. Child fields can only contain simple column names. aliases, expressions, and functions are not supported. The FROM clause can only contain simple table names.
  • In the file geographic database, the ExecuteSQL support for INSERT and UPDATE is limited to simple statements that only contain text values. In INSERT and UPDATE statements, compound expressions (such

    SET RENTAL_PRICE = (RENTAL_PRICE - 1.00)

    ) And subqueries. The DELETE statement is unrestricted. It supports any WHERE clause supported by the file geographic database.
  • If the application contains subqueries, these subqueries may not work for the file geographic database because their support is limited. For more information, see SQL reference.
• To maximize data transmission performance, consider using the load-only mode when loading a large number of records. For more information, see the "performance prompt" below ".

In addition to these differences, ArcObjects works in the same way as personal geographic databases.