I have no problem with the following but cannot see how to change the query  to accept a run time value for the User name Harry  rather than the literal value

Many thanks in advance

  with DM.ConfigurationQuery do
     begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE USER "Harry"');
        SQL.Add('PASSWORD '+Engine.QuotedSQLStr(DM.tbUsersPassword.value));
        ExecSQL;
        Close;
     end;

Michael


Basically you can't parameterise this query - I think its because the name is an identifier rather than data. The only way you can specify the name at run tine is to create the sql yourself.

Roy Lambert

Michael,

in addition to what Roy said:

you can use a stored procedure/script with params for username and
password and "compose" the statement like this:

CREATE  PROCEDURE "CreateNewUser" (IN "FUsername" VARCHAR(20) COLLATE
DEU_CI, IN "FPassword" VARCHAR(20) COLLATE DEU_CI)
BEGIN

   Execute Immediate 'CREATE USER "' + FUsername + '" PASSWORD ' +
QUOTEDSTR(FPassword);

END
VERSION 1.00

Uli

you can use a stored procedure/script with params for username and
password and "compose" the statement like this:

CREATE  PROCEDURE "CreateNewUser" (IN "FUsername" VARCHAR(20) COLLATE
DEU_CI, IN "FPassword" VARCHAR(20) COLLATE DEU_CI)
BEGIN

   Execute Immediate 'CREATE USER "' + FUsername + '" PASSWORD ' +
QUOTEDSTR(FPassword);

END

I had thought I was missing something obvious but it seems its not as simple as I had thought   I am wanting for the user to be able to create and drop Users direct from my program and as  I am not too familiar with SQL then before I research what you have suggested  does this enable me to achieve my goal and if not how do you guys enable end users to do accomplish this

Thanks

I have been struggling for the past 2 hours in trying to understand stored procedures The manual is really for reference and I cannot find anywhere an introduction to how to create or use them  I would really appreciate a very basic guide to get me up and running  EG Do  need to use the EDB Manager to create them in the first instance or not   I have tried entering the suggestion above as both the SQL and Scripts in EDBManager to no avail   

Many thanks

Mike

Michael,

> I have been struggling for the past 2 hours in trying to understand stored procedures The manual is really for reference and I cannot find anywhere an introduction to how to create or use them  I would really appreciate a very basic guide to get me up and running  EG Do  need to use the EDB Manager to create them in the first instance or not   I have tried entering the suggestion above as both the SQL and Scripts in EDBManager to no avail

If you haven't worked with stored procedures, you can get a lot of
information by googling, e.g.:

http://www.sql-server-performance.com/2003/stored-procedures-basics/

While there are some differences between SQLServer and EDB, the basics
are the same.

Regarding the procedure I sent you: what exactly is the problem? Which
error do you get?

Just use EDBManager, click on the arrow of the "New" button, choose
script and you have an empty script. Replace the code with the code I
sent you, press F9 and it should work.

Maybe a simpler approach for you would be to right click "Procedures" in
the tree view, create a new procedure, insert the params you want and
write (or paste) the sql code.
You can then test the proc by executing it within EDBManager.

Once you are familiar with stored procedures, you'll love them.
I do many things with stored procedure instead of using Delphi code.

Executing a SP from Delphi is simple - there is a component TEDBStoredProc:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2&product=rsdelphi&version=2010&topic=Executing_Stored_Procedures

Just prepare it, add the params, execute it and you are done.

If you need special help, just tell me.

Uli

Correction: The code I sent you is just a statement, so do *not* create
an empty *script*, but just an empty *statement*, paste the code and
execute it.

After you have created the proc, you can alter it in EDBManager and see
how things are working in EDBManager.

Uli

Michael


I came from DBISAM and used SQL very little. I'm using it a bit more these days, but still not a massive amount. What I did and do quite often is have a query component on the form and set the sql for that. I also use Tim's built in capabilities of the session and database to execute an SQL statement.

Unless you use this as a learning exercise you could do something like this:

 if Session.Execute('SELECT * FROM Users WHERE Name =' + QuotedStr(Staff_ID.AsString)) > 0
  then Verb := 'ALTER'
 else Verb := 'CREATE';
 Cmd := Verb + ' USER "' + Staff_ID.AsString + '" PASSWORD ' + QuotedStr(Check) + ' DESCRIPTION ' + QuotedStr(Staff_Name.AsString);
 Session.Execute(Cmnd);
 end;

That will create or alter the user, then you have to deal with permissions. The simplest, but not necessarily the best, way is to give everyone administrator rights so

if Verb = 'CREATE' then begin
Cmd := 'GRANT "Administrators" TO "' + Staff_ID.AsString + '"'
Session.Execute(Cmd);
end;

A final tip is that when you use the gui in EDBManager to carry out some command a copy of the actual SQL used is stored in Explorer | SQL History

I, and I think many others, have used this to assist in learning the dark art that is SQL


Roy Lambert

Thanks Uli and Roy You have given me a good starting point  It looks an interesting and new area for me to investigate   I will return if I have any more issues

Mike