All Forums Connectivity
jmars 2 posts Joined 10/14
23 Oct 2014
How to call a Teradata stored procedure using openJPA?

I have a Teradata Datasource that uses TeraData JDBC driver for connection running locally on Websphere 8.5.5.2.  In this datasource, I have a custom property called 'databaseName' which represents a schema from the Teradata datasource.  I have a java class that needs to call a Teradata stored procedure that accepts 2 string parameters and outputs a resultset.  The call to the Teradata stored procedure involves a concatenation of a 'databaseName' in the sql statement itself like this example:  EXEC CER505C.M_SAP_SOURCE_CLAIM_SEARCH (?, ?); The 'CER505C' represents the 'databaseName' from the Teradata datasource customer property.  I your help on how to configure this in java openJPA to make it visible in a java class.  Here's a snippet of the code:
BaseJDBDAO has this one below:
abstract class BaseJdbcDAO {

    
    ////////////////////////////////////////////////////// //////
    // PRIVATE INSTANCE VARIABLES
    ////////////////////////////////////////////////////// //////
    @Resource(name="jdbc/tera_generic")
    private DataSource dataSource;
=============================================
EDWHistoricalClaimLineDAOImpl.java below:
@Stateless
public class EDWHistoricalClaimLineDAOImpl
extends BaseJdbcDAO
implements EDWHistoricalClaimLineDAO
{
    private Logger logger = Logger.getLogger(EDWHistoricalClaimLineDAOImpl.class);
    private static final String MACRO_CLAIM_NUMBER_SQL1 = "EXEC ";
    private static final String MACRO_CLAIM_NUMBER_SQL2 = ".M_SAP_SOURCE_CLAIM_SEARCH (?, ?);";
    private static final String MACRO_UMI_SQL1 = "EXEC ";
    private static final String MACRO_UMI_SQL2 = ".M_SAP_UMI_SRVC_DATE_SEARCH (?, ?, ?, ?);";
    ////////////////////////////////////////////////////// //////
    // PRIVATE INSTANCE VARIABLES
    ////////////////////////////////////////////////////// //////
    private String databaseName;  // this is what I need to get from the custom property in the Websphere server configuration
    private String macroClaimSql;
    private String macroUmiSql;
 
Thank you

tomnolan 594 posts Joined 01/08
23 Oct 2014

FYI -- that is a macro, not a stored procedure. Regarding your question...
 
You can obtain the current database name by executing the HELP SESSION command.
 
Please refer to my blog entry on this topic:
http://developer.teradata.com/blog/tomnolan/2012/03/how-to-determine-or-switch-the-current-database-using-the-teradata-jdbc-driver
 

You must sign in to leave a comment.