Question Re: SQL Anywhere Procedures/Functions language in Technology Q&A

SQL Anywhere Procedures/Functions language

Former Member — Thu, 02 Dec 2010 15:21:39 GMT

Been an analyst using SQL Anywhere for a few years and just getting into database management. Regarding writing/developing procedures/functions, is there a particuliar programming language that is used or patterned after?

Re: SQL Anywhere Procedures/Functions language

MarkCulp — Thu, 02 Dec 2010 21:05:24 GMT

SQL Anywhere supports two dialects of stored procedures:

The original dialect was introduced into the product in 1992 and is called WATCOM SQL. The name is derived from the name of the company "WATCOM" and name of the product at that time. See http://en.wikipedia.org/wiki/Watcom for more information about Watcom the company. In its early years, WATCOM was known for its compiler technology and in particular its WATFOR Fortran compiler which was used by Universities around the world. As such, I would presume that the experience learned creating these compilers had some effect on the design of this dialect.
The second dialect is TSQL and was added to the SQL Anywhere product in version 6 and is based on the TSQL dialect developed and used by Sybase ASE (and inherited by Microsoft SQL Server).

The WATCOM SQL dialect closely resembles the ISO/ANSI SQL/2008 standard.

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Fri, 03 Dec 2010 08:54:43 GMT

@Mark: Am I right to notice some kind of bias in favour of the first dialect in your description? If so, I would fully agree:) - And I am sure Watcom SQL seems much more familiar to those with experience in common programming languages...

Re: SQL Anywhere Procedures/Functions language

MCMartin — Fri, 03 Dec 2010 11:47:21 GMT

It doesn't really matter if you prefer watcom or T-SQL because you can translate the code from watcom to T-SQL and vice versa inside the Sybase Central

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Fri, 03 Dec 2010 12:24:11 GMT

To complete the picture:

I guess your question is focused on stored functions and procedures in SQL, and that's what the answers explain.

But you can also write (or re-use) stored procedures and functions in other programming languages and create an SQL interface for them. This includes

C/C++ via Embedded SQL/ODBC
CLR (aka Dot.Net)
Java
Perl
PHP

If this is your intention, have a look at the CREATE PROCEDURE statement for external procedures.

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Fri, 03 Dec 2010 12:44:00 GMT

...though there are advanced cases of Watcom SQL code that cannot be converted to T-SQL (and possibly vice versa).

Re: SQL Anywhere Procedures/Functions language

Breck_Carter — Fri, 03 Dec 2010 12:58:19 GMT

I was wondering when someone was going to mention all the other languages 🙂

Re: SQL Anywhere Procedures/Functions language

Breck_Carter — Fri, 03 Dec 2010 12:59:52 GMT

I vote "Watcom SQL"... 99.9% of Foxhound is written in it, including the enire GUI.

Re: SQL Anywhere Procedures/Functions language

MarkCulp — Fri, 03 Dec 2010 13:22:28 GMT

My preference is Watcom SQL, but users that are familiar with ASE or MS SQL may be more comfortable using the TSQL dialect.

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Fri, 03 Dec 2010 13:51:29 GMT

@Breck: Are you saying I have insulted the general "Stealth marketing" rule? I feel ashamed:)

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Fri, 03 Dec 2010 14:13:28 GMT

@Mark: By the way, are there any iAnywhere staff members who have been involved with the development of the Watcom compiler products? (Yep, call me curios.)

Re: SQL Anywhere Procedures/Functions language

MarkCulp — Fri, 03 Dec 2010 15:45:10 GMT

@Volker: Yes, there are still a few.

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Fri, 03 Dec 2010 17:20:12 GMT

@Mark: Hmm, you can bet the curiosity isn't really satisfied, I was looking for names... - I guess us users here do not only share a admiration for you SQL heroes, there's place for compiler gurus, too:)

Re: SQL Anywhere Procedures/Functions language

MarkCulp — Fri, 03 Dec 2010 20:48:56 GMT

@Volker: I am a little reluctant to name others without their permission. I will say that I know of at least six staff that worked on the Watcom compilers/interpreters and (trying not to give away too much info) some of them are users on this forum. I'll leave it as an exercise to the reader to figure out who. Note: I am not one of them.

Re: SQL Anywhere Procedures/Functions language

VolkerBarth — Sat, 04 Dec 2010 11:06:18 GMT

@Mark: I fully appreciate your point of view, as it really would be unfair to tell about other persons's CV. (And yes, as a typical German citizen, I do care much about privacy - cf. the German Google StreetView debate...) - Besides that, I do have some suspicions:)

Re: SQL Anywhere Procedures/Functions language

Former Member — Sat, 04 Dec 2010 12:44:54 GMT

I've discovered you can't mix these two constructions in the same stored procedure (no matter how many lines apart they are) ...select <calculation> as VariableName1... and ...select VariableName2 = <calculation>... (disclaimer: I only know up to version 10.0.1)

Re: SQL Anywhere Procedures/Functions language

Former Member — Fri, 10 Dec 2010 21:56:16 GMT

Any suggestions on how to get started writing procedures/functions?