READ and PARAMETERS behavior change in versions 10, 11 and 12

[Breck_Carter]

This test1.sql file works OK...

PARAMETERS value;

SET OPTION PUBLIC.Global_database_id = '{value}';

MESSAGE STRING ( 'GlobalDBId = ', DB_PROPERTY ( 'GlobalDBId' ) ) TO CLIENT;

when processed using SQL Anywhere 9 dbisql as follows...

"%ASANY9%\\win32\\dbisql.exe"^
  -c "ENG=ddd;DBN=ddd;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 test1.sql [123]

displaying this correct result...

GlobalDBId = 123
Execution time: 0 seconds

However, if fails when processed using SQL Anywhere 10, 11 or 12 dbisql as follows...

"%SQLANY10%\\win32\\dbisql.exe"^
  -c "ENG=ddd10;DBN=ddd10;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 test1.sql [123]

"%SQLANY11%\\bin32\\dbisql.com"^
  -c "ENG=ddd11;DBN=ddd11;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 test1.sql [123]

"%SQLANY12%\\bin32\\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 test1.sql [123]

throwing this error message...

Could not execute statement.
  Invalid setting for option 'global_database_id'
  SQLCODE=-201, ODBC 3 State="42000"
  File: "test1.sql" on line 3, column 40
  SET OPTION PUBLIC.Global_database_id = '{value}'

The workaround is to wrap everything in a BEGIN block to force dbisql to apply the PARAMETERS string substitution and sending it to the server instead of trying to process the naked SET by itself:

PARAMETERS value;

BEGIN
   SET OPTION PUBLIC.Global_database_id = '{value}';

MESSAGE STRING ( 'GlobalDBId = ', DB_PROPERTY ( 'GlobalDBId' ) ) TO CLIENT;
END;

According to the docs SET OPTION PUBLIC... is not an ISQL statement. Even if it was, it should be subject to string substitution; after all, a READ statement embedded in a SQL file does have {values} substituted, and it's an ISQL-only statement.

[MarkCulp]

This looks like a bug. I have sent an email to the Tools team to have them look at it.

[MarkCulp]

This is a bug and will be fixed in a future EBF (build # is TBD).

[VolkerBarth]

@Mark: Am I right that this bug doesn't impact dbisqlc? - I'm quite sure V12 dbisqlc does handle parameters correctly, even outside SQL blocks...

[MarkCulp]

@Volker: I do not know the details of the bug (I'm just the messenger 🙂 but my experience indicates that dbisqlc does handle parameters correctly in all released versions.

[jan24]

I have a similar problem with the latest version of isql 11.0.1 build 2661 where the parameter values ​​are not transferred to the CONNECT statement. ISQL Connect dialog has not transfered value from parameters: {p_database} ...

It seems that this bug is not corrected .

This "Test file.sql" works OK in isql 9. On the other hand, ISQLc (all versions) not knows the command: >># nor OUTPUT APPEND.

PARAMETERS p_database, p_user, p_password; connect using 'ENG=Sybase;LINKS=TCPIP{};DBN={p_database};UID={p_user};PWD={p_password}'; SELECT '-- PROCEDURES --' >># "Test output {p_database}.txt"; disconnect;