|
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: "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. |
|
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.
Yes, that's true, and therefore an ISQL command like READ or INPUT/OUTPUT do fail within a code block.
(09 Jun '17, 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. 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 '17, 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 '17, 04:08)
Volker Barth
|
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;