JDBC and calling a macro

Connectivity
Enthusiast

JDBC and calling a macro

Hello,

It's easy to call a teradata procedure with the teradata JDBC driver.

But is it possible to call a macro in JDBC too?

I found no documentation of this nice feature...

Greetings
Rainer Kopp
7 REPLIES
Enthusiast

Re: JDBC and calling a macro

I haven't tried, but I think this can be accomplished via

[Statement/PreparedStatement].[execute() / executeQuery()] ;
Enthusiast

Re: JDBC and calling a macro

yes, we have tried PrepareStatement.execute(); using "call" and "execute" - but with execute/exec the DB complains
about wrong syntax...

Perhaps calling macros is not supported ?
Enthusiast

Re: JDBC and calling a macro

Here's a sample code I could get to work ....

-- Macro definition

REPLACE MACRO SEL_EMP_MAC(EMPID INTEGER)
AS
(
SELECT EMPNAME FROM EMPLOYEE WHERE EMPID = :EMPID;
);

// java program ....

import java.sql.*;

public class MacroTest001
{

public static Connection establishConnection(String args[]) throws ClassNotFoundException, SQLException
{
Class.forName("com.ncr.teradata.TeraDriver");

String jdbcUrl = "jdbc:teradata://" + args[0];
String userId = args[1];
String password = args[2];

return DriverManager.getConnection(jdbcUrl, userId, password);
}

public static void main(String args[]) throws ClassNotFoundException, SQLException
{
Connection con = establishConnection(args);
PreparedStatement ps = con.prepareStatement("EXECUTE SEL_EMP_MAC(?);");

ps.setInt(1, 100);
ResultSet rs = ps.executeQuery();

while(rs.next())
System.out.println(rs.getString("EMPNAME"));

}

}

Enthusiast

Re: JDBC and calling a macro

thanks a lot - it s working fine!

Re: JDBC and calling a macro

Hi,

macros which use select only statements are working fine that way.
Unfortunately I'm not successful executing macros that use
update and select, e.g.:

REPLACE MACRO m_get_next_id
(
in_name VARCHAR(100) NOT NULL
)
AS
(
update t_ref_max_id
set id = id + 1
where id_name = :in_name;

select id_name,
id
from t_ref_max_id
where id_name = :in_name;
);

Executing that macro using:

PreparedStatement stmt = con.prepareStatement("EXECUTE m_get_next_id(?);");
stmt.setString(1, "process_id");
ResultSet rs = stmt.executeQuery();

I get:
SQL Exception: [NCR] [Teradata JDBC Driver] : executeQuery() cannot be used when there is no result set expected; use executeUpdate() or execute()

Using Perl::DBI via ODBC it's working OK.
So, is there any way to get that working using JDBC?
Teradata Employee

Re: JDBC and calling a macro

Your multistatement macro returns both an update count and a result set. So you need to use the generic "execute" method, e.g.

boolean isResultset = stmt.execute();
// Returns FALSE, indicating first result is not a ResultSet
int updateCount = stmt.getUpdateCount;
// Returns the value of the UpdateCount result - optional
isResultSet = stmt.getMoreResults();
// Positions to next result and returns TRUE, indicating it is a ResultSet
ResultSet rs = stmt.getResultSet();
// Returns the results

// NOTE: There are no more results when both
// getMoreResults (or execute) returns FALSE (saying current result is not a ResultSet), and
// getUpdateCount returns -1 (saying current result is not an UpdateCount)

Re: JDBC and calling a macro

Thanks very much. Works great.