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.

asked 08 Jun, 11:09

jobencol's gravatar image

jobencol
4125
accept rate: 0%

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;
(09 Jun, 09:50) Chris Keating

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.

permanent link

answered 09 Jun, 08:16

Reimer%20Pods's gravatar image

Reimer Pods
4.2k354684
accept rate: 11%

converted 09 Jun, 09:24

Breck%20Carter's gravatar image

Breck Carter
27.0k448618889

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.

(09 Jun, 08:44) Volker Barth

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.

permanent link

answered 09 Jun, 09:24

Breck%20Carter's gravatar image

Breck Carter
27.0k448618889
accept rate: 21%

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?

(13 Jun, 03:20) jobencol
Replies hidden

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

(13 Jun, 04:08) Volker Barth
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:

×415
×82

question asked: 08 Jun, 11:09

question was seen: 248 times

last updated: 13 Jun, 04:11