Conditionally read sql script with dbisql

[Former Member]

Is there any way to conditionally read sql script when using dbisql?

I want to have a main script that checks what changes that have already been applied to a database and run scripts that haven´t been run yet.

The main script script1.sql would do something like:

declare @version bigint
select @version = (select version from DB_VERSION where id = 1)
if @version < 2
begin
    READ 'script2.sql'
    update DB_VERSION  set version = 2 where id = 1
end 

And I run the main script with dbisql like this:

"C:\\Program Files\\SQL Anywhere 12\\Bin64\\dbisql.com" -datasource "mydbconn" -nogui -onerror exit  script1.sql 

But it seems like using READ command in an SQL statement doesn't work.

[chris_keating]

Perhaps moving the version check into script2.sql and using a connection variable defined in script1.sql to host the database version. Here is an example (some lines wrapped for readability):

-- ** script1.sql
create variable @version bigint;
set @version = (select version from DB_VERSION where id = 1)
read c:\\temp\\script2.sql;
drop variable @version;

-- script2.sql
message string( 'Checking database version...' ) to client;
if @version < 2 then
    message string( 'Upgrading to database schema
            version ', @version, '.' ) to client;

    -- the contents of the script2.sql
    -- ...
    --

    -- update DB_VERSION set version = 2 where id = 1
    message string( 'Database schema has 
           been updated.' ) to client;
end if;

[Breck_Carter]

If you are not making use of the ISQL PARAMETERS substitution feature, and the server executable has read access to the *.SQL file, you can use EXECUTE IMMEDIATE to conditionally execute scripts; e.g.,

DECLARE @file_specificaton  LONG VARCHAR;
DECLARE @file_contents      LONG BINARY;

SET @file_specificaton = 'C:\\\\wherever\\\\whatever.sql';

SET @file_contents = dbo.xp_read_file ( @file_specificaton );

EXECUTE IMMEDIATE @file_contents;

This is the technique Foxhound uses to apply patches to itself.

[Former Member]

I have problems making it conditionally execute the script. Could you add an if statement to your example to show how you conditionally make it skip executing the SQL file?

Also I could not run your example in Interactive SQL unless I put it within a begin/end block. Is it supposed to be in a stored procedure, or is it possible to run it directly in Interactive SQL?

[VolkerBarth]

You asked for a solution within a code block, i.e. enclosed within a BEGIN/END pair (could also be part of a stored procedure) - if it would not require a code block, you could use the DBISQL READ command, and there were no question...

So yes, Breck's sample assumes a code block, and a condition might look like:

BEGIN
   DECLARE @db_version         INT;
   DECLARE @expected_version   INT = 3;
   DECLARE @file_specificaton  LONG VARCHAR;
   DECLARE @file_contents      LONG BINARY;

   -- somehow check current db (schema) version
   SET @db_version = <what_ever_...>;
   IF @db_version < @expected_version THEN
      SET @file_specificaton = 'C:\\\\wherever\\\\whatever.sql';
      SET @file_contents = dbo.xp_read_file ( @file_specificaton );
      EXECUTE IMMEDIATE @file_contents
         WITH BATCH ON WITH RESULT SET OFF;
      -- increase db version (unless done within the script)
      SET @db_version = @expected_version;
   END IF;
END;

Of course there needs to be some kind of error handling here, and possibly a "go back to backup" in case the schema upgrade has failed - DDL does autocommit, so you cannot use "rollback" to undo changes here...

[reimer_pods]

The READ command is ISQL specific (like OUTPUT). IIRC every BEGIN ... END block will not be executed by ISQL, but send to the engine to be processed there, which leads to a syntax error.

I've tried this also, but wasn't successful.

[VolkerBarth]

IIRC every BEGIN ... END block will not be executed by ISQL, but send to the engine to be processed there...

Yes, that's true, and therefore an ISQL command like READ or INPUT/OUTPUT do fail within a code block.