Smalltalk with James Robertson

Today's Smalltalk 4 You looks at inserting data into tables, and querying data from tables, in VA Smalltalk. If you have trouble viewing it here in the browser, you can also navigate directly to YouTube. To watch now, click on the image below:

.

If you have trouble viewing that directly, you can click here to download the video directly. If you need the video in a Windows Media format, then download that here.

You can also watch it on YouTube:

Today we'll look at inserting and querying data from a database in VA Smalltalk - to see how to connect, please go back to this tutorial, as we are building on it here.

Once you have a connection to your database set up (with a user who has permissions to make changes), you can insert data pretty easily. Get a reference to the table (as we did for creating it), and then create a new row object:

table := (connection openTableNamed: 'PEOPLE').
newRow := table emptyRow.

Now that you have a row, add data (as you would using a dictionary), and then tell the table to add the row. Don't forget to commit the changes:

newRow 
	at: #name put: 'James Robertson';
	at: #street put: 'River Run';
	at: #city put: 'Columbia';
	at: #state put: 'MD';
	at: #zipcode put: 12345;
	at: #phone put: '123-456-7890'.
table addRow: newRow.


"commit it"
connection commitUnitOfWork

Next, we'll query the database for the data we just inserted. Set up a query spec. For simple "select *" type queries, there's a convenience method:

"query"
resultCollection := OrderedCollection new.
querySpec := AbtQuerySpec forEntireTable: table.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow)].
^resultCollection first asDictionary

There are a number of things going on there. First, we create a query spec. Then we take the results and gather them into a collection. The results are a collection of AbtRow objects - which is where the #asDictionary API comes from:

You should take a bit of time to browse that class and look at the APIs. What if you want to use arbitrary SQL, and not an implied "Select *"? Use something like this:

resultCollection := OrderedCollection new.
querySpec := (AbtQuerySpec new)
	statement: 'SELECT * FROM PEOPLE'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow)].
^resultCollection first asDictionary.

That pretty much wraps it up for today - we'll look at some more examples next time.

Need more help? There's a screencast for other topics like this which you may want to watch. Questions? Try the "Chat with James" Google gadget over in the sidebar.

Technorati Tags: smalltalk, va smalltalk, database