Friday, June 21, 2013

Latest SQL Anywhere Updates: Doom! for 11.0.1, 12.0.1.3910 for Windows

Current builds for the active platforms...

HP-UX     12.0.1.3894 Update                 16 May 2013
 Itanium  11.0.1.2958 Update                 08 Apr 2013 (End of Life 31 May 2014)

IBM AIX   12.0.1.3894 Update                 16 May 2013
          11.0.1.2958 Update                 08 Apr 2013 (End of Life 31 May 2014)

Linux     16.0.0.1535 Update                 30 May 2013
          12.0.1.3873 Update                 05 Apr 2013
          12.0.1 Chinese,                    16 Apr 2013
                 Japanese Docs (Eclipse)     16 Apr 2013
          11.0.1.2958 Update                 08 Apr 2013 (End of Life 31 May 2014)

Mac OS    12.0.1.3901 Update                 23 May 2013
          11.0.1.2449 Update                 29 Jun 2010 (End of Life 31 May 2014)

Solaris   12.0.1.3894 Update                 16 May 2013
 SPARC    11.0.1.2958 Update                 08 Apr 2013 (End of Life 31 May 2014)

Solaris   12.0.1.3894 Update                 16 May 2013
 x64      11.0.1.2958 Update                 08 Apr 2013 (End of Life 31 May 2014)

Windows   16.0.0.1535 Update                 30 May 2013
          12.0.1.3910 Update             *** 17 Jun 2013 ***
          12.0.1 French,                     25 Sep 2012
                 English,                    25 Sep 2012
                 German,                     25 Sep 2012
                 Chinese,                    28 Mar 2013
                 Japanese Docs (HTML/PDF)    28 Mar 2013
          11.0.1.2960 Update                 16 Apr 2013 (End of Life 31 May 2014)

Other Stuff...

 Older Updates

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
Dilbert.com 1993-03-09

Wednesday, June 19, 2013

TechEd 2013 Session Catalog

There are 8 session tracks at TechEd 2013 in Las Vegas on October 21 to 25...

 SAP Technology Innovation and Strategy (TEC)
 IT Management (ITM)
 Enterprise Analytics (AE)
 Real-Time Data Platform (RDP)
 Process Orchestration, Integration, and Portal (POP)
 Custom Development (CD)
 Security, Identity Management, and Single Sign-On (SIS)
 Mobile (MOB)
but only 7 of them are represented so far in the Session Catalog...
SELECT Track, COUNT(*)
  FROM SessionDownload
 GROUP BY Track
 ORDER BY Track;

Track                                                 COUNT() 
------------------------------------------------- ----------- 
Custom Development                                         76 
Enterprise Analytics                                       70 
IT Management                                             116 
Process Orchestration, Integration, and Portal             62 
Real-Time Data Platform                                   240 
SAP Technology Innovation and Strategy                     52 
Security, Identity Management, and Single Sign-On          42 
so it's may be a bit early to look for the SQL Anywhere and MobiLink sessions but let's do that anyway...
SELECT Session_ID,
       Title 
  FROM SessionDownload
 WHERE Title       LIKE '%MobiLink%'
    OR Title       LIKE '%SQL Anywhere%'
    OR Description LIKE '%MobiLink%'
    OR Description LIKE '%SQL Anywhere%'
 ORDER BY Session_ID;

Session_ID Title                                                                                                
---------- ---------------------------------------------------------------------------- 
EA269      Everything You Need to Know to Use SAP Crystal Reports                                               
RDP109     Orbiting the Enterprise – SAP Sybase SQL Anywhere as a Satellite Server                              
RDP118     Introduction to SAP Sybase SQL Anywhere                                                              
RDP119     SAP Sybase SQL Anywhere Satellite Database Case Studies                                              
RDP121     Best Practices for Embedding Databases in Lines of Business Applications                             
RDP122     Enhancing Business Intelligence Deployments with SAP Real-Time Data Platform                         
RDP124      Getting Started with SAP Sybase SQL Anywhere, On-Demand Edition                                     
RDP141     Powerful Data Access at the Edge of the Enterprise                                                   
RDP220     Mobilizing Data-Driven Applications                                                                  
RDP222     OData Support in SAP Real-Time Data Platform                                                         
RDP278     Extending SAP HANA to SMEs Using SAP Sybase SQL Anywhere and MobiLink                                
TEC101     How to Best Embed SAP Technology                                                                     
and guess what?

No "Mobile (MOB)" Sessions! (yet)


Patience, patience, the MOB will surely be here any minute :)

Dilbert.com 2013-06-19

Monday, June 17, 2013

Building a Link Checker Inside SQL Anywhere

This is the second article about the foundations for client-server communications inside SQL Anywhere, where the server is a website and the client is a stored procedure inside a SQL Anywhere database.


Resources...
- The web-style CREATE PROCEDURE statement as described in the SQL Anywhere 16 Help.

- Descriptions of the various HTTP request methods like GET and HEAD, in Wikipedia.

- Descriptions of the associated HTTP headers, also in Wikipedia.

- Kurt Lichtner's excellent 2005 Techwave presentation on the new HTTP client introduced in SQL Anywhere 9.0.2.
... yes, this stuff's been around for a while.

