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 Carter |
This is a bug and will be fixed in a future EBF (build # is TBD). answered 10 Jan '11, 17:46 Mark Culp @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... 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. |
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; answered 04 Oct '11, 06:19 Jan24 |
This looks like a bug. I have sent an email to the Tools team to have them look at it.