ActiveX AS/400 Objects - SQL

• ActiveX AS/400 Objects - SQL

• Use this class to retrieve DB2/400 database information. This class supports read-only access to the native AS/400 database. ODBC if not involved with this class. Direct data base retrieval is referred to as Optimized SQL. This class does not have support for stored procedures.

• Contains read/write properties:

 

Name	Type	Description
AutoMessages	Boolean	Set True to halt and display error messages. Default = false
BlockCount	Long	Contains the number of records to fetch. Increasing the block count will improve response time but consume memory resources. Defaults to one.
CursorName	String	Identifies cursor name. Defaults to CURSOR1.
Name	String	Identifies SQL statement name. Defaults to SQLNAME1
NamingConvention	1	Establishes period or slash separator syntax within SQL statements. Defaults to cnvPeriod
SystemName	String	Name of AS/400 system. Defaults if blank.

 

1 Naming Convention sqlCONVENTION Enum cnvPeriod = 0, cnvSlash = 1

• Contains read-only properties:

 

Name	Type	Description
BOF	Boolean	Returns True if cursor is positioned at beginning of recordset.
Columns	SQLColumns	Column attributes and values returned from Execute method.
EOF	Boolean	Returns True if cursor is positioned at end of recordset.
Message	String	Most recent diagnostic message generated.
Opened	Boolean	Returns True if recordset is open.
RC	Long	Most recent CA/400 dll return code.
RecordCount	Long	Contains count of records in recordset.
Started	Boolean	Status flag stating whether conversation has been started.

 

• Contains methods:

 

Name	Description
Execute	Performs SQL statement passed as a required parameter.
MoveFirst	Positions the cursor to the first record within the recordset.
MoveLast	Positions the cursor after the last record within the recordset.
MoveNext	Positions the cursor to the next record relative to the current position within the recordset.
MovePrevious	Positions the cursor to the previous record relative to the current position within the recordset.

 

 

• VB, VBA SQL example:

Dim oSQL As ActiveX_AS400.SQL

Dim oCol As ActiveX_AS400.SQLColumn

Set oSQL = new ActiveX_AS400.SQL

 

‘Execute SQL statement

oSQL.NamingConvention = 1 ‘cnvSlash

Call oSQL.Execute("Select * From QIWS/QCUSTCDT")

 

‘Retrieve records in recordset and print results

If not oSQL.BOF Then Call oSQL.MoveFirst

Do while not oSQL.EOF

Printer.Print "Record number:" & oSQL.RecordCount

For each oCol in oSQL.Columns

Printer.Print oCol.Name & ":" & oCol.Value

Next

Call oSQL.MoveNext

Loop

 

‘Housekeeping

Set oSQL = Nothing

2003 - Chouinard & Myhre, Inc.