SQL Statements
The scripts below should be pretty easy to follow, so, I will just list them with a brief comment. If you are looking to do something, you should be able to find it easily by scrolling through the below scripts. The first script creates a table that has a number, string, and date column. The next two scripts insert and select rows from the table created. The last script in the set gets all the table names.
"Create the sql_table"
| sqlDef table connection |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
sqlDef := '(id smallint, last_name varchar(30), start_date date)'.
table := connection createTableNamed: 'sql_table' definition: sqlDef.
"Insert two rows"
| connection newRow table oc |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
table := (connection openTableNamed: 'sql_table').
oc := OrderedCollection new.
1 to: 2 do: [:n |
newRow := table emptyRow.
newRow
at: #ID put: n;
at: #LAST_NAME put: 'SMITH', n asString;
at: #START_DATE put: Date today.
oc add: newRow ].
connection autoCommit: true.
table addRows: oc
"select the rows"
| connection querySpec resultTable |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
querySpec := (AbtQuerySpec new) statement: 'Select * From sql_table'.
resultTable := connection resultTableFromQuerySpec: querySpec ifError: [:ex | ex
inspect].
resultTable asStrings.
"get all table names"
| table connection rows row |
(AbtDatabaseConnectionSpec forDbmClass: AbtOracle10DatabaseManager databaseName:
'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection allTableNames
The statements below show how to execute SQL statements at a lower level:
"Execute an Insert statement directly, use parameter markers"
| connection sqlString qs array |
(AbtDatabaseConnectionSpec
forDbmClass: AbtOracle10DatabaseManager
databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
sqlString := 'insert into sql_table values (:ID, :LAST_NAME, :START_DATE)'.
array := Array new: 2.
1 to: 2 do: [:i |
array
at: i
put: (Dictionary new
at: 'ID' put: i+2;
at: 'LAST_NAME' put: 'JONES', i asString;
at: 'START_DATE' put: Date today;
yourself) ].
qs := AbtQuerySpec new.
qs
name: 'insertFields';
statement: sqlString;
hostVarsShape:
(AbtCompoundType new
name: '';
addField: (AbtDatabaseLongIntegerField new
name: 'ID';
nullsOk: true;
procBindType: 1;
udt: nil);
addField: (AbtOracleVarCharField new
name: 'LAST_NAME';
length: 30;
nullsOk:
true;
procBindType: 1;
udt: nil);
addField: (AbtOracleDateField new
name: 'START_DATE';
nullsOk: true;
procBindType: 1;
udt: nil)).
connection executeQuerySpec: qs
withArrayOfValues: array
ifError: [AbtDbmSystem abtDefaultDatabaseErrorBlock.
"Execute an Insert statement directly"
| connection |
(AbtDatabaseConnectionSpec
forDbmClass: AbtOracle10DatabaseManager
databaseName: 'orcl') connect.
connection := AbtDbmSystem activeDatabaseConnection.
connection autoCommit: true.
connection executeSQLStatement: 'insert into sql_table(id, last_name) values(5,
''johnson'')'
Last modified date: 01/29/2015