Calling a macro via JDBC

Connectivity
Fan

Calling a macro via JDBC

Hi,

I'm trying to call a Teradata macro via JDBC. I'm using the following code

sql.append("execute dbp.special_macro(123,'1','20120901000000','20120904130000','20120801000000');");
PreparedStatement stmt = conn.prepareStatement(sql.toString());

ResultSet result = null;

if (stmt.execute(sql.toString())) {
result = stmt.getResultSet();

When the code reaches the line with the execute - Statement,  I always get an exception like:

[Teradata JDBC Driver] [TeraJDBC 14.00.00.08] [Error 1094] [SQLState HY000] The use of this method is not valid

I also tried with executeQuery but it was the same result.

I did neither find any hints in google for this kind of exception nor for the error code 1094.

Can anyone give me some advise, what's going wrong here?

Thank you for your help

Oliver

Tags (2)
11 REPLIES
Supporter

Re: Calling a macro via JDBC

From the JDBC documentation


Teradata Database Macros


A Teradata Database macro consists of one or more SQL statements. Macros can be executed using Statement.execute() method. The application can retrieve the result using Statement.getResultSet() or Statement.getUpdateCount() methods and in case multiple results are returned, then the application must use Statement.getMoreResults() method to iterate through these results.

Teradata Employee

Re: Calling a macro via JDBC

Ulrich is correct that the execute method must be used for executing a macro, not the executeUpdate or executeQuery method.

However, those methods are all overloaded, meaning multiple method definitions with different argument lists, and your problem is due to using the wrong overloaded version of the method.

Your problem is due to using the execute(String) method with a PreparedStatement. That is not valid. The execute(String) method is only for use with a non-prepared regular Statement.

With a PreparedStatement or CallableStatement, use the execute() method with no String argument.

Fan

Re: Calling a macro via JDBC

Oh my god, that was easy :)). It works like a charm. I copied the code from another class, but I overlooked the String parameter.

Thank you very much for your help.

Oliver

Enthusiast

Re: Calling a macro via JDBC

About SQL PRPEPARE and MACRO:

i had in mind that macros allow one parsing step only (with the first execution) , avoiding parsing at each execution of the request.

I have no idea of what SQL PREPARE does vs Macros and i am not familiar with JDBC:

Is SQL PREPARE equivalent to EXEC MACRO in term of parsing, avoiding parsing in each request ?

Thanks for sharing,

Pierre

Teradata Employee

Re: Calling a macro via JDBC

Preparing a SQL request is a completely different operation from executing an EXEC macro SQL request.

For the Teradata Database, parsing can and often does occur when preparing a SQL request, and when executing an EXEC macro SQL request.

The only time that the Teradata Database avoids parsing is when the parsed SQL request has been previously cached, and all the session and request attributes for the current request match up with the cached request. In this situation, the parsed info is obtained from the cache, and parsing can be skipped.

Enthusiast

Re: Calling a macro via JDBC

So, when a parameterized macro is submitted many times from the same session (in a pool of sessions) and parameters are used to pass a date value always filtering on the same column , can we expect an economy of the parsing step after the first execution is done ?

Same logic in case of PreparedStatement ?

Thanks,

Teradata Employee

Re: Calling a macro via JDBC

Yes, exactly.

Enthusiast

Re: Calling a macro via JDBC

Thanks Tom,

when looking for docs about cached plans i just found this page:

http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/Database_Management/B035_1094_109...

It's a little bit old (V13) and possibly incomplete, where it refers to macros only.

Is there any complete doc somewhere ?

Pierre

Teradata Employee

Re: Calling a macro via JDBC

I can't find that section in newer versions of that documentation.

This is the Connectivity forum, so it's not the best place for questions about the Teradata Database statement cache.

Please start a new forum thread in the Database forum to ask your question, and perhaps Carrie or someone else who is knowledgeable about that topic can answer your question. Thanks.