Hello again

I would like to be able to alter the version number of all my tables, functions and procedures in a single stored procedure. The tables were a cinch, but my attempt at altering the functions results in only the first record being updated, due to my misunderstanding of how to gather TWO variables, FunctionName and FunctionDef:

CREATE PROCEDURE "ResetFuncVersions" (IN "NewVersion" DECIMAL(19,2))
BEGIN
 DECLARE CatalogCursor CURSOR FOR CatalogStmt;
 DECLARE FunctionName VARCHAR DEFAULT '';
 DECLARE FunctionDef VARCHAR DEFAULT '';
 PREPARE CatalogStmt FROM 'SELECT * FROM Information.Functions';
 OPEN CatalogCursor;
 FETCH FIRST FROM CatalogCursor ('Name') INTO FunctionName;
 FETCH FIRST FROM CatalogCursor ('Definition') INTO FunctionDef;
 WHILE (NOT EOF(CatalogCursor)) DO
    EXECUTE IMMEDIATE 'ALTER ' + FunctionDef +
    ' VERSION '+ CAST(NewVersion AS VARCHAR);
    FETCH NEXT FROM CatalogCursor ('Name') INTO FunctionName;
    FETCH FROM CatalogCursor ('Definition') INTO FunctionDef;
 END WHILE;
END
VERSION 1.00!

Ideally, I would prefer to be able to just use 'UPDATE Information.Procedures SET Version = 6.35', but of course it is a system table.

If you can see where I went wrong, and if you have any suggestions regarding altering the Procedures, I would be grateful.

Regards

Peter

Peter

I believe that the information.function tables cannot be UPDATED in the way a standard table is updated. I think that you would have to fangle this a bit more and create an ALTER FUNCTION statement using the elements of the information.functions table.

i.e.

PREPARE Stmt FROM
' SELECT
 Definition
FROM Information.Functions  ';
OPEN Crsr;
FETCH FIRST FROM Crsr('Definition') INTO aDefinition;
WHILE NOT EOF(Crsr) DO
 EXECUTE IMMEDIATE
    ' ALTER ' + aDefinition + ' VERSION ' + aVersionNumber;
    FETCH NEXT FROM Crsr('Definition') INTO aDefinition;
 END WHILE;

Not as clean but does work.

Adam

Peter


Reading the OLH it seems that all you need to do is use a comma delimited list for the columns and the variables you want to extract. However, I'd go for Adam's solution. This is pretty much what I do when I want some of the on-disk database functions in my in-memory database.

Roy Lambert

Adam

Thanks for that tip. You say your example works, but I suspect that the function on which it was tested did not include a SQL statement. Most of my functions do contain a line such as:

 PREPARE TempStmt FROM 'SELECT SuperID FROM ADWOption WHERE UserID = ?';

The point being that the line:

 'ALTER ' + aDefinition + ' VERSION ' + CAST(NewVersion AS VARCHAR);

...gets broken (#700) if aDefinition contains single quotations, as in my example. Is there an in-line method that processes aDefinition so all single quotes are converted to doubles? That would allow the concatenation of 'ALTER ' + aDefinition to work.

I have included the full SP, in case I missed something.

CREATE PROCEDURE "ResetFunctionVers" (IN "NewVersion" DECIMAL(19,2))
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE aDefinition VARCHAR DEFAULT '';
 PREPARE Stmt FROM
'SELECT Definition FROM Information.Functions';
 OPEN Crsr;
 FETCH FIRST FROM Crsr('Definition') INTO aDefinition;
 WHILE NOT EOF(Crsr) DO
  EXECUTE IMMEDIATE
   'ALTER ' + aDefinition + ' VERSION ' + CAST(NewVersion AS VARCHAR);
   FETCH NEXT FROM Crsr('Definition') INTO aDefinition;
 END WHILE;
END

I think that the single quotes are what is preventing it from working here, but I could have missed the point. Again. Thanks for your help.

Regards

Peter

Peter


Tim's SQL has QUOTEDSTR - use it just like you would in Delphi.

Roy Lambert

Peter

Sorry for failing to spot this, you are quite right additional single quotes within the Definition will cause complexity. Roy's solution of including something similar to QUOTEDSTR(aDefinition) should work.

In your situation I might start by writing the following SQL:

SELECT
'ALTER ' + QUOTEDSTR(Defintion) + ' Version 6.35 ' as QuotedDef
FROM Information.Tables

This will generate a result-set where the QuotedDef field ought to work if fed into an Execute Immediate statement.

I haven't tested this, you might need to play around with the single quotes!

Sorry Peter

This version is much closer to what you need!

SELECT
REPLACE(''' Version ' WITH ' Version ' IN
REPLACE('ALTER ''FUNCTION'  WITH 'ALTER FUNCTION'  IN 'ALTER ' + QUOTEDSTR(Definition) + ' Version 6.35 ')) as QuotedDef
FROM Information.Functions

It basically uses the QUOTEDSTR function to rewrite all internal STR's with the correct doubled-single-quotes (!) but then uses REPLACE to remove the unnecessary first and last quotes. There might still be some issues to sort out with more complex functions, but I think you can make it work.

Adam


I was thinking more in terms of the EXECUTE IMMEDIATE statement ie

  EXECUTE IMMEDIATE
   'ALTER ' +QUOTEDSTR( aDefinition) + ' VERSION ' + CAST(NewVersion AS VARCHAR);

Roy Lambert

Yes Roy, it would work well in that way.

I suggested using it in the context of the initial SELECT just because that gives you a chance to see the whole result in the CLOB that comes back so you can do a bit of pre-testing.
That way you get around limitations on debugging functions in EDB, by seeing exactly what is being returned prior to feeding it into the EXECUTE.
Otherwise I always find debugging single-quotes a bit of a chore especially if you have an ALTER statement for a function which itself contains SQL statements, which might contain further statements ...

The number of single-quotes needed multiplies to the point where the eye-strain is pretty intense!

Adam

>I suggested using it in the context of the initial SELECT just because that gives you a chance to see the whole result in the CLOB that comes back so you can do a bit of pre-testing.
>That way you get around limitations on debugging functions in EDB, by seeing exactly what is being returned prior to feeding it into the EXECUTE.
>Otherwise I always find debugging single-quotes a bit of a chore especially if you have an ALTER statement for a function which itself contains SQL statements, which might contain further statements ...
>
>The number of single-quotes needed multiplies to the point where the eye-strain is pretty intense!

I find if it gets that bad the best thing to do is bail on SQL and retreat to good ol' Delphi where I have a better chance of sorting things out.

Roy