The previous article, Client-Server Via Web Services Inside SQL Anywhere, showed how to build a variety of web client and server functions and procedures, ending with a SQL Anywhere stored procedure that used an HTTP GET request to retrieve the HTML body of an Amazon product web page plus all the associated HTTP status and header fields.

This article delves a bit lower, showing how to build a SQL Anywhere stored procedure that uses HTTP HEAD requests to check the validity of external URLs stored in a database. The database in question is the one used to construct the SQL Anywhere Technical Documents web page, and the motivation is link rot: many of the 1,156 technical document links no longer work, and it's time to find out just how bad the situation is without checking ... each ... link ... by ... hand (hint: it's dire!)
[click here to go straight to the code]

Why not just run a link checker on the web page? Why DIY?

The obvious answer is, that's what this blog is all about! (doing stuff with SQL Anywhere databases)

Dilbert.com 1996-02-27

But... it's one thing to Do It Yourself, it's quite another to DIY without ever seeing an example of the "It" you're trying to "Do".

I can't remember ever having run a "link checker" program, so here goes...
  1. Step 1, google it: how do I check links on a website page

  2. First hit: Link Checker - The W3C Markup Validation Service

  3. Go there, feed it this URL: http://sqlanywhere.blogspot.ca/2012/03/tales-from-doc-face.html

    Figure 1: Home page for the W3C Link Checker

  4. Wait ... for ... a ... long ... time ... Done. Document processed in 651.56 seconds

    Figure 2: Processing report displayed by the W3C Link Checker for the Technical Documents page


The situation is dire!

Figure 3 shows that the W3C Link Checker reports 345 "server side problem" links... yup, it's a problem.

Figure 3: Summary of results displayed by the W3C Link Checker for the Technical Documents page


Isn't that all you need to know? Why DIY?

There's no question that the W3C Link Checker does a good job of parsing and checking a web page, and then telling you about all the problems.

What it doesn't do, is this:
  • The W3C Link Checker doesn't limit itself to the important content, in this case the Technical Documents links. That's understandable, it's parsing a web page, it doesn't know what's important and what isn't.

  • The W3C Link Checker doesn't report the total number of links on the page, or how many were "good" links. You can determine the total by counting the number of "Checking link ..." messages in the output (1,298), then do some arithmetic to calculate the number of "good" links, but that's a pain in the patootie.
    Checking link http://iablog.sybase.com/efarrar/2011/10/is-fuji-really-a-cloud/
    HEAD http://iablog.sybase.com/efarrar/2011/10/is-fuji-really-a-cloud/  fetched in 1.20 seconds
    
    Checking link http://www.sybase.com/detail?id=47925
    HEAD http://www.sybase.com/detail?id=47925  fetched in 1.30 seconds
    
    ... and so on, 1,296 more times
    

  • The W3C Link Checker doesn't provide any mechanism for adhoc reporting. Sure, you could scan the output, muck around with regular expressions... but I won't :)


  • The W3C Link Checker doesn't let you customize the process to, say, automate problem investigation. Yes, yes, it's open source... see cartoon above.
Note: One of the "Settings used" in Figure 2 is "Sleeping 1 second between requests to each server" which seems to indicate that it's not a good idea for a robot (like a link checker) to pound other people's servers. Being flagged as an Evil Doer is something no self-respecting client IP address wants...

...so, this DIY project will make use of the SQL Anywhere WAITFOR statement.

SQL Anywhere Link Checker: The Code

Here's the basic building block for the SQL Anywhere Link Checker:
CREATE PROCEDURE http_head_attributes (
   IN url     LONG VARCHAR )
RESULT (
   attribute  LONG VARCHAR,
   value      LONG VARCHAR )
URL '!url' 
TYPE 'HTTP:HEAD';

When a single URL is passed to this web-style procedure, SQL Anywhere tries to send an HTTP HEAD request to the web server, and if that request is successful it gets back a variable number of HTTP headers. If the attempt is unsuccessful (and '404 Not Found' counts as unsuccessful) SQL Anywhere will raise an exception which can be handled in BEGIN CATCH block.

A successful call to http_head_attributes will return a two-column attribute and value result set containing the header names and values.

Here are examples of successful and unsuccessful calls:
SELECT * FROM http_head_attributes ( 'http://www.google.com' );

attribute         value
Status            HTTP/1.0 200 OK
Body              
Expires           -1
Server            gws
X-XSS-Protection  1; mode=block
Content-Type      text/html; charset=ISO-8859-1
P3P               CP="This is not a P3P policy! See http://www.google.com/support/accounts/bin/answer.py?hl=en&answer=151657 for more info."
Cache-Control     private, max-age=0
X-Frame-Options   SAMEORIGIN
Date              Tue, 11 Jun 2013 23:06:04 GMT
Set-Cookie        NID=67=Dg4bUl ... etcetera ...UKCUD; expires=Wed, 11-Dec-2013 23:06:04 GMT; path=/; domain=.google.com; HttpOnly

SELECT * FROM http_head_attributes ( 'http://www.google.com/unknown' );

