網頁

2010年11月17日 星期三

Genero Sub-string expressions

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 TypeSupports col[x,y] sub-strings?Provides sub-string function?
GeneroDBYesSUBSTR(expr,start,length)
IBM DB2 UDB (Unix)NoSUBSTR(expr,start,length)
Informix (1)YesSUBSTR(expr,start,length)
Microsoft SQL Server (2)NoSUBSTRING(expr,start,length)
MySQLNoSUBSTR(expr,start,length)
Oracle Database ServerNoSUBSTRING(expr,start,length)
PostgreSQLNoSUBSTRING(expr FROM start FOR length )
Sybase ASANoSUBSTR(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 TypeRowid keyword?Rowid type?
GeneroDBROWIDINTEGER
IBM DB2 UDB (Unix)nonenone
Informix (1)ROWIDINTEGER
Microsoft SQL Server (2)nonenone
MySQLnonenone
Oracle Database ServerROWIDCHAR(18)
PostgreSQLOIDinternal type
Sybase ASAnonenone
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 TypeSupport for MATCHES operator?
GeneroDBYes
IBM DB2 UDB (Unix)No
Informix (1)Yes
Microsoft SQL Server (2)No
MySQLNo
Oracle Database ServerNo
PostgreSQLNo
Sybase ASANo
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,2
This is not possible with all database servers:
Database Server TypeGROUP BY colindex, ... ?
GeneroDBNo
IBM DB2 UDB (Unix)No
Informix (1)Yes
Microsoft SQL Server (2)No
MySQLYes
Oracle Database ServerNo
PostgreSQLYes
Sybase ASANo
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 TypeLength function?Counts trailing blanks for CHAR() columns?Return value when NULL
GeneroDBLENGTH(expr)NoNULL
IBM DB2 UDB (Unix)LENGTH(expr)YesNULL
Informix (1)LENGTH(expr)NoNULL
Microsoft SQL Server (2)LEN(expr)NoNULL
MySQLLENGTH(expr)NoNULL
Oracle Database ServerLENGTH(expr)YesNULL
PostgreSQLLENGTH(expr)YesNULL
Sybase ASALENGTH(expr)NoNULL
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.
01 MAIN
02   DEFINE n INTEGER
03   DEFER INTERRUPT
04   OPTIONS SQL INTERRUPT ON
05   DATABASE test1
06   WHENEVER ERROR CONTINUE
07   -- Start long query (self join takes time)
08   -- From now on, user can hit CTRL-C in TUI mode to stop the query
09   SELECT COUNT(*) INTO n FROM customers a, customers b
10        WHERE a.cust_id <> b.cust_id
11   IF SQLCA.SQLCODE == -213 THEN
12    DISPLAY "Statement was interrupted by user..."
13      EXIT PROGRAM 1
14   END IF
15   WHENEVER ERROR STOP
16   ...
17 END 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 TypeSQL Interruption APISQL error code for interrupted query
GeneroDB 3.80SQLCancel()Native error -30005
IBM DB2 UDB 9.xSQLCancel()Native error -952
Informixsqlbreak()Native error -213
Microsoft SQL Server 2005 (SNC driver only)SQLCancel()SQLSTATE HY008
MySQLN/A?
Oracle Database Server 8.x, 9.x, 10.xOCIBreak()Native error -1013
PostgreSQL 8.xPQCancel()SQLSTATE 57014
Sybase ASAN/A?

沒有留言:

張貼留言