Re: JDBCR4 calling repeatedly

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