Hi

I have written a script that works OK in EDB as long as I am positioned
on a database.

If not I get a message like this:-

ElevateDB Error #403 An error occurred with the statement at line 4 and
column 25 (The database Configuration is read-only and this operation
cannot be performed (DROP TABLE ExportUsers))

I want to run this SQL as a JOB, but it won't run, presumably for the
same reason.

I've tried to use database specifiers like this  'DROP TABLE
"DB9999"."ExportUsers"', but that gives a syntax error because of the
specifier.

Somehow I need to create link to the database that will work in a JOB.

My script is below the signature

Cheers

Jeff
--
BEGIN
   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE "ExportUsers"';
   EXCEPTION
   END;

    EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "ExportUsers"
   (
      "Name" VARCHAR(40),
      "Password" VARCHAR(40),
      "Roles" VARCHAR(1024),
      PRIMARY KEY ("Name")
      )'
   ;

   EXECUTE IMMEDIATE '

   INSERT INTO "ExportUsers"
   SELECT U.Name,
          U.Password,
          CAST(LIST(UR.Name) AS VARCHAR(1024)) AS "Roles"
   FROM Configuration.Users U
   LEFT OUTER JOIN Configuration.UserRoles UR ON UR.GrantedTo = U.Name
   WHERE UR.Name <> ''Public''
     AND UR.Name <> ''Administrators''
   GROUP BY U.Name
   ORDER BY U.Name'
   ;
--
--  more stuff .......
--
   EXECUTE IMMEDIATE '
   EXPORT TABLE "ExportUsers"
   TO "ExportUsers.CSV"
   IN STORE "STORE-Transfers"'
   ;

   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE "ExportUsers"';
   EXCEPTION
   END;

END

Jeff,

> I have written a script that works OK in EDB as long as I am positioned
> on a database.

USE [<DatabaseName>]

is what you want.

From the manual:

Usage
--------------------------------------------------------------------------------
Use this statement in a script or job definition to open a new database
or close a database that was previously opened. To open a new database,
specify the database name after the USE keyword. Opening a new database
automatically closes the current database that is open. To close the
current database and not open a new database, leave the database name
blank. This reverts the current database back to the default database,
which is always the system-defined Configuration database for jobs.

Uli

On 26/08/2015 6:46 p.m., Uli Becker wrote:
> Jeff,
>
>> I have written a script that works OK in EDB as long as I am positioned
>> on a database.
>
> USE [<DatabaseName>]
>
> is what you want.
>
>  From the manual:    ...

Thanks Uli

I *have* read the manual from end to end, honestly

As always, the solution is obvious once you know!

Cheers

Jeff
--

Jeff,

<< I have written a script that works OK in EDB as long as I am positioned on a database. >>

Sorry I missed this post.  I got totally tied up in a new EWB release last week, and missed the DBISAM/EDB forums for a couple of days.  My apologies.

Tim Young
Elevate Software
www.elevatesoft.com