SQL Anywhere Procedures/Functions language

[Former Member]

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?

[MarkCulp · Accepted solution]

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.

[VolkerBarth · Accepted solution]

@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...

[Breck_Carter · Accepted solution]

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

[MarkCulp · Accepted solution]

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

[VolkerBarth · Accepted solution]

@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.)

[MarkCulp · Accepted solution]

@Volker: Yes, there are still a few.

[VolkerBarth · Accepted solution]

@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:)

[MarkCulp · Accepted solution]

@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.

[VolkerBarth · Accepted solution]

@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:)

[VolkerBarth]

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.

[Breck_Carter]

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

[VolkerBarth]

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

[Former Member]

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

[MCMartin]

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

[VolkerBarth]

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

[Former Member]

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)