Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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
32.5k5417261050
accept rate: 20%

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
24.9k10141297
accept rate: 41%

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

×106

question asked: 09 Jan '11, 17:45

question was seen: 2,296 times

last updated: 04 Oct '11, 06:19