Navigation:  Programming Cookbook > Database Connectivity >

Executing SQL Statements

Previous pageReturn to chapter overviewNext page

Many non-query SQL statements do not answer a result table at all. In these situations it is more appropriate to use DBConnection>>exec: rather than #query:. This works in a similar way, except that it does not answer a DBResultSet, but rather an instance of DBStatement that has just executed the SQL. Typically, you would use #exec: for performing create, update and delete operations on the database and also when using the various cataloguing operations that SQL provides. Let us try some examples to illustrate this.

s := c exec: 'delete from Orders where OrderID=10250'.

 

If this operation fails it will raise an exception. Hopefully, this statement did not do so, so we can assume that the order was deleted successfully. To find out how many rows were affected by the statement you can use:

s numRows. "Display it"

 

Just to confirm that this particular order is no longer in the Orders table why not execute the following query:

c query: 'select * from Orders where OrderID=10250'. "Inspect it"

 

If the delete operation had found no matching rows to remove then the statement would still succeed but #numRows would answer zero.

Let's try an update operation to modify a record, this time in the Products table.

s := c exec: 'update Products set UnitPrice=17.5 where ProductName=''Tofu'''.

s numRows. "Display it"

 

Just validate that the update succeeded:

(c query: 'select UnitPrice from Products where ProductName=''Tofu''') at: 1. "Display it"