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:
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.
[st4u143-iPhone.m4v ( Size: 6430069 )]