How to call a Teradata stored procedure using openJPA?

Connectivity
N/A

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

1 REPLY
Teradata Employee

Re: How to call a Teradata stored procedure using openJPA?

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-...