I wrote a lotusscript agent wich launch an stored procedure in a DB2 database. It works ok, but there is a performance and security problem: Notes launch automatically a previous select from a system table (SYSIBM.SYSPARMS) to obtain the names and types of the parameters. We dont like that, because it degrades the performance and we should give select permisions on the table to all users.
I’ve tried both Lotus Connector and ODBC classes, and got the same results. Is there a way to launch the SP withouth the previous select?
Thank you.
This is the code wich launch the SP using LC:
'Input parameters
Set empid = parameters.Append(“WK_INPUT1”,LCTYPE_TEXT)
empid.Value = “DATA1”
Set empid = parameters.Append(“WK_INPUT2”,LCTYPE_TEXT)
empid.Value = “DATA2”
Set empid = parameters.Append(“WK_INPUT3”,LCTYPE_NUMERIC)
empid.Value = 1000
'Output parameters
Set empid = parameters.Append(“WK_OUTPUT1”,LCTYPE_NUMERIC)
Set empid = parameters.Append(“WK_OUTPUT2”,LCTYPE_TEXT)
Set empid = parameters.Append(“WK_OUTPUT3”,LCTYPE_TEXT)
Set empid = parameters.Append(“WK_OUTPUT4”,LCTYPE_INT)
'Execute the stored procedure
Dim count As Integer
count = src.call(parameters,1,result_set)
This is the code wich launch the SP using ODBC:
Dim result As New ODBCResultSet
…
Dim a,b,c,d
status= result.ExecProcedure(“OWNER.SP_NAME”,“DATA1”,“DATA2”,1000,a,b,c,d)
This is the select we dont like to be launched:
SQLProcedureColumnsW( hStmt=1:1, szProcCatalog=Null Pointer, cbProcCatalog=0, szProcSchema=“DESA” - X"4400450053004100", cbProcSchema=-3, szProcName=“RAESS002” - X"52004100450053005300300030003200", cbProcName=-3, szColumnName=Null Pointer, cbColumnName=0 )
---> Time elapsed - +1,850000E-004 seconds
( StmtOut=“SELECT SCHEMA, NAME, PARMNAME, TYPESCHEMA, CASE WHEN DATATYPEID=497 OR SOURCETYPEID=497 THEN ‘INTEGER’ WHEN DATATYPEID=501 OR SOURCETYPEID=501 THEN ‘SMALLINT’ WHEN (DATATYPEID=481 OR SOURCETYPEID=481) AND LENGTH=4 THEN ‘REAL’ WHEN (DATATYPEID=481 OR SOURCETYPEID=481) AND LENGTH=8 THEN ‘FLOAT’ WHEN DATATYPEID=453 OR SOURCETYPEID=453 THEN ‘CHARACTER’ WHEN DATATYPEID=449 OR SOURCETYPEID=449 THEN ‘VARCHAR’ WHEN DATATYPEID=457 OR SOURCETYPEID=457 THEN ‘LONG VARCHAR’ WHEN DATATYPEID=485 OR SOURCETYPEID=485 THEN ‘DECIMAL’ WHEN DATATYPEID=469 OR SOURCETYPEID=469 THEN ‘GRAPHIC’ WHEN DATATYPEID=465 OR SOURCETYPEID=465 THEN ‘VARGRAPHIC’ WHEN DATATYPEID=473 OR SOURCETYPEID=473 THEN ‘LONG VARGRAPHIC’ WHEN DATATYPEID=405 OR SOURCETYPEID=405 THEN ‘BLOB’ WHEN DATATYPEID=409 OR SOURCETYPEID=409 THEN ‘CLOB’ WHEN DATATYPEID=413 OR SOURCETYPEID=413 THEN ‘DBCLOB’ WHEN DATATYPEID=385 OR SOURCETYPEID=385 THEN ‘DATE’ WHEN DATATYPEID=389 OR SOURCETYPEID=389 THEN ‘TIME’ WHEN DATATYPEID=393 OR SOURCETYPEID=393 THEN ‘TIMESTAMP’ ELSE ‘’ END, LENGTH, SCALE, ROWTYPE, ORDINAL, CCSID FROM “SYSIBM”.SYSPARMS WHERE RTRIM(SCHEMA) =‘DESA’ AND RTRIM(NAME) =‘RAESS002’ AND ROUTINETYPE = ‘P’ ORDER BY SCHEMA, NAME, ORDINAL FOR FETCH ONLY” )