Sending Email From SQL Anywhere 12 Via Google's SMTP Server

SQL Anywhere lets you send email via MAPI and SMTP from inside your SQL code by calling system procedures like xp_sendmail(). However, many shops don't allow MAPI messages to be sent from within program code, and they don't run SMTP servers at all.

One solution is to use a Google Mail account to send emails via Google's SMTP server smtp.gmail.com.

(This article is based on Eric Farrar's original announcement of Gmail support in SQL Anywhere 12
and Mark Culp's update on the subject of using the Equifax certificate.)

In order to use smtp.gmail.com you need to call xp_startsmtp and provide three pieces of identification:

1. a Google Mail account,

2. the password for that account and

3. the SSL certificate currently used by Google Mail.

Items 1 and 2 are easy to get; here's how to get number 3:

• Choose a folder to store the SSL certificate; e.g., c:\temp

• Ask around to see if anyone knows what SSL certificate Google is currently using for smtp.gmail.com, and where you can get it from.

• Today's answers are "Equifax Secure Certificate Authority, and you can get if from GeoTrust".

• [skipping ahead] Open this link in your browser: Download - Equifax Secure Certificate Authority (Base-64 encoded X.509)

  

• Click right-mouse - Save As... anywhere on the page of carp stuff you see ( the page you get, not the image above :)

• Choose a File name: c:\temp\Equifax_Secure_Certificate_Authority.cer

• Choose a Save as type: Web Page, Complete

• Click on Save, then

• Edit the following script to specify the certificate file specification: SET @trusted_certificates = 'c:\\temp\\Equifax_Secure_Certificate_Authority.cer';

CREATE PROCEDURE send_email_via_gmail (
   IN @recipient       LONG VARCHAR,
   IN @subject         LONG VARCHAR,
   IN @message         LONG VARCHAR )
BEGIN

DECLARE @return_code           INTEGER;
DECLARE @smtp_sender           LONG VARCHAR;
DECLARE @smtp_server           LONG VARCHAR; 
DECLARE @smtp_port             INTEGER; 
DECLARE @timeout               INTEGER;
DECLARE @smtp_sender_name      LONG VARCHAR;
DECLARE @smtp_auth_username    LONG VARCHAR; 
DECLARE @smtp_auth_password    LONG VARCHAR;
DECLARE @trusted_certificates  LONG VARCHAR;

MESSAGE STRING ( '@recipient = "', @recipient, '"' ) TO CLIENT;
MESSAGE STRING ( '@subject = "', @subject, '"' ) TO CLIENT;
MESSAGE STRING ( '@message = "', @message, '"' ) TO CLIENT;

SET @smtp_sender          = 'your.name@gmail.com';
SET @smtp_server          = 'smtp.gmail.com';
SET @smtp_port            = 587;  -- 25 for standard SMTP, 587 for gmail
SET @timeout              = 10;   -- default is 60 seconds
SET @smtp_sender_name     = 'Your Name';
SET @smtp_auth_username   = 'your.name@gmail.com';
SET @smtp_auth_password   = 'your.password';
SET @trusted_certificates = 'c:\\temp\\Equifax_Secure_Certificate_Authority.cer';

@return_code = CALL xp_startsmtp ( 
   smtp_sender          = @smtp_sender,  
   smtp_server          = @smtp_server,  
   smtp_port            = @smtp_port,  
   timeout              = @timeout,
   smtp_sender_name     = @smtp_sender_name,
   smtp_auth_username   = @smtp_auth_username, 
   smtp_auth_password   = @smtp_auth_password,
   trusted_certificates = @trusted_certificates );

MESSAGE STRING ( 'xp_startsmtp @return_code = ', @return_code ) TO CLIENT;

@return_code = CALL xp_sendmail ( 
   recipient    = @recipient,  
   subject      = @subject,  
   "message"    = @message );

MESSAGE STRING ( 'xp_sendmail @return_code = ', @return_code ) TO CLIENT;

CALL xp_stopsmtp(); -- do not bother to check return code

EXCEPTION WHEN OTHERS THEN
   CALL xp_stopsmtp();

END;

You will also have to change a bunch of other "your.name" stuff; I'll leave that up to you.

Here's how to call that procedure from inside your SQL code:

CALL send_email_via_gmail (
   'breck.carter@gmail.com',
   'Testing...',
   'Hello, World!' );

You can also call it via dbisql to send an email from a Windows command file:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Testing...',^
  'Hello, World!' )

The backup command files from Demonstrating Full and Incremental Backups can be modified as follows to send OK / FAILED email messages; see the dbisql commands at lines 43 and 59 in 05_run_dbbackup12_full.bat:

REM ******************************************************************
REM Create empty backup\generation_temp subfolder
MD backup
CD backup
RD /S /Q generation_temp
MD generation_temp
CD ..

ECHO ********************************************^
*********************************************>>backup\generation_temp\dbbackup_log.txt
ECHO Full dbbackup started >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  -o backup\generation_temp\dbbackup_log.txt^
  -x^
  backup\generation_temp

IF ERRORLEVEL 1 GOTO backup_failed

REM ******************************************************************
REM Move backup to generation10 subfolder
CD backup
RD /S /Q generation1
RENAME generation2 generation1
RENAME generation3 generation2
RENAME generation4 generation3
RENAME generation5 generation4
RENAME generation6 generation5
RENAME generation7 generation6
RENAME generation8 generation7
RENAME generation9 generation8
RENAME generation10 generation9
RENAME generation_temp generation10
CD ..
DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO Full dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Full dbbackup OK - ddd12',^
  'See folder %CD%\backup\generation10' )

GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed
ECHO Error: Full dbbackup failed >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Full dbbackup FAILED - ddd12',^
  'See diagnostics in %CD%\backup\generation_temp\dbbackup_log.txt' )

GOTO end

:end

Also see the dbisql commands at lines 42 and 58 in 07_run_dbbackup12_incremental.bat:

ECHO ********************************************^
*********************************************>>backup\generation10\dbbackup_log.txt
ECHO Incremental dbbackup started >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

REM ******************************************************************
REM Make sure a full backup exists
IF EXIST "backup\generation10\ddd12.db" ( GOTO check_full_log_backup ) ELSE ( GOTO full_backup_is_missing )

:check_full_log_backup
IF EXIST "backup\generation10\ddd12.log" ( GOTO full_backup_exists ) ELSE ( GOTO full_backup_is_missing )

:full_backup_is_missing
ECHO Create a full backup before an incremental backup. >>backup\generation10\dbbackup_log.txt
GOTO backup_failed

:full_backup_exists
REM ******************************************************************
REM Backup to generation10\logs subfolder
CD backup\generation10
MD logs
CD ..\..

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  -o backup\generation10\dbbackup_log.txt^
  -n^
  -t^
  -x^
  backup\generation10\logs

IF ERRORLEVEL 1 GOTO backup_failed

REM ******************************************************************
REM Backup OK
DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO Incremental dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Incremental dbbackup OK - ddd12',^
  'See folder %CD%\backup\generation10\logs' )

GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed
ECHO Error: Incremental dbbackup failed >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  CALL send_email_via_gmail (^
  'breck.carter@gmail.com',^
  'Incremental dbbackup FAILED - ddd12',^
  'See diagnostics in %CD%\backup\generation10\dbbackup_log.txt' )

GOTO end

:end

Here's what the "OK" emails look like:

Gmail Motion

Demonstrating Full and Incremental Backups

This article demonstrates two Windows command files which can be used to automate both full and incremental log backups of a SQL Anywhere 12 database:

• The first command file creates a full backup of the database and transaction log files and preserves the last 10 full backups.

• The second command file creates an incremental backup of the transaction log file and stores an unlimited number of those backups together with the most recent full backup.

Those command files are numbers 5 and 7 in a series of 15 command files used to present the demonstration; here's where you can download the full set...


Here's the $read_me.txt file:

