Using MS ADO in Visual LANSA

Date:Archived
Product/Release:Visual LANSA
Abstract:A sample and considerations when using Microsoft's ActiveX Data Objects (ADO) in Visual LANSA
Submitted By:LANSA Technical Support

Some considerations and advice when using MS ADO (or general ActiveX Components) in Visual LANSA:

  • Have a good understanding of the ADO Object Model.
  • When invoking methods, specify all the required parameters in ActiveX controls, even with a blank or null field (i.e. to specify the default). Not following this recommendation may cause fatal errors during runtime.
  • Use the Recordset's Collect collection to retrieve field values (instead of the Visual Basic like syntax: recordset!fieldname).
  • Boolean values can be retrieved as alphanumeric "True" or "False" values.
  • There is a special consideration with the EOF (or BOF). You have to check for these values when moving the pointer on the table. Otherwise, a fatal error would occur when going beyond the file limits. FYI, there is a related note on the MS Visual C ++ documentation with respect to this point (i.e. need to rename EOF).
  • Whenever you have a problem, try the same functionality in MS Visual Basic. You can use the Object Browser to see the Object details (e.g. Properties, Methods, Events and data types). Once you get it working in VB, you may try to adapt it to Visual LANSA.
  • Some collections items may require the "Item" object as a prefix, to get the item details. For example, in the following line:
    #adoconnection.Properties.Item<'Data Source'> Value('SUPPSERVER')
    the Item object needs to be specified, while VB may simply default it like: cn.Properties("Data Source").Value = ServerName
    (where cn is the ADO connection object).
  • Use the Feature Help (in Visual LANSA) to see the Component details. To do that, you position the cursor on the DEF_COM line and press F2.

Following is an example of using an ADO against a MS SQL Server Database. It was tested on a Windows NT Workstation. You may need to redefine your ADO ActiveX components if working on a different environment.

