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.

asked 09 Jan '11, 17:45

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

1

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

(10 Jan '11, 08:47) Mark Culp

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

permanent link

answered 10 Jan '11, 17:46

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275
accept rate: 40%

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

(11 Jan '11, 08:25) Volker Barth
1

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

(11 Jan '11, 23:24) Mark Culp

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;

permanent link

answered 04 Oct '11, 06:19

Jan24's gravatar image

Jan24
75348
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102

question asked: 09 Jan '11, 17:45

question was seen: 1,187 times

last updated: 04 Oct '11, 06:19