There are 8 session tracks at TechEd 2013 in Las Vegas on October 21 to 25...
but only 7 of them are represented so far in the Session Catalog...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)
so it's may be a bit early to look for the SQL Anywhere and MobiLink sessions but let's do that anyway...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
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
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.
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.
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.
[click here to go straight to the code]
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
.jpg)
CREATE PROCEDURE http_head_attributes ( IN url LONG VARCHAR ) RESULT ( attribute LONG VARCHAR, value LONG VARCHAR ) URL '!url' TYPE 'HTTP:HEAD';
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.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"
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;
.jpg "Interactive SQL CALL catalog_link_checker()")
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
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
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]
...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.
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;
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...
Let's say you use Drupal software (or some other product, it doesn't really matter, but let's say it's Drupal),
Have you ever heard of Parkinson's Law?
Sure you have. Now how about this version?
Work expands so as to fill the time available for its completion.
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.
Requirements expand to fill the computing power available.
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".
+-+due+to+-+accident+(to)+-+watercraft+-+burning+-+unpowered+-+water+skis+V90.27.jpg)
When making outlandish claims, it's always important to check your sources:
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!"
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!"
"%SQLANY16%\bin64\dbinit.exe" ddd16A.db "%SQLANY16%\bin64\dbinit.exe" ddd16B.db
Here's the code, plus a test SELECT..."%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"
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 -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."%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"
CREATE SERVICE web_service
TYPE 'RAW'
AUTHORIZATION OFF
USER DBA
AS CALL web_server_procedure (
:service_parm1,
:service_parm2 );
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 :)http://localhost:12345/web_service?service_parm1=Hello&service_parm2=World
Here's the code for a SQL Anywhere web client function:"%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"
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.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';
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
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 -->
Tip: If you don't get any rows back, it might mean TYPE 'HTTP:GET' is missing from the web-style CREATE PROCEDURE statement.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' );
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
SELECT TRIM ( value ) AS Body FROM web_client_procedure ( '0345529057' ) WHERE attribute = 'Body';
