× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.