DBISQL will run the statements in the order they appear in the file. If
you want to alter the flow of the batch, you can use conditional logic
(IF/THEN/ELSE statements). Am I not understanding your requirements??

Paul Horan[TeamSybase]

<dpillay.lists (AT) gmail (DOT) com> wrote

All,

Given the situation that I have an SQL file with multiple queries,
does Sybase provide a way to parse that file and execute each of the
queries in turn?

The individual queries themselves are mutually exclusive from the rest
but being able to parse properly (say how to parse a "create
procedure" statement) is the actual problem.

- Dinesh.

Dinesh,

or are you thinking of a way to check for syntax errors without executing
the queries?
Then dbisql -x should do.

HTH
Volker

"Paul Horan[TeamSybase]" <phoran AT sybase DOT com> wrote in
news:4725f91d (AT) forums-1-dub (DOT) ..

On Oct 29, 7:15 pm, "Paul Horan[TeamSybase]" <phoran AT sybase DOT
com> wrote:
What I actually needed was a way to parse the SQL so that I can
control the transaction. Its not 1 file I'm talking about, its
multiples of them.

Is there any way to do it or is dbisql the only option. I was thinking
one way could be to concatenate the files into one and then feed them
to dbisql.

- Dinesh.

On Oct 29, 8:38 pm, "Volker Barth" <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de>
wrote:
I'll paint a bigger picture. My application needs to upgrade the DB to
sync it with the app version. We have currently say 10 sql files that
push the DB schema from version 1 to version 2. What we currently are
doing is to run dbisql on each of those 10 files to upgrade. But in
case we face an issue during say the 5th file, we would have committed
till the 4th and hence negate a rollback.

I'm considering the following options:-

1. Concatenate the files into 1 and then execute dbisql on that one
file. The files are different for logical separations only. Also, does
DBISQL auto-commit on a DDL statement?

2. Parse those 10 files and then execute the parsed SQL over a jdbc
connection to the DB during which I can control commits.

Thoughts?

- Dinesh.

DDL statements like CREATE and ALTER all do automatic commits. If one
of these statements *fails* during execution, the changes are undone
because each statement is atomic, but when one of these statements
succeeds its effect is permanent. So, you don't really have any
control over the transactions when doing DDL.

As Paul suggested, one approach is to wrap each DDL statement in an IF
so the script can be run against any database; e.g., not upgraded,
partially upgraded or fully upgraded already. Write each IF so that it
independently checks the current "state" of the database. Then just
put everything in one script and run it... if it fails, fix the script
and re-run it.

Here are some template examples:

IF NOT EXISTS ( SELECT * FROM SYSTABLE
WHERE SYSTABLE.table_name = 't' ) THEN
CREATE TABLE t ...
ALTER TABLE t ADD CONSTRAINT ...
END IF;

IF NOT EXISTS ( SELECT *
FROM SYSPUBLICATION
INNER JOIN SYSARTICLE
ON SYSARTICLE.publication_id
= SYSPUBLICATION.publication_id
INNER JOIN SYSTABLE
ON SYSTABLE.table_id = SYSARTICLE.table_id
WHERE SYSPUBLICATION.publication_name = 'p'
AND SYSTABLE.table_name = 't' ) THEN
ALTER PUBLICATION p ADD TABLE t ...
END IF;

IF NOT EXISTS ( SELECT * FROM SYSTABLE INNER JOIN SYSCOLUMN
ON SYSCOLUMN.table_id = SYSTABLE.table_id
WHERE SYSTABLE.table_name = 't'
AND SYSCOLUMN.column_name = 'c' ) THEN
ALTER TABLE t ADD c ...
ALTER PUBLICATION p MODIFY TABLE t ...
END IF;

Breck


On Mon, 29 Oct 2007 10:00:45 -0700, "dpillay.lists (AT) gmail (DOT) com"
<dpillay.lists (AT) gmail (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

We did this for hundreds of customers at my previous employer... The
approach we came up with was to have individual .SQL files for each
"object" - table, proc, function, view, etc... Each would have a statement
like:
IF not exists (select * from sys.syscolumns where table_name = 'myTable' and
column_name = 'myNewColumn' ) then
ALTER TABLE myTable...
END IF;

We also had one "controller" .SQL file, that did nothing but READ statements
against those individual .SQL files. So, during the course of the
month/quarter/release cycle, each developer would be responsible for
creating the .SQL file that did the work, and modifying the controller .SQL
file that invoked it.

Paul Horan[TeamSybase]

<dpillay.lists (AT) gmail (DOT) com> wrote

DDL would not (technically) be described as "queries". Most (all?) DDL
autocommits, so one can't talk about the statements being in a
"transaction".

AFAIK therefore, your only choice is to follow these steps:

(1) create a backup of your database
(2) run the script, logging any errors
(3) if any errors in (2), replace the (incompletely) altered database with
the backup (this is your rollback) and report the errors, fix the script and
then start from (1) again.

Cheers

--
Andrew Giulinn
Senior Analyst/Programmer
Integrated Aviation Software Pty Ltd

<dpillay.lists (AT) gmail (DOT) com> wrote