2011 04 01  Demonstrate Restoring from an Incremental Backup

Run these commands in order to create, backup, modify and restore a SQL Anywhere 12 database:

01_run_everything.bat                    CALLs all the command files 02 through 15
02_run_dbinit12.bat                      create database
03_run_dbspawn_dbsrv12.bat               start database
04_run_script.bat                        run 04s_script_to_initialize_data.sql
05_run_dbbackup12_full.bat               full backup
06_run_script.bat                        run 06s_script_to_change_data.sql
07_run_dbbackup12_incremental.bat        incremental backup
08_run_script.bat                        run 08s_script_to_change_data.sql
09_run_dbbackup12_incremental.bat         [ repeat 07 ]
10_run_script.bat                        run 10s_script_to_change_data.sql
11_run_dbbackup12_incremental.bat         [ repeat 07 ]
12_stop_drop_database.bat                delete database
13_restore_database.bat                  apply incremental log backups
14_run_dbspawn_dbsrv12.bat                [ repeat 03 ]
15_run_script.bat                        run 15s_script_to_display_data.sql

To delete everything so the demonstration can be repeated:

16_stop_drop_everything.bat

Command file 01_run_everything.bat automates the whole demonstration by calling every other command file from 02 through 15:

CALL 02_run_dbinit12.bat
CALL 03_run_dbspawn_dbsrv12.bat
CALL 04_run_script.bat
CALL 05_run_dbbackup12_full.bat
CALL 06_run_script.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 08_run_script.bat
CALL 09_run_dbbackup12_incremental.bat
CALL 10_run_script.bat
CALL 11_run_dbbackup12_incremental.bat
CALL 12_stop_drop_database.bat
CALL 13_restore_database.bat
CALL 14_run_dbspawn_dbsrv12.bat
CALL 15_run_script.bat
PAUSE All done...

Command file 02_run_dbinit12.bat starts the demonstration by creating an empty SQL Anywhere 13 database:

"%SQLANY12%\bin32\dbinit.exe"^
ddd12.db

Command file 03_run_dbspawn_dbsrv12.bat starts that database:

"%SQLANY12%\bin32\dbspawn.exe"^
-f "%SQLANY12%\bin32\dbsrv12.exe"^
-o dbsrv12_log_ddd12.txt^
-oe dbsrv12_log_fatal_ddd12.txt^
-os 10M^
-x tcpip^
-zl^
-zp^
-zt^
ddd12.db 

Command file 04_run_script.bat create a table and inserts one row:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 04s_script_to_initialize_data.sql

Here is the script file 04s_script_to_initialize_data.sql:

CREATE TABLE t1 (
pkey   INTEGER NOT NULL PRIMARY KEY,
data   INTEGER NOT NULL DEFAULT 0 );

INSERT t1 VALUES ( 1, 1 );

COMMIT;

Rotating Full Backup

Command file 05_run_dbbackup12_full.bat creates the full backup; you can set up a Windows Task Scheduler entry to run it on a regular basis (e.g., every day at 1 AM):

REM ******************************************************************
REM Create empty backup\generation_temp subfolder
MD backup
CD backup
RD /S /Q generation_temp
MD generation_temp
CD ..

ECHO ********************************************^
*********************************************>>backup\generation_temp\dbbackup_log.txt
ECHO Full dbbackup started >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbbackup.exe"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
-o backup\generation_temp\dbbackup_log.txt^
-x^
backup\generation_temp

IF ERRORLEVEL 1 GOTO backup_failed

REM ******************************************************************
REM Move backup to generation10 subfolder
CD backup
RD /S /Q generation1
RENAME generation2 generation1
RENAME generation3 generation2
RENAME generation4 generation3
RENAME generation5 generation4
RENAME generation6 generation5
RENAME generation7 generation6
RENAME generation8 generation7
RENAME generation9 generation8
RENAME generation10 generation9
RENAME generation_temp generation10
CD ..
DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO Full dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed
ECHO Error: Full dbbackup failed >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt
GOTO end

:end

Line 3 of the full backup command file 05_run_dbbackup12_full.bat makes sure that the backup subfolder exists. Lines 4 and 5 deletes the subfolder backup\generation_temp if it exists, and line 6 creates it again; this is where the new backup files will reside until the backup process has finished successfully.

The ECHO command on lines 9 and 10 writes a separator line to diagnostic log file dbbackup_log.txt. A new copy of dbbackup_log.txt is started for each full backup, and more records are added by this command file and by the later incremental log backups.

Lines 15 through 19 run dbbackup.exe to create a backup copy of the database and log files in the backup\generation_temp folder. The -x option tells dbbackup to delete and restart the primary transaction log file after the backup log file has been written. The -y option is not used because this command file doesn't overwrite any files.

The IF command on line 21 skips to the end of the command file if dbbackup.exe failed.

The commands on lines 23 through 37 preserve up to 10 most recent full backups:

• The oldest subfolder (generation1) is deleted if it exists,

• the 9 newer subfolders generation2 to 10 are given earlier names generation 1 through 9, if they exist, and

• the newest subfolder generation_temp is renamed generation10.

The commands on lines 38 through 42 record a successful backup in the diagnostic log file, and the commands on lines 44 through 50 record a failure.

Here's a snippet from dbbackup_log.txt showing the full backup:

*****************************************************************************************
Full dbbackup started 
Fri 04/01/2011 
09:32 AM
SQL Anywhere Backup Utility Version 12.0.1.3298
(582 of estimated 582 pages, 100% complete)
Transaction log truncated
Database backup completed
Volume in drive C is OS
Volume Serial Number is 5CD7-70E9

Directory of C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10

04/01/2011  09:32 AM    <DIR>          .
04/01/2011  09:32 AM    <DIR>          ..
04/01/2011  09:32 AM               291 dbbackup_log.txt
04/01/2011  09:32 AM         2,367,488 ddd12.db
04/01/2011  09:32 AM             8,192 ddd12.log
3 File(s)      2,375,971 bytes

Total Files Listed:
3 File(s)      2,375,971 bytes
2 Dir(s)  114,212,237,312 bytes free
Full dbbackup OK 
Fri 04/01/2011 
09:32 AM

Command file 06_run_script.bat inserts another row:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 06s_script_to_change_data.sql

Here is the script file 06s_script_to_change_data.sql:

INSERT t1 VALUES ( 2, 2 );

COMMIT;

Incremental Log Backup

Command file 07_run_dbbackup12_incremental.bat creates an incremental log backup; you can set up a Windows Task Scheduler entry to run it on a regular basis (e.g., every hour from 6 AM to 6 PM):

ECHO ********************************************^
*********************************************>>backup\generation10\dbbackup_log.txt
ECHO Incremental dbbackup started >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

REM ******************************************************************
REM Make sure a full backup exists
IF EXIST "backup\generation10\ddd12.db" ( GOTO check_full_log_backup ) ELSE ( GOTO full_backup_is_missing )

:check_full_log_backup
IF EXIST "backup\generation10\ddd12.log" ( GOTO full_backup_exists ) ELSE ( GOTO full_backup_is_missing )

:full_backup_is_missing
ECHO Create a full backup before an incremental backup. >>backup\generation10\dbbackup_log.txt
GOTO backup_failed

:full_backup_exists
REM ******************************************************************
REM Backup to generation10\logs subfolder
CD backup\generation10
MD logs
CD ..\..

"%SQLANY12%\bin32\dbbackup.exe"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
-o backup\generation10\dbbackup_log.txt^
-n^
-t^
-x^
backup\generation10\logs

IF ERRORLEVEL 1 GOTO backup_failed

REM ******************************************************************
REM Backup OK
DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO Incremental dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed
ECHO Error: Incremental dbbackup failed >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:end

Lines 1 through 5 of the incremental log backup command file 07_run_dbbackup12_incremental.bat assume that a full backup already exists in the backup\generation10 subfolder, so it's OK to continue writing information to the diagnostic log file dbbackup_log.txt.

