|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/19/2007 12:16:00 PM
Posts: 5,
Visits: 1
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
I haven't tried, but I think this can be accomplished via
[Statement/PreparedStatement].[execute() / executeQuery()] ;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/19/2007 12:16:00 PM
Posts: 5,
Visits: 1
|
|
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 ?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
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"));
}
}
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/19/2007 12:16:00 PM
Posts: 5,
Visits: 1
|
|
|
thanks a lot - it s working fine!
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/1/2008 11:26:03 AM
Posts: 2,
Visits: 5
|
|
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?
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 7/2/2008 5:38:40 PM
Posts: 280,
Visits: 297
|
|
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)
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/1/2008 11:26:03 AM
Posts: 2,
Visits: 5
|
|
|
Thanks very much. Works great.
|
|
|
|