SQLService Example
This RDMLX form provides an example of using the SQLService. The form allows you to Connect to a database with a JDBC driver, then to Execute a Query against this database. To view the data the user will run a READ command to download the data from the server and display it in a list view.
Note that in this example connection is to an IBM i database called JSMJDBC (since this is an IBM i example, the database name is a library name). The driver name used in the CONNECT command corresponds to the driver name and path defined in the SQLService properties file. The file being accessed is called TBLNAME and it consists of the fields ID, NAME, AGE, SALARY, DEPT, and GRADE.
To run this example you will need to do the following:
a. Ensure that you have the JDBC driver for the database installed into the jar directory of your LANSA Integrator instance. Also ensure that the SQLServices.properties file contains the details of this JDBC driver and the database that you are connecting to.
b. Define two fields in the LANSA Repository:
- #JSMSTS, Char, Length 20
- #JSMMSG, Char, Length 255
c. Set the #JSMSRV field to the correct JSM server (and port) value.
This code example uses the following steps:
1. Define the JSMCOMMAND related fields.
2. Define the fields that will be used to map the table fields to the columns in the results list. The fields defined here should match the length and type of the fields defined in the table being queried.
3. Define the 2 fields required for the list that is used to hold the field column mappings.
4. Define the list that will hold the column mappings. This should be a two column list. - the first column in this list will hold the column name and the second field will hold the field name.
5. Define two working lists using the fields defined in the step 2. These lists will be populated by the READ command from the result list that the query generates. In this example, 2 lists are created to demonstrate how you can keep going back to the result list to access different columns as and when required. Two browselists are defined that are the same shape as these working lists, and these will be used to display the results on a screen.
6. Set up the JSM server settings.
7. Connect to the JSM system then the SQLService.
8. The JSMX_OPEN Built In Function is used to connect this JSMX client to the Java Services Manager, and to start a thread for the service.
9. The Service_Load(SQLService) command loads and initializes the service.
10. Connect to the JDBC driver.
11. Define the column field mappings. This is done by setting #FIELD to the appropriate column and the #1. COLUMN to the appropriate field name (from the table). Each mapping will be added as a new entry to the #MAPLST working list, and then a SET PARAMETER(*MAP) command will be issued. Notice that a SERVICE_LIST is passed as part of this command - the fields defined here are those used in the mapping list. The mapping list must also be specified in the TO_GET portion of the JSM command.
12. Disconnect from the JDBC driver then the JSM system.
13. When the form is closed, disconnect from the service, unload the temporary directory and close the service.
***************************************************
*
* This RDMLX form provides an example of using the
* SQLService. The form allows you to Connect to a
* database with a JDBC driver, then to Execute a
* Query against this database. To view the data the
* user will run a Read command to download the data
* from the server and display it in a list view.
*
* Note that in this example connection is to a
* IBM i database called JSMJDBC (since this is a
* IBM i example, the database name is a library name).
* The driver name used in the CONNECT command
* corresponds to the driver name and path defined
* in the SQLService properties file. The file being
* accessed is called TBLNAME and it consists of the
* fields ID, NAME, AGE, SALARY, DEPT, and GRADE.
*
* Disclaimer : The following material is supplied as
* sample material only. No warranty concerning this
* material or its use in any way whatsoever is
* expressed or implied.
*
***************************************************
FUNCTION OPTIONS(*DIRECT)
Begin_Com Role(*EXTENDS #PRIM_FORM) Clientheight(380) Clientwidth(679) Height(407) Left(313) Top(101) Width(687)
Define_Com Class(#PRIM_STBR) Name(#STBR_1) Displayposition(1) Height(24) Left(0) Messageposition(1) Parent(#COM_OWNER) Tabposition(1) Tabstop(False) Top(356) Width(679)
Define_Com Class(#PRIM_LTVW) Name(#READA) Componentversion(2) Displayposition(2) Fullrowselect(True) Height(150) Left(168) Parent(#COM_OWNER) Showsortarrow(True) Tabposition(2) Top(24) Width(500)
Define_Com Class(#PRIM_PHBN) Name(#READ1) Caption('Read 1') Displayposition(3) Left(40) Parent(#COM_OWNER) Tabposition(3) Top(61)
Define_Com Class(#PRIM_PHBN) Name(#READ2) Caption('Read 2') Displayposition(4) Left(43) Parent(#COM_OWNER) Tabposition(4) Top(112)
Define_Com Class(#PRIM_LVCL) Name(#LVCL_1) Displayposition(1) Parent(#READA) Source(#SQLREAD) Width(100)
* Define the JSMCOMMAND related fields
Define Field(#JSMCMD) Type(*Char) Length(255)
Define Field(#JSMHND) Type(*Char) Length(4)
Define Field(#JSMSRV) Type(*Char) Length(50)
* Define the fields that will be used to map the table fields to the columns in the results list. The fields defined here should match the length and type of the fields defined in the table being queried
Define Field(#COL1) Type(*Char) Length(010)
Define Field(#COL2) Type(*Char) Length(020)
Define Field(#COL3) Type(*Dec) Length(008) Decimals(0)
Define Field(#COL4) Type(*Dec) Length(012) Decimals(2)
* Define the 2 fields required for the list that is used to hold the field column mappings.
Define Field(#FIELD) Type(*Char) Length(010)
Define Field(#COLUMN) Type(*Char) Length(030)
* Define the list that will hold the column mappings. This should be a two column list - the first column in this list will hold the column name and the second field will hold the field name
Def_List Name(#MAPLST) Fields(#FIELD #COLUMN) Type(*Working)
* Define 2 working lists using the fields defined in the step 2. These lists will be populated by the Read command from the result list that the query generates. We are creating 2 here to demonstrate how we can keep going back to the result list to access different columns as and when required. We also define 2 browselists of the same shape as these working lists, and these will be used to display the results on a screen
Def_List Name(#WRKLST1) Fields(#COL1 #COL3) Type(*Working)
Def_List Name(#WRKLST2) Fields(#COL1 #COL2 #COL4) Type(*Working)
EVTROUTINE handling(#com_owner.Initialize)
SET #com_owner caption(*component_desc)
* Set up the JSM server settings
#JSMSRV := 'LANSA01:4560'
* Execute the method to connect to the SQLService.
#COM_OWNER.Connect
* Set up the mapping
#COM_OWNER.Mapping
* Execute the query
#COM_OWNER.RunQuery
ENDROUTINE
* Connect to the JSM system then the SQLService
Mthroutine Name(Connect)
* 'Open service'
* The JSMX_OPEN Builtin Function is used to connect this JSMX client to the Java Services Manager, and to start a thread for the service.
Use Builtin(JSMX_OPEN) With_Args(#JSMSRV) To_Get(#JSMSTS #JSMMSG #JSMHND)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
* 'Load service'
* The Service_Load(SQLService) command loads and initializes the service.
#JSMCMD := 'Service_Load Service (SQLService)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
* Connect to the JDBC driver
#JSMCMD := 'Connect Driver(DB2) Database(JSMJDBC) User(ALICK) Password(MEL123)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
Endroutine
Mthroutine Name(Mapping)
* Define the column field mappings. This is done by setting #FIELD to the appropriate column and the #COLUMN to the appropriate field name (from the table). Each mapping will be added as a new entry to the #MAPLST working list, and then a SET PARAMETER(*MAP) command will be issued. Notice that a SERVICE_LIST is passed as a part of this command - the fields defined here are those used in the mapping list. The mapping list must also be specified in the TO_GET portion of the JSM command.
#FIELD := COL1
#COLUMN := ID
Add_Entry To_List(#MAPLST)
#FIELD := COL2
#COLUMN := NAME
Add_Entry To_List(#MAPLST)
#FIELD := COL3
#COLUMN := AGE
Add_Entry To_List(#MAPLST)
#FIELD := COL4
#COLUMN := SALARY
Add_Entry To_List(#MAPLST)
#JSMCMD := 'Set Parameter(*Map) Service_List(FIELD,COLUMN)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #MAPLST)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
ENDROUTINE
Mthroutine Name(RunQuery)
#JSMCMD := 'Execute Query(Select ID,NAME,AGE,SALARY From TBLNAME)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
ENDROUTINE
*Disconnect from the JDBC driver then the JSM system
Mthroutine Name(Disconnect)
* Disconnect from the service
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND DISCONNECT) To_Get(#JSMSTS #JSMMSG)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
* 'Unload service'
* This command is required to unload the service and to remove the temporary directory.
#JSMCMD := 'Service_Unload'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
* 'Close service'
* The final step in the process is to close the service.
Use Builtin(JSMX_CLOSE) With_Args(#JSMHND) To_Get(#JSMSTS #JSMMSG)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
Endroutine
* When the form is closed we want to disconnect from the service
EVTROUTINE HANDLING(#COM_OWNER.Closing) OPTIONS(*NOCLEARMESSAGES *NOCLEARERRORS)
#COM_OWNER.Disconnect
ENDROUTINE
EVTROUTINE HANDLING(#READ1.Click)
Clr_List #WRKLST1
Clr_List #READA
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND 'Read Service_List(COL1,COL3) Scroll(*NO)') To_Get(#JSMSTS #JSMMSG #WRKLST1)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
Selectlist #wrklst1
#SQLREAD := #COL1.Trim + ' ' + #COL3.AsString.Trim
Add_Entry #reada
EndSelect
ENDROUTINE
EVTROUTINE HANDLING(#READ2.Click)
Clr_List #WRKLST2
Clr_List #READA
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND 'Read Service_List(COL1,COL2,COL4) Scroll(*NO)') To_get(#JSMSTS #JSMMSG #WRKLST2)
#COM_OWNER.Check(#JSMSTS #JSMMSG)
SelectList #WRKLST2
#SQLREAD := #COL1.Trim + ' ' + #COL2.Trim + ' ' + #COL4.AsString.Trim
Add_Entry #READA
ENDSELECT
ENDROUTINE
* The following method routine is used by all the JSMX commands to handle any errors that are encountered.
Mthroutine Name(Check)
Define_Map For(*Input) Class(#JSMSTS) Name(#I_STATUS)
Define_Map For(*Input) Class(#JSMMSG) Name(#I_MESSAGE)
Message Msgtxt(#JSMSTS.Trim + ': ' + #JSMMSG)
Endroutine
END_COM