Lines 7 through 16 put that assumption to the test by ensuring the backup file copies of the database and transaction log exist in the backup\generation10 subfolder; if they don't exist, there's really no point creating a further incremental log backup.

Lines 18 through 23 make sure that the backup\generation10\logs subfolder exists. It might exist already, and that's ok; unlike the full backup command file, this command file always writes the new incremental log backup file to the same subfolder rather than creating a new subfolder.

Lines 25 through 31 run dbbackup.exe to create an incremental backup copy of the transaction log file in the backup\generation_temp\logs folder. The

• -n option tells dbbackup to rename the backup copy of the transaction log file from ddd12.log to yymmddxx.log, where yymmdd is the date and xx is AA, AB, AC, ...,

• the -t option to backup the transaction log file, not the database file, and

• the -x option tells dbbackup to delete and restart the primary transaction log file after the backup log file has been written.

The -y option is not used because this command file doesn't overwrite any files.

The IF command on line 33 skips to the end of the command file if dbbackup.exe failed.

The commands on lines 35 through 41 record a successful backup in the diagnostic log file, and the commands on lines 43 through 49 record a failure.

Here's a snippet from dbbackup_log.txt showing one incremental log backup:

*****************************************************************************************
Incremental dbbackup started 
Fri 04/01/2011 
09:32 AM
SQL Anywhere Backup Utility Version 12.0.1.3298
(2 of estimated 2 pages, 100% complete)
Transaction log truncated
Database backup completed
Volume in drive C is OS
Volume Serial Number is 5CD7-70E9

Directory of C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10

04/01/2011  09:32 AM    <DIR>          .
04/01/2011  09:32 AM    <DIR>          ..
04/01/2011  09:32 AM             1,221 dbbackup_log.txt
04/01/2011  09:32 AM         2,367,488 ddd12.db
04/01/2011  09:32 AM             8,192 ddd12.log
04/01/2011  09:32 AM    <DIR>          logs
3 File(s)      2,376,901 bytes

Directory of C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs

04/01/2011  09:32 AM    <DIR>          .
04/01/2011  09:32 AM    <DIR>          ..
04/01/2011  09:32 AM             8,192 110401AA.log
1 File(s)          8,192 bytes

Total Files Listed:
4 File(s)      2,385,093 bytes
5 Dir(s)  114,212,229,120 bytes free
Incremental dbbackup OK 
Fri 04/01/2011 
09:32 AM

Command file 08_run_script.bat inserts a third row and creates a second table:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 08s_script_to_change_data.sql

Here is the script file 08s_script_to_change_data.sql:

INSERT t1 VALUES ( 3, 3 );

COMMIT;

CREATE TABLE t2 (
pkey   INTEGER NOT NULL PRIMARY KEY,
data   INTEGER NOT NULL DEFAULT 0 );

INSERT t2 VALUES ( 11, 11 );

COMMIT;

Command file 09_run_dbbackup12_incremental.bat creates another incremental log backup:

CALL 07_run_dbbackup12_incremental.bat

Command file 10_run_script.bat inserts a second row in the new table:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 10s_script_to_change_data.sql

Here is the script file 10s_script_to_change_data.sql:

INSERT t2 VALUES ( 22, 22 );

COMMIT;

Command file 11_run_dbbackup12_incremental.bat creates another incremental log backup:

CALL 07_run_dbbackup12_incremental.bat

Restore

Command file 12_stop_drop_database.bat simulates disaster by deleting the database:

PAUSE Are you sure you want to delete the database?

"%SQLANY12%\bin32\dbstop.exe" -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql" -y

PAUSE Wait until the database is stopped, then

ERASE /F ddd12.db
ERASE /F ddd12.log

Command file 13_restore_database.bat restores the full backup and applies the incremental log backups:

ECHO ********************************************^
*********************************************>>backup\generation10\dbbackup_log.txt
ECHO Restore started >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

COPY backup\generation10\ddd12.db

REM Note: -o writes to the dbbackup diagnostic file in this example.

"%SQLANY12%\bin32\dbsrv12.exe"^
-o backup\generation10\dbbackup_log.txt^
-oe dbsrv12_log_fatal_ddd12.txt^
-os 10M^
ddd12.db^
-ad backup\generation10\logs

REM ******************************************************************
REM Restore finished
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

Here's a snippet from dbbackup_log.txt showing how the dbsrv12 -ad option worked to apply the incremental transaction logs:

