READ and PARAMETERS

The ISQL READ and PARAMETERS statements are wonderful things when you need to apply string substitutions to SQL code when it's being compiled or executed.

Here's how to use them:

1. Assign a name to each different string substitution value you want applied to a SQL file.

2. Code each name in a PARAMETERS statement at the top of the SQL file, like this:

   PARAMETERS column_name, column_value, primary_key_value;

3. Code the parameter name in {curly braces} down in the body of the SQL file wherever you want the actual value to be substituted; for example:

   UPDATE t SET t.{column_name} = '{column_value}' WHERE t.pkey = {primary_key_value};
   

4. Code the actual values in [square braces], left to right in the same order as the PARAMETERS statement, in a READ statement on the ISQL command line, like this:

   "%SQLANY12%\bin32\dbisql.com"^
     -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
     READ ENCODING Cp1252 p1.sql [data2] [Hello, world!] [3]

5. Run the ISQL command line to have the actual values substituted like this:

   UPDATE t SET t.data5 = 'Hello, world!' WHERE t.pkey = 3;

Here's a demonstration; first, the table definition:

CREATE TABLE t (
   pkey  INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data1  VARCHAR ( 100 ) NOT NULL DEFAULT '',
   data2  VARCHAR ( 100 ) NOT NULL DEFAULT '',
   data3  VARCHAR ( 100 ) NOT NULL DEFAULT '' );

INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
INSERT t DEFAULT VALUES;
COMMIT;

SELECT * FROM t ORDER BY pkey;

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','',''
4,'','',''

Here's a procedure in a file called p1.sql:

PARAMETERS column_name, column_value, primary_key_value;

CREATE PROCEDURE p1()
BEGIN
   UPDATE t SET t.{column_name} = '{column_value}' WHERE t.pkey = {primary_key_value};
   COMMIT;
END;

Here's the dbisql command used to compile p1.sql:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 p1.sql [data2] [Hello, world!] [3]

Here's a test of the procedure p1:

CALL p1();
SELECT * FROM t ORDER BY pkey;

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','Hello, world!',''
4,'','',''

---

Now, supposing you have lots and lots of SQL files, many or all of them needing PARAMETERS substitution, and you want run one ISQL command line to compile them all. Here's how:

1. Create a master SQL file containing one READ statement for each of the other files.

2. Code a master PARAMETERS statement at the top of the master SQL file to define the complete set of substitution values across all the other files.

3. Use nested [{square and curly braces}] to pass the actual values from the READ statement on the master ISQL command line, down through the READ statements inside the master SQL file, and on to the other SQL files.

Here's a sample master.sql file:

PARAMETERS column_name, column_value, primary_key_value, global_database_id;
READ ENCODING Cp1252 p1.sql [{column_name}] [{column_value}] [{primary_key_value}];
READ ENCODING Cp1252 p2.sql [{global_database_id}] [{column_name}] [{primary_key_value}];

Here's the master ISQL command line:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 master.sql [data2] [Hello, world!] [3] [123]

Here's the new p2.sql file:

PARAMETERS global_database_id, column_name, primary_key_value;

CREATE PROCEDURE p2()
BEGIN
   SET OPTION PUBLIC.global_database_id = '{global_database_id}';
   UPDATE t SET t.{column_name} = UPPER ( t.{column_name} } WHERE t.pkey = {primary_key_value};
   COMMIT;
END;

Here's a test of p1 and p2:

CALL p1();
CALL p2();
SELECT DB_PROPERTY ( 'GlobalDBId' );
SELECT * FROM t ORDER BY pkey;

DB_PROPERTY('GlobalDBId')
'123'

pkey,data1,data2,data3
1,'','',''
2,'','',''
3,'','HELLO, WORLD!',''
4,'','',''

---

Tip: If you're using SQL Anywhere 10 or later, and you see an error like this,

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}'

try wrapping the offending statement in a BEGIN END block to force ISQL to handle the string substitution properly.

For example, change your SQL file from this (which works OK in SQL Anywhere 9 but throws SQLCODE = -201 in versions 10, 11 and 12)...

PARAMETERS value;

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

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

to this...

PARAMETERS value;

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

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

which works in all four versions of SQL Anywhere to display this...

GlobalDBId = 123
Execution time: 0 seconds