HTTP request failed. Status code '404 Not Found'
SQLCODE=-983, ODBC 3 State="HY000"
Tip: The "Body" value is empty, and it should be, but that is not always the case! For example, SELECT * FROM http_head_attributes ( 'http://www.sap.com' ) returns the full Body value, all 28,672 bytes of it. That behavior is rare (other pages on sap.com don't do it) but it's something to keep in mind when issuing an HTTP HEAD request.

Figure 4 shows where the URLs to be checked come from. One row exists in the article table for each entry displayed on the Technical Documents web page, one row is inserted in the link_check table each time an article is checked by the SQL Anywhere Link Checker, and one row is inserted in the http_header table for each row returned by a call to web_client_procedure:

Figure 4: ER Diagram for the Technical Documents database


Here's the code for the main link checker procedure:
CREATE PROCEDURE catalog_link_checker()
BEGIN

DECLARE @url_count         INTEGER;
DECLARE @url_counter       INTEGER;
DECLARE @link_checked_at   TIMESTAMP;
DECLARE @status            LONG VARCHAR;

SELECT COUNT(*)
  INTO @url_count
  FROM article
 WHERE url LIKE 'http%';

SET @url_counter = 0;

TRUNCATE TABLE http_header;
TRUNCATE TABLE link_check;

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT artificial_id AS @artificial_id,
       url           AS @url
  FROM article
 WHERE url LIKE 'http%'
 ORDER BY order_by_date DESC,
       title DESC
FOR READ ONLY
DO

   WAITFOR DELAY '00:00:01';

   SET @url_counter     = @url_counter + 1;
   SET @link_checked_at = CURRENT TIMESTAMP;

   INSERT link_check VALUES ( @artificial_id, @link_checked_at );

   MESSAGE STRING ( 'URL ', @url_counter, ' of ', @url_count, ': ', @url ) TO CONSOLE;

   BEGIN TRY

      INSERT http_header
      SELECT @artificial_id,
             @link_checked_at,
             attribute,
             value
        FROM http_head_attributes ( @url );

      SET @status = '(unknown)';

      SELECT value
        INTO @status
        FROM http_header
       WHERE artificial_id   = @artificial_id
         AND link_checked_at = @link_checked_at
         AND attribute       = 'Status';

      MESSAGE STRING ( 'Status: ', @status ) TO CONSOLE;

   END TRY
   BEGIN CATCH
      MESSAGE STRING ( 'ERROR_MESSAGE() = ',  ERROR_MESSAGE() )  TO CONSOLE;
      MESSAGE STRING ( 'ERROR_SQLCODE() = ',  ERROR_SQLCODE() )  TO CONSOLE;
      MESSAGE STRING ( 'ERROR_SQLSTATE() = ', ERROR_SQLSTATE() ) TO CONSOLE;
      INSERT http_header VALUES ( @artificial_id, @link_checked_at, 'ERROR_MESSAGE()',  ERROR_MESSAGE() );
      INSERT http_header VALUES ( @artificial_id, @link_checked_at, 'ERROR_SQLCODE()',  ERROR_SQLCODE() );
      INSERT http_header VALUES ( @artificial_id, @link_checked_at, 'ERROR_SQLSTATE()', ERROR_SQLSTATE() );
   END CATCH;

   COMMIT;

END FOR;

END;
  • The FOR loop starting on line 19 steps through each row of the article table in the same order the entries are displayed on the web page, and selects just the primary key and URL values.

  • The WAITFOR DELAY statement on line 29 is a crude implementation of the "Sleeping 1 second between requests to each server" suggestion taken from Figure 2. It's crude because this code sleeps between all requests, not just requests to the same server, and that means this code will take a bit longer to run than the W3C Link Checker.

  • The INSERT on line 34 records the fact that this URL was checked on such-and-such a date and time. This level of record-keeping isn't important right now (hence the TRUNCATE TABLE statements on lines 16 and 17) but it might be in the future if someone wanted to know when a particular document was available and when it wasn't, similar to the "Retrieved" dates that Wikipedia shows in the References sections.

  • The INSERT on lines 40 to 45 copies all the headers from the call to http_head_attributes into the http_header table. If the call fails, control passes to the BEGIN CATCH block on line 59, and three surrogate rows are inserted for the three basic SQL Anywhere exception diagnostic values: ERROR_MESSAGE(), ERROR_SQLCODE() and ERROR_SQLSTATE().
Figure 5 shows that the Link Checker takes 2,084 seconds to run; 1,156 of those seconds are spent in the WAITFOR DELAY statements, the rest waiting for web servers to respond.

Figure 6 shows a few snippets from the dbsrv16 -o filespec.txt diagnostic log containing some URLs that worked (Status: 200 OK) and some that didn't (SQLCODE -981).

Figure 5: Running the Link Checker in ISQL


Figure 6: Link Checker MESSAGE TO CONSOLE Output
I. 06/14 04:39:15. URL 1 of 1156: http://download.sybase.com/ianywhere/documents/UsingCrystalWithSQLAnywhere_FINAL.pdf
I. 06/14 04:39:15. Status: HTTP/1.1 200 OK
I. 06/14 04:39:16. URL 2 of 1156: http://sqlanywhere.blogspot.ca/2012/10/example-recursive-union-inverted-order.html
I. 06/14 04:39:16. Status: HTTP/1.0 200 OK
I. 06/14 04:39:17. URL 3 of 1156: http://sqlanywhere.blogspot.ca/2012/10/example-recursive-union-tree-traversal.html
I. 06/14 04:39:17. Status: HTTP/1.0 200 OK
...
I. 06/14 04:40:10. URL 38 of 1156: http://iablog.sybase.com/paulley/2012/03/limitations-of-proxy-tables/
I. 06/14 04:40:11. ERROR_MESSAGE() = Unable to connect to the remote host specified by 
                      'http://iablog.sybase.com/paulley/2012/03/limitations-of-proxy-tables/'
I. 06/14 04:40:11. ERROR_SQLCODE() = -981
I. 06/14 04:40:11. ERROR_SQLSTATE() = WW050
I. 06/14 04:40:12. URL 39 of 1156: http://iablog.sybase.com/paulley/2012/03/nhibernate-3-3-0cr1-released/
I. 06/14 04:40:13. ERROR_MESSAGE() = Unable to connect to the remote host specified by 
                      'http://iablog.sybase.com/paulley/2012/03/nhibernate-3-3-0cr1-released/'
I. 06/14 04:40:13. ERROR_SQLCODE() = -981
I. 06/14 04:40:13. ERROR_SQLSTATE() = WW050
...
I. 06/14 05:13:53. URL 1154 of 1156: http://www.sybase.com/detail?id=47851
I. 06/14 05:13:55. Status: HTTP/1.1 200 OK
I. 06/14 05:13:56. URL 1155 of 1156: http://www.sybase.com/detail?id=20313
I. 06/14 05:13:56. Status: HTTP/1.1 200 OK
I. 06/14 05:13:57. URL 1156 of 1156: http://www.sybase.com/detail?id=10915
I. 06/14 05:13:57. Status: HTTP/1.1 200 OK

Here's an adhoc query that summarizes the "dire situation" by classifying all the articles by the status of their URLs:
SELECT '1. Articles With Good Links:' AS title, 
       COUNT(*)                       AS count
  FROM article
       INNER JOIN link_check
          ON link_check.artificial_id = article.artificial_id
       INNER JOIN ( SELECT *
                      FROM http_header
                     WHERE TRIM ( attribute ) = 'Status' 
                       AND TRIM ( value ) LIKE 'HTTP/% 200 OK'
                  ) AS http_header
          ON http_header.artificial_id   = link_check.artificial_id
         AND http_header.link_checked_at = link_check.link_checked_at

UNION ALL
SELECT '2. Articles Without Links:', 
       COUNT(*)
  FROM article
 WHERE url NOT LIKE 'http%'

UNION ALL
SELECT '3. Rotted iAnywhere Blog Links:',
       COUNT(*)
  FROM article
       INNER JOIN link_check
          ON link_check.artificial_id = article.artificial_id
       INNER JOIN ( SELECT *
                      FROM http_header
                     WHERE TRIM ( attribute ) = 'ERROR_MESSAGE()' 
                       AND TRIM ( value ) LIKE 'Unable to connect to the remote host specified by ''http://iablog.sybase.com%'
                  ) AS http_header
          ON http_header.artificial_id   = link_check.artificial_id
         AND http_header.link_checked_at = link_check.link_checked_at

UNION ALL
SELECT '4. Invalid Response Links:',
       COUNT(*)
  FROM article
       INNER JOIN link_check
          ON link_check.artificial_id = article.artificial_id
       INNER JOIN ( SELECT *
                      FROM http_header
                     WHERE TRIM ( attribute ) = 'ERROR_MESSAGE()' 
                       AND TRIM ( value )     = 'Invalid response from the HTTP server'
                  ) AS http_header
          ON http_header.artificial_id   = link_check.artificial_id
         AND http_header.link_checked_at = link_check.link_checked_at

UNION ALL
SELECT '5. Other:',
       COUNT(*)
  FROM article
       INNER JOIN link_check
          ON link_check.artificial_id = article.artificial_id
 WHERE NOT EXISTS ( 
   SELECT *
     FROM http_header
    WHERE http_header.artificial_id = article.artificial_id
      AND (    (     TRIM ( attribute ) = 'Status' 
                 AND TRIM ( value )     LIKE 'HTTP/% 200 OK' )
            OR (     TRIM ( attribute ) = 'ERROR_MESSAGE()' 
                 AND TRIM ( value )     LIKE 'Unable to connect%http://iablog.sybase.com%' )
            OR (     TRIM ( attribute ) = 'ERROR_MESSAGE()' 
                 AND TRIM ( value )     = 'Invalid response from the HTTP server' ) ) ) 

UNION ALL
SELECT '6. Total Articles:',
       COUNT(*)
  FROM article
ORDER BY 1;

title                                 count 
------------------------------- ----------- 
1. Articles With Good Links:            791 
2. Articles Without Links:               18 
3. Rotted iAnywhere Blog Links:         345 
4. Invalid Response Links:               18 
5. Other:                                 2 
6. Total Articles:                     1174 

Execution time: 0.095 seconds

What's Going To Happen To The Technical Documents Page?

As the summary shows, only (!) 29% of the links have rotted away (the iAnywhere blog posts on sybase.com). Some of them have been moved to sap.com but it's not clear how many, nor is it clear how to map the old-to-new-URLs... it's probably a long ... slow ... manual ... process to make the repairs, which is why it hasn't been started yet.

And it won't be, at least not until the fate of the remaining documents on sybase.com is determined. There are literally hundreds of whitepapers, Techwave presentations, Insider magazine articles and so on. Chances are, a few will move, many will be abandoned, and all links to sybase.com will rot away.

As the rot rate rises, enthusiasm sinks, and at at some point (40 percent? 50? 60?), the link to the Technical Documents page itself will rot away.

Like I said, the situation is dire, but only for those who care :)