*****************************************************************************************
Restore started 
Fri 04/01/2011 
09:32 AM
I. 04/01 09:32:48. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/01 09:32:48. Developer edition, not licensed for deployment.
I. 04/01 09:32:48.  I. 04/01 09:32:48. Copyright © 2001-2011, iAnywhere Solutions, Inc.
I. 04/01 09:32:48. Portions copyright © 1988-2011, Sybase, Inc. All rights reserved.
I. 04/01 09:32:48. Use of this software is governed by the Sybase License Agreement.
I. 04/01 09:32:48. Refer to http://www.sybase.com/softwarelicenses.
I. 04/01 09:32:48.  I. 04/01 09:32:48. Connection limit (licensed seats): 3
I. 04/01 09:32:48. Processors detected: 1 (containing 8 logical processors)
I. 04/01 09:32:48. Maximum number of physical processors the server will use: 1
I. 04/01 09:32:48. This server is licensed to:
I. 04/01 09:32:48.     Developer Edition
I. 04/01 09:32:48.     Restricted Use
I. 04/01 09:32:48. Running Windows 7 Build 7600  on X86_64
I. 04/01 09:32:48. Server built for X86 processor architecture
I. 04/01 09:32:49. 3216K of memory used for caching
I. 04/01 09:32:49. Minimum cache size: 2956K, maximum cache size: 3473660K
I. 04/01 09:32:49. Using a maximum page size of 4096 bytes
I. 04/01 09:32:49. Multiprogramming level: minimum:8, current:20, maximum:80
I. 04/01 09:32:49. Automatic tuning of multiprogramming level is enabled
I. 04/01 09:32:49. Starting database "ddd12" (C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:49. Database recovery in progress
I. 04/01 09:32:49.     Last checkpoint at Fri Apr 01 2011 09:32
I. 04/01 09:32:49.     Checkpoint log...
I. 04/01 09:32:49.     Transaction log: C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs\110401AA.log...
I. 04/01 09:32:49. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:49. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:50. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:50. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:50.     Transaction log: C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs\110401AB.log...
I. 04/01 09:32:50. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51.     Transaction log: C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs\110401AC.log...
I. 04/01 09:32:51. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52.     Checkpointing...
I. 04/01 09:32:52. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52. Recovery complete
I. 04/01 09:32:52. Database server shutdown automatically after log applied
I. 04/01 09:32:52. Database server stopped at Fri Apr 01 2011 09:32
Fri 04/01/2011 
09:32 AM

Command file 14_run_dbspawn_dbsrv12.bat starts the freshly-restored database:

CALL 03_run_dbspawn_dbsrv12.bat

Command file 15_run_script.bat displays all the data in the database:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 15s_script_to_display_data.sql

Here is the script file 15s_script_to_display_data.sql:

SELECT 't1', * FROM t1
UNION ALL
SELECT 't2', * FROM t2 
ORDER BY 1, 2;

The output shows that both tables and all 5 rows have been successfully restored:

't1' pkey        data
----------------------------
t1   1           1
t1   2           2
t1   3           3
t2   11          11
t2   22          22

(5 rows)

Execution time: 0.02 seconds

Command file 16_stop_drop_everything.bat can be used to clean up so the demonstration can be started over again from scratch:

PAUSE Are you sure you want to delete all the files?

"%SQLANY12%\bin32\dbstop.exe" -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql" -y

PAUSE Wait until the database is stopped, then

ERASE /F ddd12.db
ERASE /F *.log
ERASE /F dbsrv12_log_ddd12.txt
ERASE /F dbsrv12_log_fatal_ddd12.txt
RMDIR /S /Q backup

Proxy {Variables}

There's a new feature in SQL Anywhere 12.0.1 that didn't get documented in the Help:

{Variable} references in remote access CREATE SERVER and CREATE EXISTING TABLE statements which allow you to dynamically change the remote server and remote table names without altering or re-executing the CREATE statements

Whew! That's a mouthful, not exactly suitable as a name for this new feature, so let's call it this:

Proxy {Variables}

While it doesn't have its own Help topic yet, it did get mentioned: Go to the Create directory access servers topic in the Help, then scroll down to Example 2 where you will see these examples of proxy {variables} in the CREATE SERVER ... USING and CREATE TABLE ... AT clauses:

CREATE SERVER dir CLASS 'directory' USING 'root={@directory}\\users;subdirs={@subdirs}';

CREATE EXISTING TABLE dbo.userdir AT '{@server};;;{@curuser}';

Here's another example; this code runs on database ddd1 to display row counts on databases ddd2 and ddd3 via proxy tables:

BEGIN
DECLARE other_engine_name    VARCHAR ( 128 );
DECLARE other_database_name  VARCHAR ( 128 );
DECLARE other_table_name     VARCHAR ( 128 );
DECLARE row_count            BIGINT;

CREATE SERVER other_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG={other_engine_name};DBN={other_database_name}';

SET other_engine_name   = 'ddd2';
SET other_database_name = 'ddd2';
SET other_table_name    = 'SYSARTICLE';

CREATE EXISTING TABLE proxy_systable
   AT 'other_server..SYS.SYSTABLE';

CREATE EXISTING TABLE proxy_table 
   AT 'other_server..SYS.{other_table_name}';

FOR f_fetch1 AS c_fetch1 INSENSITIVE CURSOR FOR
SELECT proxy_systable.table_name AS proxy_table_name 
  FROM proxy_systable
 WHERE proxy_systable.table_type = 'VIEW'
   AND proxy_systable.creator    = 0      -- SYS
 ORDER BY proxy_systable.table_name
FOR READ ONLY
DO

   SET other_table_name = proxy_table_name;

   SELECT COUNT(*)
     INTO row_count
     FROM proxy_table;

   MESSAGE STRING ( other_engine_name, ' ', other_table_name, ' ', row_count ) TO CLIENT;

END FOR;

SET other_engine_name   = 'ddd3';
SET other_database_name = 'ddd3';

FOR f_fetch2 AS c_fetch2 INSENSITIVE CURSOR FOR
SELECT proxy_systable.table_name AS proxy_table_name 
  FROM proxy_systable
 WHERE proxy_systable.table_type = 'VIEW'
   AND proxy_systable.creator    = 0      -- SYS
 ORDER BY proxy_systable.table_name
FOR READ ONLY
DO

   SET other_table_name = proxy_table_name;

   SELECT COUNT(*)
     INTO row_count
     FROM proxy_table;

   MESSAGE STRING ( other_engine_name, ' ', other_table_name, ' ', row_count ) TO CLIENT;

END FOR;

END;

• The CREATE SERVER statement on lines 7 and 8 contains two dynamic {variable} references to the local DECLARE variables defined on lines 2 and 3. Those variables don't have values yet because CREATE SERVER statements aren't actually executed until necessary; e.g., when a CREATE EXISTING TABLE is executed.

• The CREATE EXISTING TABLE statement on lines 17 and 18 contains another dynamic {variable} reference to the local variable defined on line 4. At this point the local variable must have a value (see line 12) because CREATE EXISTING TABLE is an executable statement. The other two {variables} must also have values at this point (see lines 10 and 11).

• The FOR loop on lines 20 through 37 displays the row counts for all the system views on the remote database, in this case ddd2. The SET statement on line 29 dynamically changes the {variable} that controls which remote table is actually used to satisfy the FROM clause on line 33.

• The SET statements on lines 39 and 40 change the remote database to ddd3, which causes the identical FOR loop on lines 42 through 59 to display the row counts for a completely different set of system views.

Here's the output:

ddd2 ST_GEOMETRY_COLUMNS 0
ddd2 ST_SPATIAL_REFERENCE_SYSTEMS 5
ddd2 ST_UNITS_OF_MEASURE 5
...
ddd2 SYSVIEW 128
ddd2 SYSVIEWS 128
ddd2 SYSWEBSERVICE 0
ddd3 ST_GEOMETRY_COLUMNS 0
ddd3 ST_SPATIAL_REFERENCE_SYSTEMS 5
ddd3 ST_UNITS_OF_MEASURE 5
...
ddd3 SYSVIEW 128
ddd3 SYSVIEWS 128
ddd3 SYSWEBSERVICE 0

Tip: This stuff doesn't actually work in the GA build of 12.0.1, you have to get the first EBF 12.0.1.3298 here.

Chrome Rocks!

There are a number of disadvantages to being an early adopter. One of those is becoming so discouraged with all the bugs and glitches in the new product that you stop using it, even when everything's fixed and the new product becomes Best of Breed.

Even when the new product becomes much better than the siht you're using now.

That was me two years ago, with the Google Chrome browser... I tried it... Crash! Kaboom!... and immediately ran back into the arms of Firefox and IE.

Now, Firefox and IE have driven me in the other direction; here's the story, step-by-step...

• The other day Firefox 3.6 insisted on downloading some new patches, but

• Norton Internet Security quarantined a critical component in that download, then

• Firefox (not unexpectedly) refused to start without that component, making it clear that

• (who am I to argue?) this is Mozilla's way of telling me to upgrade, and when

• I downloaded Firefox 4 it seemed to work OK, until
  
  

• Firefox 4 started going Crash! Kaboom! every time I touched Gmail, plus

• the rest of it was cluttered and confusing and just plain ugly, so for some (hitherto unexplained and clearly irrational) reason

• I decided to try Internet Explorer 9 and that seemed to go OK, except for the fact

• the new GUI is so completely hosed up that it can't even display the Minimize / Maximize / Close icons properly, plus

• the rest of it was cluttered and confusing and just plain ugly, leaving me...

...discouraged.

"What have I got to lose? Let's try Chrome again!"

Wow! What a difference two years makes! Not only is Chrome fast, Fast, FAST!

• it works with Gmail (surprise, surprise),

• it doesn't mess up the display, and

• the GUI is clean, Clean, CLEAN!

Plus, it got Today's Cool New Features like

• dragging a tab to create a separate window,

• dragging tabs between windows,

• an "Inspect element" feature that includes an execution profiler...
  
  

• and a convenient Console for in-your-face displays of JavaScript errors...
  
  

My experience with Chrome has been so much better than Firefox and IE that I've already made it my default browser, and I'm going to include "Start Foxhound via Chrome" shortcuts in the next release of Foxhound.

What's wrong with Chrome?

Here are the problems I've had so far, with workarounds:

• There's no window <TITLE> bar: if the page titles don't all fit on the tab bar, you can use a hover-over text box to see each one in turn.

• It's hard to find where to set options: click on the wrench icon to see a menu; if the option you want isn't in "Options", you might find it in "Tools".

• It's easy to get the bookmark bar confused with the tab bar: you could turn off the bookmark bar, but it's really handy so just get used to it.

• Chrome isn't anywhere near as popular as Firefox or IE: that I don't understand, especially for geeks and nerds; here are the browser stats for this blog:
  
  

The fRiDaY File

This was first published on March 27, 2006:

12.0.1.3298 EBF Now Available

The first EBF for SQL Anywhere 12.0.1 on Windows 32 and 64-bit is now available.

Note: This EBF has been uploaded to the website twice recently, same build but different file sizes...

March 22 - 237,964,016 bytes
March 24 - 237,963,968 bytes

...to fix a setup issue; the installed software is the same.

---

Some other new EBFs are also available here:

HP-UX Itanium 11.0.1.2574 EBF

Sun Solaris SPARC 11.0.1.2572 EBF

Sun Solaris x64 11.0.1.2574 EBF

Windows x86/x64 12.0.0.2644 EBF

Product Suggestion: Nested Internal Procedures

SQL Anywhere already has nested BEGIN blocks, complete with variable scope rules, local variables, even local EXCEPTION handling. What it needs is an extension to the BEGIN statement to define nested internal PROCEDURE blocks with CALL, RETURN and parameter passing.

A local variable defined at the front of a BEGIN block is created when the block is entered and destroyed when the block terminates. The scope of a local variable includes all the executable statements in the same BEGIN block, as well as all statements in any nested BEGIN block. - page 262, SQL Anywhere Studio 9 Developer's Guide

Before...

Here's an excerpt from a large stored procedure that mimics "calls" to local sections of code by using Y/N variables and IF statements. All the code is local to one single CREATE PROCEDURE because of the large number of local variables (over 200, not shown) that are shared across the different sections; using a separate CREATE PROCEDURE for each section of code would require thousands of lines of additional code to implement parameter passing.

CREATE PROCEDURE rroad_monitor_options (
   ...
BEGIN
   DECLARE @do_task_load_criteria_into_@variables                     VARCHAR ( 1 );
   DECLARE @do_task_copy_inputable_@variables_into_@old_variables     VARCHAR ( 1 );
   DECLARE @do_task_load_factory_settings_into_inputable_@variables   VARCHAR ( 1 );
   DECLARE @do_task_cancel_alerts_as_required                         VARCHAR ( 1 );
   DECLARE @do_task_replace_entire_target_row_with_@variables         VARCHAR ( 1 );
   ...
   SET @do_task_load_criteria_into_@variables                   = 'N';
   SET @do_task_copy_inputable_@variables_into_@old_variables   = 'N';
   SET @do_task_load_factory_settings_into_inputable_@variables = 'N';
   SET @do_task_cancel_alerts_as_required                       = 'N';
   SET @do_task_replace_entire_target_row_with_@variables       = 'N';
   ...
   WHEN @submit_form = 'Show Criteria' THEN 
      SET @do_task_load_criteria_into_@variables                   = 'Y';
      SET @do_task_copy_inputable_@variables_into_@old_variables   = 'Y';

   WHEN @submit_form = 'Switch Display' THEN 
      SET @do_task_load_criteria_into_@variables                   = 'Y';
      SET @do_task_copy_inputable_@variables_into_@old_variables   = 'Y';

   WHEN @submit_form = 'Restore Factory Settings' THEN
      SET @do_task_load_criteria_into_@variables                   = 'Y';
      SET @do_task_copy_inputable_@variables_into_@old_variables   = 'Y';
      SET @do_task_load_factory_settings_into_inputable_@variables = 'Y';
      SET @do_task_cancel_alerts_as_required                       = 'Y';
      SET @do_task_replace_entire_target_row_with_@variables       = 'Y';
   ...
   
   IF @do_task_load_criteria_into_@variables = 'Y' THEN
      ...
   END IF;

   IF @do_task_copy_inputable_@variables_into_@old_variables = 'Y' THEN
      ...
   END IF;

   IF @do_task_load_factory_settings_into_inputable_@variables = 'Y' THEN
      ...
   END IF;

   IF @do_task_cancel_alerts_as_required = 'Y' THEN
      ...
   END IF;

   IF @do_task_replace_entire_target_row_with_@variables = 'Y' THEN
      ...
   END IF;
   ...

...and After

Here's what that excerpt might look like with the crude SET and IF statements changed to CALL and INTERNAL PROCEDURE statements:

CREATE PROCEDURE rroad_monitor_options (
   ...
BEGIN
   ...
   WHEN @submit_form = 'Show Criteria' THEN 
      CALL load_criteria_into_@variables();
      CALL copy_inputable_@variables_into_@old_variables();

   WHEN @submit_form = 'Switch Display' THEN 
      CALL load_criteria_into_@variables();
      CALL copy_inputable_@variables_into_@old_variables();

   WHEN @submit_form = 'Restore Factory Settings' THEN
      CALL load_criteria_into_@variables();
      CALL copy_inputable_@variables_into_@old_variables();
      CALL load_factory_settings_into_inputable_@variables();
      CALL cancel_alerts_as_required();
      CALL replace_entire_target_row_with_@variables();
   ...

   INTERNAL PROCEDURE load_criteria_into_@variables()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE copy_inputable_@variables_into_@old_variables()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE load_factory_settings_into_inputable_@variables()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE cancel_alerts_as_required()
   BEGIN
      ...
   END;

   INTERNAL PROCEDURE replace_entire_target_row_with_@variables()
   BEGIN
      ...
   END;
   ...

Since the main point behind an internal procedure is to take advantage of local variable scope rules (inheritance), I could live without parameter passing altogether... but here's what the full BNF might look like:

<internal_procedure>        ::= INTERNAL PROCEDURE <procedure_name>
                                   "(" [ <parameter_list> ] ")"
                                   [ RESULT "(" <result_set_column_list> ")" ]
                                   <begin_block>

<parameter_list>            ::= <parameter_specification> { "," <parameter_specification> }

<parameter_specification>   ::= [ <parameter_disposition> ]
                                  <parameter_name>
                                  <data_type>
                                [ <parameter_default> ]

<parameter_disposition>     ::= IN
                              | OUT
                              | INOUT  -- default

<parameter_default>         ::= DEFAULT <expression>

<result_set_column_list>    ::= <result_set_column> { "," <result_set_column> }

<result_set_column>         ::= <identifier> <data_type>

In Praise Of Recursion

I'd like to say the code posted last week solved the problem it was intended to solve, but alas, that was not the case. The job of resetting the Alerts criteria for a target database is a lot more complex than replacing a row in a table; it includes

• checking to see if each setting has actually changed for each different alert (all 29 of them),

• and if it has,

• and if that alert is currently in effect,

• then that alert must be cancelled,

• and that in turn might require an "All Clear" email to be sent

• as well as rows to be inserted in other tables.

None of that was accomplished by the cute little FOR INSERT SELECT * loop posted earlier.

The good news is, all the code to perform those tasks for a single target database already existed inside the stored procedure being modified... all it needed was

A DIFFERENT Cute Little FOR Loop

Here's the actual solution; a lot of code's been removed for clarity (about 6500 lines worth) but nothing else has been changed:

CREATE PROCEDURE rroad_monitor_options (
   IN @submit_form         VARCHAR ( 100 ),
   IN @sampling_id         UNSIGNED INTEGER,
   IN @display_html_page   VARCHAR ( 1 ) )
   RESULT ( html_output LONG VARCHAR )
BEGIN

...

IF @sampling_id = 0 THEN

   CASE @submit_form

      ...

      WHEN 'Force Default Settings On All Targets' THEN

         -- Perform 'Restore Default Settings' tasks on each target row.

         FOR f_alerts_criteria AS c_alerts_criteria INSENSITIVE CURSOR FOR
         SELECT rroad_alerts_criteria.sampling_id AS @target_sampling_id
           FROM rroad_alerts_criteria
          WHERE rroad_alerts_criteria.criteria_set_type = 'Target Specific'
          ORDER BY rroad_alerts_criteria.sampling_id
         FOR READ ONLY
         DO

            -- Recursively call this procedure.

            CALL rroad_monitor_options ( 
               'Restore Default Settings',
               @target_sampling_id,
               'N' );                       -- @display_html_page

         END FOR;

         ...

   END CASE;

ELSE -- @sampling_id > 0

   CASE @submit_form

      ...

      WHEN 'Restore Default Settings' THEN

         -- existing code to reset the alerts criteria for a single target database

      ...

• The CREATE PROCEDURE on lines 1 through 5 is normally called from an HTML service. The first parameter @submit_form tells the procedure what button was pressed; in this case, the existing 'Restore Default Settings' button versus the new 'Force Default Settings On All Targets'.

• The second parameter @sampling_id is 0 for the row containing default values, and 1, 2, 3 ... for rows representing actual target databases being monitored by Foxhound.

• The third parameter @display_html_page is 'Y' when the procedure is called from the HTML service and 'N' when it is being called elsewhere.

• The WHEN on lines 16 through 35 handles the 'Force Default Settings On All Targets' button press. The FOR loop steps through each target database with each primary key being placed in @target_sampling_id.

• The recursive CALL on lines 30 through 33 is the heart of this solution. The three parameter values are set up to pretend the user has pressed the 'Restore Default Settings' on a single target database, with @display_html_page set to 'N' because nothing is being sent back to the browser.

• The WHEN starting on line 47 marks the begining of the existing code which handles a 'Restore Default Settings' button press. This is the code invoked by the earlier recursive CALL.

To tell the truth, I can't remember the last time I coded a recursive call in the real world, if I ever have... and it probably won't happen again any time soon.

Which is too bad, because when the technique works, it works really well. And SQL Anywhere fully supports recursion and all the stacking and unstacking it requires, no fuss, no muss.

Question: What about performance?

Answer: 100 targets in less than two seconds, so nobody's complaining.

The fRiDaY File

This was first published on March 26, 2006:

New 12.0.1 and 11.0.1 Releases

These new SQL Anywhere releases were posted on March 15:

12.0.1 Upgrade for IBM AIX, Mac OS, Sun Solaris SPARC

11.0.1.2569 EBF for Windows x86, x64

In Praise Of SELECT *

Foxhound has several examples of "The Table From Hell". Some of them are performance nightmares with millions of rows and gigabytes of data being inserted and deleted on a daily basis.

Others are relational nightmares with kludgy designs that seemed like a good idea at the time but have evolved to become perpetual PITAs.

The "alerts criteria" table is an example of the latter, with over 200 columns when there should be about 20. Here's an excerpt:

CREATE TABLE rroad_alerts_criteria (
   criteria_set_type                                            VARCHAR ( 100 ) NOT NULL CHECK ( @criteria_set_type IN ( 
                                                                   'Factory Settings', 
                                                                   'Saved Defaults', 
                                                                   'Target Specific' ) ),
   sampling_id                                                  UNSIGNED INTEGER NOT NULL,  -- sampling_id = 0 for 'Factory Settings'
                                                                                            -- sampling_id = 0 for 'Saved Defaults'
                                                                                            -- sampling_id > 0 for 'Target Specific'
   send_alert_emails                                            VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   mail_recipient                                               LONG VARCHAR NOT NULL,
   use_html_in_emails                                           VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   email_url_host_port                                          LONG VARCHAR NOT NULL,
   display_alerts                                               VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   previous_sample_set_number_processed                         UNSIGNED BIGINT NOT NULL,
   previous_gathered_sample_finished_at                         TIMESTAMP NOT NULL,
   current_sample_finished_at                                   TIMESTAMP NOT NULL,

   -- Alert #1. Foxhound has been unable to gather samples for [a001p1] or longer.

   alert1_enabled                                               VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   alert1_is_in_effect                                          VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   alert1_in_effect_at                                          TIMESTAMP NOT NULL,
   alert1_all_clear_at                                          TIMESTAMP NOT NULL,
   alert1_threshold_lost_sample_accumulated_msec                BIGINT NOT NULL CHECK ( @column >= 10000 ),               -- a001p1
   alert1_actual_lost_sample_accumulated_msec                   BIGINT NOT NULL,

   -- Alert #2. The heartbeat time has been 1s or longer for 10 or more recent samples.

   alert2_enabled                                               VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   alert2_is_in_effect                                          VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   alert2_in_effect_at                                          TIMESTAMP NOT NULL,
   alert2_all_clear_at                                          TIMESTAMP NOT NULL,
   alert2_threshold_canarian_msec                               BIGINT NOT NULL,                                          -- a002p1
   alert2_threshold_canarian_msec_sample_count                  BIGINT NOT NULL CHECK ( @column >= 1 ),                   -- a002p2
   alert2_actual_canarian_msec_threshold_reached_sample_counter BIGINT NOT NULL,

   ...

   -- Alert #29. There have been [1] or more cache panics per second for [10] or more recent samples.

   alert29_enabled                                               VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   alert29_is_in_effect                                          VARCHAR ( 1 ) NOT NULL CHECK ( @column IN ( 'Y', 'N' ) ),
   alert29_in_effect_at                                          TIMESTAMP NOT NULL,
   alert29_all_clear_at                                          TIMESTAMP NOT NULL,
   alert29_threshold_cache_panics                                BIGINT NOT NULL CHECK ( @column >= 1 ),                -- a029p1
   alert29_threshold_cache_panics_sample_count                   BIGINT NOT NULL CHECK ( @column >= 1 ),                -- a029p2
   alert29_actual_cache_panics_threshold_reached_sample_counter  BIGINT NOT NULL,

   PRIMARY KEY ( criteria_set_type, sampling_id ) );

With a table like that, any day you can code SELECT * instead of a SELECT-endless-list is a good day, and that's what I was hoping for when faced with this maintenance chore:

• Add a button "Force Default Settings On All Targets" to do the following:
  
  
  • Retrieve the "default settings" row with primary key [criteria_set_type; sampling_id] = ['Saved Defaults'; 0].

  • Use that row to update all the rows with primary key [criteria_set_type; sampling_id] = ['Target Specific'; 1, 2, 3, ...].

One brute-force solution might be to SELECT FROM the 'Saved Defaults' row into two hundred local variables, then loop through an UPDATE with two hundred SETs.

A slightly-less-brutish solution might be to move the FROM clause into the UPDATE; this avoids the local variables, but still has the two hundred SETs.

But wait, there's a better way...

Go Brute-Free With SELECT *

   SELECT *
     INTO LOCAL TEMPORARY TABLE temp_alerts_criteria
     FROM rroad_alerts_criteria
    WHERE rroad_alerts_criteria.criteria_set_type = 'Saved Defaults'
      AND rroad_alerts_criteria.sampling_id       = 0;

   FOR f_alerts_criteria AS c_alerts_criteria INSENSITIVE CURSOR FOR
   SELECT rroad_alerts_criteria.sampling_id AS @target_sampling_id
     FROM rroad_alerts_criteria
    WHERE rroad_alerts_criteria.criteria_set_type = 'Target Specific'
    ORDER BY rroad_alerts_criteria.sampling_id
   FOR READ ONLY
   DO

      -- Set the temporary table primary key to match the 'Target Specific' row.

      UPDATE temp_alerts_criteria 
         SET temp_alerts_criteria.criteria_set_type = 'Target Specific',
             temp_alerts_criteria.sampling_id       = @target_sampling_id;

      -- Copy all the temporary table columns into the 'Target Specific' row.

      INSERT rroad_alerts_criteria
      ON EXISTING UPDATE
      SELECT *
        FROM temp_alerts_criteria;

   END FOR;

Here's how it works:

• The SELECT on lines 1 through 5 creates a single-row temporary table holding the 'Saved Defaults' row. This table has exactly the same columns as the permanent rroad_alerts criteria table, and except for the primary key values, the single row holds exactly the same data as each and every 'Target Specific' row is going to hold in the end.

• The FOR statement on lines 7 through 13 retrieves the sampling_id column for each 'Target Specific' row.

• The UPDATE on lines 17 through 19 changes the temporary table primary key to match the current 'Target Specific' row.

• The INSERT ON EXISTING UPDATE statement on lines 23 through 26 replaces the 'Target Specific' with the data from the 'Saved Defaults' row.

The two uses of SELECT * don't just save initial coding effort, they make this block of code immune to most schema changes involving the rroad_alerts_criteria table, like adding or removing data columns.

Of course, none of this would be possible without SQL Anywhere's wonderful INTO LOCAL TEMPORARY TABLE and ON EXISTING UPDATE clauses.

SQLAnywhere-Forum.Sybase.com

The new SQL Anywhere question and answer website is now up and running, for real, no more beta...

SQLAnywhere-Forum.Sybase.com

The fRiDaY File

This was first published on March 25, 2006:

Great Moments in History: Fun With NULLs

One of the reasons lots of people (like me) don't like SQL NULLs is because so many people (like me) have trouble with them.

Some folks, however, have more trouble than others. Like the original creators of Oracle who confused NULL with the empty string.

Well, why else would Oracle automatically convert '' to NULL?

Not just convert it, but define it as being the same as NULL. In other words, in Oracle the predicate '' IS NULL is true:



Oracle's not the only one. Sun's JDBC-to-ODBC bridge was unable to distinguish an SQL NULL string from empty string...

Not a bug

It must be a feature because...

This is not a bug. Some databases consider an empty string to be synonymous with NULL.

Never mind what ANSI says, what does ANSI know?

Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as ''). Null is defined by the ISO SQL standard as different from both an empty string and the numerical value 0, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values.

Maybe it's a good thing Oracle bought Sun, gather all the stupids bugs in one place so they're easier to avoid.

Celebrating LIST() and STRING()

Here's a situation that pops up from time to time when working with MobiLink: The consolidated and remote databases are ready to go and all the MobiLink scripts have been coded, and it's time to start testing... but there's no data in any of the tables.

What's the quickest way to insert a single row into each table just to get started running synch tests?

To be more specific, how can you execute an INSERT statement that looks like this template, for each and every table in the database, so every column has at least one value?

INSERT owner.table VALUES ( 
            value    -- column description
          , value    -- column description
          , value    -- column description
);

Here's a SQL script that will do just that, using the following features of SQL Anywhere:

• the LIST() aggregate function is called twice, once to build each INSERT statement, and again to build each VALUES entry within an INSERT,

• the STRING() function is also used twice, to convert expressions into strings and concatenate them into longer strings, once for each INSERT and once for each VALUES entry,

• the EXECUTE IMMEDIATE statement is used to apply all the inserts to the database, and

• the WAIT_FOR_COMMIT option is used so it doesn't matter what order the inserts are executed in (referential integrity isn't checked until after all the inserts are finished).

If you're like me, aggregate functions (especially those OLAP WINDOW things) are mysterious to behold... but LIST() is worth the effort to understand because it's so flexible and powerful:

BEGIN
DECLARE @insert_statements   LONG VARCHAR;

SELECT LIST ( 
          STRING (
             'INSERT ', USER_NAME ( SYSTABLE.creator ), '.', SYSTABLE.table_name, ' VALUES ( \x0D\x0A',
             '            ',
             ( SELECT LIST ( 
                         STRING (
                            CASE SYSDOMAIN.domain_name
                               WHEN 'bigint'            THEN '99                 -- '
                               WHEN 'binary'            THEN '99                 -- '
                               WHEN 'bit'               THEN '1                  -- '
                               WHEN 'char'              THEN '''x''                -- '
                               WHEN 'date'              THEN 'CURRENT DATE       -- '
                               WHEN 'decimal'           THEN '99                 -- '
                               WHEN 'double'            THEN '99                 -- '
                               WHEN 'float'             THEN '99                 -- '
                               WHEN 'integer'           THEN '99                 -- '
                               WHEN 'long binary'       THEN '''x''                -- '
                               WHEN 'long nvarchar'     THEN '''x''                -- '
                               WHEN 'long varbit'       THEN '1                  -- '
                               WHEN 'long varchar'      THEN '''x''                -- '
                               WHEN 'nchar'             THEN '''x''                -- '
                               WHEN 'numeric'           THEN '99                 -- '
                               WHEN 'nvarchar'          THEN '''x''                -- '
                               WHEN 'smallint'          THEN '99                 -- '
                               WHEN 'st_geometry'       THEN 'NULL               -- '
                               WHEN 'time'              THEN 'CURRENT TIME       -- '
                               WHEN 'timestamp'         THEN 'CURRENT TIMESTAMP  -- '
                               WHEN 'timestamp with time zone' THEN 'CURRENT UTC TIMSTAMP  -- '
                               WHEN 'tinyint'           THEN '99                 -- '
                               WHEN 'uniqueidentifier'  THEN 'NEWID()            -- '
                               WHEN 'unsigned bigint'   THEN '99                 -- '
                               WHEN 'unsigned int'      THEN '99                 -- '
                               WHEN 'unsigned smallint' THEN '99                 -- '
                               WHEN 'varbinary'         THEN '''x''                -- '
                               WHEN 'varbit'            THEN '1                  -- '
                               WHEN 'varchar'           THEN '''x''                -- '
                               WHEN 'xml'               THEN '''''                 -- '
                            END CASE,
                            SYSCOLUMN.column_name,
                            '    ',
                            SYSDOMAIN.domain_name,
                            CASE SYSDOMAIN.domain_name
                               WHEN 'bigint'            THEN ''
                               WHEN 'binary'            THEN ''
                               WHEN 'bit'               THEN ''
                               WHEN 'char'              THEN STRING ( '( ', SYSCOLUMN.width, ')' )
                               WHEN 'date'              THEN ''
                               WHEN 'decimal'           THEN STRING ( '( ', SYSCOLUMN.width, ', ', 
                                                                            SYSCOLUMN.scale, ')' )
                               WHEN 'double'            THEN ''
                               WHEN 'float'             THEN ''
                               WHEN 'integer'           THEN ''
                               WHEN 'long binary'       THEN ''
                               WHEN 'long nvarchar'     THEN ''
                               WHEN 'long varbit'       THEN ''
                               WHEN 'long varchar'      THEN ''
                               WHEN 'nchar'             THEN STRING ( '( ', SYSCOLUMN.width, ')' )
                               WHEN 'numeric'           THEN STRING ( '( ', SYSCOLUMN.width, ', ', 
                                                                            SYSCOLUMN.scale, ')' )
                               WHEN 'nvarchar'          THEN STRING ( '( ', SYSCOLUMN.width, ')' )
                               WHEN 'smallint'          THEN ''
                               WHEN 'st_geometry'       THEN ''
                               WHEN 'time'              THEN ''
                               WHEN 'timestamp'         THEN ''
                               WHEN 'timestamp with time zone' THEN ''
                               WHEN 'tinyint'           THEN ''
                               WHEN 'uniqueidentifier'  THEN ''
                               WHEN 'unsigned bigint'   THEN ''
                               WHEN 'unsigned int'      THEN ''
                               WHEN 'unsigned smallint' THEN ''
                               WHEN 'varbinary'         THEN STRING ( '( ', SYSCOLUMN.width, ')' )
                               WHEN 'varbit'            THEN STRING ( '( ', SYSCOLUMN.width, ')' )
                               WHEN 'varchar'           THEN STRING ( '( ', SYSCOLUMN.width, ')' )
                               WHEN 'xml'               THEN ''
                            END CASE ),
                         '\x0D\x0A          , '
                         ORDER BY SYSCOLUMN.column_id )
                 FROM SYSCOLUMN
                         INNER JOIN SYSDOMAIN
                                 ON SYSDOMAIN.domain_id = SYSCOLUMN.domain_id
                WHERE SYSCOLUMN.table_id = SYSTABLE.table_id ),
             '\x0D\x0A); ' ),
          '\x0D\x0A\x0D\x0A'
          ORDER BY SYSTABLE.table_name )
  INTO @insert_statements
  FROM SYSTABLE
 WHERE USER_NAME ( SYSTABLE.creator ) = 'GROUPO'
   AND SYSTABLE.table_type = 'BASE';

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';

EXECUTE IMMEDIATE @insert_statements;

COMMIT;

END;         

• The outer SELECT statement on lines 4 through 91 retrieves each base table belonging to GROUPO in the SQL Anywhere 12 demo database, using data from the SYSTABLE catalog view.

• The outer LIST() function call on lines 4 through 87 gathers up all the SYSTABLE rows into a single string, and that string is placed in a variable on line 88.

• The STRING() function call on lines 5 through 85 builds the first parameter passed to the outer LIST() function: the generated INSERT.

• The two carriage return - line feed pairs on line 86 form the second parameter for the outer LIST() function: the separator between each INSERT in the list.

• The ORDER BY clause on line 87 instructs the outer LIST() to sort the generated INSERT statements in table name order.

• The inner SELECT on lines 8 through 84 retrieves each column for the table in the outer SELECT, using data from the SYSCOLUMN and SYSDOMAIN catalog views.

• The inner LIST() function call on lines 8 through 80 gather all the SYSCOLUMN rows into a single string.

• The STRING() function call on lines 9 through 78 is the first parameter passed to the inner LIST(), the separator is specified on line 79 and the list's ORDER BY clause is on line 80.

• The CASE expression on lines 9 through 41 determines what literal to use for this column in the VALUES list.

• The expressions on lines 42 through 78 build a --comment that's very handy if the output has to be customized.

Here's what the first few generated INSERT statements look like in the string:

INSERT GROUPO.Contacts VALUES ( 
            99                 -- ID    integer
          , 'x'                -- Surname    char( 20)
          , 'x'                -- GivenName    char( 20)
          , 'x'                -- Title    varchar( 34)
          , 'x'                -- Street    char( 30)
          , 'x'                -- City    char( 20)
          , 'x'                -- State    char( 16)
          , 'x'                -- Country    char( 16)
          , 'x'                -- PostalCode    char( 10)
          , 'x'                -- Phone    char( 13)
          , 'x'                -- Fax    char( 13)
          , 99                 -- CustomerID    integer
); 

INSERT GROUPO.Customers VALUES ( 
            99                 -- ID    integer
          , 'x'                -- Surname    char( 20)
          , 'x'                -- GivenName    char( 20)
          , 'x'                -- Street    char( 30)
          , 'x'                -- City    char( 20)
          , 'x'                -- State    char( 16)
          , 'x'                -- Country    char( 16)
          , 'x'                -- PostalCode    char( 10)
          , 'x'                -- Phone    char( 13)
          , 'x'                -- CompanyName    char( 32)
); 

INSERT GROUPO.Departments VALUES ( 
            99                 -- DepartmentID    integer
          , 'x'                -- DepartmentName    char( 40)
          , 99                 -- DepartmentHeadID    integer
); 

This method doesn't just work for the demo database, it works for a lot of real-world databases... but not all.

When it doesn't work perfectly, however, it's a good start: Change the code to save the generated inserts in a file instead of executing them directly, and then edit the file to fix whatever's wrong.

A Return To The True Path

If you're trying to explain the Hallmarks of SQL Anywhere but you're pressed for time, here's what you can say instead:

"Watcom does things the way they should be done."

Nowhere in SQL Anywhere does that statement hold true more often than in the choice of default values for various options and settings. For example,

• string comparisons are case insensitive by default,

• the database page size is 4K by default,

• a transaction log is created by default, and

• string comparisons include trailing blanks by default (in spite of what ANSI says)

and that's just the dbinit utility; there are literally hundreds of other intelligent defaults throughout SQL Anywhere. If all you know is SQL Anywhere that might not seem like such a big thing, but if you're familiar with, say, Oracle or DB2 you know all about dumb defaults.

The fact that dumb default values are so rare in SQL Anywhere is the reason that it's Big News when a dumb default gets fixed. Here's the latest...

Database Checksums Are Now The Default!

When database page checksum validation was first introduced in SQL Anywhere Version 10, the default was "off". That means if you were creating a new database, and you hadn't heard about this new feature, you probably didn't specify dbinit -s and as a consequence your database pages were created without checksums.

And as a consequence of that you didn't receive early notification of database file corruption.

Eventually, all dumb defaults get fixed. That was the case with 4K page sizes (yes, the default was once 2K) and now, with Version 12, dbinit -s is the default which means everyone gets checksums by default.

The fRiDaY File

...How LinkedIn Helps The Downtrodden:

Assertion Relief

The way SQL Anywhere handles assertions is in direct conflict with the goal of providing a self-managing database (see Automatic Restart).

Further evidence of this conflict may be found in the facts that this feature request has received 8 positive votes plus one positive comment in the past year, but no replies:

---

Recover from single-row assertion errors

Sometimes the server raises an assertion error for a problem with a single row. Here is a really obscure example, but there are other more common ones:

   *** ERROR *** Assertion failed: 200610 (11.0.1.2276)

   Attempting to normalize a non-continued row (0x12345:0x0)

When this happens it is very difficult if not impossible to rescue the database. In this case any attempt to SELECT that row causes the server to stop, and because there is no indication what row is involved, and because there are millions of rows in that table, the database had to be restored from a backup.

Here's an analogy: A airliner is halfway across the Pacific when the light in one of the lavatories burns out (the assertion); this in turn causes all the engines to shut down and the plane to crash in the middle of the ocean.

In the real world, one of the flight attendants puts an "out of order" sign on the lavatory and makes a note for the aircraft maintenance crew to "replace bulb".

In the SQL Anywhere world, the server should throw an exception to the affected client, log the problem for later attention... AND CARRY ON!

...no need to spread baggage and body parts over miles of ocean!

Comment...

I think this one deserves a stir-up. The whole business of assertions needs some examination. If we are going down the self-managing database route and (let's say) an index gets corrupted, isn't there an argument that the engine should just get on and fix it. Certainly flash some red lights and let people know, but do the fix anyway. After all, what's the dba going to do - drop and recreate the index, and then keep an eye on things. Obviously harder / not possible if it's real data corruption.

---

Analogies Prove Nothing

A good analogy can be used to explain an important point or position, but even the best analogy breaks down when closely examined. The airliner analogy is no exception; here's a problem report that indicates the trouble with assertions is worse than the analogy indicates: a single assertion can bring a whole fleet of aircraft crashing down:

---

"We have an SQLAnywhere infrastructure setup where we have one database service that runs multiple database instances. This has been working well for quite some time..."

"However, yesterday we experienced an "Assertion Failed" error that took down the whole database service, but we could not find which database had caused this problem. Our troubleshooting process eventually led us to having to split each database into its own separate service which then got our customers up and running in a stable way and when the crash happened again we were able to pinpoint the single database with the problem."

---

This response indicates that some relief is on the way... if crashing only one aircraft instead of the whole fleet can be called relief:

---

"...we plan to make changes to allow corrupted databases to be stopped without bringing down the server. Printing database information to related assertion failures should be part of that."

---

Good news

News that some action, any action on assertions is being taken is good news in itself. It sure beats the old excuse that assertions are necessary to protect data integrity.

More, however, is necessary... much more.

Oops, sorry!

It's been over a year since the last "Database Deadpool" article was posted here: We Don't Need No Steenking Backup!

Maybe the situation's getting better, or maybe not... not when it's Google:

Google Gmail Snafu Lesson: Backup, Backup, Backup

Gmail is hard at work restoring service to about 40,000 Gmail users after a software bug deleted their e-mail messages, folders, labels and filters.

...

However, it's not just Gmail and other Webmail services that are the problem; we're increasingly using cloud-based tools for work and communication such as Twitter, Facebook, Google Docs, Microsoft Office Live, Tumblr, Wordpress, Blogger, Posterous, Flickr, Picasa, and on and on.

But that doesn't mean you should forego a solid back-up plan for all your online data. If the worst ever does happen, and a free Web service dumps your stuff permanently, the only response you can reasonably expect from these companies is: "oops, sorry."