***************************************************
* 
* COMPONENT: DBADO
* 
* DESCRIPTION: Test ActiveX Data Objects
* 
***************************************************
FUNCTION OPTIONS(*DIRECT)
BEGIN_COM CAPTION('ADO test') HEIGHT(237) WIDTH(423)
* ADO connection
DEFINE_COM CLASS(#ADOCON.Connection) NAME(#ADOCONNECTION) REFERENCE(*DYNAMIC)
* ADO recordset
DEFINE_COM CLASS(#ADORS.Recordset) NAME(#ADORECORDSET) REFERENCE(*DYNAMIC)
* Form components
* fields
DEFINE_COM CLASS(#STD_TEXT.Visual) NAME(#STD_TEXT) CAPTION('F74 Object name:') DISPLAYPOSITION(1) HEIGHT(19) LABELTYPE(Caption) LEFT(48) PARENT(#COM_OWNER) READONLY(True) 	TABPOSITION(1) TOP(40) WIDTH(297)
DEFINE_COM CLASS(#STD_NUM.Visual) NAME(#STD_NUM) CAPTION('F74 number of records') DISPLAYPOSITION(2) HEIGHT(19) LABELTYPE(Caption) LEFT(48) PARENT(#COM_OWNER) READONLY(True) TABPOSITION(2) TOP(96) WIDTH(224)

* Push buttons
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#PB_NEXT) CAPTION('Move Next') DISPLAYPOSITION(3) HEIGHT(33) LEFT(32) PARENT(#COM_OWNER) TABPOSITION(3) TOP(144) WIDTH(89)
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#PB_LAST) CAPTION('Move Last') DISPLAYPOSITION(4) HEIGHT(33) LEFT(160) PARENT(#COM_OWNER) TABPOSITION(4) TOP(144) WIDTH(89)
DEFINE_COM CLASS(#PRIM_PHBN) NAME(#PB_FIRST) CAPTION('Move First') DISPLAYPOSITION(5) HEIGHT(33) LEFT(288) PARENT(#COM_OWNER) TABPOSITION(5) TOP(144) WIDTH(81)
DEFINE_COM CLASS(#PRIM_STBR) NAME(#STBR_1) DISPLAYPOSITION(6) HEIGHT(24) LEFT(0) MESSAGEPOSITION(1) PARENT(#COM_OWNER) TABPOSITION(6) TABSTOP(False) TOP(186) WIDTH(415)

* ADO connection variables
DEFINE FIELD(#CSTRING) TYPE(*CHAR) LENGTH(256)
DEFINE FIELD(#CUSERID) TYPE(*CHAR) LENGTH(12)
DEFINE FIELD(#CPASSW) TYPE(*CHAR) LENGTH(12)
DEFINE FIELD(#COPTIONS) TYPE(*DEC) LENGTH(7) DECIMALS(0)
DEFINE FIELD(#SQLLXF74) TYPE(*CHAR) LENGTH(256)
DEFINE FIELD(#CURTYPE) TYPE(*DEC) LENGTH(7) DECIMALS(0)
DEFINE FIELD(#LOCKTYPE) TYPE(*DEC) LENGTH(7) DECIMALS(0)
DEFINE FIELD(#RSOPTIONS) TYPE(*DEC) LENGTH(7) DECIMALS(0)
DEFINE FIELD(#RSEOF) TYPE(*CHAR) LENGTH(1)
EVTROUTINE handling(#com_owner.Initialize)
SET #com_owner caption(*component_desc)

* Create ADO Connection and Record Set
set_ref #adoconnection to(*CREATE_AS #ADOCON.Connection)

* set DB provider
set #adoconnection Provider('sqloledb')

* Set SQLOLEDB connection properties
set #adoconnection.Properties.Item<'Data Source'> Value('SUPPSERVER')
set #adoconnection.Properties.Item<'Initial Catalog'> Value('LX805SS7')
set #adoconnection.Properties.Item<'User ID'> Value('sa')
set #adoconnection.Properties.Item<'Password'> Value('')

* Open ADO connection
Invoke #adoconnection.Open ConnectionString(#cstring) UserID(#cuserid) Password(#cpassw) Options(#coptions)

* Create ADO Record Set
set_ref #adorecordset to(*CREATE_AS #ADORS)

* set connection parameters
* Use client cursor to enable AbsolutePosition property
* Cursor Type set to 3 = adOpenStatic
change #curtype '3'

* Lock Type set to 1 = adLockReadOnly
change #locktype '1'

* Options set to 2 = adCmdTable
change #rsoptions '2'

* set ADO table
change #SQLLXF74 'LX_F74'

* Open record set based on the active connection
Invoke #adorecordset.Open Source(#SQLLXF74) ActiveConnection(#adoconnection) CursorType(#curtype) LockType(#locktype) Options(#rsoptions)

* retrieve number of records
set #std_num Value(#adorecordset.RecordCount)

* set DB fields
execute setfields
ENDROUTINE
EVTROUTINE HANDLING(#PB_NEXT.Click)
change #rseof #adorecordset.EOF

* check if it is EOF before moving to next record
IF COND('#rseof *eq F')
Invoke #adorecordset.MoveNext
ENDIF
change #rseof #adorecordset.EOF

* also check, if it is not EOF, after moving to next record
IF COND('#rseof *eq F')

* set DB fields
execute setfields
ELSE
message 'You already in the last record of the file'
ENDIF
ENDROUTINE
EVTROUTINE HANDLING(#PB_LAST.Click)
Invoke #adorecordset.MoveLast

* set DB fields
execute setfields
ENDROUTINE
EVTROUTINE HANDLING(#PB_FIRST.Click)
Invoke #adorecordset.MoveFirst

* set DB fields
execute setfields
ENDROUTINE
SUBROUTINE NAME(setfields)

* set DB fields
set #std_text Value(#adorecordset.Collect<'X74OBJ'>)

* more fields ...
ENDROUTINE
END_COM

* **************************************************
* *
* ActiveX Components:
* *
* **************************************************
* *
* COMPONENT . : ADOCON *
* DESCRIPTION : ADO Connection *
* *
* **************************************************
FUNCTION OPTIONS(*DIRECT)
BEGIN_COM PROGID('ADODB.Connection.2.1') TYPELIBID ('{00000201-0000-0010-8000-00AA006D2EA4}2.1,0')
END_COM
* **************************************************
* *
* COMPONENT . : ADORS *
* DESCRIPTION : ADO Recordset *
* *
* **************************************************
FUNCTION OPTIONS(*DIRECT)
BEGIN_COM PROGID('ADODB.Recordset.2.1') TYPELIBID('{00000201-0000-0010-8000-00AA006D2EA4}2.1,0')
END_COM