Monday, October 22, 2018

Let's Be Blunt

SQL Anywhere really does do things the way they should be done. That's why the title of this blog has changed.

Another thing that's changed is the row of page links at the top: "Community" is gone because ( in order to serve you better? :) the "SAP SQL Anywhere Community" has been deleted.

The Forum link is still there. That's where the real community has always been, and remains.

Two SQL Anywhere Help links remain: DCX because that's the only way to see Version 16 and earlier, and the SAP Help Portal because it works fairly well and is a lot faster than DCX.

Plus, there are two Foxhound links: one to the Help where all the "Performance Tips" live, and one to the White Paper that shows off some of the features.

The rest are gone because they were just clutter: use the Portal search instead.

Saturday, October 6, 2018

Characteristic Errors, Revision 8

A characteristic error is a mistake so easy to make it appears you're actively encouraged to make it by the very nature of the software you're using.

Here are the latest entries...

  1. SQL Anywhere: Forgetting to code an INTO @local_variable clause on a SELECT inside a deeply-nested PROCEDURE, thus accidentally returning an additional result set to an higher FROM call, then wondering why you get SQLCODE -866 (returned a result set with a different schema than expected), or SQLCODE -873 (returned multiple result sets), or both.

  2. SQL Anywhere: Expecting to see MESSAGE TO CLIENT output with the SQL Anywhere 17 ISQL GUI. Don't bother looking, it's virtually inaccessible. Use MESSAGE TO CONSOLE instead, or revert to the version 16 ISQL which works just fine with version 17 databases.

