Teradata Forums / Drivers and UDFs / Teradata / JDBC and calling a macro / Latest Posts

RE: JDBC and calling a macro

Lars Hamann — Tue, 01 Apr 2008 11:26:55 GMT

Thanks very much. Works great.

RE: JDBC and calling a macro

Fred Pluebell — Fri, 28 Mar 2008 13:22:07 GMT

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)

RE: JDBC and calling a macro

Lars Hamann — Fri, 28 Mar 2008 11:21:02 GMT

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?

RE: JDBC and calling a macro

Rainer Kopp — Thu, 19 Jul 2007 12:16:32 GMT

thanks a lot - it s working fine!

RE: JDBC and calling a macro

joedsilva — Wed, 18 Jul 2007 01:21:02 GMT

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"));

}

}

RE: JDBC and calling a macro

Rainer Kopp — Tue, 17 Jul 2007 13:11:02 GMT

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 ?

RE: JDBC and calling a macro

joedsilva — Tue, 17 Jul 2007 00:01:49 GMT

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

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

JDBC and calling a macro

Rainer Kopp — Mon, 16 Jul 2007 13:01:36 GMT

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