Re: JDBCR4 calling repeatedly

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

Don,

I'm not sure this is the best mailing list to ask this question on. 
Since this is an RPG-related question, I will answer it on the RPG400-L 
mailing list.


On 7/4/2016 8:33 PM, Don Brown wrote:
> I was sure I had seen something on this in the archives but I just can't
> find the answer.
>
> We are processing a file which requires information from an external MSSQL
> database as part of a load process.
>
> We are using Scott's JDBCR4 service program and all is working fine.
>
> I am just wanting to determine from a performance perspective what needs
> to be closed on each call.
>
> The code below is in a service program that gets called repeatedly.
>
> Questions:
> 1. Can I move the    jdbc_close(conn);  to a separate procedure that I
> call after all records are processed ?
>     If this is the case then I will also move the initial connection to
> only be executed once, something like
>     OpenConn();
>     Process load - repeated calls to the getMSSQL_info
>     CloseConn()
>
> 2. Should I change the where clause to use a parameter marker and use
> JDBC_PrepStmt and JBDC_ExecPrepRqy for better performance ?
>
> 3. Do I need to execute JDBC_FreePrepStmt every time or can I just reset
> the value of the parameter and run the JDBC_ExecPrepQry ?
>
>
> Currently the process is like this; (Modified for simplicity and to
> provide concept)
>
>    driver = 'net.sourceforge.jtds.jdbc.Driver'  ;
>    url    = 'jdbc:jtds:sqlserver://xxxxxxx:1433' ;
>    database = 'SomeHistory'  ;
>    userid   = 'xxx'  ;
>    passwrd = 'xxxxxxxxxx'  ;
>    sql = 'Select  ISNULL(TB.ID1,0), +
>                   ISNULL(TD.ID2,0)  +
>          from               SomeHistory.dbo.Table1  TB with (nolock) +
>                  inner join SomeHistory.dbo.Table2 TD with (nolock) +
>           on      TB.ID1 = TD.ID2 +
>           where   TB.ID1 =' + %editc(Job:'X');
>
>    prop = JDBC_Properties();
>    JDBC_setProp(prop: 'User'    : %trim(userid));
>    JDBC_setProp(prop: 'Password': %trim(passwrd));
>    JDBC_setProp(prop: 'DatabaseName': %trim(database));
>
>    conn = JDBC_ConnProp( %trim(driver)
>                        : %trim(url)
>                        : prop );
>    JDBC_freeProp(prop);
>
>    if (conn = *NULL);
>        return '*Error';
>    endif;
>
>    // Query the database
>    rs = jdbc_ExecQry( conn  : %trim(sql)   );
>
>    // Get row from table
>    dow (jdbc_nextRow(rs));
>        Value1 = jdbc_getCol(rs: 1);
>        Value2 = jdbc_getCol(rs: 2);
>    enddo;
>
>    jdbc_freeResult(rs);
>    jdbc_close(conn);
>    return Value1;
>
>
> Thank you for any assistance
>
> Don Brown