Here's the full list...
  1. SQL: Seeing too little data, or no data at all, because a predicate in the WHERE clause effectively turned your OUTER JOIN into an INNER JOIN.

  2. SQL: Seeing too much data because a missing predicate in the WHERE clause effectively turned your INNER JOIN into a CROSS JOIN.

  3. SQL: Getting the wrong COUNT() or SUM() because you forgot to code WHERE ... IS NOT NULL, or you *did* code it when you shouldn't have.

  4. SQL: Getting the wrong answer because you forgot that, in general, NULL values [cough] suck.

  5. SQL Anywhere: Not seeing MESSAGE output because you forgot to run SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';

  6. SQL Anywhere: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.

  7. SQL Anywhere: Coding ENDIF where END IF was required, or vice versa (before Version 11).

  8. SQL Anywhere: Connecting to the wrong server because you forgot DOBROAD=NONE (before Version 12).

  9. SQL Anywhere: Forgetting the asterisk in SELECT TOP 10 FROM ...

  10. SQL Anywhere: Coding IF NOT VAREXISTS ( 'x' ) THEN ... instead of IF VAREXISTS ( 'x' ) = 0 THEN ...

  11. SQL Anywhere: Coding the wrong magic numbers 1, 2, 3, ... in the get_value() and set_value() calls in an EXTERNAL C DLL function.

  12. SQL Anywhere: Getting proxy table ODBC errors because the engine's running as a service and you've set up a User DSN instead of System DSN.

  13. SQL Anywhere: Getting file-related errors because the file specifications are relative to the server rather than the client.

  14. SQL Anywhere: Getting file-related errors because the engine's running as a service without the necessary permissions.

  15. SQL Anywhere: Coding CREATE TRIGGER IF NOT EXISTS instead of CREATE OR REPLACE TRIGGER, or vice versa for CREATE TABLE (in 11.0.1 or later).

  16. SQL Anywhere: Getting integer arithmetic when you wanted fractional parts because you forgot to CAST.

  17. Stored procedure debugger: Setting it to watch a specific user id other than the one you're using to test your code.

  18. Sybase Central: Setting it to display objects for owner names other than the one you're interested in.

  19. Copy and paste: Forgetting to edit after pasting; e.g., Copy and paste SET @continue = 'Y' into the body of a WHILE loop and then forgetting to change it to 'N'.

  20. MobiLink: Forgetting to call ml_add_column for any of the columns you're trying to synchronize, thus guaranteeing yourself a "Sassen Frassen Fricken Fracken!" moment when you run the first test.

  21. MobiLink: Forgetting to call ml_add_[various] with the NULL parameter to delete old ml_[whatever] rows, thus ending up with thousands of orphan system table rows in the consolidated database.

  22. OLAP Windowing: Coding the wrong combination of ASC and DESC in an inner OVER ORDER BY clause and the outer SELECT ORDER BY: different when they should be the same, the same when they should be different, or some other variation of "wrong combination"...
                SELECT older_sample_set.sample_set_number
                  INTO @20_older_sample_set_number
                  FROM ( SELECT TOP 20
                                ROW_NUMBER() OVER ( ORDER BY rroad_sample_set.sample_set_number ASC ) AS scrolling_row_number,
                                rroad_sample_set.sample_set_number                                    AS sample_set_number
                           FROM rroad_sample_set
                          WHERE rroad_sample_set.sampling_id       = @sampling_id
                            AND rroad_sample_set.sample_set_number < @sample_set_number
                          ORDER BY rroad_sample_set.sample_set_number DESC ) AS older_sample_set
                 WHERE older_sample_set.scrolling_row_number = 20;

  23. MobiLink: Forgetting to call ml_add_column() when trying to use named parameters instead of "?" in versions 10 and 11 MobiLink scripts, resulting in a "What the ... ? Sassen Frassen Fricken Fracken!" moment during the first test (thank you, Jeff Albion).

  24. SQL: Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected (thank you, Ron Hiner).

  25. HTTP web services: Leaving an & in the code when a ? is required, and vice versa, when editing service URLs; e.g., 'HTTP://localhost:12345/web_service&service_parm2=!parm2'

  26. SQL Anywhere: Forgetting that not all functions look like functions: SELECT CAST ( CURRENT TIMESTAMP, VARCHAR )

  27. Batch file: Trailing spaces on SET commands; e.g., SELECT CAST ( xp_getenv ( 'DEBUG_MESSAGES' ) AS VARCHAR ) returns 'OFF ' instead of 'OFF' after SET DEBUG_MESSAGES=OFF

  28. Forum: Clicking Reply on the main Question or Answer entry instead of the comment you wanted.

  29. SQL Anywhere: Forgetting to run dblog to tell the database file where the log is now, after moving the database and log files to a different folder (thank you, Justin Willey).

  30. SQL Anywhere: Having to look up WAIT in the Help ... every ... single ... time, to be reminded that's it's WAITFOR, not WAIT.

  31. SQL: Forgetting to check the SELECT against the GROUP BY, resulting in "Function or column reference to ... must also appear in a GROUP BY" (thank you, Glenn Paulley).

  32. SQL: Coding too much in the GROUP BY (like, say, the primary key) so every group contains but a single row (thank you, Glenn Paulley).

  33. Design: Forgetting to accomodate or prevent loops in a tree structure, resulting in a tree traversal process that pegs the CPU at 100%... forever (thank you, Ove B).

  34. MobiLink: Unwittingly using a variety of user ids when running sync*.sql, updating MobiLink scripts and running the MobiLink server, resulting in inexplicable inconsistencies.

  35. MobiLink: Accidentally creating multiple script versions and then getting them crossed up when updating MobiLink scripts and running the MobiLink client.

  36. SQL Anywhere: Forgetting to run the 32-bit version of SQL Anywhere when working with Excel proxy tables.

  37. ODBC Administrator: Running the 64-bit version (huh?) of odbcad32.exe (say what?) when you need 32-bit version at C:\WINDOWS\SysWOW64\odbcad32.exe (oh, fer #*@&!!!)

  38. ODBC Administrator: Forgetting to click OK ... twice ... to actually save your new ODBC DSN after celebrating your success with Test Connection.

  39. ODBC Administrator: Setting up an ODBC DSN on the wrong computer: "It goes with the client!" ... but sometimes it's not obvious where the client is actually located.

  40. Security: Forgetting which Windows user id you're using on which system, then spending too much time with Windows menus, firewall software and Google searches before the "Doh!" moment.

  41. SQL: Getting an exception that is not only completely inexplicable, but absolutely impossible for the statement that raised it... until you think to look inside the triggers.

  42. SQL Anywhere: Getting an exception because a FOR loop variable has a scope conflict with a column name, or worse, NOT getting an exception, just a wrong result.

  43. SQL: Forgetting the comma between two columns in SELECT list, thus turning the second column name into a profoundly misleading alias name for the first column.

  44. SQL Anywhere: Coding SET OPTION instead of SET TEMPORARY OPTION, then wondering where the commit came from.

  45. SQL: Not bothering to check that you have the same number of columns and arguments in an INSERT statement and then wondering why you have an error (thank you, Justin Willey).

  46. SQL: Forgetting to omit your autoincrement primary key from your INSERT column name list (thank you, Justin Willey).

  47. Foxhound: Changing the "Default" Monitor option settings, then expecting them to apply to an existing target database.

  48. Foxhound: Creating two separate Foxhound Monitor sessions for the same actual target database, perhaps one using a DSN and the other a DSN-less connection string.

  49. SQL Anywhere: Forgetting to code AUTOSTOP OFF on a START DATABASE statement, then wondering why the database immediately stopped after starting.

  50. SQL Anywhere: Running a new version of dbupgrade.exe on a older version of a database file that is running on an older version of the database server and not noticing it did not have the desired effect.

    For example, running version 16 of dbupgrade.exe that connects to a version 12 database that is running on dbsrv12.exe will appear to run OK but none of the new version 16 stored procedures will exist after the "upgrade":
    SELECT * FROM sa_certificate_info ( 'whatever' );
    Could not execute statement.
    Procedure 'sa_certificate_info' not found
    SQLCODE=-265, ODBC 3 State="42S02"
    Line 1, column 1
    (Continuing after error)
    The version 16 dbupgrade.exe says it ran "SQL Anywhere Upgrade Utility Version" but the SYSHISTORY table says the version was '':
    SQL Anywhere Upgrade Utility Version
    Database upgrade started
    Creating system views
    Creation of system views completed
    Setting option values
    Setting option values completed
    Creating migration procedures
    Creation of migration procedures completed
    Creating jConnect procedures
    Creation of jConnect procedures completed
    Database upgrade finished
    Upgrade completed - shut down database and archive transaction log now
    SELECT operation, version, first_time, last_time
     WHERE operation = 'UPGRADE';
    operation,  version,        first_time,                last_time
    'UPGRADE',  '', '2018-09-04 08:36:01.000',  '2018-09-04 08:36:01.000'
    To work as expected, a new version of dbupgrade.exe must connect to a database running on the new version of the database server, either by connecting to a database that is already running on the new server or by implicitly starting that database on the new server via the dbupgrade -c'DBF=...' connection parameter.

  51. SQL Anywhere: Not making the SELECT TOP ORDER BY clause sufficiently deterministic, then wondering why it doesn't pick the same top row every time.

    For example, a SELECT TOP 1 with an ORDER BY that doesn't specify enough columns to exactly determine which of 2 or more rows are the TOP 1 will run OK but it may not pick the same top row every time.

  52. SQL Anywhere: Not checking other ORDER BY clauses when changing one of several ORDER BY clauses in the same query; e.g., not checking a ROW_NUMBER() OVER ( ORDER BY ... ) when changing the SELECT ORDER BY, then wondering why there's something funky about the result set.

    Here's an example from down inside Foxhound:
      FROM ( SELECT TOP 500 
                    ROW_NUMBER() OVER ( ORDER BY rroad_v_sample_history_menu_up_count_locator.locator_number ASC ) AS scrolling_row_number,
                    rroad_v_sample_history_menu_up_count_locator.locator_number                                    AS locator_number,
                    rroad_v_sample_history_menu_up_count_locator.secondary_sort_order_number                       AS secondary_sort_order_number
               FROM rroad_v_sample_history_menu_up_count_locator
              ORDER BY rroad_v_sample_history_menu_up_count_locator.locator_number ASC,
                       rroad_v_sample_history_menu_up_count_locator.secondary_sort_order_number ASC 
           ) AS newer_sample_set
     WHERE newer_sample_set.scrolling_row_number = 500;

  53. SQL Anywhere: Forgetting to code an INTO @local_variable clause on a SELECT inside a deeply-nested PROCEDURE, thus accidentally returning an additional result set to an higher FROM call, then wondering why you get SQLCODE -866 (returned a result set with a different schema than expected), or SQLCODE -873 (returned multiple result sets), or both.

  54. SQL Anywhere: Expecting to see MESSAGE TO CLIENT output with the SQL Anywhere 17 ISQL GUI. Don't bother looking, it's virtually inaccessible. Use MESSAGE TO CONSOLE instead, or revert to the version 16 ISQL which works just fine with version 17 databases.