Not sure whether this is possible, I may be writing it wrong:

DECLARE Crsr CURSOR FOR Stmt;
DECLARE DBName VARCHAR(80);
  PREPARE Stmt FROM
     'SELECT Name FROM Configuration.Databases';
  OPEN Crsr;
  FETCH FIRST FROM Crsr ('Name') INTO DBName;
  WHILE NOT EOF(Crsr) DO                                                           
  USE DBName;
    EXECUTE IMMEDIATE
      'CREATE MODULE "UIDModule" PATH ''C:\EDBData\Modules\UIDModule.dll'' ';
    EXECUTE IMMEDIATE
      'CREATE FUNCTION "UID" () RETURNS INTEGER EXTERNAL NAME "UIDModule" ';
    FETCH NEXT FROM Crsr ('Name') INTO DBName;
  END WHILE;
  CLOSE Crsr;

What I am trying to do is to register a new module, and then a new function which uses this module on multiple databases in a single function.

I am trying to return the list of databases and then pull the DBName out to put into the "USE" section, but the script doesn't take it.

I have also tried using the form:

    EXECUTE IMMEDIATE
      'CREATE MODULE "'+DBName+'.UIDModule" PATH ''c:\EDBData\FullwellMill\BackUp\UIDModule.dll'' ';

And it doesn't like this either.

... any suggestions?

Adam


Looking at USE in the OLH its using a database object name and you're using a string which is why no go.

You can try using EXECUTE IMMEDIATE to alter the database in use (haven't tried it here) eg

EXECUTE IMMEDIATE 'USE '+DBName;

but I doubt that will work since it won't be executed in the context of the script.

What would work is creating a script in code to do the job and then load and run that. In Delphi an absolute doddle but in pure SQL I'm not sure.

Roy Lambert [Team Elevate]

>>What would work is creating a script in code to do the job and then load and run that. In Delphi an absolute doddle >>but in pure SQL I'm not sure.

Thanks Roy. This is really meant to run on a server far from a Delphi app, but I can work around the problem ...

Adam,

<< Not sure whether this is possible, I may be writing it wrong: >>

Roy is correct - it's the USE statement.  You cannot use variables with the
USE statement because the engine needs to be able to compile any called
procedures/functions in the script using the proper database, and it needs
the database information at compile-time.

You'll have to use separate scripts in order to do what you want, each with
its own USE statement.  This is one of the things that I want to improve
upon, though, by adding the ability to execute scripts from within another
script.  That will allow you to USE each database, and then execute a script
that does what you want with each database.

--
Tim Young
Elevate Software
www.elevatesoft.com