Re: JDBCR4 calling repeatedly

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

Hi Don,

I moved this discussion.  It was originally on Midrange-L, but as it's 
related to RPG, I thought RPG400-L would be a better choice.

1) Yes, of course you can put it in a procedure.  (Why wouldn't you be 
able to?  I don't know of anything that can't be put into a procedure -- 
assuming it's coded properly, of course.)

2) Yes, using a parameter marker is quicker (and safer, security-wise) 
than concatenating a value if you plan to run the statement more than 
once. Of course, a big part of how much difference this makes depends on 
how the database engine itself works.  I'm not especially familiar with 
MS SQL Server -- but it sure couldn't hurt!

3) You would free the result set, but not the statement itself. Then 
change the parameter marker and run the statement again. Only free the 
statement after you're done with all of the times you plan to run it.

You might also consider looking at Dieter's ARDGATE tool as an 
alternative to JDBCR4. It lets you run the Java part of things in a Java 
Application Server which can improve performance, especially if you're 
running this in multiple jobs at the same time.

-SK


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