Dilbert.com 1998-04-11





Friday, June 14, 2013

Characteristic Errors, Revision 3

UPDATE: See the latest version of this article here.


It's only been a few days since this list had 27 entries and now it has 35...

[click here to see the new entries]

A characteristic error is an error that is so easy to make that it appears you are being actively encouraged to make it by the very nature of the computer program you are using.

For example, sending an email without the attachment is a characteristic error of all email programs.
...except Gmail. Gmail warns you about missing attachments... Gmail is magic!
Here are some errors that are characteristic of SQL in general, SQL Anywhere in particular, and some companion programs.
  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 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


    New entries...

  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.



Wednesday, June 12, 2013

SAP TechEd 2013 - Las Vegas, October 21–25

For our friends in other parts of the world, the SAP TechEd schedules for Amsterdam and Bangalore may be reached via the TechEd home page.

For those of you within range of Vegas, now is the time to do two (2) things:

Mark your calendar...



and join the mailing list...



That's it for now!



Monday, June 10, 2013

PSA: Use Different Passwords On Different Websites

Let's say you use Drupal software (or some other product, it doesn't really matter, but let's say it's Drupal),

  • and you have an account at drupal.org,

  • and the password you use on Drupal is the same password you use other places, like your email service (but not your bank, you're not stupid),

  • and you have also given that email address to other websites (say, for example, your bank... it's OK to give your email address to your bank).
Now, let's say Drupal is hacked (which it has been, see below),
  • and let's say the hacker got your email address (which they did),

  • and they got your Drupal password in clear text (which Drupal says didn't happen, but let's say they're lying),

  • and the hacker signs in to your email service with your email address and your email-same-as-Drupal password

  • and then he... it's politically correct to call evil-doers "he", isn't it? ...he changes your email password on your email service


  • so now he can read your email but you can't

  • and then the hacker takes a guess at which bank you use (and gets it right... there are only so many banks out there)

  • and he clicks on "Lost password?" on your bank website

  • and he gives them your email address

  • which the bank recognizes as a real customer email address (you)

  • so the bank sends out a "Password Reset" email to your email address

  • except it's the hacker that gets the email, not you,

  • and now the hacker signs in to your bank account and changes your bank password and, well, does other stuff.
Like clean out your account, apply for a line of credit, clean that out too, you know...

...banking stuff.

Maybe some identity theft stuff, too.

You thought you were safe, didn't you? You used a different password for your bank because your bank is "important" but all that other stuff (Drupal, email, etc), that's just "stuff", it's OK to use the same password for them, isn't it?

And you always use GOOD passwords, all go0fY-upPer-and-lowercase-and-d1g1ts stuff... what could possibly go wrong?

The solution: Don't use the same password on more than one website


BTW, PSA means "Public Service Announcement", which is what this has been.


from: Holly Ross-Drupal Association
reply-to: password@association.drupal.org
to: drupal-notify@drupal.org
date: Wed, May 29, 2013 at 4:01 PM
subject: Important Security Update: Reset your Drupal.org Password

Dear community member,

We respect the privacy of your information, which is why, as a precautionary measure, we are writing to let you know about an incident that involves your personal information. The Drupal.org Security and Infrastructure Teams have discovered unauthorized access to account information on Drupal.org and groups.drupal.org. Information exposed includes usernames, email addresses, and country information, as well as hashed passwords. However, we are still investigating the incident and may learn about other types of information compromised, in which case we will notify you accordingly.

This unauthorized access was made via third-party software installed on the Drupal.org server infrastructure, and was not the result of a vulnerability within the Drupal software itself. This notice applies specifically to user account data stored on Drupal.org and groups.drupal.org, and not to sites running Drupal generally.

We have implemented additional security measures designed to prevent the recurrence of such an attack, and to protect the privacy of our community members.

The next time you attempt to log into your account, you will be required to create a new password.

Below are steps you can take to further protect your personal information online. We encourage you to take preventative measures now to help prevent and detect the misuse of your information.

First, we recommend as a precaution that you change or reset passwords on other sites where you may use similar passwords, even though all passwords on Drupal.org are stored salted and hashed. All Drupal.org passwords are both hashed and salted, although some older passwords on groups.drupal.org were not salted. To make your password stronger:

* Do not use passwords that are simple words or phrases
* Never use the same password on multiple sites or services
* Use different types of characters in your password (uppercase letters, lowercase letters, numbers, and symbols).

Second, be cautious if you receive emails asking for your personal information and be on the lookout for unwanted spam. It is not our practice to request personal information by email. Also, beware of emails that threaten to close your account if you do not take the "immediate action" of providing personal information.

For more information, please review the security announcement and FAQ at https://drupal.org/news/130529SecurityUpdate. If you find any reason to believe that your information has been accessed by someone other than yourself, please contact the Drupal Association immediately, by sending an email to password@association.drupal.org.

We regret that this incident has occurred and want to assure you we are working hard to improve security.

Thank you,
Holly Ross
Drupal Association Executive Director


Friday, June 7, 2013

The fRiDaY File - How do you say that in XML?

Have you ever heard of Parkinson's Law?


Work expands so as to fill the time available for its completion.
Sure you have. Now how about this version?

Requirements expand to fill the computing power available.
If you've been in this business (computer system development) for more than a year or so, you know the absolute truth of Parkinson's Law.

However, if you need further proof (say, for management) here it is; Rand Paul's comedy routine on C-Span (yes, that's right, "Rand Paul", "comedy" and "C-Span" all in one sentence):


Burning water skis?

Yup, it's a code, V90.27 to be exact, or more specifically, "Drowning (accidental) - due to - accident (to) - watercraft - burning - unpowered - water skis V90.27".


When making outlandish claims, it's always important to check your sources:

To the burning water skis.

Wednesday, June 5, 2013

Client-Server Via Web Services Inside SQL Anywhere

Have you ever found yourself copying code from brief Help topics, terse tutorials and concise examples without really understanding how that code works or even what it's doing?

Then, when it doesn't work, you hack around and Google for fixes and finally it seems to be doing what you want, so you ship it and try forget it?

And you feel a bit guilty for having done that?

If you're an Alpha or Beta, the answer is "Of course not! I created the technology, and those tutorials aren't terse, they're incredibly verbose!"

Dilbert.com 1998-07-15

If you're a Tech Charlatan, the answer is "Of course not! I understand every new technology the moment it's announced! Look, I watch TED, I made puns out of SOAP and DISH before you were born!"

Dilbert.com 1995-11-09

If you're one of those, this article is NOT for you


Disclaimer: This is not a tutorial on web services inside SQL Anywhere, it doesn't rise to that level.

It isn't even a "Getting Started" article, it's not up to that high standard either. And it certainly isn't a Help topic.

No, this article comes from the sub-basement level, below the the parking garage, in the University Of SQL Anywhere annex on the other side of town.

This article is for all the folks who took stuff apart when they were kids, to see "how it worked", and couldn't get it back together... except this time, your parents aren't going to yell at you. And it's for the folks who didn't have the courage to take stuff apart, but still wanted to see...

...because this time, you're can read this article by yourself and nobody will know. It won't make you an Alpha or a Beta, but it won't make you a Tech Charlatan either.

OK, my expectations have been lowered, what IS this article?

This article is about the foundations (footings, actually) for client-server communications inside SQL Anywhere, where the server is a website (or web service) and the client is a stored procedure or function, some or all of which reside inside SQL Anywhere databases. It's about passing data across the internet back and/or forth between blocks of SQL code running inside SQL Anywhere, with no application servers involved, no C# or Java or Python or VB or .NET and certainly no ORMs.

( cue muttered curses, sounds of room emptying... is anyone left? OK, fine, I'll talk to the hotel staff... :)

Step Zero: Create a couple of SQL Anywhere databases

Here's the Windows commands for that, or you can use Sybase Central if you want...
"%SQLANY16%\bin64\dbinit.exe" ddd16A.db

"%SQLANY16%\bin64\dbinit.exe" ddd16B.db

Step 1: Write a web server procedure that returns an HTML web page in a string

First, you have to start one of the databases (call it "Server A") and ISQL too...
"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  ddd16A.db

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=ddd16A;DBN=ddd16A;UID=dba;PWD=sql;CON=ddd16A"
Here's the code, plus a test SELECT...
CREATE PROCEDURE web_server_procedure (
   IN @service_parm1   LONG VARCHAR,
   IN @service_parm2   LONG VARCHAR )
RESULT ( html_string LONG VARCHAR )
BEGIN

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING ( 
  '<HTML> 
   <BODY BGCOLOR=#FFFFFF>
   <TABLE>
      <TR>
         <TD STYLE="padding: 20">', @service_parm1, '</TD>
         <TD STYLE="padding: 20">', @service_parm2, '</TD>
      </TR>
   </TABLE>
   </BODY>
   </HTML>' );

END;

SELECT * FROM web_server_procedure ( 'Hello', 'World' );

html_string
-------------------------------------------
<HTML> 
   <BODY BGCOLOR=#FFFFFF>
   <TABLE>
      <TR>
         <TD STYLE="padding: 20">Hello</TD>
         <TD STYLE="padding: 20">World</TD>
      </TR>
   </TABLE>
   </BODY>
   </HTML>            
(1 rows)
Execution time: 0.024 seconds
At this point there's nothing "web" about this "web server procedure" except for the fact it returns HTML... and other than looking like HTML, it's just ordinary text. In particular, the dbsrv16.exe command does not specify the -xs option, so the built-in SQL Anywhere HTTP server is not running.

The ordinary CREATE PROCEDURE statement starting on line 1 receives two string parameters and returns a single-row, single-column result set. That's what a SQL Anywhere "HTML web procedure" looks like: zero or more parameters that come from the URL, and a big fat HTML string as the result set.

The CALL to sa_set_http_header() on line 7 is required for web procedures that return HTML pages to the browser. Other kinds of web procedures might return images, for example, so 'text/html' will have to be changed to something else.

The SELECT starting on line 9 calls STRING() to build up an HTML string. Remember, at this point it's just text that happens to contain HTML thingies.

The SELECT * on line 23 is a unit test; it shows what web_server_procedure returns.

Step 2: Write a web service that returns an HTML web page to the browser

Web services require the builtin HTTP server to be running, so at this point you have to shut down Server A and start it again like this:
"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -xs http(port=12345)^
  ddd16A.db

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=ddd16A;DBN=ddd16A;UID=dba;PWD=sql;CON=ddd16A"
The -xs option tells SQL Anywhere to start the builtin HTTP server, and the port=12345 is only necessary if you've already got a local web server running (say, Foxhound) on port 80.

Here's the code for the web service; the CREATE SERVICE statement is really just a wrapper around the CALL to web_server_procedure.

Tip: Don't confuse CREATE SERVICE with remote servers (CREATE SERVER) or Linux/Windows services (dbsvc), they're not related.
CREATE SERVICE web_service 
   TYPE 'RAW'
   AUTHORIZATION OFF
   USER DBA
   AS CALL web_server_procedure ( 
      :service_parm1,  
      :service_parm2 );
The names "service_parm1" and "service_parm2" correspond to parameter names that will be coded in the URL in the browser, and the funky ":" syntax is something the SQL Anywhere parser needs.

And here it is, the URL to code in the browser...
http://localhost:12345/web_service?service_parm1=Hello&service_parm2=World
Tip: The ? comes in front of the first parameter, & in front of the others... getting that wrong is one of THE most common mistakes ever made :)



Step 3: Write a web client function that can call the web service

So, you can call the web server procedure directly, that was shown in Step 1. But what if you wanted to call that procedure from somewhere else, like a different SQL Anywhere database?

And what if you didn't want to go through some kind of "remote procedure call" mechanism, but wanted to call the web service instead? There's no CALL statement for web services.

And there's no web browser inside a SQL Anywhere database, so you can't code the URL like in Step 2.

Or can you?

At this point, Server A is still running, still processing HTTP requests. A second server (call it Server B) is also required, but it doesn't need HTTP:
"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  ddd16B.db

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=ddd16B;DBN=ddd16B;UID=dba;PWD=sql;CON=ddd16B"
Here's the code for a SQL Anywhere web client function:
CREATE FUNCTION web_client_function (
   IN parm1   LONG VARCHAR,
   IN parm2   LONG VARCHAR )
RETURNS LONG VARCHAR
URL 'HTTP://localhost:12345/web_service?service_parm1=!parm1&service_parm2=!parm2';
The parameter names parm1 and parm2 are chosen by you, just for this web-style CREATE FUNCTION statement; they don't have scope outside it.

The IN parm1 line defines the parameter name, and !parm1 refers to the parameter value at runtime; same thing for IN parm2 and !parm2.

Here's a demo:
  • A function call inside a SELECT on Server B

  • uses the URL to go out across the (local) internet,

  • fires up the web service inside Server A,

  • feeds it 'xxx' and 'yyy'

  • and gets back the web page as a single string...
SELECT web_client_function ( 'xxx', 'yyy' );

web_client_function('xxx','yyy')
-----------------------------------------
<HTML> 
   <BODY BGCOLOR=#FFFFFF>
   <TABLE>
      <TR>
         <TD STYLE="padding: 20">xxx</TD>
         <TD STYLE="padding: 20">yyy</TD>
      </TR>
   </TABLE>
   </BODY>
   </HTML>                
(1 rows)
Execution time: 0.044 seconds
Now you can honestly say you have "consumed a web service"... so much more sophisticated than "I called a function", doncha think?

Tip: Use simple parameter names like "parm1". Don't use "@service_parm1" or "service_parm1" or even "@parm1", they might compile but they will not work.

Step 4: Write a web client function that calls an external web service

Yes, you can go get stuff off real websites; here's how:
CREATE FUNCTION web_client_function()
RETURNS LONG VARCHAR
URL 'http://www.amazon.com/' 
TYPE 'HTTP:GET';

SELECT web_client_function();

web_client_function()
--------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script type="text/javascript">var ue_t0=ue_t0||+new Date();</script>
<script>var BtechCF = {a:1,cf:function(){if(--BtechCF.a == 0){ uet('cf');}},inc:function(){BtechCF.a++;}};</script>
<!--btech-iplc-->
  <script type="text/javascript">
  var btiplv;
     new Image().src = "http://g-ecx.images-amazon.com/images/G/01/gno/beacon/BeaconSprite-US-01._V397411194_.png";
  </script>

... etcetera ...

</body>
</html>
<!-- MEOW -->
Yeah, seriously, I know; <!-- MEOW -->

Tip: If you get '405 Method Not Allowed' or '503 Service Temporarily Unavailable', it might mean TYPE 'HTTP:GET' is missing from the web-style CREATE FUNCTION statement.

Step 5: Write a web client procedure that can call an external web service

Here's an example that's looks like Step 4 except
CREATE PROCEDURE web_client_procedure (
   IN ASIN    LONG VARCHAR )
RESULT (
   attribute  LONG VARCHAR,
   value      LONG VARCHAR )
URL 'http://www.amazon.com/dp/!ASIN' 
TYPE 'HTTP:GET';

SELECT * FROM web_client_procedure ( '0345529057' );
Tip: If you don't get any rows back, it might mean TYPE 'HTTP:GET' is missing from the web-style CREATE PROCEDURE statement.

Here's a SELECT that trims down the output a bit:
SELECT CAST ( attribute AS VARCHAR ( 20 ) ) AS attribute,
       CAST (
          STRING ( 
             LEFT ( TRIM ( value ), 60 ),
             IF LENGTH ( TRIM ( value ) ) > 60
                THEN '...'
                ELSE ''
             ENDIF )  
          AS VARCHAR ( 63 ) ) AS value 
  FROM web_client_procedure ( '0345529057' );

attribute            value                                                           
-------------------- --------------------------------------------------------------- 
Status               HTTP/1.1 200 OK                                                 
Body                 <html>
                     <head>
                     <script type="text/javascript">var ue_t0=ue_t... 
cache-control        no-cache                                                        
x-sap-pg             book_display_on_website                                         
Content-Type         text/html; charset=ISO-8859-1                                   
x-amz-id-2           hTtuCROD6gAIAKBqbfCpSpzjfvatUHoZf+7Chh/eKOrsHb/Fxeq7yhcpokR4... 
x-amz-id-1           0QJ30295GDTVSSP47EDW                                            
pragma               no-cache                                                        
Vary                 Accept-Encoding,User-Agent                                      
x-frame-options      SAMEORIGIN                                                      
Set-cookie           session-id=186-7664488-2512739; path=/; domain=.amazon.com; ... 
expires              -1                                                              
Connection           close                                                           
p3p                  policyref="http://www.amazon.com/w3c/p3p.xml",CP="CAO DSP LA... 
Server               Server                                                          
Date                 Fri, 31 May 2013 17:44:15 GMT                                   
(16 rows)
Execution time: 2.445 seconds
The "Body" value is all that the web client function in Step 4 returned; here, you get all sorts HTTP stuff, even the cookie.

You can use Interactive SQL to feed the Body string back into a browser to see what you got; here's how:
  • Run a SELECT like this in Interactive SQL:
    SELECT TRIM ( value ) AS Body
      FROM web_client_procedure ( '0345529057' )
     WHERE attribute = 'Body';
    

  • On the Results pane, doubleclick on the '<html> <head> ...' value.

  • The "Value of column 'Body'" dialog box will open.

  • Select the HTML Preview tab, which will appear empty.
Then click on the Open in Browser button to see the Body string opened as local temporary "file:///" in the browser.



What's missing?

Two big things are missing from this article: First, gathering raw HTML ain't the most effective way to get data from other people's web services.

Second, some details have been left out, like
  • Where does HTTP fit into all of this?

  • What the heck is TYPE 'HTTP:GET' anyway?

  • What's the story on all those attributes like Content-Type and pragma and p3p?
This, however, is the academic sub-basement. More effective ways to get data won't be found here, you have to go at least one level up.

And guess what? There's a level below sub-basement, that's where HTTP lives, maybe lower.

In other words, out of scope, sorry :)


Monday, June 3, 2013

Latest SQL Anywhere Updates: 16.0.0.1535 LinWin, 12.0.1.3895 Windows

Current builds for the active platforms...

HP-UX     12.0.1.3894 Update                 16 May 2013
 Itanium  11.0.1.2958 Update                 08 Apr 2013

IBM AIX   12.0.1.3894 Update                 16 May 2013
          11.0.1.2958 Update                 08 Apr 2013

Linux     16.0.0.1535 Update             *** 30 May 2013 ***
          12.0.1.3873 Update                 05 Apr 2013
          12.0.1 Chinese,                    16 Apr 2013
                 Japanese Docs (Eclipse)     16 Apr 2013
          11.0.1.2958 Update                 08 Apr 2013

Mac OS    12.0.1.3901 Update                 23 May 2013
          11.0.1.2449 Update                 29 Jun 2010

Solaris   12.0.1.3894 Update                 16 May 2013
 SPARC    11.0.1.2958 Update                 08 Apr 2013

Solaris   12.0.1.3894 Update                 16 May 2013
 x64      11.0.1.2958 Update                 08 Apr 2013

Windows   16.0.0.1535 Update             *** 30 May 2013 ***
          12.0.1.3895 Update             *** 28 May 2013 ***
          12.0.1 French,                     25 Sep 2012
                 English,                    25 Sep 2012
                 German,                     25 Sep 2012
                 Chinese,                    28 Mar 2013
                 Japanese Docs (HTML/PDF)    28 Mar 2013
          11.0.1.2960 Update                 16 Apr 2013

Other Stuff...

 Older Updates

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1 and 16.0) are shown here.

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.