Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Create function on multiple databases using a script |
Tue, Nov 29 2011 6:24 AM | Permanent Link |
Adam Brett Orixa Systems | 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? |
Tue, Nov 29 2011 7:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Nov 30 2011 2:06 PM | Permanent Link |
Adam Brett Orixa Systems | >>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 ... |
Wed, Nov 30 2011 5:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |