Alter column statement replicated from SA12 to ASA9 db

[Former Member]

Using SQLRemote with a SA12 consolidated database and remote databases with a mix of SA12 and ASA9, we have an issue with altering columns.

SQL executed at consolidated SA12 db (we're using MODIFY syntax to support ASA9):

ALTER TABLE dba.table_name MODIFY column_name NOT NULL;

The alter statement is run in passthrough mode to ensure it's also being replicated to all remote databases.

In a ASA9 db the statement fails, reason that the MODIFY syntax is now changed to

ALTER TABLE dba.table_name ALTER column_name NOT NULL;

ASA9 is not compatible with this syntax, and requires the MODIFY keyword. Is there any way of not having the sql changed from modify to alter syntax?

[VolkerBarth · Accepted solution]

We had the same issue lately, and the only way around I'm aware of is to use dynamic SQL, i.e. you do not send the original DDL statement to the remotes but send a (temporary) stored procedure that does issue the DDL statement locally (and thereby in the local version-specific dialect...), call that SP and drop if afterwards.

OK for smaller changes, possibly nasty for lots of ALTER statements....

Something like

PASSTHROUGH FOR SUBSCRIPTION TO <...>;

create procedure STP_TempUpdate()
begin
   execute immediate 'ALTER TABLE dba.table_name MODIFY column_name NOT NULL;';
end;
call STP_TempUpdate();
commit;
drop procedure STP_TempUpdate;

COMMIT;
PASSTHROUGH STOP;

[VolkerBarth · Accepted solution]

FWIW, I see that I had used that case to ask for a simpler approach here (though still answered):

Add an option to SQL Remote's passthrough mode to leave syntax unmodified

[Former Member · Accepted solution]

excellent suggestion! I presume the option in passthrough mode is not yet implemented (sa12). Your workaround should do the trick, but I do prefer this as a passthrough mode setting.

[VolkerBarth · Accepted solution]

I presume the option in passthrough mode is not yet implemented (sa12).

Given the fact that there has not been any comment on that suggestion (and particularly not a comment by Sybase engineers like Reg), I would think it is not even planned to implement that feature...

[Breck_Carter · Accepted solution]

Speaking as a person who happily supports clients running on 5.5, I do have say this: the two alternatives to dealing with a problem are "fix it or FAQ it". In this case, "FAQ it" would mean adding an entry to the V12 Behavior Changes section for SQL Remote, which I support for two reasons: V9 isn't supported by Engineering any more, and a workaround exists.

Not offended yet? Well, try this: Workarounds are a fact of life. If you have a workaround that keeps antique remotes alive in 2013, be proud! (but don't ask Netflix to deliver Orange Is The New Black on VHS 🙂

[VolkerBarth · Accepted solution]

Your good advice is accepted, of course:)

However, the suggestion was no meant to fix SQL Remote for older versions (and I bet a fix that would change behaviour in v12 is not going to happen), it should help to avoid this kind of problems in coming version, say v16/v17 with v11/v12 remotes. - Just ask yourself how the new v16 DDL for the new role-based security model will be sent to remotes (or how it will be received there...) - Experience has shown that even attempts to omit "new syntax" might fail as the sent statement might nevertheless contain new syntax under the covers.

[VolkerBarth · Accepted solution]

But in the end, yes, yes, an existing workaround is sufficient to solve a current problem:)

"A bird in the hand is worth two in the bush." (or, as German speakers use: "Der Spatz in der Hand ist besser als die Taube auf dem Dach.").

[Breck_Carter · Accepted solution]

Oh, don't get me started on the new role-based security model, with its unintended consequences 🙂

[Breck_Carter · Accepted solution]

"help to avoid this kind of problems in coming version" - an excellent point, perhaps defeating my argument altogether. SQL Remote is a wonderful product, and supporting ancient remotes has always been one of its hallmarks, so this insistence on upselling, er, upgrading passthrough syntax seems an oversight that should be corrected.

"No, I do NOT want fries with my burger!"

Of course, I'm being hypocritical... when Foxhound displays CREATE TABLE syntax for a 5.5 database, it doesn't stick to 5.5 syntax 🙂