All Forums Connectivity
RainerKopp 7 posts Joined 05/07
16 Jul 2007
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

joedsilva 505 posts Joined 07/05
17 Jul 2007

I haven't tried, but I think this can be accomplished via[Statement/PreparedStatement].[execute() / executeQuery()] ;

RainerKopp 7 posts Joined 05/07
17 Jul 2007

yes, we have tried PrepareStatement.execute(); using "call" and "execute" - but with execute/exec the DB complainsabout wrong syntax...Perhaps calling macros is not supported ?

joedsilva 505 posts Joined 07/05
18 Jul 2007

Here's a sample code I could get to work ....-- Macro definitionREPLACE 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")); }}

RainerKopp 7 posts Joined 05/07
19 Jul 2007

thanks a lot - it s working fine!

larshamann 2 posts Joined 03/08
28 Mar 2008

Hi,macros which use select only statements are working fine that way.Unfortunately I'm not successful executing macros that useupdate 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, idfrom t_ref_max_idwhere 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?

Fred 1096 posts Joined 08/04
28 Mar 2008

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 ResultSetint updateCount = stmt.getUpdateCount;// Returns the value of the UpdateCount result - optionalisResultSet = stmt.getMoreResults();// Positions to next result and returns TRUE, indicating it is a ResultSetResultSet 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)

larshamann 2 posts Joined 03/08
01 Apr 2008

Thanks very much. Works great.

You must sign in to leave a comment.