Question Re: Altering a runTime database in Technology Q&A

Altering a runTime database

Former Member — Wed, 07 Dec 2011 05:43:21 GMT

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?

Re: Altering a runTime database

former_SQLA_member1694880 — Wed, 07 Dec 2011 06:14:58 GMT

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

Re: Altering a runTime database

chris_keating — Wed, 07 Dec 2011 08:05:17 GMT

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.

Re: Altering a runTime database

Former Member — Wed, 07 Dec 2011 10:36:56 GMT

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

Re: Altering a runTime database

chris_keating — Wed, 07 Dec 2011 11:04:11 GMT

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.

Re: Altering a runTime database

Breck_Carter — Sun, 11 Dec 2011 14:56:39 GMT

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

Re: Altering a runTime database

former_SQLA_member1694871 — Wed, 14 Dec 2011 13:05:10 GMT

Do you have a link on this Breck?

Jeff Gibson

Re: Altering a runTime database

Breck_Carter — Wed, 14 Dec 2011 14:40:03 GMT

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.