Altering a runTime database

[Former Member]

In SQL Anywhere 11.0, I am using the rteng11 startline. Aware as I am that I can distribute runtime databases if I use no stored procs or triggers, I end all my SQL from the Powerbuilder front end program. I have distributed a small number of program instances. Now, I would like to Alter the DB. No problem with new installations, but must make alterations to databases that are already installed. In the case where I am adding columns, I start by tsting whether the column exists.

-- modify database
select 1 into :li_retcode from systable key join syscolumn 
   where table_name ='project_list' and column_name='in_date';

That works fine then

if li_retcode = 1 then 
   alter table  project_list  drop in_date;
end if

unfortunately the alter table statement (which works in the ISQL tab of the DB painter in Powerbuilder gives me an undeclared variable error.

If I cannot send database alterations to a runtime database how can I proceed?

[chris_keating]

There are some options:

• bring the database back into the development environment, make the changes, and redeploy the database.
• create a new database with the altered schema, deploy it, and have a utility application that copies the data from the old db into the new db.

[Former Member]

OK, is there a way to unload to a file and load back to a new table that will work with rteng11 nd PB?

Thanks,

Melvyn Polatchek

[chris_keating]

You can using the UNLOAD statement to unload to a file.

UNLOAD TABLE groupo.departments TO 'departments.csv'

and use LOAD statement to load into a new database:

load table groupo.departments from 'departments.csv'

You cannot unload the database using dbunload utility (or its equivalents) as that generates statements which cannot be executed in the runtime edition.

Another option is to use a PowerBuilder Pipeline to move the data from the old database to the new database.

[Breck_Carter]

Heads up... AFAIK the runtime engine is no longer available for SQL Anywhere version 12.

[former_SQLA_member1694871]

Do you have a link on this Breck?

Jeff Gibson

[Breck_Carter]

I don't have a public link, it was mentioned in a post on a private newsgroup; someone had noticed it wasn't shipping with PB 12.5 and the response was that it had been discontinued as of SQL Anywhere 12. There never was much publicity about it so perhaps its demise is being kept under wraps.

[former_SQLA_member1694880]

PB's ESQL does not understand ALTER TABLE statement ;). However, RTEng does not allow DDL anyway.