. .

st4u

ST 4U 147: Using SQL Directly with VA Smalltalk

October 19, 2011 8:54:10.736

Today's Smalltalk 4 You looks at direct SQL access from VA Smalltalk. It's pretty simple, so long as you remember that the API differs when you use select (as opposed to any other SQL operator). 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:

SQL Access.

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 accessing a relational database using direct SQL statements in VA Smalltalk - to see how to connect, please go back to this tutorial, as we are building on it here. You'll want to take a look at class AbtQuerySpec if you intend to work at this level with your database accessing. The main thing to know is this: if you are querying (using select), then you use #resultTableFromQuerySpec: If you are using any other kind of SQL statement, use #executeQuerySpec: instead. The way you utilize this is pretty simple. To query, use the following kind of code:


"using AbtQuerySpec"
resultCollection := OrderedCollection new.
querySpec := AbtQuerySpec new.
querySpec statement: 'select * from people'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow)].
^resultCollection.

It will look the same with a where clause:


"with a where clause"
resultCollection := OrderedCollection new.
querySpec := AbtQuerySpec new.
querySpec statement: 'select * from people where zipcode = 12345'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow)].
^resultCollection first asDictionary

To use any other SQL statement:


"insert with a query spec"
querySpec := AbtQuerySpec new.
querySpec statement: 'insert into people values (''Fred Flintstone'', ''Rubble Way'', ''Old Town'', ''PH'', 23456, ''456-7890-1234'')'.
connection executeQuerySpec: querySpec.

"commit it"
connection commitUnitOfWork

"delete with a query spec"
querySpec := AbtQuerySpec new.
querySpec statement: 'delete from people where zipcode = 23456'.
connection executeQuerySpec: querySpec.

"commit it"
connection commitUnitOfWork

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: , , ,

Enclosures:
[st4u147-iPhone.m4v ( Size: 5910209 )]

posted by James Robertson

 Share Tweet This