I’m about to show my total ignorance on how to write Stored Procedures using Dynamic SQL so please keep the chuckling and guffaws to a minimum.

I’m trying to write a stored procedure to add a user to the system Users table and my database Users table, as well as assign them to a role.  I know how to do
this using Static SQL (well at least in MS SQL anyway) but haven’t a clue how to do it with Dynamic SQL in ElevateDB.  Here’s my uninspiring attempt:


PROCEDURE "AddUser" (IN "PUsername" VARCHAR(30) COLLATE ANSI, IN "PPassword" VARCHAR(20) COLLATE ANSI, IN "PAdmin" BOOLEAN, IN "PExpires"
BOOLEAN, IN "PExpiryDate" DATE, IN "PEnabled" BOOLEAN)

BEGIN             

  DECLARE Result CURSOR FOR SQLStatement;

  -- Add user to system Users table   

  PREPARE SQLStatement FROM

     'CREATE USER PUsername PASSWORD PPassword';

  EXECUTE SQLStatement;

  -- Add user to database Users table

  PREPARE SQLStatement FROM

     'INSERT INTO MyDatabase.Users

        (Username, Admin, Expires, ExpiryDate, Enabled)

      VALUES

        (PUsername, PAdmin, PExpires, PExpiryDate, True)';

  EXECUTE SQLStatement;      

  -- Add user to the appropriate role

  PREPARE SQLStatement FROM

     'IF PAdmin = True THEN

         GRANT Administrators TO PUsername

      END';

  EXECUTE SQLStatement;

END


As you can probably see I have no idea what I'm doing.  My problem is that ElevateDB doesn't seem to like my parameters.  Please tell me what I'm doing
wrong.  Be gentle.

Thanks.

Regards,

SteveG

Steve,

> As you can probably see I have no idea what I'm doing.  My problem is that ElevateDB doesn't seem to like my parameters.  Please tell me what I'm doing
> wrong.  Be gentle.

This should work:

PROCEDURE "Test" (IN "PUsername" VARCHAR(30) COLLATE ANSI, IN
"PPassword" VARCHAR(20) COLLATE ANSI, IN "PAdmin" BOOLEAN, IN "PExpires"
BOOLEAN, IN "PExpiryDate" DATE, IN "PEnabled" BOOLEAN)
BEGIN

   DECLARE Result CURSOR FOR SQLStatement;
   -- Add user to system Users table
   PREPARE SQLStatement FROM
      'CREATE USER "' + PUsername + '" Password ''' + PPassword + '''';
   EXECUTE SQLStatement;

   -- Add user to the appropriate role
   if PAdmin = True then
     PREPARE SQLStatement FROM
      'GRANT "Administrators" TO ' + PUsername;
     EXECUTE SQLStatement;
   End If;

   -- Add user to database Users table
   PREPARE SQLStatement FROM
      'INSERT INTO MyUsers
         (Username, Admin, Expires, ExpiryDate, Enabled)
       VALUES
         (?, ?, ?, ?, True)';
   EXECUTE SQLStatement using PUsername, PAdmin, PExpires, PExpiryDate;

END

1. Generally you can pass the parameters by using the wildcard "?" and
adding the parameters with "using".
2. Sometimes dynamic parameters are not allowed - this seems to be the
case with "Create User" and "Grant...". (At least they didn't work when
I tried them.) In this case you can compose the statement. Don't miss
the correct number of quotes.
3. You shouldn't have a table named "User" in your database. That's why
I renamed it to "MyUser".

Regards Uli

> As you can probably see I have no idea what I'm doing.  My problem is
> that ElevateDB doesn't seem to like my parameters.  Please tell me
> what I'm doing wrong.  Be gentle.

What I do when I'm not sure about syntax or methodology, is do it
manually in EDB Manager, then look at the SQL History--it's a wonderful
help!

--
David Cornelius
Cornelius Concepts

Steve,

In addition to Uli's excellent response, I would just add that EXECUTE
IMMEDIATE is the easiest route when executing DDL statements, which can't
use parameters, thus making the whole DECLARE/PREPARE/EXECUTE cycle nothing
more than excess statements.

PROCEDURE "AddUser" (IN "PUsername" VARCHAR(30) COLLATE ANSI, IN
"PPassword" VARCHAR(20) COLLATE ANSI, IN "PAdmin" BOOLEAN, IN "PExpires"
BOOLEAN, IN "PExpiryDate" DATE, IN "PEnabled" BOOLEAN)

BEGIN

  -- Add user to system Users table
  EXECUTE IMMEDIATE 'CREATE USER ' + QUOTEDSTR(PUsername, '"') +
                                          ' PASSWORD ' +
QUOTEDSTR(PPassword, '"');

  -- Add user to the appropriate role
  IF PAdmin = True THEN
      EXECUTE IMMEDIATE 'GRANT Administrators TO ' + QUOTEDSTR(PUsername,
'"');
  END;

END

BTW, hush-hush on QUOTEDSTR(), it's not supposed to be in there until 2.03.


--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim, Uli and David for your help.  Much appreciated.

And thanks for the QuotedStr tip Tim!  I won't tell anyone.

Steve

Tim,

> BTW, hush-hush on QUOTEDSTR(), it's not supposed to be in there until 2.03.

I love this.

Uli

I wanted to say thanks again Tim, Uli and David for your help.  After not having a clue on how to write a single stored procedure in ElevateDB, I've now written over
40 for my new application.  And amazingly they all work.  Wonders will never cease.

Regards,

Steve

Steve,

<< I wanted to say thanks again Tim, Uli and David for your help.  After not
having a clue on how to write a single stored procedure in ElevateDB, I've
now written over 40 for my new application.  And amazingly they all work. >>

Cool, I'm glad that you're making progress.  Also, in case you didn't
know, you can prototype a stored procedure as a script in EDB in the EDB
Manager.  There's a built-in script debugger in the EDB Manager that you can
use to set breakpoints, examine local variables, etc.  It's very helpful
with complicated scripts/procedures/functions/jobs/triggers.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

> Cool, I'm glad that you're making progress.  Also, in case you didn't
> know, you can prototype a stored procedure as a script in EDB in the EDB
> Manager.  There's a built-in script debugger in the EDB Manager that you can
> use to set breakpoints, examine local variables, etc.  It's very helpful
> with complicated scripts/procedures/functions/jobs/triggers.

Thanks Tim, I didn't know that.  That's sounds very useful - I'll check it out.  EDB Manager is an excellent application.  I use it constantly and find it invaluable.  

Which reminds me, one suggestion I have for EDB Manager is that it would be great if I could select some lines out of a block of SQL code and execute just the
selected code.

Regards,

Steve

Steve,

<< Which reminds me, one suggestion I have for EDB Manager is that it would
be great if I could select some lines out of a block of SQL code and execute
just the selected code. >>

Do you mean in a script ?  EDB scripts are a little more difficult to handle
like this because they rely on executing from the begin to the end, and
aren't just a series of SQL statements.

--
Tim Young
Elevate Software
www.elevatesoft.com