Re: How can a recordset returned by a sproc be updated?
From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 07/28/04
- Next message: Anuradha: "Work around for dynamic SQL"
- Previous message: Andrew J. Kelly: "Re: Object creation not appearing in consequent log backup"
- In reply to: Uri Dimant: "Re: How can a recordset returned by a sproc be updated?"
- Next in thread: Richard Hayward: "Re: How can a recordset returned by a sproc be updated?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 28 Jul 2004 13:52:24 +0100
You won't get a plain text update statement. ADO/ODBC will use sp_prepare
and a few other extended procedures to execute the statements it builds on
the basis of the metadata that is send along with the resultset from the
stored procedure.
-- Jacco Schalkwijk SQL Server MVP "Uri Dimant" <urid@iscar.co.il> wrote in message news:OQep44JdEHA.2504@TK2MSFTNGP12.phx.gbl... > Jacco > While I run the test on my box I haven't seen update statement as OP said. > I > did exactly the same thing as he is described. > > "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> > wrote > in message news:OPVZy1JdEHA.4092@TK2MSFTNGP10.phx.gbl... >> Hi Richard, >> >> With the resultset metadata is returned to the client that allows ADO and >> ODBC etc to create update , insert delete statements etc against the base >> tables. This works for both stored procedures and views. For views you >> can >> prevent this metadata being sent by creating the view with the WITH >> VIEW_METADATA option. This will return metadata for the view instead of > the >> underlying tables. >> >> You can revoke or deny permissions on the base tables as well if you want > to >> prevent updates. >> >> -- >> Jacco Schalkwijk >> SQL Server MVP >> >> >> "Richard Hayward" <richard@tortoise.demon.co.uk> wrote in message >> news:jj9gg01lvosqnu6kp0ok7m8oiogufj5rud@4ax.com... >> >I have an application written in Delphi connecting to MSSQL 2000 via >> > ADO. >> > >> > At the server, using the example pubs database I've created a stored >> > procedure: >> > >> > CREATE PROCEDURE spAuthor AS >> > select * from authors >> > >> > My application displays the resulting records in a grid. >> > To my surprise, the application is also able, via ADO, to update the >> > table in pubs. >> > >> > Watching whats going on with SQL profiler, somehow SQL gets >> > generated, to update the correct table on the >> > server. How can that be possible? I gave the application no knowledge >> > of which table my data was coming from. Can ADO 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, SQL is somehow generated to update the underlying table. >> > >> > >> > Could anyone enlighten me as to how this is possible? >> > >> > regards >> > Richard >> > >> > >> > richard@tortoise.demon.co.uk >> >> > >
- Next message: Anuradha: "Work around for dynamic SQL"
- Previous message: Andrew J. Kelly: "Re: Object creation not appearing in consequent log backup"
- In reply to: Uri Dimant: "Re: How can a recordset returned by a sproc be updated?"
- Next in thread: Richard Hayward: "Re: How can a recordset returned by a sproc be updated?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
