3.20 Sub-string expressions
Only INFORMIX supports sub-string specification with square brackets:
SELECT * FROM item WHERE item_code[1,4] = "XBFG"However, most database servers support a function that extracts sub-strings from a character string:
| Database Server Type | Supports col[x,y] sub-strings? | Provides sub-string function? |
| GeneroDB | Yes | SUBSTR(expr,start,length) |
| IBM DB2 UDB (Unix) | No | SUBSTR(expr,start,length) |
| Informix (1) | Yes | SUBSTR(expr,start,length) |
| Microsoft SQL Server (2) | No | SUBSTRING(expr,start,length) |
| MySQL | No | SUBSTR(expr,start,length) |
| Oracle Database Server | No | SUBSTRING(expr,start,length) |
| PostgreSQL | No | SUBSTRING(expr FROM start FOR length ) |
| Sybase ASA | No | SUBSTR(expr,start,length) |
Warning: INFORMIX allows you to update some parts of a [VAR]CHAR column by using the sub-string specification (
UPDATE tab SET col[1,2] ='ab' ). This is not possible with other databases.Review the SQL statements using sub-string expressions and use the database specific sub-string function.
You could also create your own SUBSTRING() user function in all databases that do not support this function, to have a common way to extract sub-strings. In Microsoft SQL Server, when you create a user function, you must specify the owner as prefix when using the function. Therefore, you should create a SUBSTRING() user function instead of SUBSTR().
3.21 Using ROWIDs
Rowids are implicit primary keys generated by the database engine. Not all database servers support rowids:
| Database Server Type | Rowid keyword? | Rowid type? |
| GeneroDB | ROWID | INTEGER |
| IBM DB2 UDB (Unix) | none | none |
| Informix (1) | ROWID | INTEGER |
| Microsoft SQL Server (2) | none | none |
| MySQL | none | none |
| Oracle Database Server | ROWID | CHAR(18) |
| PostgreSQL | OID | internal type |
| Sybase ASA | none | none |
Warning: INFORMIX fills the SQLCA.SQLERRD[3] register with the ROWID of the last updated row. This register is an INTEGER and cannot be filled with rowids having CHAR(*) type.
Search for ROWID and SQLCA.SQLERRD[3] in your code and review the code to remove the usage of rowids.
3.22 MATCHES operator
The MATCHES operator allows you to scan a string expression:
SELECT * FROM customer WHERE customer_name MATCHES "A*[0-9]"Here is a table listing the database servers which support the MATCHES operator:
| Database Server Type | Support for MATCHES operator? |
| GeneroDB | Yes |
| IBM DB2 UDB (Unix) | No |
| Informix (1) | Yes |
| Microsoft SQL Server (2) | No |
| MySQL | No |
| Oracle Database Server | No |
| PostgreSQL | No |
| Sybase ASA | No |
The MATCHES operator is specific to INFORMIX SQL and Genero db. There is an equivalent standard operator: LIKE. We recommend to replace MATCHES expressions in your SQL statements with a standard LIKE expression. MATCHES uses
* and ? as wildcards. The equivalent wildcards in the LIKE operator are % and _. Character ranges [a-z] are not supported by the LIKE operator.Remark: The BDL language provides a MATCHES operator which is part of the runtime system. Do not confuse this with the SQL MATCHES operator, used in SQL statements. There is no problem in using the MATCHES operator of the BDL language.
Warning: A program variable can be used as parameter for the MATCHES or LIKE operator, but you must pay attention to blank padding semantics of the target database. If the program variable is defined as a CHAR(N), it is filled by the runtime system with trailing blanks, in order to have a size of N. For example, when a CHAR(10) variable is assigned with "ABC%", it contains actually "ABC%<6 blanks>". If this variable is used as LIKE parameter, the database server will search for column values matching "ABC" + some characters + 6 blanks. To avoid automatic blanks, use a VARCHAR(N) data type instead of CHAR(N).
3.23 GROUP BY clause
Some databases allow you to specify a column index in the GROUP BY clause:SELECT a, b, sum(c) FROM table GROUP BY 1,2This is not possible with all database servers:
| Database Server Type | GROUP BY colindex, ... ? |
| GeneroDB | No |
| IBM DB2 UDB (Unix) | No |
| Informix (1) | Yes |
| Microsoft SQL Server (2) | No |
| MySQL | Yes |
| Oracle Database Server | No |
| PostgreSQL | Yes |
| Sybase ASA | No |
Search for GROUP BY in your SQL statements and use explicit column names.
3.24 LENGTH() function
Not all database servers support the LENGTH() function, and some have specific behavior:
| Database Server Type | Length function? | Counts trailing blanks for CHAR() columns? | Return value when NULL |
| GeneroDB | LENGTH(expr) | No | NULL |
| IBM DB2 UDB (Unix) | LENGTH(expr) | Yes | NULL |
| Informix (1) | LENGTH(expr) | No | NULL |
| Microsoft SQL Server (2) | LEN(expr) | No | NULL |
| MySQL | LENGTH(expr) | No | NULL |
| Oracle Database Server | LENGTH(expr) | Yes | NULL |
| PostgreSQL | LENGTH(expr) | Yes | NULL |
| Sybase ASA | LENGTH(expr) | No | NULL |
Search for LENGTH in your SQL statements and review the code of the database-specific function. You could also define your own LEN() user function to have a common function in all databases. In Microsoft SQL Server, when you create a user function, you must specify the owner as prefix when using the function. Therefore, you should create a LEN() user function instead of LENGTH().
Remark: The BDL language provides a LENGTH built-in function which is part of the runtime system. Do not confuse this with the SQL LENGTH() function, used in SQL statements. There is no problem in using the LENGTH() function of the BDL language. However, the LENGTH() function of the language returns zero when the string expression is NULL.
3.25 SQL Interruption
With Informix, it is possible to interrupt a long-running query if the SQL INTERRUPT ON option is set by the Genero program. The database server returns SQLCODE -213, which can be trapped to detect a user interruption.
01MAIN02DEFINE n INTEGER03DEFER INTERRUPT04OPTIONS SQL INTERRUPT ON05DATABASE test106WHENEVER ERROR CONTINUE07-- Start long query (self join takes time)08-- From now on, user can hit CTRL-C in TUI mode to stop the query09SELECT COUNT(*) INTO n FROM customers a, customers b10WHERE a.cust_id <> b.cust_id11IF SQLCA.SQLCODE == -213 THEN12DISPLAY "Statement was interrupted by user..."13EXIT PROGRAM 114END IF15WHENEVER ERROR STOP16 ... 17END MAIN
When SQL Interruption is available for a database server type, Genero database drivers implement it to behave as in Informix, converting the native error to the code -213. Not all database servers support SQL interruption:
| Database Server Type | SQL Interruption API | SQL error code for interrupted query |
| GeneroDB 3.80 | SQLCancel() | Native error -30005 |
| IBM DB2 UDB 9.x | SQLCancel() | Native error -952 |
| Informix | sqlbreak() | Native error -213 |
| Microsoft SQL Server 2005 (SNC driver only) | SQLCancel() | SQLSTATE HY008 |
| MySQL | N/A | ? |
| Oracle Database Server 8.x, 9.x, 10.x | OCIBreak() | Native error -1013 |
| PostgreSQL 8.x | PQCancel() | SQLSTATE 57014 |
| Sybase ASA | N/A | ? |
沒有留言:
張貼留言