Watcom Sql Syntax

0 views

Skip to first unread message

Niklas Terki

unread,

Aug 3, 2024, 5:15:39 PM8/3/24

to kalwamacdo

The problem was as @Michael pointed out, there are two different type of dialects - T-SQL and WATCOM-SQL, I had to rewrite while loop in Watcom-SQL syntax to be compatible with rest of Watcom-sql syntax -

A general question. I am developing for Sybase SQL Anywhere 10. For backwards comptibility reasons, almost all our Stored procedures are written in Transact-SQL.Are there any advantages or disadvantages to using T-SQL instead of the Watcom dialect?

The documentation for SQL Anywhere T-SQL compatibility is available online. There are some database options that change behaviour to more closely match what you would expect from Sybase ASE. In addition, there are some functions that can be used to translate from one syntax to another.

Note that if you want to start adding statements in the Watcom dialect into an existing stored procedure, you will need to change the SP so that it is entirely written in the Watcom dialect. You cannot mix syntaxes in a SP, trigger, or batch.

What KM said - on the other hand, the "Watcom" dialect is much closer to ISO/ANSI-standard SQL, so that dialect is more likely to match to some other products and to appeal to people familiar with SQL standards.

if you ever try to port to SQL Server (or you go for a job on SQL Server), Sybase T-SQL is very close to SQL Server T-SQL. Sybase and MS joined up back in the day, so the core of those languages are very similar.

When I execute this variant I get an ISQL Error that says Could not execute statement. Syntax error near 'DECLARE' on line 5 SQLCODE=-131, ODBC 3 State="42000". Study of this gives rise to two questions:

In your first example you are using a WATCOM SQL block BEGIN ... END statement, and within a SQL block all local declarations (i.e. DECLARE statements) must be prior to any executable (non-declarative) statement. In your case you have a DROP TABLE and a CREATE ... TABLE before the second DECLARE @B statement and hence this violates the WATCOM SQL rules.

If you remove the semicolons then the statement is TRANSACT SQL syntax and the rules are slightly different - a statement block is composed of a sequence of statements in any order. See the TSQL BEGIN statement documentation.

I guess I have to add another comment after reviewing that documentation more closely. I looked at that yesterday and saw the "variable-declaration", but the subsequent body of the BEGIN/END block is defined only as a "statement-list". And given that DECLARE is a valid statement, I guess I falsely assumed it was a valid statement within a "statement-list". Are there other such caveats; i.e., statements that can't be used in a statement-list in such a block?

There are many differences between WATCOM SQL and TSQL and they are hard to enumerate because some are very subtle. A simple way to see the high level differences is to take note of the [TSQL] annotations in the documentation - if a statement is annotated with [TSQL] then the statement is part of the TSQL dialect and should not be mixed with WATCOM SQL dialect. Statements that do not contain a [TSQL] annotation are either WATCOM SQL dialect or are consisten with/ok to be used with WATCOM SQL procedures.

Forum problems?Maintenance logContact UsTerms of ServicePrivacy PolicyCopyright/Trademark InfoLegal

Powered by SQL Anywhere 17
Disclaimer: Opinions expressed here are those of the poster and do notnecessarily reflect the views of the company.

If '=' is replaced with 'default', the procedure gets created sucessfully. If a database, that was created with an earlier SQLA version, is rebuilt the procedure keeps working, but can't be modified without the noted change.

Btw, your comment to my other post regarding SQLDIALECT made me look at your sample again. In SC (12.0.1.4436) I can add that function using ISQL and then convert it to Watcom (changes syntax) and to TSQL (ditto). So it seems somewhat inbetween.

FWIW the parser seems to be [cough] flexible when dealing with so-called TSQL features; i.e., some of those features are freely available in Watcom-SQL scripts and they don't change the so-called "dialect".

Second - as you might imagine, TSQL compatibility is extraordinarily difficult since TSQL is a moving target. In particular the move by Microsoft to include statement delimiters (semicolons) in their TSQL dialect makes Microsoft TSQL much closer to SQL Anywhere's WATCOM dialect in terms of syntax, but moves it further away from ASE's version of TSQL.

