In article <4a255827$1@forums-1-dub>, cinfosb (AT) videotron (DOT) ca says...
Of course we're biased here, but I love SQLA for its ease of maintenance
and relatively low hardware requirements for small installations, plus
its ability to scale to quite large and busy installations. I have
found it easier to develop against than MS SQL Server as well.

My application involves continuous 24x7 streams of data from almost 400
remote sites dumping approx 2.5 million pieces of data per day into a 7
GB SQLA database, and it has been working great for several years with
the only real maintenance being when I had to add new functionality to
it.

--
/~\ The ASCII
\ / Ribbon Campaign
X Against HTML
/ \ Email!

Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

SQL Anywhere is a full functional relational database. The dialect of SQL supported by SQL Anywhere
is referred to as Watcom-SQL. SQL Anywhere also supports a large subset of Transact-SQL, the dialect
of SQL supported by Sybase Adaptive Server Enterprise.

SQL Dialect information for SQL Anywhere can be found here:
http://dcx.sybase.com/index.php#http...ql-compat.html

--
Joshua Savill
Sybase Inc. - Product Manager


Stéphane Beauchemin wrote:

In article <4a268858$1@forums-1-dub>, cinfosb (AT) videotron (DOT) ca says...
With Sybase Central (the graphical management tool that comes with it),
it's trivial - a minute or two at most (plus actual execution time, of
course).


--
/~\ The ASCII
\ / Ribbon Campaign
X Against HTML
/ \ Email!

Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Stéphane Beauchemin wrote:

With SQL commands:

CREATE TABLE ...
ALTER TABLE ...
CREATE INDEX ...

or using Sybase Central if you prefer GUI.

Dmitri.

Hi again,

Sorry for the delay on my reply but I had to put energy on a big project
and put my SQLA study aside for a while...

To conclude my investigation regarding database maintenance using SQLA, am
I right understanding that, if I have remote customers which have no clue on
how to use any form of database tools and those same customers are using
C#/C++ apps interfacing with SQLA databases which again need maintenance, I
will have no choice but to supply my customers small apps that use SQL
scripts made of appropriate CREATE/UPDATE TABLE/INDEX commands so they can
update their databases properly?

Honestly I really thought that, like Clarion (Softvelocity) apps
interfaced with FileManager (Capesoft) which I've used for years, I would be
able to have my application check by itself for any differences between it's
internal dictionnary and the actual SQL database layout and adjust it
accordingly... But I think with SQL, it's definitely a different story...
I just want to avoid being onsite everytime a customer application needs
such database layout adjustments...

Any other comments, advices or hints?

Stéphane

Stéphane,

if I understand you correctly, your requirements are:

1. Automated maintenance of databases on customer sites.

2. Have an "internal application-specific data model" (residing in user
tables or a textual representation or whatever) and to have an
automatism to compare that "required data model" with the current system
catalog and then to apply any changes.

For 1:
When you deliver a SA-based app on remotes sides, clients will of course
have to use any kind of licenced SA setup. This typically includes
database tools for maintenance actions like doing backups or validation.
Those actions can be done with separate command line tools like DBBACKUP
(for use in batches and the like), with SQL Statements like BACKUP
DATABASE or API-based with the DbTools-API. So you are quite free to use
them from within your own app or through separat command files.

In order to alter the database schema (i.e. add tables/columns and the
like) you will typically use some kind of SQL script with the according
DDL statements like ALTER TABLE. This can be run from within your
application or with the builtin tools like Interactive SQL and the
smaller dbisqlc).
(If you are onsite, the GUI-based SQL Central is fine, too, but it's
surely not the way to silently do DDL on customer sites.)

BTW, a regular "database repair process" is not at all needed with SA in
my experience - SA is a real database system, not some kind of MS ACCESS


For 2:
SA has no builtin tool for the second point but of course you are free
to develop a small tool that would just do that. As every SQL system, SA
supports the reading of the system catalog in order to find out what
tables/columns exist - see the system tables description for that. And
of course you can then build appropriate DDL statements to add just
those tables/columns that are missing. (Besides adding,
modifying/dropping tables/columns etc. is possible, too - SA has a
really powerful ALTER TABLE statement.)


HTH

Volker


Stéphane Beauchemin wrote:

To add to the second point. With version 11 of the SA 'dbunload' utility
offers unloading schema into a sql file *ordered by object name*. Having 2
files (ordered the same) from 2 different databases you can easily see the
differences using any of file compare utitlities (e.g. Beyond Compare).

Have a look at this blog:

http://sqlanywhere.blogspot.com/2008...e-schemas.html

But, you still will have to develop some mechanism if you want to do it
automatically. .. e.g. store version of your software on the database table
and have the 'schema###.sql' associated with each version. If your client's
version is < current version -> apply current schema script.

Make sure your schema upgrade script is rerunable from any point and bullet
proof, so there is no chance of loosing data etc..


Lucjan



"Volker Barth" <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> wrote