![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
![]() |
Wed, Jun 18 2014 6:33 AM | Permanent Link |
Michael Saunders | 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; |
Wed, Jun 18 2014 6:58 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Jun 18 2014 10:49 AM | Permanent Link |
Uli Becker | 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 |
Wed, Jun 18 2014 12:46 PM | Permanent Link |
Michael Saunders | 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 |
Wed, Jun 18 2014 4:32 PM | Permanent Link |
Michael Saunders | 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 |
Thu, Jun 19 2014 4:07 AM | Permanent Link |
Uli Becker | 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 |
Thu, Jun 19 2014 4:29 AM | Permanent Link |
Uli Becker | 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 |
Thu, Jun 19 2014 4:38 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Jun 19 2014 5:42 AM | Permanent Link |
Michael Saunders | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |