Intercepting SQL 000 codes
To check that a query has successfully retrieved data and continue processing according to this condition, you can check that the size of the collection of rows returned from a database query is greater than 0.
The following examples test the size of the ordered collection containing the result table rows as follows:
(resultCollection size = 0)
ifTrue: ["true block"]
ifFalse: ["false block"]
These examples contain two blocks of code that you can execute separately to illustrate how to test the results of a database query.
•In the first block of code, the query returns a result table. Because size = 0 returns false, the ifFalse: block is executed and an inspector opens on the ordered collection containing the result table rows.
•In the second block of code the query returns an empty result table (because there are no records in the CUSTOMERS table with customer number N00001). Because size = 0 returns true, the ifTrue: block is executed and a message box displays explaining the results of the query.
This example assumes that you have an active database manager.
To use this sample code, follow these steps:
1. Evaluate the first block using the Execute command.
2. Browse the inspector and then close it.
3. Evaluate the second block using the Execute command.
4. Close the message box.
"Test a query - succeed"
| connection querySpec result resultCollection |
resultCollection := OrderedCollection new.
connection := AbtDbmSystem activeDatabaseConnectionWithAlias: 'SampleConSpec'.
querySpec := (AbtQuerySpec new)
statement: 'SELECT STAFF.NAME, STAFF.ID FROM STAFF'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow asString)].
(resultCollection size = 0)
ifTrue: [CwPrompter new title: 'Database query failed']
ifFalse: [resultCollection inspect].
"Test a query - fail"
| connection querySpec result resultCollection |
resultCollection := OrderedCollection new.
connection := AbtDbmSystem activeDatabaseConnectionWithAlias: 'SampleConSpec'.
querySpec := (AbtQuerySpec new)
statement: 'SELECT *
FROM STAFF
WHERE STAFF.ID = 20000'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow asString)].
(resultCollection size = 0)
ifTrue: [CwMessagePrompter message: 'Database query returned empty table'
title: ' '.]
ifFalse: [resultCollection inspect.].
Last modified date: 01/29/2015