Specifically, the CREATE PROCEDURE and CREATE FUNCTION statements in SQL Anywhere have never supported the combination of using IN, OUT, or INOUT with the TSQL default parameter assignment ('='). Transact-SQL has never supported the IN, OUT, INOUT declarations for procedure variables and still does not; see -us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017.

However, if one omits the IN declaration for the parameter, as in the enhancement request here (Enhancement Request: in SP definition allow "=" instead of "default" for Watcom dialect (SA-17)) then the TSQL default assignment will work even though the procedure uses WATCOM syntax. The example used in that other post does work for me with current SA17 software and with 12.0.1.

Is the script originally written in Transact-SQL? SQL Anywhere has some native support for Transact-SQL statement syntax - but it looks like you are currently using Watcom SQL (the native SQL Anywhere dialect) for this statement (due to the semi-colon). Are you re-writing the script to target the Watcom SQL or Transact-SQL dialect?

The body of a Watcom-SQL procedure or trigger is a compound statement, and variables must be declared with other declarations, such as a cursor declaration (DECLARE CURSOR), immediately following the BEGIN keyword. In a Transact-SQL procedure or trigger, there is no such restriction.

And I was mislead by the error dialog, the statement that was really causing the problem was the previous statement. I was trying to initialize the integer variable with value. So no it seems it's my "SET" statement that doesn't run. Here is the entire script in all it's glory.

The "Could not execute statement. Syntax error near 'DECLARE' on line 4" error is the Watcom-SQL 'CREATE VARIABLE' issue that I had identified in the help - your DECLARE statements in Watcom SQL have to be grouped together at the start of the compound statement:

For your issue, I'm going to assume that you do not want convert this script to Watcom SQL and would like to remain using Transact-SQL. If so, there are two rules you need to use here:

I do love having physical copies of books, especially reference material like this. Most people will probably think I'mweird, but I find this more convenient to refer to in many (but not all) cases. Being able to physically flip throughpages at will and back and forth between several different pages as needed just feels a ton more convenient to me thendoing the same with a PDF. And I've always preferred reading text like this on real paper. The obvious downsides versusan electronic copy is the lack of ability to do a Ctrl-F to find something specific and also not being able tocopy+paste something from it.

So what's so special about Watcom anyway? Well, for me personally, it was the first C compiler I ever used. Not thisversion though. In 1996 I remember my dad bought me a book, "C DiskTutor" by L. John Ribar (published in 1992) thatcame with a disk containing sample code and a stripped down copy of the Watcom C 8.5 compiler. The disk actually had afew bad spots on it and I remember he called the publisher and they sent out another disk. I didn't realize it at thetime, but the version of the compiler on the disk lacked the ability to compile code with any memory model other thenthe small model, so it wasn't suitable for really any "real world" use. Certainly good enough for introductoryexercises though, which was obviously the intended purpose.

I didn't spend a whole lot of time using it though. I remember getting frustrated with fixing my own bugs as there wasno included debugger with this stripped down version. At least I don't remember there being one. But also I remembertrying to make sense of the poor compiler error messages was tough for me being completely new to C at the time.Finally, I also remember really hating how I had to quit out of EDIT.COM to compile and run my code. The diskincluded some simple text editor which seemed to be written specifically for this book. It did have the ability to runthe compiler from within the editor, but I vaguely remember arriving at the conclusion that this editor was not verygood and a bit buggy. EDIT.COM ended up being a less-frustrating option for writing code. Keep in mind my only otherprogramming experience at this time was a little over a year with QBasic which had a much faster and easieredit/compile/run cycle. I didn't understand why anyone would want to use something else when it was so much easier! Ofcourse years later I learnt that many people at that time used better editors then EDIT.COM and actually really likedwriting code in their editor of choice and using external build scripts / Makefiles. Heh.

Beyond my nostalgia though, Watcom was used by all sorts of game developers who cared about code efficiency in theearly-to-mid 90's, such as id Software with Doom and 3D Realms with Duke Nukem 3D amongst many others. Plus it shippedwith a royalty-free version of the well known DOS extender DOS/4GW. However, while it was better at code generationthen the competition (Borland, Microsoft, etc), it lacked in the end-user experience. Borland and Microsoft had bettertooling in the form of IDE's (Watcom didn't ship an IDE with their compiler until 10.0) and easier to use command linetools.