Pythian Group

MySQL does not follow the ANSI SQL standard for quoting. MySQL’s default quoting behavior is that either single or double quotes can be used to quote a string (this gets me into trouble when I work with Oracle databases, as double quotes do not indicate a string!).

mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

mysql> SELECT "alive";
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

Bare words are dealt with in context; in this case, a bare word would be parsed as a column name:

mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

Backquotes are the way MySQL escapes table names. So, if you want a reserved word, number or operator to be the name of an object (ie, a table named “1″ or a column named “date”) you need to use backquotes to avoid a syntax error….for example:
Read the rest of this entry . . .

After setting up dg4odbc to connect to a SQL Server database (read ‘Tips on Using dg4odbc on 64-bit Linux’), I spent last week trying to get refreshes of materialized views to work in 11g, using the same DDL as in 8i. I was very frustrated to see the refreshes break in 11g.

The DDL gives no obvious clues. The remote table:

desc MYTABLE@SQLSERVER
Name                           Null?    Type
------------------------------ -------- ----------------------------
MY_ID                          NOT NULL NUMBER(5)
MY_DESC                        NOT NULL VARCHAR2(40)

SQL> create materialized view MV_MYTABLE
2 as select *
3 FROM   MYTABLE@SQLSERVER
4 WHERE  MY_ID IS NOT NULL
5 AND    MY_DESC IS NOT NULL;

Materialized view created.

SQL> begin
2  DBMS_SNAPSHOT.REFRESH( 'MV_MYTABLE','C');
3  end;
4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from SQLSERVER
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

The reason, it turns out, is the ANSI-compliance of dg4odbc. The remote table has a row where the column MY_DESC is storing a '' (a zero-length string).

SQL Server treats the zero-length string as different from NULL. Oracle 11g treats zero-length strings as the same as NULL. Read the rest of this entry . . .