|
Home > Archive > SQL Anywhere Feedback > February 2006 > API for DDL statements
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
API for DDL statements
|
|
|
| API for DDL statements:
It will be a good feature that will help to create users for the database &
other comments like providing access to groups with API.
This will help to execute these commands dynamically from an interface other
than Sybase Central using GUI where the user who is executing the command is
not a programmer.
This is something like in ASE.
ex: sp_add_user
Thanks
vsv
| |
| David Fishburn 2006-02-25, 9:40 am |
| "vsv" <nospam@nospam.com> wrote in news:43fcec08$1@foru
ms-1-dub of
sybase.public.sqlanywhere. product_futures_disc
ussion:
v> API for DDL statements:
v> It will be a good feature that will help to create users for the
v> database & other comments like providing access to groups with API.
v> This will help to execute these commands dynamically from an interface
v> other than Sybase Central using GUI where the user who is executing
v> the command is not a programmer.
v> This is something like in ASE.
v> ex: sp_add_user
Why not just issue the DDL from any program?
create teable ...
grant connect to "dave" identified by "mypassword";
grant group to "DBA";
grant membership in group "DBA" to "dave";
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).
EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm
Developer Community / Whitepapers
http://www.ianywhere.com/developer
CaseXpress - to report bugs
http://casexpress.sybase.com
CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
| |
|
| I would like to do it dynamically like
grant connect to :variable_usr identified by :variable_pwd
You can't embed the (grant connect) command in ODBC dynamically or could we?
This will help a novice user on click of a button issue the grant command by
providing user id & pwd that he needs to create.
thanks
vsv
"David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
news:Xns97738912C1EF
Cfishburnsybasecom@1
27.0.0.1...
> "vsv" <nospam@nospam.com> wrote in news:43fcec08$1@foru
ms-1-dub of
> sybase.public.sqlanywhere. product_futures_disc
ussion:
>
> v> API for DDL statements:
> v> It will be a good feature that will help to create users for the
> v> database & other comments like providing access to groups with API.
> v> This will help to execute these commands dynamically from an interface
> v> other than Sybase Central using GUI where the user who is executing
> v> the command is not a programmer.
> v> This is something like in ASE.
> v> ex: sp_add_user
>
> Why not just issue the DDL from any program?
> create teable ...
>
> grant connect to "dave" identified by "mypassword";
> grant group to "DBA";
> grant membership in group "DBA" to "dave";
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> [url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>
| |
| Breck Carter 2006-02-25, 9:40 am |
| Your application can build the GRANT command in a string and then use
EXECUTE IMMEDIATE. If you want some tips 'n' hints about that, just
ask.
The SQL Anywhere philosophy has always been to provide powerful DDL
commands rather the ASE / Transact SQL approach of providing obscure
system stored procedures. The best example of this is the infamous
sp_foreignkey procedure which actually does not even implement
referential integrity... for many years ASE did not *have* declarative
(DDL) RI, you had to write all the triggers yourself.
Anyway, feel free to ask for specific sp_things, especially if you can
make a business case... but many of us prefer the complete power and
freedom of EXECUTE IMMEDIATE, and don't want to learn how to specify
long lists of parameters since we already know how to code the DDL.
Breck
On 23 Feb 2006 16:06:16 -0800, "vsv" <nospam@nospam.com> wrote:
>I would like to do it dynamically like
>grant connect to :variable_usr identified by :variable_pwd
>
>You can't embed the (grant connect) command in ODBC dynamically or could we?
>
>This will help a novice user on click of a button issue the grant command by
>providing user id & pwd that he needs to create.
>
>thanks
>vsv
>
>
>"David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
> news:Xns97738912C1EF
Cfishburnsybasecom@1
27.0.0.1...
>
--
breck.carter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
| |
|
| Breck,
thanks for your response.
I should then take this discussion in general group.
Because I don't know if execute immediate works for "grant connect"
I would be glad to hear for an example that will work.
Thanks
vsv
"Breck Carter" < NOSPAM__bcarter@risi
ngroad.com> wrote in message
news:5v0uv15vpubpkpi
2ecdskfos57d726073s@
4ax.com...
> Your application can build the GRANT command in a string and then use
> EXECUTE IMMEDIATE. If you want some tips 'n' hints about that, just
> ask.
>
> The SQL Anywhere philosophy has always been to provide powerful DDL
> commands rather the ASE / Transact SQL approach of providing obscure
> system stored procedures. The best example of this is the infamous
> sp_foreignkey procedure which actually does not even implement
> referential integrity... for many years ASE did not *have* declarative
> (DDL) RI, you had to write all the triggers yourself.
>
> Anyway, feel free to ask for specific sp_things, especially if you can
> make a business case... but many of us prefer the complete power and
> freedom of EXECUTE IMMEDIATE, and don't want to learn how to specify
> long lists of parameters since we already know how to code the DDL.
>
> Breck
>
> On 23 Feb 2006 16:06:16 -0800, "vsv" <nospam@nospam.com> wrote:
>
>
> --
> breck.carter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
| |
| Breck Carter 2006-02-25, 9:41 am |
| Yeah, execute immediate works just fine... it can be tricky
syntax-wise if you're not familiar with techniques for embedding
program code in character strings, but it is super-duper-powerful :)
Here's an example from SQL; you can do exactly the same stuff in
PowerScript:
---------------------------------------------------------------------
BEGIN
DROP PROCEDURE grant_connect_and_me
mbership;
EXCEPTION WHEN OTHERS THEN
END;
CREATE PROCEDURE grant_connect_and_me
mbership (
IN @group_id VARCHAR ( 128 ),
IN @user_id VARCHAR ( 128 ),
IN @password VARCHAR ( 128 ) )
BEGIN
DECLARE @sql LONG VARCHAR;
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP,
' grant_connect_and_me
mbership ( ''',
@group_id,
''', ''',
@user_id,
''', ''***'' )' ) TO CONSOLE DEBUG ONLY;
SET @sql = STRING ( 'REVOKE CONNECT FROM "', @user_id, '"' );
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @sql ) TO
CONSOLE DEBUG ONLY;
BEGIN
EXECUTE IMMEDIATE @sql;
EXCEPTION WHEN OTHERS THEN
END;
SET @sql = STRING ( 'GRANT CONNECT TO "', @user_id, '" IDENTIFIED
BY "', @password, '"' );
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @sql ) TO
CONSOLE DEBUG ONLY;
EXECUTE IMMEDIATE @sql;
SET @sql = STRING ( 'GRANT MEMBERSHIP IN GROUP "', @group_id, '" TO
"', @user_id, '"' );
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @sql ) TO
CONSOLE DEBUG ONLY;
EXECUTE IMMEDIATE @sql;
END; -- grant_connect_and_me
mbership
---------------------------------------------------------------------
-- Testing...
/*
SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';
CALL grant_connect_and_me
mbership ( 'DBA', 'Ainslie', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Briana', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Calista', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Delmar', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Electra', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Fabriane', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Genevieve', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Hunter', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Inari', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Jordan', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Khalil', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Lisette', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Marlon', 'SQL' );
CALL grant_connect_and_me
mbership ( 'DBA', 'Nissa', 'SQL' );
*/
Breck
On 24 Feb 2006 12:34:44 -0800, "vsv" <nospam@nospam.com> wrote:
>Breck,
>thanks for your response.
>I should then take this discussion in general group.
>Because I don't know if execute immediate works for "grant connect"
>I would be glad to hear for an example that will work.
>Thanks
>vsv
>
>"Breck Carter" < NOSPAM__bcarter@risi
ngroad.com> wrote in message
> news:5v0uv15vpubpkpi
2ecdskfos57d726073s@
4ax.com...
>
--
breck.carter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
| |
|
| Oh Yeah.....The proc has to be in WATCOM-SQL syntax.
It needs ";" <gr>
If Procedure is created in T-SQL format: it doesn't work.
Thanks so much.
vsv
"Breck Carter" < NOSPAM__bcarter@risi
ngroad.com> wrote in message
news:q7quv1lfi7sqnv3
bu9qlu43i1ielobudsc@
4ax.com...
> Yeah, execute immediate works just fine... it can be tricky
> syntax-wise if you're not familiar with techniques for embedding
> program code in character strings, but it is super-duper-powerful :)
>
> Here's an example from SQL; you can do exactly the same stuff in
> PowerScript:
>
> ---------------------------------------------------------------------
> BEGIN
> DROP PROCEDURE grant_connect_and_me
mbership;
> EXCEPTION WHEN OTHERS THEN
> END;
>
> CREATE PROCEDURE grant_connect_and_me
mbership (
> IN @group_id VARCHAR ( 128 ),
> IN @user_id VARCHAR ( 128 ),
> IN @password VARCHAR ( 128 ) )
> BEGIN
>
> DECLARE @sql LONG VARCHAR;
>
> MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP,
> ' grant_connect_and_me
mbership ( ''',
> @group_id,
> ''', ''',
> @user_id,
> ''', ''***'' )' ) TO CONSOLE DEBUG ONLY;
>
> SET @sql = STRING ( 'REVOKE CONNECT FROM "', @user_id, '"' );
> MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @sql ) TO
> CONSOLE DEBUG ONLY;
>
> BEGIN
> EXECUTE IMMEDIATE @sql;
> EXCEPTION WHEN OTHERS THEN
> END;
>
> SET @sql = STRING ( 'GRANT CONNECT TO "', @user_id, '" IDENTIFIED
> BY "', @password, '"' );
> MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @sql ) TO
> CONSOLE DEBUG ONLY;
> EXECUTE IMMEDIATE @sql;
>
> SET @sql = STRING ( 'GRANT MEMBERSHIP IN GROUP "', @group_id, '" TO
> "', @user_id, '"' );
> MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @sql ) TO
> CONSOLE DEBUG ONLY;
> EXECUTE IMMEDIATE @sql;
>
> END; -- grant_connect_and_me
mbership
>
> ---------------------------------------------------------------------
> -- Testing...
>
> /*
> SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';
> CALL grant_connect_and_me
mbership ( 'DBA', 'Ainslie', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Briana', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Calista', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Delmar', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Electra', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Fabriane', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Genevieve', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Hunter', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Inari', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Jordan', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Khalil', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Lisette', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Marlon', 'SQL' );
> CALL grant_connect_and_me
mbership ( 'DBA', 'Nissa', 'SQL' );
> */
>
> Breck
>
>
> On 24 Feb 2006 12:34:44 -0800, "vsv" <nospam@nospam.com> wrote:
>
>
> --
> breck.carter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
|
|
|
|
|