While SQL Anywhere has supported sending email for many releases, it was not able to send email through secure SMTP servers (such as the popular smtp.gmail.com server) because they require TLS connections. This limitation had been mentioned before by users trying to set up the SQL Anywhere Monitor to use GMail accounts for sending alert messages.
In SQL Anywhere “Innsbruck”, the xp_startsmtp function has been enhanced to accept a certificate file that is used to authenticate a remote SMTP server. The remainder of this post will be a tutorial on setting up SQL Anywhere to send email through GMail’s SMTP server.
According to Google, their SMTP server is located at smtp.gmail.com, and TLS connections are made on port 587. In order to connect, we will need to supply xp_startsmtp with the path to the root certificate that Google is using as their certifying authority. Most mail application include the root CA certificates of the major certifying authorities such as Verisign, Equifax, etc. However, since SQL Anywhere is not a mail reader, it does not come bundled with these root certificates. How can we determine what certifying authority GMail is using, and where do we get the root certificate?
One way is to determine this is to use a program like OpenSSL to create a raw connection to the SMTP server and examine the certificate chain. (As it turns out, since GMail is so popular, a quick Google search for “smtp.gmail.com root certificate’ would give us the answer. However, we are trying to find a general solution that will work equally well for less popular services)
You can create a raw connection to smtp.gmail.com with openssl using:
Examining these lines tells us that the smtp.gmail.com certificate is signed by the Thawte Premium Server CA certificate issued by Thawte Consulting. There are two ways we can obtain a copy of this root certificate.
The first, and easiest, is to visit the certifying authorities’ website and download their root certificate. For example, the Thawte certificates can be downloaded from https://www.thawte.com/roots/index.html.
However, if it is a popular root certificate, chances are your operating system or browser may already contain the certificate. For example, to export the Thawte Premium Server CA certificate out of IE 8.0 you can go Tools –> Internet Options –> Content –> Certificates –> Trusted Root Certificates. Find “Thawte Premium Server CA” in the list, and hit the “Export” button. In the Export wizard, choose “Base-64 (.CER) format”. Other browsers such as FireFox and Chrome allow you to export certificates in a similar way.
Now that we have all the pieces, we are ready to start sending emails. Start a SQL Anywhere “Innsbruck” database and connect to it through Interactive SQL. Issue the following query:
If a connection was successful, this query will return a 0. If the connection was not successful, consult the SQL Anywhere documentation to determine the meaning of the error code.
Once the connection has been established, you can use xp_sendmail to send emails.
I have been spending a lot of time recently preparing SQL Anywhere for use on Amazon EC2. Deep in the nuts-and-blots of the Amazon Web Services system, Hash-based Message Authentication Codes (HMAC) are used for authenticating all requests. Usually this authentication is handled automatically by the tools that you are using (ex. AWS Management Console). However, if you want to talk directly to the Amazon Web Services through HTTP, you will need the ability to apply an HMAC code to each of your requests. This is exactly the situation I found myself in the other day when I wanted to see if SQL Anywhere could interact directly with Amazon S3 (hopefully my findings on this will be the subject of a future post).
I am excited to announce the beta availability of a new project from Sybase, UltraLiteWeb. UltraLiteWeb is a set of enhancements to the open source Gears project that aims to provide it with additional data management and synchronization capabilities.
Gears, an open source project first started a few years ago, was originally conceived to create more powerful web applications by enhancing browsers with new features and functionality. One of its original features was the ability for web applications to create local SQLite databases that it could use to store application data. However, one of the results of having web applications that can store local data is that you end up with multiple copies of the data that need to be resolved through synchronization. Unfortunately, the lack of integrated synchronization support within SQLite, along with the lack of encryption, makes it a difficult fit in enterprise scenarios.
UltraLiteWeb was designed to address these problems.
UltraLiteWeb is a patched build of Gears that enhances it by giving developers the option of using UltraLite as an alternative to SQLite. With UltraLiteWeb, developers can take advantage of the robust synchronization logic that is already integrated into UltraLite. By using UltraLiteWeb in conjunction with a MobiLink server, developers will be able to create robust, secure, data rich browser-based applications without needing to worry about all the complications of synchronization.
UltraLiteWeb introduces a new JavaScript API for communicating with the UltraLite databases that is unlike any of the other UltraLite APIs. This is because the UltraLiteWeb API is based off of the already existing Database API in Gears. A sample of the JavaScript API is shown below:
1
2
3
4
5
6
7
8
// create databasevar db = sybase.ultraliteweb.factory.create('beta.uldatabase');var options ={case:"Respect",// make database case-sensitive
page_size:"8k",// set 8kb page size
date_order:"MDY"// set date order to be Month-Day-Year};
db.open('database-cars', options);
UltraLiteWeb is available in beta for Firefox on Windows and Linux, and Internet Explorer on Windows. For more information, take a look at the About and FAQ pages. The UltraLiteWeb code is available under the Apache 2 license.
If you have any questions or feedback about UltraLiteWeb, please feel free to post them on the SQL Anywhere Web Development forum.
SQL Anywhere support for Python was first added a year ago with the release of SQL Anywhere 11.0.0. The code for the Python driver is open source, and publicly hosted on Google Code. To further broaden our Python support, we have recently created a SQL Anywhere driver for one of Python’s most popular web development frameworks, Django.
Django is a, “high-level Python Web framework that encourages rapid development and clean, pragmatic design.” Django includes its own Object-relational-mapper framework that allows programmers to interact with the database using only Python objects. However unlike some other ORMs, it does allow programmers to drop into SQL at any time.
If you have never tried Django, one of its features that is worth checking out is the slick administration panel that Django automatically creates.
Similar to the Python driver, the Django code is open source, and publicly hosted on Google Code. If you have any feedback or questions, feel free to ask them on the SQL Anywhere Web Development forum.
There was recently a question in the SQL Anywhere Web Development Forum regarding how to use TYPE 'HTML' SQL Anywhere web services with dynamically generated queries that use EXECUTE IMMEDIATE code. In answering that question, I created a function called dynamic_sql_to_html that mimicked the output of the built-in TYPE 'HTML' services. The function takes a single query string as input, and returns a fully-formatted HTML page as output. The code for that function can be found here.
For this blog post, I have created a simplified version of the function called dynamic_sql_to_html_table that only returns the HTML code for the table definition, allowing it to be used more flexibly.
I have been accepted as a speaker at ZendCon 2009 in San Jose in October. The conference runs from October 19th to the 22nd. I am speaking on the 20th from 10:00 am – 11:00 am on the topic Planning for Synchronization with Browser-Local Databases
The availability of browser-local databases (HTML5, Gears) is blurring the boundary between online web applications and offline desktop applications. However, these technologies introduce a new problem: data synchronization. This talk highlights the hidden challenges of synchronization and how to plan for them. Topics include primary key generation, handling deletes, scalability, and performance.
The simple INI file has been a popular way for applications to persist application data in a human-editable, flat-file. A example INI file is:
1
2
3
4
5
6
7
8
; last modified Thursday, July 23 2009 @ 3:59 PMname= Eric Farrar company= Sybase iAnywhere [Car]make= Honda model= Prelude ; “S” trimlineyear= 1993
While there is no specific standard on how INI files are organized, typically they contain:
a single flat-file that is arranged into a set of key/value pairs
pairs can be grouped under section headings
comments start at a semi-colon, and run to the end of a line
In most cases, an INI file is implemented as a simple way to persist application data and preferences after shutdown. Of course, if an application already contains an embedded database (such as SQL Anywhere), there should be no need to use an INI file since the database is the natural place to persist information. The only times that it would be useful for SQL Anywhere to interface with an INI file is when that file is maintained by an application outside of your control.
I recently ran into such a case, and I wrote of couple of simple procedures to let SQL Anywhere read and write INI files. The code for the two procedures, read_ini() and write_ini() can be found here.
Throughout this series we have been incrementally building an external JavaScript stored procedure to parse, and insert JSON-encoded data into a SQL Anywhere database. In this final post, we will modify our procedure to accept JSON strings with more complex embedded objects and arrays. We will also make the procedure available directly through a SQL Anywhere web service.
In the first part of this series we looked at the anatomy of a basic JavaScript stored procedure, and in the second part we discussed how to include well-known JavaScript libraries. In this part, we will get to the real meat: scripting the JDBC connection and interacting with the database. [Read more →]
In the first part of this series we looked at creating and running Rhino-compiled JavaScript stored procedures inside SQL Anywhere. In this part we will look at how to include JavaScript libraries.
As I have mentioned in other posts, JavaScript is best known for its use in the browser. JavaScript in the browser has no concept of including libraries inside the script (similar to import or include in other languages). Instead, libraries are added by adding the appropriate <script type='text/javascript' src='...' /> tags in the HTML file. Under the covers, the browser creates a single JavaScript scope, and it evaluates all of the <script> tags in that scope.
For example, suppose we include the JavaScript JSON2 library inside our HTML file, what happens? Looking at the code in that file, it does two things: First, it creates a JSON object in the global scope. Second, it defines a self-invoking function that adds two methods, parse and stringify, to the JSON object. All future scripts executed in that browser scope will now have access to the JSON object and its methods. The important thing to note here is that it is the browser that is handling combining (executing) all of these scripts inside the same scope. Similarly, if we want to include multiple scripts together in our stored procedure, we will need to do the work of setting up a scope, and executing all the scripts within it.
To accomplish this we will need to write some Java code that manages a Rhino context and scope. To demonstrate it, we will create a stored procedure that returns a JSON-formatted array of the argument to that stored procedure. We will use the JavaScript JSON2 library to generate the JSON object. [Read more →]
