 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Richard Hayward
Guest
|
 Posted: Sun Jul 25, 2004 10:30 pm Post subject: How can ADOStoredProc update? |
 |
|
I'm using D6 and MSSQL 2000.
The simplest application I can think of.
At the server, using the example pubs database I've created a stored
procedure:
CREATE PROCEDURE spAuthor AS
select * from authors
Then, in Delphi, I have an
ADOConnection,
ADOStoredProc connecting to spAuthor,
DataSource,
DBGrid
The grid displays the records.
If I now edit one of the records in the grid, the changes are posted
back to the table in pubs. Watching whats going on with SQL profiler,
Delphi has somehow generated SQL to update the correct table on the
server. How can it do that? I gave it no knowledge of which table my
data was coming from. Can it somehow get at the internal text of the
stored procedure? Or does the returned recordset carry with it the
names of the tables its fields came from?
Next, I tried making a view and having the stored procedure select
from the view instead:
CREATE VIEW vAuthors
AS
select *
from authors
CREATE PROCEDURE spAuthor AS
select * from vAuthors
but still, Delphi generates SQL to update the underlying table.
Could anyone enlighten me as to how this is possible?
regards
Richard
[email]richard (AT) tortoise (DOT) demon.co.uk[/email]
|
|
| Back to top |
|
 |
Richard Hayward
Guest
|
| Posted: Mon Jul 26, 2004 6:35 pm Post subject: Re: How can ADOStoredProc update? |
|
|
Hi Brian,
thanks for your help.
| Quote: | Ado does the updating along with the ole Db driver for SQL server.
|
The SQL update commands I can see in SQL Profiler were constructed
somehow on the client machine?
| Quote: | If you want to prevent editing your dataset then don't grant update privileges
to the stored procedure
|
In EM (Enterprise Manager) ? As I understand it, the sproc can do
whatever its creator has permission to do. EM offers no way to grant a
sproc update privleges on a table. All you can do is grant users EXEC
permission on the sproc. Anyway, the sproc shouldn't know how to
update, all it can do is select:
CREATE PROCEDURE spAuthor AS
select * from authors
Are you saying that the ado/driver installation on the client machine
can see inside the sproc to discover where it gets its data from?
regards
Richard
[email]richard (AT) tortoise (DOT) demon.co.uk[/email]
|
|
| Back to top |
|
|
Michael Cessna
Guest
|
| Posted: Thu Jul 29, 2004 8:57 pm Post subject: Re: How can ADOStoredProc update? |
|
|
Richard,
The proc returns a recordset and a recordset contains the meta data that it
needs to update the recordset.
Regards,
Michael Cessna
"Richard Hayward" <richard (AT) tortoise (DOT) demon.co.uk> wrote
| Quote: | Hi Brian,
thanks for your help.
Ado does the updating along with the ole Db driver for SQL server.
The SQL update commands I can see in SQL Profiler were constructed
somehow on the client machine?
If you want to prevent editing your dataset then don't grant update
privileges
to the stored procedure
In EM (Enterprise Manager) ? As I understand it, the sproc can do
whatever its creator has permission to do. EM offers no way to grant a
sproc update privleges on a table. All you can do is grant users EXEC
permission on the sproc. Anyway, the sproc shouldn't know how to
update, all it can do is select:
CREATE PROCEDURE spAuthor AS
select * from authors
Are you saying that the ado/driver installation on the client machine
can see inside the sproc to discover where it gets its data from?
regards
Richard
[email]richard (AT) tortoise (DOT) demon.co.uk[/email]
|
|
|
| Back to top |
|
|
Richard Hayward
Guest
|
| Posted: Fri Jul 30, 2004 8:48 am Post subject: Re: How can ADOStoredProc update? |
|
|
Hello Michael,
| Quote: | The proc returns a recordset and a recordset contains the meta data that it
needs to update the recordset.
|
Thanks, for your reply.
So what I'm trying to find is some way of writing my stored procedures
that prevents the return of any metadata enabling the client to deduce
anything about where the stored procedure gets its data from.
I did start a small thread on this in
microsoft.public.sqlserver.programming and one possible soultion would
seem to be to have the stored procedures get data from views, never
tables, where the views are created with the:
WITH VIEW_METADATA
option.
I'd like to know if there are any other ways.
regards
Richard
[email]richard (AT) tortoise (DOT) demon.co.uk[/email]
|
|
| Back to top |
|
|
Michael Cessna
Guest
|
| Posted: Fri Jul 30, 2004 7:23 pm Post subject: Re: How can ADOStoredProc update? |
|
|
Richard,
Are there just certain fields in the recordset that you don't want to be
updated? If it's all fields...then just open the recordset as read-only (a
non-updatable recordset).
Mike
"Richard Hayward" <richard (AT) tortoise (DOT) demon.co.uk> wrote
| Quote: | Hello Michael,
The proc returns a recordset and a recordset contains the meta data that
it
needs to update the recordset.
Thanks, for your reply.
So what I'm trying to find is some way of writing my stored procedures
that prevents the return of any metadata enabling the client to deduce
anything about where the stored procedure gets its data from.
I did start a small thread on this in
microsoft.public.sqlserver.programming and one possible soultion would
seem to be to have the stored procedures get data from views, never
tables, where the views are created with the:
WITH VIEW_METADATA
option.
I'd like to know if there are any other ways.
regards
Richard
[email]richard (AT) tortoise (DOT) demon.co.uk[/email]
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
