SQL Anywhere database - Get all data out a to single file, eg in XML

Author

Get all data out a to single file, eg in XML

Andrew Giulinn

2005-06-22, 8:23 pm

Hello all

Using ASA 9.0.2.3044.

I am looking to validate a database schema modification, by comparing the
data in two databases, one from before and one from after executing the DDL
and DML statements involved in the schema modification.

My current technique only goes as far as checking that the row counts in
each table have only changed where expected. I do this by executing the
following SQL in each database, outputing the results to a file and then
doing a text compare of the two text files:

SELECT TABLE_NAME, COUNT FROM SYS.SYSTABLE WHERE CREATOR = <our table owner>
AND TABLE_TYPE = BASE ORDER BY TABLE_NAME;

But I want to get more precise with the check. With that in mind, I am
trying to work out a way to compare the actual data in all tables of the two
databases.

Without knowing any other way to do it, I am looking for a way to get all
data from all tables (owned by <our table owner> ) into a single file. I am
thinking that this might be available with an XML output format. I intend
to do this for each of the two databases and then do a text compare of the
files, checking that only expected changes have occurred.

So, two questions:

(1) Is there another (better) way that can be suggested to do the
field-level comparison?

(2) What commands/SQL can I use to get all data from all tables (owned by
<our table owner> ) into a single file, eg in XML format. UNLOAD doesn't
seem to have an option that does this.

Thanks

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

Andrew Giulinn

2005-06-27, 3:23 am

If anyone is ever interested, I have got what I wanted - not easily and not
in XML. I remain open to ideas of better ways to do a data-level comparison
between two databases.

My solution is a stored procedure that creates a cursor to move through the
tables of interest in SYS.SYSTABLE and, for each:

execute immediate 'UNLOAD select * from ' || TName || ' to OutFileName
APPEND ON'

with variations to get the info out in the form that I want.

Cheers

Andrew

"Andrew Giulinn" <NO.oscar.SPAM@dsa.com.au> wrote in message
news:42ba0315@forums
-1-dub...
> Hello all
>
> Using ASA 9.0.2.3044.
>
> I am looking to validate a database schema modification, by comparing the
> data in two databases, one from before and one from after executing the
> DDL and DML statements involved in the schema modification.
>
> My current technique only goes as far as checking that the row counts in
> each table have only changed where expected. I do this by executing the
> following SQL in each database, outputing the results to a file and then
> doing a text compare of the two text files:
>
> SELECT TABLE_NAME, COUNT FROM SYS.SYSTABLE WHERE CREATOR = <our table
> owner> AND TABLE_TYPE = BASE ORDER BY TABLE_NAME;
>
> But I want to get more precise with the check. With that in mind, I am
> trying to work out a way to compare the actual data in all tables of the
> two databases.
>
> Without knowing any other way to do it, I am looking for a way to get all
> data from all tables (owned by <our table owner> ) into a single file. I
> am thinking that this might be available with an XML output format. I
> intend to do this for each of the two databases and then do a text compare
> of the files, checking that only expected changes have occurred.
>
> So, two questions:
>
> (1) Is there another (better) way that can be suggested to do the
> field-level comparison?
>
> (2) What commands/SQL can I use to get all data from all tables (owned by
> <our table owner> ) into a single file, eg in XML format. UNLOAD doesn't
> seem to have an option that does this.
>
> Thanks
>
> --
> Andrew Giulinn
> Senior Analyst/Programmer
> Integrated Aviation Software Pty Ltd
>

Sponsored Links