Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SS2K
Expertise: Intermediate
Aug 15, 2002

Update SQL Server records via HTTP

Thanks to the SQL Server extensions for the Web and XML, it is now possible to query a SQL Server database (and get the result as XML) as well as insert, update, and delete records in a database. In this tip I'll show a few examples of this technique. Here's a first example that shows how you can send an SQL command to delete a record:

http://srv/app?delete%20from%20employees%20where%20employeeId=1
Needless to say, this technique makes your database prone to all sort of malicious attacks. You can limit the risk by using command templates that you have configured. These templates work a bit like stored procedures that embed the data manipulation commands. Here is a template that deletes one record from the Employees table.

<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'> 
  <sql:header >
    <sql:param name="employeeId">0</sql:param>  
  </sql:header>                                          
  <sql:query >
    delete from employees where employeeId=@employeeId
  </sql:query>                                            
</root>
Notice that the template can take arguments, exactly as a stored procedure does. In the above example, the only parameter is employeeId and is declared in the sql:header section. If this field is omitted when the template is used, its default value is zero. The sql:query section contains the actual SQL command and uses the argument, which appears here as @employeeId. Assuming that you've save the template in a file named DeleteEmployee.xml, here's how you can invoke the template via HTTP:

http://srv/app/template/DeleteEmployee.xml?employeeId=101
Giuseppe Dimauro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date