If you’re new to IQ (or SQL Anywhere) you may run into an oddity that if you connect with ISQL the following code works but doesn’t work in DBISQL:
WHERE col1_date >= '06/01/2014'
This is because any connection to IQ using the native connection (SQL Anywhere connection) that is used by dbisql and the like expects string to datetime format of “YYYY-MM-DD HH:NN:SS.SSS”. This is not changeable by setting an option. The string needs to be converted using a style (datetime string style # 103) prior to being used as a SARG.
WHERE col1_date >= convert(datetime, '06/01/2014', 103)
Even those of us that have been around for eons tend to forget ‘little’ gotchas like this.
From http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X41864.htm :
Key “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.
0 or 100
mon dd yyyy hh:mm AM (or PM)
dd mon yy
mon dd, yy
9 or 109
Default + milliseconds
mon dd yyyy hh:mm:ss AM (or PM)
16 or 116
mon dd yyyy HH:mm:ss
yy/mm/ddHH:mm AM (or PM)
The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.
SAMP is a messaging protocol that enables astronomy software tools to interoperate and communicate. IVOA members have recognised that building a monolithic tool that attempts to fulfil all the requirements of all users is impractical, and it is a better use of our limited resources to enable individual tools to work together better. One element of this is defining common file formats for the exchange of data between different applications. Another important component is a messaging system that enables the applications to share data and take advantage of each other’s functionality. SAMP is intended to build on the success of a prior messaging protocol, PLASTIC, which has been in use since 2006 in over a dozen astronomy applications and has proven popular with users and developers. SAMP is an IVOA-endorsed standard that builds on this success. It is also intended to form a framework for more general messaging requirements. – excerpt
Alasdair chose to work with Perl for his implementation of the draft specification. What I find interesting, is that while it is designed for astronomy tools, it is generic enough for it to be implemented outside of that scope. (pun intended) I can easily see it being used as in a data federation type application. The protocol is XML-RPC based and is easily understood.
Great work! 🙂
Oh, you want to know what he has called his Perl implementation? It’ll cost ya! Just joking.. it is called Perl::Samp and consists of:
Perl::Samp requires you to install the following modules from CPAN:
The opinions expressed within are the sole rantings of a raving lunatic and in no way reflect the rantings, fits, tantrums, errors, corrections, allocutions, or aimless thoughts of Sybase or its employees or of TeamSybase or ISUG. Any resemblance to reasonable thought, or any official or published opinion of Sybase, TeamSybase or ISUG is merely coincidental, and should be totally ignored.